Home » Python » Python programs

Python program to create an SQL table

Here, we are going to learn how to create an SQL program in Python?
Submitted by Shivang Yadav, on February 14, 2021

Python programming language is a high-level and object-oriented programming language developed by Guido Van Rossum, when he was working at CWI (Centrum Wiskunde & Informatica) which is a National Research Institute for Mathematics and Computer Science in the Netherlands.

In this era of machine learning and AI, the language has become so versatile that it can be used for performing multiple tasks. And backend development is one of them.

Using Python, we can access and manipulate databases and perform other backend tasks. Python has a library named 'pymysql' to perform the mySQL task and execute the queries.

One database manipulation method that can be performed using Python is creating a new table on the server using Python.

For this we need to have simple steps:

• Step 1: Connect to database using connect() method.
• Step 2: Create a command to execute the query using cursor() method.
• Step 3: Write the query to be executed to perform the task.
• Step 4: Execute the query using the execute() method.

Program to create an SQL table in Python

import  pymysql as ps

try:
    # cn is an object which hold the reference of database engine
    cn = ps.connect(host='localhost',port=3306,user='root',password='123',db='tata')

    '''
    cursor() is used to create command
    object, which is use to supply sql queries
    to database engine
    '''
    cmd = cn.cursor()

    query = "create table products(productid varchar(10) primary key,productname varchar(45),productrate decimal(10),mfdate date)"

    cmd.execute(query)

    print("Table Created..")

    cn.commit()
    cn.close()
except Exception as e:
    print("Error:",e)

Output:

Table Created..

On the server the table is created based on the query.

Python database (SQL) programs »