SQLite INSERTING records using SQLAlachemy

SQLite SQLite insert

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

Adding single record / row

To our student table we will use SQL INSERT to add one row or record.
  • Video Tutorial on INSERT query



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.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


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-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer