SQL using Python

SQL stands for Structured Query Language. SQL is a programming language in which queries are made on relational databases. SQL is used widely in the field of Data Science, and SQL is used with Python to make the work easy. Structured query language is used to organize and query data. Also, SQL is also used for ad-hoc analysis of model results. There are many tools for using SQL in Python.

SQL is a type of database language that is specially designed for retrieving and managing data in relational databases such MySQL, MS Access, SQL server, Oracle, Sybase, Postgres, and many more relational databases. SQL was designed by IBM in the 1970s.

There are basic commands that SQL supports, such as the CREATE command for creating the table, the SELECT command for selecting and retrieving the data, and the INSERT command is used to insert or add the data into the relational database. UPDATE command is used to update the record in the table; DELETE is used to delete the record or table in the relational database, and DROP is used to delete the table completely.

Data Definition Language (DDL):

Data Definition language is used to define the structure of the databases. For example, DDL is used to create the table and modify the structure of the database object, including tables, views, schema indexes, etc. The DDL commands are CREATE for creating the new table, a view of the table; ALTER command is used for modifying the existing database objects such as the table; DROP command is used for deleting the entire table, a view of the table; TRUNCATE command is used to truncate the whole table in one execution.

Data Manipulation Language (DML)

Data Manipulation Language in SQL is used to add, delete, and modify data in a database. The DML commands are SELECT, which is used for retrieving certain records form tables; INSERT command, which is used for inserting the record in the database table; UPDATE command is used to modify the records in the relational table; and DELETE command, which is used for deleting the records, in the table.

Data Control Language:

Data Control Language command is used to control access to the record in the table. DCL commands are GRANT that gives a privilege to user, REVOKE is used to take back grant from user.

SQL is widely used in the data science domain to retrieve large amounts of data and process it. Nowadays, most of the companies are data driven. The data is stored in a relational database, and this data is managed and processed with the help of a database management system. Database management system makes the work easy and organized. So, it is important to integrate programming language with the database management tools. There are various operations performed by SQL on the data that is stored in the databases, such as inserting records, deleting records and many more.

Modules such as psycopg2, mysql.connector are types of modules that are used to connect SQL with Python and makes the work easy.

Let's see a simple code for SQL in Python:

Code:

import psycopg2

#Establish connection
conn = psycopg2.connect(
    database = "MSc Data Science",
    user = 'postgres',
    password = 'aditya@2001',
    host = '127.0.0.1',
    port = '5432'
)

#creating a cursor object
cursor = conn.cursor()
sql = '''CREATE TABLE STUDENT(
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    ROLL_No INT NOT NULL UNIQUE
)'''
cursor.execute(sql)
print("Table created successfully")
conn.commit()
conn.close()

Output:

Table Created Successfully

Explanation:

In the above code, mysql.connector module is imported, and a connection is established, with the host as localhost, the user as root, the password being 123456, and the database being covid-liver. So, these are some parameters that are inputted while connecting to the MySQL server. A cursor object is made for the connection, and the SQL query is run to create the table with some field. The cursor object is executed with the SQL query, and the table is created successfully.

Code to insert the record.

Code:

import mysql.connector

#Establish connection
conn = mysql.connector.connect(
    host="localhost",
    user="root",  # Change to your MySQL username
    password="123456",  # Change to your MySQL password
    database="covid-liver"  # Change to your desired database name
)

#creating a cursor object
cursor = conn.cursor()

cursor.execute('''INSERT INTO STUDENT(FIRST_NAME, LAST_NAME, AGE, SEX,
   ROLL_No) VALUES ('ADITYA', 'SHAKYA', 21, 'M', 222819)''')

conn.commit()
print("Record inserted")
conn.close()

Output:

Record inserted.

Explanation:

In the above code, record is inserted into the database table, first the SQL server is connected to the database using Python and the data is inserted to the table.

There are many other ways to use SQL in Python, such as:

SQLite/ MS-SQL Server/ Oracle/ MySQL/ Et Cetera
Pandas Query
DataFrame Querying

To connect with SQL using Python, import the Python library that helps to connect the database with Python and a cursor object is created so that the SQL commands are created.

