Add record to database table using insert query

We will use SQL INSERT to add a record to our database student table.
We defined my_cursor as connection object.

my_cursor.execute("INSERT INTO  `my_tutorial`.`student` (`id` ,`name` ,`class` ,`mark` ,`gender`) \
                  VALUES ('36',  'King',  'Five',  '45',  'male')")
print("Rows Added  = ",my_cursor.rowcount)
my_connect.commit() # record added
my_connect.close()

Output

Rows Added  =  1

We used rowcount to get the number of records added to our student table.

Above code will add one record to our student table .

We can capture the error message if any returned by MySQL database by using try except. Here we have kept one wrong column name class1 to generate an error message. Here is the code.

my_cursor = my_connect.cursor() # my_connect is the connection 
try:
    my_cursor.execute("INSERT INTO `my_tutorial`.`student`(`id`,`name`,`class1`,`mark`,`gender`) \
                  VALUES ('36',  'King',  'Five',  '45',  'male')")
    print("Rows Added  = ",my_cursor.rowcount)
    my_connect.commit() # record added
except mysql.connector.Error as my_error:
    print(my_error)
    
my_connect.close()

Output is here

1054 (42S22): Unknown column 'class1' in 'field list'

Using parameterized queries

Place holders are used inside the query and while executing the query the variables are used. This way we can prevent injection attacks using our Query.

my_cursor = my_connect.cursor() # my_connect is the connection 
try:
    query="INSERT INTO  `my_tutorial`.`student` (`id` ,`name` ,`class` ,`mark` ,`gender`) \ 
	       VALUES(%s,%s,%s,%s,%s)"
    my_data=(36,'King','Five',45,'male')
    
    my_cursor.execute(query,my_data)
    
    print("Rows Added  = ",my_cursor.rowcount)
    my_connect.commit() # record added
except mysql.connector.Error as my_error:
    print(my_error)
    
my_connect.close()

Adding multiple records by using executemany()

We have more than one record to insert then we need to run the query multiple time. We can create tuple with our data and then run executemany(). Here is the code.

my_cursor = my_connect.cursor() # my_connect is the connection 
try:
    query="INSERT INTO  `my_tutorial`.`student` (`id` ,`name` ,`class` ,`mark` ,`gender`) \
	       VALUES(%s,%s,%s,%s,%s)"
    my_data=[(36,'King','Five',45,'male'),
            (37,'Queen','Four',44,'Female'),
            (38,'Jack','Three',42,'male')]
    
    my_cursor.executemany(query,my_data)
    
    print("Rows Added  = ",my_cursor.rowcount)
    my_connect.commit() # records added
except mysql.connector.Error as my_error:
    print(my_error)
    
my_connect.close()

Output is here

Rows Added  =  3

Getting record id by lastrowid

We can keep one column as auto incremented, so each time a record is added one unique number is assigned to the record.

more on Auto Incremented Field.

We will add one record and collect the value of auto incremented field by using lastrowid

Note : We have changed the structure of the table and made the first column id as auto incremented field.

my_cursor = my_connect.cursor() # my_connect is the connection 
try:
    query="INSERT INTO  `my_tutorial`.`student` (`name` ,`class` ,`mark` ,`gender`) \
            VALUES(%s,%s,%s,%s)"
    my_data=('King','Five',45,'male')
                
    my_cursor.execute(query,my_data)
    print("Rows Added  = ",my_cursor.rowcount)
    print("last row id  = ",my_cursor.lastrowid)
    my_connect.commit() # record added
except mysql.connector.Error as my_error:
    print(my_error)
    
my_connect.close()

Output is here

Rows Added  =  1
last row id  =  36

Insert record in MySQL database table.

Using parameterized queries

Adding multiple records by using executemany()

Getting record id by lastrowid

Subscribe