Create a table in MySQL Server using Python with PyMySQL

Using PyMySQL a connection object can be obtained. From this connection a cursor object is created and a create table SQL statement is executed.

MySQL Updated 7 Mar 2017 2 min read

Overview

  • Python has several client APIs available to connect to the MySQL database server and perform the database operations.

  • The focus here is how to use one such API - PyMySQL, to create a table in MySQL Server.

  • PyMySQL is fully written in Python and can be used from Python implementations - CPython, PyPy and IronPython.

  • PyMySQL supports the high level APIs as defined in the Python Database API Specification.

  • PyMySQL is released under the MIT License.

Create a MySQL Table using PyMySQL

  • Import the PyMySQL module into the python program

  • Create a connection object using PyMySQL module by specifying the

    • Database server
    • Database user
    • Password
    • Database Name
    • Encoding
  • Create a cursor object and pass the valid SQL - create table statement as the parameter to the execute method of the cursor object

python
CREATE TABLE Employee(id int, LastName varchar(32), FirstName varchar(32), DepartmentCode int)
  • A database table is created in the MySQL server

  • To verify that the table is listed under the database, issue one more SQL command

python
Show tables

using the cursor object.

  • The results of the Show tables SQL command can be printed and the presence of the new table named Employee in the listing can be seen.

Example

python
# import the mysql client for python

import pymysql

# Create a connection object

dbServerName    = "127.0.0.1"

dbUser          = "root"

dbPassword      = ""

dbName          = "test"

charSet         = "utf8mb4"

cusrorType      = pymysql.cursors.DictCursor

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

                                     db=dbName, charset=charSet,cursorclass=cusrorType)

try:

    # Create a cursor object

    cursorObject        = connectionObject.cursor()

    # SQL query string

    sqlQuery            = "CREATE TABLE Employee(id int, LastName varchar(32), FirstName varchar(32), DepartmentCode int)"

    # Execute the sqlQuery

    cursorObject.execute(sqlQuery)

    # SQL query string

    sqlQuery            = "show tables"

    # Execute the sqlQuery

    cursorObject.execute(sqlQuery)

    #Fetch all the rows

    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',)

The results of the command are returned as a tuple – with each tuple containing a 1-element tuple.