Python sqlite inserting single and multiple records to table

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

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

Adding single record / row

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

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

my_query="INSERT INTO student values(null,'New Name', 'Four', 65, 'female')"
my_conn.execute(my_query)

We can also read the ID ( alow known as rowid ) of most recently added row.

x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0])

Output

r_set=my_conn.execute(q,my_data)
print(r_set.lastrowid)

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.

my_data=(None,'Secon Name','Five',75,'male')
my_query="INSERT INTO student values(?,?,?,?,?)"
my_conn.execute(my_query,my_data)

We can check the ROWID of this added record. ( code with output as 3 )

x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0]) # 3

Adding multiple records / rows

More records can be added by using executemany() function.

my_data=[(9, 'Tes Qry', 'Six', 78, 'male'),
(10, 'Big John', 'Four', 55, 'female'),
(11, 'Ronald', 'Six', 89, 'female'),
(12, 'Recky', 'Six', 94, 'female'),
(13, 'Kty', 'Seven', 88, 'female')]

my_query="INSERT INTO student values(?,?,?,?,?)"
my_conn.executemany(my_query,my_data)

We can check the row id of the last inserted records by using last_insert_rowid().

x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0])

Output is

Number of rows added

Number of records added by using rowcount

my_data=[(18, 'Big John', 'Four', 55, 'female'),
(19, 'Ronald', 'Six', 89, 'female'),
(20, 'ONe more', 'Six', 89, 'female')]
my_query="INSERT INTO student values(?,?,?,?,?)"
curs=my_conn.executemany(my_query,my_data)
print(curs.rowcount)

Output

Printing Error Message

By using try except we can print the error message if any and that will help in debugging the script.

try:
    my_data=[(24, 'Big John', 'Four', 55, 'female'),
        (25, 'Ronald', 'Six', 89, 'female'),
        (26, 'ONe more', 'Six', 89, 'female')]
    my_query="INSERT INTO student values(?,?,?,?,?)"
    curs=my_conn.executemany(my_query,my_data)
    print(curs.rowcount())
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output ( as we already added the records with the above IDs )

error:  UNIQUE constraint failed: student.id

Understanding SQLite's `lastrowid`

AUTOINCREMENT and INTEGER Primary Key column of SQLite database table

When working with SQLite databases, it's often necessary to get the unique ID of the last inserted record. SQLite's lastrowid feature makes this task straightforward. Let's see how this works with an example of a student database.

Creating the Student Table

First, let's create a student table. We'll define columns for id, name, class, mark, and gender. The id column is set as an integer primary key.


my_conn.execute('''CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text,
                      mark integer,
                      gender text  )''')

Inserting Data and Retrieving the Last Row ID

After inserting a new record, we can retrieve the unique ID assigned to it using lastrowid.


my_query = "INSERT INTO student(name,class,mark,gender) \
            values('New Name', 'Four', 65, 'Female')"
r_set = my_conn.execute(my_query)
print(r_set.lastrowid)

This snippet will insert a new student record and print the ID assigned to it by SQLite.

Understanding AUTOINCREMENT

SQLite's AUTOINCREMENT keyword ensures each new record receives a unique ID that has never been used, even if previous records are deleted.

Difference with AUTOINCREMENT

Without AUTOINCREMENT, SQLite assigns the next highest available ID. However, with AUTOINCREMENT, SQLite ensures the ID is always one higher than the highest ID ever used, guaranteeing uniqueness.

Example with AUTOINCREMENT

Let's consider a case where the highest ROWID is 5. If this record is deleted and a new one is added, the new ROWID without AUTOINCREMENT would be 5. With AUTOINCREMENT, it would be 6.


my_conn.execute('''CREATE TABLE IF NOT EXISTS student(id integer primary key AUTOINCREMENT, 
                      name text, 
                      class text,
                      mark integer,
                      gender text  )''')

This approach ensures data integrity and is crucial for maintaining unique identifiers throughout the database's lifespan.

Conclusion

SQLite's lastrowid attribute is incredibly useful for getting the ID of the last inserted row, particularly when you need to reference the new record immediately. Understanding the impact of using AUTOINCREMENT helps in designing databases that ensure data consistency and integrity.

Copy this code from Github and run in your colab
https://github.com/plus2net/Python-basics/blob/main/SQLite_AUTOINCREMENT.ipynb