Create A Table In MySQL Server Using Python With PyMySQL

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

 

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.

 

Example:

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

('A',)

('B',)

('Employee',)

('Student',)

('exper',)

 

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


Copyright 2020 © pythontic.com