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