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 tablesusing 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.