How to Insert Values into MySQL Table using Python

Need to insert values into MySQL table using Python?

If that’s the case, I’ll show you to steps to accomplish this task using a simple example.

But before we begin, here is the general syntax that you may use to insert values into MySQL table:

 

INSERT INTO Database_Name.Table_Name (Column1_Name, Column2_Name,...)

VALUES

('Column1_Value1', 'Column2_Value2',...),

('Column1_Value3', 'Column2_Value4',...)

 

Let’s now review an example with the steps to insert values into MySQL table using Python.

Steps to Insert Values into MySQL Table using Python

(1) Suppose that you created a simple database in MySQL, where:

  • The database name is: test_database
  • This database has only one table called: names_table
  • The names_table contains the following data and fields:

 

First_NameLast_NameAge
JonSnow22
MariaSmith34
EmmaJones51
BillYu63
JackGreen27

 

In step 4 (to be reviewed below), we will see how to insert two new records into the names_table by using Python.

 

(2) Next, you may want to run the following SELECT query in MySQL to check the current records in the names_table:

 

SELECT * FROM test_database.names_table

 

And this is what you should get in MySQL for our example:

 

How to Insert Values into MySQL Table using Python

 

(3) Now you’ll need to establish a connection between Python and MySQL. To create such a connection, you’ll need to capture:

  • The host – which is usually ‘localhost’ for local instances
  • The user – In my case, the user is ‘root’
  • The password – In my instance, I created the password of ‘1q2w3e4r’
  • The database – for our example the database is ‘test_database’

In addition, you’ll need to install, and then import, the MySQLdb module.

You may wish to check the following tutorial that explains how to establish a connection between Python and MySQL from scratch!

Here is the Python code that can be used to connect Python to MySQL (note that you’ll need to make the adjustment to your connection parameters):

 

import MySQLdb
 
db = MySQLdb.connect(host='localhost',    
                     user='root',         
                     passwd='1q2w3e4r',  
                     db='test_database')        
 
cur = db.cursor()
 
cur.execute('SELECT * FROM test_database.names_table')
 
for row in cur.fetchall():
    print row
 
db.close()

 

Once you run the above code in Python, you’ll get the same values as we saw in step-2:

 

Insert Values into MySQL Table using Python

 

(4) Let’s now say that you want to insert the following 2 new records into the names_table:

 

First_NameLast_NameAge
MikeJordan55
MiaMogran66

 

Using the generic ‘Insert’ syntax that we saw at the beginning of this post, you can then apply the following code in Python to insert the 2 records into the MySQL table.

Do not forget to add db.commit() at the end of the Python code to ensure that the Insert command would be applied.

 

import MySQLdb
 
db = MySQLdb.connect(host='localhost',    
                     user='root',         
                     passwd='1q2w3e4r',  
                     db='test_database')        
 
cur = db.cursor()
 
cur.execute('''

            INSERT INTO test_database.names_table (First_Name, Last_Name, Age)
            VALUES
            ('Mike', 'Jordan',55),
            ('Mia', 'Mogran',66)

            ''')

db.commit()

 

(5) Finally, you may want to check that the 2 new records got inserted into MySQL by running:

 

SELECT * FROM test_database.names_table

 

The new records should now be reflected at the bottom of the names_table:

 

How to Insert Values into MySQL Table using Python