Posts: 30
Threads: 8
Joined: Mar 2021
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)
Posts: 582
Threads: 1
Joined: Aug 2019
(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)")
Posts: 30
Threads: 8
Joined: Mar 2021
(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()
Posts: 582
Threads: 1
Joined: Aug 2019
Apr-12-2021, 10:25 AM
(This post was last modified: Apr-12-2021, 10:25 AM by ibreeden.)
(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).
Posts: 30
Threads: 8
Joined: Mar 2021
Apr-12-2021, 02:17 PM
(This post was last modified: Apr-12-2021, 02:17 PM by vj78.)
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 -
Posts: 582
Threads: 1
Joined: Aug 2019
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()
Posts: 30
Threads: 8
Joined: Mar 2021
Apr-13-2021, 12:16 AM
(This post was last modified: Apr-13-2021, 12:16 AM by vj78.)
(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()
Posts: 7,320
Threads: 123
Joined: Sep 2016
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.
Posts: 30
Threads: 8
Joined: Mar 2021
(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.
|