Insert record in MySQL database table using sqlalchemy

";require "templates/body_start.php";?>We will use SQL INSERT to add a record to our database student table.
We defined my_conn as connection object.

In all the codes below replace userid, password and database_name with your MySQL login and database details.

from sqlalchemy import create_engine,textmy_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")my_conn = my_conn.connect() # connection object or connection string

We are using student table. Use the code to create your sample student table.
student table with SQL Dump Storing MySQL Login details securely →
Add single record to student table.

from sqlalchemy import create_engine,textmy_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")my_conn = my_conn.connect() # connection object or connection stringid=my_conn.execute(text("INSERT INTO  `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`) \                  VALUES ('King1',  'Five',  '45',  'male')"))print("Row Added  = ",id.rowcount)

Output

Row Added  =  1

In our student table we have used one auto incremented id column. We will collect this id after adding record to database.

from sqlalchemy import create_engine,textmy_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")my_conn = my_conn.connect() # connection object or connection stringid=my_conn.execute(text("INSERT INTO  `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`) \                  VALUES ('King1',  'Five',  '45',  'male')"))print("ID of Row Added  = ",id.lastrowid)

Output

ID of Row Added  =  46

Using parameterized query to add record

Always use parameterized query when the data is coming from unknown sources. This is required to prevent injection attack.

from sqlalchemy import create_engine, textmy_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")my_conn = my_conn.connect()query = "INSERT INTO  `student` (`name` ,`class` ,`mark` ,`gender`) \        VALUES (:name, :class, :mark, :gender)"my_data = {    "name": 'King Li',    "class": 'Four',    "mark": 58,    "gender": 'Female'}id = my_conn.execute(text(query), my_data)  # insert dataprint("ID of Row Added  = ", id.lastrowid)

Output

ID of Row Added  =  45

Using multiple records to add

from sqlalchemy import create_engine, textmy_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")my_conn = my_conn.connect()try:    query = "INSERT INTO `student` (`name`, `class`, `mark`, `gender`) \		VALUES (:name, :class, :mark, :gender)"    my_data = [        {"name": "King", "class": "Five", "mark": 45, "gender": "male"},        {"name": "Queen", "class": "Four", "mark": 44, "gender": "Female"},        {"name": "Jack", "class": "Three", "mark": 42, "gender": "male"}    ]    id = my_conn.execute(text(query), my_data)    print("Rows Added = ", id.rowcount)except:    print("Database error")

Output

Rows Added  =  3

Capturing error message

If we want specific error message then can use like this.

from sqlalchemy import create_engine,textfrom sqlalchemy.exc import SQLAlchemyErrormy_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")my_conn=my_conn.connect()try:    query="INSERT INTO  `student` (`name` ,`class` ,`mark` ,`gender`)  VALUES (:name, :class, :mark, :gender)"    my_data = [        {"name": "King", "class": "Five", "mark": 45, "gender": "male"},        {"name": "Queen", "class": "Four", "mark": 44, "gender": "Female"},        {"name": "Jack", "class": "Three", "mark": 42, "gender": "male"}    ]    id=my_conn.execute(text(query),my_data)    print("Rows Added  = ",id.rowcount)except SQLAlchemyError as e:        error = str(e.__dict__.get('orig', e)) # get error message         print(error)

Listing all tables of the database

r_set=my_conn.execute(text("SHOW TABLES"));for row in r_set:    print(row)

Listing all row of a table

r_set=my_conn.execute(text("SELECT * FROM student"));for row in r_set:    print(row)

Tkinter window to add user entered data to MySQL table « More on Error handling