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.