Create A Table In MySQL Server Using Python With PyMySQL


  • 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


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


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.



# import the mysql client for python

import pymysql


# Create a connection object

dbServerName    = ""

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)



    # 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



    # SQL query string

    sqlQuery            = "show tables"   


    # Execute the sqlQuery




    #Fetch all the rows

    rows                = cursorObject.fetchall()


    for row in rows:


except Exception as e:

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











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

Copyright 2023 ©