Python Basics

Python PyMySQL Insert Record

In previous tutorial, we have learned how to create a table using pymysql and python. In this article we are going to learn how to insert record into table. For reference look at the below table.

emp_id emp_name emp_age
1 Raj 22
2 Rahul 24
3 Vivek 26
4 Ajay 28
5 Suraj 29

In the table, I am going to insert above five records.

Assuming you have basic understanding of mysql, remember following format specifiers:

  • For Varchar : %s
  • For Int : %d
  • For Float: %f

And also, you must have basic understanding of tuple in python before proceeding futher.

Inserting record into table involves following three steps.

Step 1: Create Connection

Step 2: Insert Record

Step 3: Close connection

From the above table reference, lets insert the first record.

Id is 1, name is Raj and age is 22.

Therefore, the sql command to insert record would be

sql=”insert into employee_info(id,emp_name,emp_age)values(1,’raj’,22)”

The above sql command would run fine with out any problem. In 99% cases, we are going to receive the values from different sources and the values must be in some variable. So lets see what would be the sql command if the values are in variables.

id=1

name=’raj’

age=22

sql=”insert into employee_info(id,emp_name,emp_age)values(%d,’%s’,%d)”

In the above sql command, in the place of values, I had mentioned the format specifiers. pymysql and python at the backend are now aware, the first value would be an integer, the second value would be a string or varchar, and the third value would be an integer.

Note: %s is wrapped in between single quote. In mysql whenever we insert varchar, we wrap it with single quote.

So far, I had created the sql command with format specifiers but what about the values?

We pass the values in the form of tuple.

dataval=(id,name,raj)

Thats it. Our sql command is completely ready. Now lets see the whole code.

Insert Record into Table

conn=pymysql.connect(‘localhost’,’root’,”,’employee_db’)

cursor=conn.cursor

sql=”insert into employee_info(id,emp_name,emp_age)values(%d,’%s’,%d)”

dataval=(id,name,raj)

try:

cursor.execute(sql,dataval)

conn.commit()

print(“record inserted succesfully”)

except pymysql.Error as pe:

print(pe) #if any error occurs while inserting record, it will print here.

conn.rollback();

finally:

cursor.close()

conn.close()

Few more new things you must have noticed in the above code. Lets analyse one by one.

Try, except – While performing a task such as inserting record, the code may encounter error. So it is wise to use try except. Try code block executes only if there are no errors. Other wise the except block is going to print the error.

pymysql.Error

It contains the raised error list. So by printing this error, we can know if any error is present in the code. If we encounter an error, then we must immediate roll back the connection variable by calling conn.rollback()

finally:

If no error is present, then inside finally block, we must close the cursor and conn variable.

In similar fashion, I can insert rest of the record into the table.

conn.commit

conn.commit() function finalises the insert feature. If this code misses, then the insert will not make effect. So whenever we change the data of table through sql command, we must have to run this function, so that the changes in the table becomes permanent.