sqlalchemy connection and Add record to database table using insert query

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,text
my_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.
Storing MySQL Login details securely
Add single record to student table.

from sqlalchemy import create_engine,text
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
my_conn = my_conn.connect() # connection object or connection string
id=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,text
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
my_conn = my_conn.connect() # connection object or connection string
id=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, text

my_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 data

print("ID of Row Added  = ", id.lastrowid)

Output

ID of Row Added  =  45

Using multiple records to add


from sqlalchemy import create_engine, text

my_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,text
from sqlalchemy.exc import SQLAlchemyError
my_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)

By using Tkinter we can create one GUI interface to store user entered inputs in MySQL table

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

Python functions for MySQL Collect records Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Insert record in MySQL database table using sqlalchemy

Using parameterized query to add record

Using multiple records to add

Capturing error message

Listing all tables of the database

Listing all row of a table

Subhendu Mohapatra