Insert record in MySQL using python

Insert record in MySQL using python is our next article in this python MySQL series, IN the previous article, we learn how to install python package MySQL client to make a connection between python and MySQL.

To insert a record in MySQL table has the following syntax

Insert into tablename values (value1,value2,value3……………………….value N)

The problem in this approach, the placement of values must be the same as the order of columns in the table and cannot skip any value. To overcome this situation we have another syntax of the insert statement

Insert  into TableName(column name, column name…..) values ( value1, value2, value3)

For example suppose, we have a table of users that have the following structure

Field name                data type          size          constaints
ID                        int (10)  primary key   auto_increment
Username                  char(30)
Userpassword              char (30)

To add a new record in the above table, the following SQL command is required.

Insert into USER(username,userpassword) values ( ‘rakesh’,’testing123’);

Programming Part

import MySQLdb
data = MySQLdb.connect("localhost", "root", "", "binarynote")
cursor = data.cursor()
sql = "insert into user(uname,upass) values('archana','testing');"
cursor.execute(sql)
data.close()

The above Python program import MySQLdb and store its data connection in a variable data , using the same variable generated cursor in order to execute our SQL command.

The major drawback of the above program is – It inserts a fixed value inside the table USER, The output of the sample program is as follows

Take input in python variables and replace the same in your SQL query.

Name  = input(“Enter your username :”)
Password  = input (“Enter your  password :”)
Sql  = “insert into user(uname, upass) value (‘”. Uname. “’,’”. Upass .”’);”;
import MySQLdb
data = MySQLdb.connect("localhost", "root", "", "binarynote")
cursor = data.cursor()
uname = input("Enter your user ID :")
upass = input("Enter your Password :")
sql = "insert into user(uname,upass) values('{}','{}');".format(uname, upass)
cursor.execute(sql)
data.close()
print("Record Added................")

Now the program is able to insert user-defined values inside the user table.