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.
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
# Create a connection object
connectionObject = pymysql.connect(host=dbServerName, user=dbUser, password=dbPassword,
# SQL string to create a MySQL table
# Execute the sqlQuery
# List the tables using SQL command
# Execute the SQL command
#Fetch all the rows - from the command output
# Insert rows into the MySQL Table
# SQL Query to retrive the rows
#Fetch all the rows - for the SQL Query
for row in rows:
except Exception as e:
(1, 'Albert', 'Einstein', 10)