Python sqlite inserting single and multiple records to table using SQLAlchemy

We are using our student table. Check how to create database and student table here.

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
my_path="D:\\testing\\sqlite\\my_db.db" #Change the path 
my_conn = create_engine("sqlite:///" + my_path)

We will use our connection string my_conn in our scripts below.

Adding single record / row

To our student table we will use SQL INSERT to add one row or record.

INSERT Query to add single or multiple rows to table & get number of rows added with lastrowid

We have 5 columns in our table but we are using NULL as data for first column ( id ). As id column is an auto incremented id column , SQLite will add next highest number to this column while adding the row. If it is the first record then the ID value will be 1, for the next record added the ID value will be 2 and so on..

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
q="INSERT INTO student values(null,'New Name', 'Four', 65, 'female')"
try:
  r_set=my_conn.execute(q)
except SQLAlchemyError as e:
  error=str(e.__dict__['orig'])
  print(error)
else:
  print("No of records added  : ",r_set.rowcount)
  print("Id of last record added : ",r_set.lastrowid)

Output

No of records added  :  1
Id of last record added :  40

We used lastrowid to get id of last inserted row to the table.

Using parameter

Always use parameterized query when the data is coming from unknown sources. Use ? as placeholder and a provide a tuple to pass the value to query or execute() method. This is required to prevent injection attack.

We have used placeholders ( ? ) in our query and note that my_data is a tuple used for passing value to execute() method for our query.

from sqlalchemy.exc import SQLAlchemyError
my_data=(None,'Second Name','Five',75,'male')
q="INSERT INTO student values(?,?,?,?,?)"
try:
  r_set=my_conn.execute(q,my_data)
except SQLAlchemyError as e:
  error=str(e.__dict__['orig'])
  print(error)
else:
  print("No of records added  : ",r_set.rowcount)
  print("id of last record added : ",r_set.lastrowid)

Adding multiple records / rows

More records can be added by executing singl query .

from sqlalchemy.exc import SQLAlchemyError
my_data=[(None, 'Tes Qry', 'Six', 78, 'male'),
(None, 'Big John', 'Four', 55, 'female'),
(None, 'Ronald', 'Six', 89, 'female'),
(None, 'Recky', 'Six', 94, 'female'),
(None, 'Kty', 'Seven', 88, 'female')]

q="INSERT INTO student values(?,?,?,?,?)"
try:
  r_set=my_conn.execute(q,my_data)
except SQLAlchemyError as e:
  error=str(e.__dict__['orig'])
  print(error)
else:
  print("No of records added  : ",r_set.rowcount)

Number of rows added

Number of records added by using rowcount

print("No of records added  : ",r_set.rowcount)

Output

We can’t get last inserted id or lastrowid when multiple records are added.

Checking the added records

We can always display last 10 records or recently added records by using ORDER BY with LIMIT query.

q="SELECT * FROM STUDENT order by id desc limit 0,10"
r_set=my_conn.execute(q)
for row in r_set:
  print(row)

Add record to SQLite table using Tkinter GUI Sqlite Connection delete update select Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite INSERTING records using SQLAlachemy

Adding single record / row

Using parameter

Adding multiple records / rows

Number of rows added

Checking the added records

Subscribe