SQLite INSERTING records using SQLAlachemy

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


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
5
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
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





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