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.