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