How to create table and insert data using MySQL and Flask

Question

I am trying to insert data into a MySQL table. I'm able to connect to the database but when trying to insert data it is giving internal server error.

from flask import Flask
from flaskext.mysql import MySQL

mysql = MySQL()
app = Flask(__name__)
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'test'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

@app.route("/")
def create_table():
    cursor = mysql.get_db().cursor()
    query = "CREATE TABLE potlala (id INT NOT NULL PRIMARY KEY, name  VARCHAR(40), email VARCHAR(40))"
    query = "INSERT INTO pot13 (id, name, email) VALUES ('2222', 'Maria',  '[email protected]')"
    cursor.execute(query)
    return "123"

if __name__ == "__main__":
    app.run()

Run the script from the command line and catch the error, print it and include it here. Also, have you tried using SQLAlchemy? — John
– John, Commented Jun 4, 2015 at 22:06
@john yeah I ran the script from the command line,no error pop up but when I localhost it gives me internal server error and One thing more when I'm changing "2222" to '1234' in query section ,it is giving me HTTP 200:OK but not inserting anything into database. — immrsteel
– immrsteel, Commented Jun 4, 2015 at 22:46
Encapsulate the block in try catch and catch the error. There will definitely be an output. — John
– John, Commented Jun 4, 2015 at 22:47
I'm completely new to this things,Will SQLAlchemy do the work of CRUD with mysql using python and flask? — immrsteel
– immrsteel, Commented Jun 4, 2015 at 22:48
It alleviates the need to write sql queries. Queries become: dbdefs.potlala.query.filter_by(patella_id = your_variable) — John
– John, Commented Jun 4, 2015 at 22:49

davidism · Accepted Answer · 2015-06-04 23:52:25Z

5

You set query to a create table statement, but never execute it before setting it to an insert statement and trying to execute that. The table doesn't exist, so the insert fails. Execute the first query first.

query = "CREATE TABLE ..."
cursor.execute(query)
query = "INSERT INTO ..."
cursor.execute(query)

answered Jun 4, 2015 at 23:52

davidism

128k31 gold badges417 silver badges348 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yasel · Accepted Answer · 2015-07-27 19:47:09Z

5

There are two things that you should do:

Execute your queries separately.
Commit (or rollback) your changes using the sql connection.

connection = mysql.get_db()
cursor = connection.cursor()
query = "CREATE TABLE potlala (id INT NOT NULL PRIMARY KEY, name  VARCHAR(40), email VARCHAR(40))"
cursor.execute(query)
query = "INSERT INTO pot13 (id, name, email) VALUES ('2222', 'Maria',  '[email protected]')"
cursor.execute(query)
connection.commit()

edited Jul 27, 2015 at 19:47

Yasel

3,1684 gold badges42 silver badges49 bronze badges

answered Jul 27, 2015 at 18:52

CompCy

511 bronze badge

1 Comment

Young Emil Over a year ago

be aware of sql injection here ('2222', 'Maria', '[email protected]')

Collectives™ on Stack Overflow

How to create table and insert data using MySQL and Flask

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related