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 a template 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), you’ll see how to insert two new records into the names_table 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 would 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 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’ template that was introduced 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 get reflected at the bottom of the names_table:

How to Insert Values into MySQL Table using Python