Best post on Create and Insert query : Python + MySQL-3

Create and Insert query in Python.

In this blog we are going to use create and insert query in python and MySQL. Database connectivity plays important role in developing dynamic applications.

After establishing connection with SQL, we can create new databases and tables using python script to automate work.

Create Query

Create Query is used to create database and table in SQL using python script.

Create query to create database

NOTE: For creating a database using python script we need not mention database parameter while defining connection object.

Syntax:

cur.execute("Create database database_name")

Let us see the code to create database using python script

"""
Author : ITVoyagers (itvoyagers.in)

Date :5th June 2020

Description : Program to establish Database connectivity using Python script and create a database.

"""
import mysql.connector
db=mysql.connector.connect(user='root',passwd='root',host='127.0.0.1')
# prepare a cursor object using cursor() method
cursor = db.cursor()
# execute SQL query using execute() method.
cursor.execute("Create database itvoyagers")
# disconnect from server
db.close()

OUTPUT

To check if database is created we can run show databases query in MySQL Command Line Client or by using python script. Here we are using show databases query in MySQL Command Line Client.

Output of show databases command after creating database using python script (itvoyagers.in)
Output of show databases command after creating database using python script (itvoyagers.in)

Create query to create table

As we have already created database, we need to use database before creating table in MySQL Command Line Client while working in MySQL.

Syntax:

cur.execute("Create table table_name(columnm_name1 datatype constraint,columnm_name2 datatype constraint ")

Here:-
contraint is optional Let us see the code to create table using python script in database

"""
Author : ITVoyagers (itvoyagers.in)

Date :5th June 2020

Description : Program to establish Database connectivity using Python script and create table in database.

"""
import mysql.connector
db=mysql.connector.connect(user='root',passwd='root',host='127.0.0.1',database='itvoyagers')
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS SUSCRIBERS")
# Create table as per requirement
sql = """CREATE TABLE SUSCRIBERS(
         SUSCRIBER_FNAME  CHAR(20) NOT NULL,
         SUSCRIBER_LNAME  CHAR(20),
         AGE INT,  
         GENDER CHAR(1),
         INTERESTS CHAR(20))"""
cursor.execute(sql)
print("Table Created Successfully");
# disconnect from server
db.close()

OUTPUT

Here we will get output as table created on successful execution of code. Also we can check if table is created or not in MySQL Command Line client by using show tables command.

Output displayed after creating table on python prompt (itvoyagers.in)
Output displayed after creating table on python prompt (itvoyagers.in)
Output displayed after creating table on MySQL prompt (itvoyagers.in)
Output displayed after creating table on MySQL prompt (itvoyagers.in)

Insert Query

When we create a table in created database, we can add values inthat table corresponding to the columns.
To add values in tables SQL provides insert query.
We can embed insert query in python script to automate process.

Syntax:

cur.execute("Insert into table_name valiues(value1, value2,...))
Other Syntax of insert query is mentioned below.
cur.execute("Insert into table_name (columnm_name1 ,columnm_name2,..") Values (value1, value2,...))
Here in both syntax it is mandatory to mention values of corresponding columns in same order as that of columns. Let us see the code to insert values to table using python script in database

"""
Author : ITVoyagers (itvoyagers.in)

Date :5th June 2020

Description : Program to establish Database connectivity using Python script and insert values in table.

"""
import mysql.connector
db=mysql.connector.connect(user='root',passwd='root',host='127.0.0.1',database='itvoyagers')
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO SUSCRIBER(SUSCRIBER_FNAME,
         SUSCRIBER_LNAME, AGE, GENDER, INTERESTS)
         VALUES ('Kiara', 'R', 21, 'F', 'Python')"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   print ("Data Inserted Successfully...!")
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
# disconnect from server
db.close()

OUTPUT

In this code we will get output on python prompt after successful execution of code. We can check if values are inserted or not in MySQL Command Line client by using select query or by using select query in python script.

Output of this code on python prompt after execution on insert query (itvoyagers.in)
Output of this code on python prompt after execution on insert query (itvoyagers.in)
Output of this code on MySQL prompt after execution on insert query (itvoyagers.in)
Output of this code on MySQL prompt after execution on insert query (itvoyagers.in)

You can also check out other posts related to python file handling, GUI, and basic python.

Leave a Comment