Inserting Rows into MySQL database table using Python and PyMySQL

Rows can be inserted into MySQL table using PyMySQL by obtaining a connection object and executing the insert statement with a cursor object.

MySQL Updated 7 Mar 2017 3 min read

Overview

Connecting to a mainstream database server and performing SQL operations is very common to Python applications. There are several database drivers or DB APIs developed for connecting to MySQL Server and other database servers from a Python Application. The focus here is to connect to a MySQL Server and insert rows into a database table using PyMySQL.

PyMySQL is developed using all Python. It adheres to the Python Database API specification. PyMySQL comes with an MIT license.

Inserting rows into a MySQL database table using Python

The Python Database API specification makes it very simple to connect to any database server and perform database operations using SQL.

The steps to insert a number of rows onto a MySQL database table are:

  • Create a connection object using PyMySQL module.
  • Obtaining a connection object involves calling the connect factory method with several database parameters like
    • IP address or Host Name on which the MySQL database server is running
    • The database user name
    • Password for the user
    • Name of the database to connect to
    • Encoding used
    • Cursor type and any other required parameters
  • Once a connection object is obtained, a cursor object needs to be created to execute any SQL statement over the database connection.
  • Upon calling the execute() method by providing the SQL statement as a string - any SQL operation is performed on the database server.
  • The same way, an SQL INSERT statement can be executed and new rows can be inserted on to the database.
  • The database table can be queried and the rows from the tables can be printed on to the console.

Example

The Python example code connects to a MySQL database and creates a new table and inserts a row into the table. It prints the id/primary-key of the last inserted row. The Python example queries the MySQL table and prints the query results.

python
# Example Python program to insert rows into a MySQL database table

# import the mysql client for python

            import pymysql

# Create a connection object

            dbServerName    = "127.0.0.1"

            dbUser          = "root"

            dbPassword      = ""

            dbName          = "test"

            charSet         = "utf8mb4"

connectionObject   = pymysql.connect(host=dbServerName, user=dbUser, password=dbPassword,

                                                 db=dbName, charset=charSet)

try:

                # Create a cursor object

                cursorObject            = connectionObject.cursor()

    # SQL string to create a MySQL table

                sqlCreateTableCommand   = "CREATE TABLE Employee(id int(11) AUTO_INCREMENT PRIMARY KEY, LastName varchar(32), FirstName varchar(32), DepartmentCode int)"

    # Execute the sqlQuery

                cursorObject.execute(sqlCreateTableCommand)

    # List the tables using SQL command

                sqlShowTablesCommand    = "show tables"

    # Execute the SQL command

                cursorObject.execute(sqlShowTablesCommand)

    #Fetch all the rows - from the command output

                rows                = cursorObject.fetchall()

                for row in rows:

                    print(row)

    # Insert rows into the MySQL Table

                insertStatement = "INSERT INTO Employee (id, LastName, FirstName, DepartmentCode) VALUES (1,\"Albert\",\"Einstein\",10)"

                cursorObject.execute(insertStatement)

                # Get the primary key value of the last inserted row

                print("Primary key id of the last inserted row:")

                print(cursorObject.lastrowid)

    # SQL Query to retrive the rows

                sqlQuery    = "select * from Employee"

    #Fetch all the rows - for the SQL Query

                cursorObject.execute(sqlQuery)

                rows = cursorObject.fetchall()

    for row in rows:

                    print(row)

except Exception as e:

                print("Exeception occured:{}".format(e))

finally:

                connectionObject.close()

Output

Output
('A',)

('B',)

('Employee',)

('Student',)

('exper',)

(1, 'Albert', 'Einstein', 10)