Insert record in MySQL database table using sqlalchemy

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
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
We are using student table. Use the code to create your sample student table.

Add single record to student table.
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

id=my_conn.execute("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
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

id=my_conn.execute("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
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

query="INSERT INTO  `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`) VALUES(%s,%s,%s,%s)"
my_data=('King','Five',45,'male')
    
id=my_conn.execute(query,my_data)
print("ID of Row Added  = ",id.lastrowid)
Output
ID of Row Added  =  45

Using multiple records to add

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
try:
    query="INSERT INTO  `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`)  VALUES(%s,%s,%s,%s)"
    my_data=[('King','Five',45,'male'),
            ('Queen','Four',44,'Female'),
            ('Jack','Three',42,'male')]
    
    id=my_conn.execute(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
from sqlalchemy.exc import SQLAlchemyError
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
try:
    query="INSERT INTO  `student9` (`name` ,`class` ,`mark` ,`sex`)  VALUES(%s,%s,%s,%s)"
    my_data=[('King','Five',45,'male'),
            ('Queen','Four',44,'Female'),
            ('Jack','Three',42,'male')]
    
    id=my_conn.execute(query,my_data)
    
    print("Rows Added  = ",id.rowcount)
    
except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        print(error)

Listing all tables of the database

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

Listing all row of a table

r_set=my_conn.execute("SELECT * FROM student");
for row in r_set:
    print(row)
Tkinter window to add user entered data to MySQL table More on Error handling


plus2net.com



Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer