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.
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.
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.
You can also check out other posts related to python file handling, GUI, and basic python.