Python
In this tutorial we will learn to insert data into MySQL database using Python.
To understand this tutorial you must have some knowledge of SQL, SQL queries and MySQL database.
Note!
For complete MySQL tutorial click here.
To learn more about INSERT query in MySQL check out this MySQL - INSERT INTO Table tutorial.
In this tutorial we will be working with an employee table.
employee
The table already has some rows which are shown below.
mysql< SELECT * FROM employee; +------------+-----------+----------+---------------------+ | employeeid | firstname | lastname | created_at | +------------+-----------+----------+---------------------+ | e01 | John | Doe | 2018-01-01 10:20:30 | | e02 | Jane | Doe | 2018-01-01 12:13:14 | | e03 | Peter | Parker | 2018-01-02 15:16:17 | | e04 | Bruce | Banner | 2018-01-03 10:20:30 | | e05 | Bruce | Wayne | 2018-01-04 12:00:00 | +------------+-----------+----------+---------------------+ 5 rows in set (0.00 sec)
Here is the create table query for the employee table.
CREATE TABLE `employee` ( `employeeid` varchar(32) NOT NULL, `firstname` varchar(32) NOT NULL, `lastname` varchar(32) NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`employeeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
To insert data into a table we have the following steps.
mysql.connector.connect()
To insert data into a table we take help of the execute method which takes two arguments - a SQL query and values in tuple form.
execute
In the following Python program we are inserting a new employee data.
# import module import mysql.connector # import errorcode from mysql.connector import errorcode # get db connection try: cnx = mysql.connector.connect( user='root', password='root1234', host='127.0.0.1', database='mydb' ) except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print('Invalid credential. Unable to access database.') elif err.errno == errorcode.ER_BAD_DB_ERROR: print('Database does not exists') else: print('Failed to connect to database') except: print("Unknown error occurred!") # insert operation try: # cursor cnxCursor = cnx.cursor() # sql query query = "INSERT INTO employee (employeeid, firstname, lastname, created_at) VALUES (%s, %s, %s, %s)" # values values = ("e06", "Tony", "Stark", "2018-01-05, 12:13:14") # insert data into the table cnxCursor.execute(query, values) # commit cnx.commit() # total number of rows inserted print("Total rows inserted: %d" % cnxCursor.rowcount) except mysql.connector.Error as err: print("Error: %s" % err.message) except: print("Unknown error occurred!") finally: # close cursor cnxCursor.close() # close connection cnx.close()
On success we will get the following output.
Total rows inserted: 1
If we now check the employee table we will get the new entry.
mysql< SELECT * FROM employee; +------------+-----------+----------+---------------------+ | employeeid | firstname | lastname | created_at | +------------+-----------+----------+---------------------+ | e01 | John | Doe | 2018-01-01 10:20:30 | | e02 | Jane | Doe | 2018-01-01 12:13:14 | | e03 | Peter | Parker | 2018-01-02 15:16:17 | | e04 | Bruce | Banner | 2018-01-03 10:20:30 | | e05 | Bruce | Wayne | 2018-01-04 12:00:00 | | e06 | Tony | Stark | 2018-01-05 12:13:14 | +------------+-----------+----------+---------------------+ 6 rows in set (0.00 sec)
To insert multiple rows we create a list of values that are in tuple form and take help of the executemany method.
executemany
In the following Python program we are inserting two new employee records into the employee table.
# import module import mysql.connector # import errorcode from mysql.connector import errorcode # get db connection try: cnx = mysql.connector.connect( user='root', password='root1234', host='127.0.0.1', database='mydb' ) except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print('Invalid credential. Unable to access database.') elif err.errno == errorcode.ER_BAD_DB_ERROR: print('Database does not exists') else: print('Failed to connect to database') except: print("Unknown error occurred!") # insert operation try: # cursor cnxCursor = cnx.cursor() # sql query query = "INSERT INTO employee (employeeid, firstname, lastname, created_at) VALUES (%s, %s, %s, %s)" # values values = [ ("e07", "Doctor", "Strange", "2018-01-05, 13:14:15"), ("e08", "Doctor", "Who", "2018-01-05, 13:14:15") ] # insert data into the table cnxCursor.executemany(query, values) # commit cnx.commit() # total number of rows inserted print("Total rows inserted: %d" % cnxCursor.rowcount) except mysql.connector.Error as err: print("Error: %s" % err.message) except: print("Unknown error occurred!") finally: # close cursor cnxCursor.close() # close connection cnx.close()
The above code will print the following output on success.
Total rows inserted: 2
If we now check the employee table we will get to see the new entries.
mysql< SELECT * FROM employee; +------------+-----------+----------+---------------------+ | employeeid | firstname | lastname | created_at | +------------+-----------+----------+---------------------+ | e01 | John | Doe | 2018-01-01 10:20:30 | | e02 | Jane | Doe | 2018-01-01 12:13:14 | | e03 | Peter | Parker | 2018-01-02 15:16:17 | | e04 | Bruce | Banner | 2018-01-03 10:20:30 | | e05 | Bruce | Wayne | 2018-01-04 12:00:00 | | e06 | Tony | Stark | 2018-01-05 12:13:14 | | e07 | Doctor | Strange | 2018-01-05 13:14:15 | | e08 | Doctor | Who | 2018-01-05 13:14:15 | +------------+-----------+----------+---------------------+ 8 rows in set (0.00 sec)
In the next tutorial we will learn how to fetch data from the table.
Have fun coding.