Let's see how to connect SQLite with the local database. To do this, install the sqlite module by typing the command in the command prompt:

Command:

The above command helps you to install the sqlite module in Python that is used to connect the local database with Python.

Pandas Query:

Pandas Query helps in querying the data frame directly in Python without connecting to the database as, we are simple doing the query as the data is already read by the system.

Here is a simple code to connect sqlite3 with Python:

Code:

import sqlite3
# Connecting to the database
connection = sqlite3.connect("covid-liver.db")
# cursor
crsr = connection.cursor() 
print("Connected to the database") 
# close the connection
connection.close()

Output:

Connected to the database

Explanation:

In the above code, the sqlite3 module is imported, and the connection is made by calling the connection method, the database is passed, and the name of the database is covid-liver.db. A cursor object is made for the connection, and the database is connected.

The cursor object helps connect the database and execute SQL queries. The cursor object acts as middleware between the database connection and the SQL query. The cursor object helps in fetching the data from the database. All the commands are executed with the help of cursor objects.

Creating Tables Using SQLite3:

After connecting to the database, the cursor object is created. Let's see how to create the tables using Python in the database and how to execute other queries.

An object of SQL command is created to execute the SQL queries. It is very easy to execute the command in Python. The execute() method of the cursor is called, and the SQL command is passed to the object. After executing the command, the connection should be closed.

Let's see how to create a table in Python using SQLite3:

Code:

import sqlite3
 
# Connecting to the database
connection = sqlite3.connect("covid-liver.db")
 
# cursor
crsr = connection.cursor()
 
# SQL command to create a table in the database
sql_command = """CREATE TABLE employee ( 
staff_number INTEGER PRIMARY KEY, 
fname VARCHAR(20), 
lname VARCHAR(30), 
gender CHAR(1), 
joining DATE);"""
 
# execute the statement
crsr.execute(sql_command)
print('Table created') 
# close the connection
connection.close()

Output:

Table created

Explanation:

In the above code, the sqlite3 module is imported, and the connection is made by passing the database name to the connect method. After establishing the connection, the cursor object is created to execute the queries. An SQL command is input as the comments, and this command is passed to the execute method, and the table is created. After creating the table, the connection is closed.

Code for printing the table in the database:

Code:

import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('covid-liver.db')

# Create a cursor object
cursor = conn.cursor()

# Execute a query to fetch the cursor for the table names
.execute("SELECT name FROM sqlite_master WHERE type='table';")

# Fetch all the table names
tables = cursor.fetchall()

# Print the table names
for table in tables:
    print(table[0])

# Close the connection
conn.close()

Output:

emp
employee

Explanation:

In the above code, the sqlite3 module is imported, and a connection is established by passing the covid-liver database and the cursor object is created. The command is executed to fetch the tables in the database. The fetchall() method is used to fetch all the tables in the database, and with the help of the for-loops, all the tables are printed. The table is printed with the index 0 as it gives the tuple object. The table names are emp and employee in the database. After printing all the tables in the database. The connection is closed.

Let's see how to insert the records the table in SQL using Python.

Inserting Records in the Table in SQL using Python

To insert a record into the SQL table, we can write the command in string, and with the help of the execute() method, we can add the record to the table.

Code:

import sqlite3
connection = sqlite3.connect("covid-liver.db")
crsr = connection.cursor()
 
# SQL command to insert the data in the table
sql_command = """INSERT INTO employee VALUES (23, "Rishabh",\
"Bansal", "M", "2014-03-28");"""
crsr.execute(sql_command)
sql_command = """INSERT INTO employee VALUES (1, "Bill", "Gates",\
"M", "1980-10-28");"""
crsr.execute(sql_command)
connection.commit()
 
# close the connection
connection.close()

Explanation:

In the above code, the sqlite3 module is imported, a connection is made with the help of connect() method and the database is passed to the connect() method. A cursor of the connection is made, and two SQL commands are inputted with the data. With the help of the execute() method, the record is inserted into the table. After executing the commands, the connection is committed and closed.

Let's see how to insert multiple data at a time, and the data is entered by the user:

