Create A MySQL Database Using Python And PyMySQL

Overview:

  • A database in MySQL is created with CREATE DATABASE SQL Statement.
  • CREATE DATABASE is part of Data Definition Statements.
  • A database in MySQL can contain zero to many tables. For each table created inside a MySQL Database a corresponding file is created under the MySQL data directory, which acts as the data storage for that specific table.
  • A MySQL database can be created programmatically using Python and the client API PyMySQL which is fully implemented using Python.

 

Create a MySQL Database using PyMySQL:

  • Import the PyMySQL module.
  • The SQL Command CREATE DATABASE can be executed using the cursor object obtained through a connection object.
  • In the Python example below the following CREATE DATABASE Command is used for creating a database:

                                   

CREATE DATABASE NewDatabase;

 

  • The example uses the same cursor instance to issue another SQL Command, this time to list the name of databases available in the database server.

                         

SHOW DATABASES

  • The results of the SHOW DATABASES command is printed onto the console in which the name of the newly created database appears along with the other database names.
  • To create tables inside a MySQL Server database refer to the article: Create A Table In MySQL Server Using Python With PyMySQL

 

Example:

# import the mysql client for python

import pymysql

 

# Create a connection object

databaseServerIP            = "127.0.0.1"  # IP address of the MySQL database server

databaseUserName            = "root"       # User name of the database server

databaseUserPassword        = ""           # Password for the database user

 

newDatabaseName             = "NewDatabase" # Name of the database that is to be created

charSet                     = "utf8mb4"     # Character set

cusrorType                  = pymysql.cursors.DictCursor

 

connectionInstance   = pymysql.connect(host=databaseServerIP, user=databaseUserName, password=databaseUserPassword,

                                     charset=charSet,cursorclass=cusrorType)

 

try:

    # Create a cursor object

    cursorInsatnce        = connectionInstance.cursor()                                    

 

    # SQL Statement to create a database

    sqlStatement            = "CREATE DATABASE "+newDatabaseName  

 

    # Execute the create database SQL statment through the cursor instance

    cursorInsatnce.execute(sqlStatement)

 

    # SQL query string

    sqlQuery            = "SHOW DATABASES"

 

    # Execute the sqlQuery

    cursorInsatnce.execute(sqlQuery)

 

    #Fetch all the rows

    databaseList                = cursorInsatnce.fetchall()

 

    for datatbase in databaseList:

        print(datatbase)

 

except Exception as e:

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

 

finally:

    connectionInstance.close()

 

Output:

{'Database': 'information_schema'}

{'Database': 'NewDatabase'}

{'Database': 'mysql'}

{'Database': 'performance_schema'}

 

The database NewDatabase which is created programattically is listed as the second entry in the output


Copyright 2020 © pythontic.com