Python Basics

Python PyMySQL Create Table

The first job, in python database project can be creating a table. In this article, we will learn how to create a table in mysql database using python and pymysql. (See following table example)

emp_id emp_name emp_age
1 Raj 22
2 Rahul 24
3 Vivek 26
4 Ajay 28
5 Suraj 29

Lets see how can we create a table just like above one.

In the above table, we have three columns.

  • The first column is an id, which is an integer in this case
  • The second column is employee name column which varchar.
  • The third column is employee age column which is integer.

Before creating a table, we must have a clear vision or understanding about all the required columns and its data type. (If any thing goes wrong, we have option to change, but it is wise to be sure everything is at its right place before proceeding further)

When ever we need to create a table using pymysql in python projects, we are bound to follow three steps.

  1. Step 1: Establish Database Connection
  2. Step 2: Create table
  3. Step 3: Close the database connection

Note: Once all the required job is complete, we should immediate close the database connection. Otherwise hackers may take advantage of such situation.

In previous article, I have discussed about database connection establishment.

In this article, just before running the sql command to create table, one more important thing we must have to remember.

Connection variable itself can not run any sql command. To run the sql command we must have to use the pymysql inbuilt feature cursor, which is function of connection variable to run a sql command.

So keep in mind, if you have sql command and if you want to run the sql command then you must have create a cursor and use it.

How to create a cursor object?

#once connection variable is established, we can create cursor object as below

conn=pymysql.connect(‘localhost’,’root’,”,’employee_db’)

#In the above connection is established with the database and conn variable contains the connection functionality

#lets create a cursor object

cursor=conn.cursor()

In the above code I have called the cursor function of conn object, and it returns the cursor. I have used the cursor variable for easy understanding, you can take any name you want which should not contain space or any special characters.

Note: In the above cursor variable contains execute function which can run any sql command. So the code is like below.

sql=’sql command’

cursor=conn.cursor()

cursor.execute(sql)

Create a Table

#Step 1

conn=pymysql.connect(‘localhost’,’root’,”,’employee_db’)

#Step 2

cursor=conn.cursor()

sql=’create table employee_info(id int primary, emp_name varchar(100),emp_age int)’

cursor.execute(sql)

#Step 3

cursor.close()

conn.close()

If I run the above code, employee_info table is created in the database.