Learn Python – Insert Operation- Basic and advance

Adding a record to the table

The INSERT INTO announcement is used to add a report to the table. In python, we can mention the format specifier (%s) in region of values.

We provide the real values in the structure of tuple in the execute() method of the cursor.

Consider the following example.

Example

import mysql.connector  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
#creating the cursor object  
cur = myconn.cursor()  
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"  
  
#The row values are provided in the form of tuple   
val = ("John", 110, 25000.00, 201, "Newyork")  
  
try:  
    #inserting the values into the table  
    cur.execute(sql,val)  
  
    #commit the transaction   
    myconn.commit()  
      
except:  
    myconn.rollback()  
  
print(cur.rowcount,"record inserted!")  
myconn.close()  

Output:

1 record inserted!

Insert multiple rows

We can also insert more than one rows at as soon as the usage of the python script. The a couple of rows are cited as the listing of a number of tuples.

Each aspect of the list is handled as one specific row, whereas each factor of the tuple is dealt with as one specific column fee (attribute).

Consider the following example.

Example

import mysql.connector  
      
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"  
val = [("John", 102, 25000.00, 201, "Newyork"),("David",103,25000.00,202,"Port of spain"),("Nick",104,90000.00,201,"Newyork")]  
      
try:  
    #inserting the values into the table  
    cur.executemany(sql,val)  
  
    #commit the transaction   
    myconn.commit()  
    print(cur.rowcount,"records inserted!")  
      
except:  
    myconn.rollback()  
  
myconn.close()  

Output:

3 records inserted! 

Row ID

In SQL, a particular row is represented via an insertion identification which is recognized as row id. We can get the remaining inserted row id by way of the usage of the attribute lastrowid of the cursor object.

Consider the following example.

Example

import mysql.connector  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
#creating the cursor object  
cur = myconn.cursor()  
      
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"  
      
val = ("Mike",105,28000,202,"Guyana")  
      
try:  
    #inserting the values into the table  
    cur.execute(sql,val)  
  
    #commit the transaction   
    myconn.commit()  
      
    #getting rowid  
    print(cur.rowcount,"record inserted! id:",cur.lastrowid)  
  
except:  
    myconn.rollback()  
  
myconn.close()  

Output:

1 record inserted! Id: 0