Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
error when dealing with uuid
#11
Ok, as per suggestion, I used python to include the UUID() in the INSERT statement and it works.

I have another error when I use %s. The reason I said %s is when I hard code the values it works.

Error Message: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s, %s, uuid())'

mydb = mysqldb.connect(host="xxxxx", user="xxxxx",password="xxxxxxx", db="xx", use_unicode=True,charset="utf8")
            mycursor = mydb.cursor()
            myparams = (username, password,  )
            print(myparams)

            mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s, %s,  uuid())")
            myresult = mycursor.execute(mysql, myparams)
            mydb.commit()
            return (myresult)
Reply
#12
(Apr-12-2021, 01:10 AM)vj78 Wrote: mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s, %s,  uuid())")
I think you should do:
myparams = (username, password, uuid() )
...
mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s, %s,  %s)")
vj78 likes this post
Reply
#13
(Apr-12-2021, 07:33 AM)ibreeden Wrote:
(Apr-12-2021, 01:10 AM)vj78 Wrote: mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s, %s,  uuid())")
I think you should do:
myparams = (username, password, uuid() )
...
mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s, %s,  %s)")

I have updated the code: (It is still giving me the same error message)

myparams = (username, password, uuid.uuid1(), )
            print(myparams)

            mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s , %s, %s)")
            myresult = mycursor.execute(mysql, myparams)
            mydb.commit()
Reply
#14
(Apr-12-2021, 01:10 AM)vj78 Wrote: Error Message: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s, %s, uuid())'
It cannot give the same message if you changed the code to:
 mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s , %s, %s)")
And please show the complete message (in [ERROR] tags).
Reply
#15
This is the complete error message:
[2021-04-12 08:51:39,948] ERROR in app: Exception on /adduser1 [POST]
Traceback (most recent call last):
  File "C:\source\repos\pythonsql\pythonsql\env2\lib\site-packages\flask\app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\source\repos\pythonsql\pythonsql\env2\lib\site-packages\flask\app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\source\repos\pythonsql\pythonsql\env2\lib\site-packages\flask\app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\source\repos\pythonsql\pythonsql\env2\lib\site-packages\flask\_compat.py", line 39, in reraise
    raise value
  File "C:\source\repos\pythonsql\pythonsql\env2\lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\source\repos\pythonsql\pythonsql\env2\lib\site-packages\flask\app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "C:\xampp3\htdocs\app.py", line 151, in adduser1
    myresult = user.user().adduser(username, password, admin)
  File "C:\xampp3\htdocs\user.py", line 25, in adduser
    mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s , %s, %s)")
  File "C:\source\repos\pythonsql\pythonsql\env2\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\source\repos\pythonsql\pythonsql\env2\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\source\repos\pythonsql\pythonsql\env2\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s , %s, %s)' at line 1
127.0.0.1 - - [12/Apr/2021 08:51:39] "←[35m←[1mPOST /adduser1 HTTP/1.1←[0m" 500 -
Reply
#16
Not exactly what I hoped for. Can you not run it outside the web page? And in your code you print the tuple "myparams". What does it print? Are the parameters correct?
By the way, is this still your code?
myparams = (username, password, uuid.uuid1(), )
            print(myparams)
 
            mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s , %s, %s)")
            myresult = mycursor.execute(mysql, myparams)
            mydb.commit()
You do twice "mycursor.execute()". That is not correct. It must be:
myparams = (username, password, uuid.uuid1(), )
print(myparams)
 
mysql = "INSERT INTO user (username, userpassword, userguid ) VALUES (%s , %s, %s)"
myresult = mycursor.execute(mysql, myparams)
mydb.commit()
mycursor.close()
mydb.close()
vj78 likes this post
Reply
#17
(Apr-12-2021, 05:59 PM)ibreeden Wrote: Not exactly what I hoped for. Can you not run it outside the web page? And in your code you print the tuple "myparams". What does it print? Are the parameters correct?
By the way, is this still your code?
myparams = (username, password, uuid.uuid1(), )
            print(myparams)
 
            mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s , %s, %s)")
            myresult = mycursor.execute(mysql, myparams)
            mydb.commit()
You do twice "mycursor.execute()". That is not correct. It must be:
myparams = (username, password, uuid.uuid1(), )
print(myparams)
 
mysql = "INSERT INTO user (username, userpassword, userguid ) VALUES (%s , %s, %s)"
myresult = mycursor.execute(mysql, myparams)
mydb.commit()
mycursor.close()
mydb.close()

Thank you! I updated the code and removed the double execute statements and updated the uuid1() to a string and its working now. Also, added the close statements. Below are the changes I made after your suggestions:

myparams = (username, password, str(uuid.uuid1()), )
            print(myparams)

            mysql = "INSERT INTO user (username, userpassword, userguid ) VALUES (%s , %s, %s)"
            myresult = mycursor.execute(mysql, myparams)
            mydb.commit()
            mycursor.close()
            mydb.close()
Reply
#18
There is no reason to use phpMyAdmin/XAMPP when using Flask,i would never use it this Python usage not PHP.
The standard way to deal to with database in Flask is to use Flask-SQLAlchemy.
Then use DATABASE_URI to connect to database of choice.
So the setup could look like this.
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@server/db'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __repr__(self):
        return f'User {self.username} Email {self.email}'
Can look this post there you see a full setuo using sqlite3 so only one line URI change to eg switch to use MySQL.
vj78 likes this post
Reply
#19
(Apr-13-2021, 12:10 PM)snippsat Wrote: There is no reason to use phpMyAdmin/XAMPP when using Flask,i would never use it this Python usage not PHP.
The standard way to deal to with database in Flask is to use Flask-SQLAlchemy.
Then use DATABASE_URI to connect to database of choice.
So the setup could look like this.
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@server/db'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __repr__(self):
        return f'User {self.username} Email {self.email}'
Can look this post there you see a full setuo using sqlite3 so only one line URI change to eg switch to use MySQL.

I use the XAMPP to manage APACHE install and MYSQL. I will try to migrate to Flask-SQLAlchemy.
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020