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