Inserting Rows Into MySQL Database Table Using Python And PyMySQL

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.

# 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:

('A',)

('B',)

('Employee',)

('Student',)

('exper',)

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

 


Copyright 2020 © pythontic.com