Code:

# importing module
import sqlite3
connection = sqlite3.connect("covid-liver.db")
crsr = connection.cursor()
pk = [2, 3, 4, 5, 6]
f_name = ['Nikhil', 'Nisha', 'Abhinav', 'Raju', 'Anshul']
l_name = ['Aggarwal', 'Rawat', 'Tomar', 'Kumar', 'Aggarwal']

gender = ['M', 'F', 'M', 'M', 'F']
date = ['2019-08-24', '2020-01-01', '2018-05-14', '2015-02-02', '2018-05-14']
 
for i in range(5):
    crsr.execute(f'INSERT INTO emp VALUES ({pk[i]}, "{f_name[i]}", "{l_name[i]}", "{gender[i]}", "{date[i]}")')

connection.commit()
 
# close the connection
connection.close()

Output:

(2, 'Nikhil', 'Aggarwal', 'M', '2019-08-24')
(3, 'Nisha', 'Rawat', 'F', '2020-01-01')
(4, 'Abhinav', 'Tomar', 'M', '2018-05-14')
(5, 'Raju', 'Kumar', 'M', '2015-02-02')
(6, 'Anshul', 'Aggarwal', 'F', '2018-05-14')

Explanation:

In the above code, the sqlite3 module is imported, the connection is made using connect method and the database is passed to the connect() method. The cursor of the connection is made, and the primary key is input as a list. The first and last names of the users are input with the help of the list, and the gender of each user is also input, as well as the date the user entered the data as a list. Since the number of users is 5, so the data is inserted one by one by iterating each data of the user such as primary, first name, last name, gender, and the data they entered the data in the for-loop and the execution is made one by one. After inserting all the data, the connection is committed, and the connection is closed.

After inserting the data, let's see how to show the data in the table:

Code:

# importing the module
import sqlite3
connection = sqlite3.connect("covid-liver.db")
crsr = connection.cursor()
crsr.execute("SELECT * FROM emp")
ans = crsr.fetchall()
for i in ans:
    print(i)

Output:

(2, 'Nikhil', 'Aggarwal', 'M', '2019-08-24')
(3, 'Nisha', 'Rawat', 'F', '2020-01-01')
(4, 'Abhinav', 'Tomar', 'M', '2018-05-14')
(5, 'Raju', 'Kumar', 'M', '2015-02-02')
(6, 'Anshul', 'Aggarwal', 'F', '2018-05-14')

Explanation:

In the above code, the SQLite3 module is imported, the connection is made, and the cursor of the connection is made. The SQL query is input in the string and the data is fetched with the help of fetchall() method and the whole data is printed from the table.

Let's see how to update the data in the SQL table using Python:

Updating the Data in SQL Table using Python

The following is the syntax for updating the data in the SQL using Python:

Syntax:

UPDATE table_name SET column1 = value1, column2 = value2,…
WHERE condition;

The above syntax is used to update the record in the table. The UPDATE keyword is used to update the data, where the table name and the column name is the column value that should be updated and WHERE clause is used to set the condition.

Code:

# Import module
import sqlite3
 
# Connecting to sqlite
conn = sqlite3.connect('covid-liver.db')
cursor = conn.cursor()
cursor.execute('''UPDATE emp SET lname = "Jyoti" WHERE fname="Nisha";''')
conn.commit()
conn.close()

Output:

(2, 'Nikhil', 'Aggarwal', 'M', '2019-08-24')
(3, 'Nisha', 'Jyoti', 'F', '2020-01-01')
(4, 'Abhinav', 'Tomar', 'M', '2018-05-14')
(5, 'Raju', 'Kumar', 'M', '2015-02-02')
(6, 'Anshul', 'Aggarwal', 'F', '2018-05-14')

Explanation:

In the above code, the command that is passed in the execute() method is used to update the last name to Jyoti where the first name of the employee is Nisha that is the condition and the table is updated.

Conclusion:

SQL is widely used in data analysis and data science domain where programmers have to deal with data and find the valuable information from the data. There are many other modules that help in connecting SQL with Python.

Next TopicType and isinstance in python

← prev next →