Accessing your databases through a programming language allows the creation of applications that can store and retrieve data directly from or for a user interface. There are various Python SQL libraries which do this task for the programming language Python. Here we will discuss SQLite which is the most popular Python SQL library out there.
All companies whether large or small use databases. A database is nothing but an organized collection of data. Data is organised into rows, columns, and tables and it is indexed to make it easier to find relevant information. Databases offer numerous functionalities by which one can manage large amounts of information easily over the web, and high-volume data input and output over a typical file such as a text file.
A DBMS or Database Management System is a software for interaction between users and databases. These users are not necessarily human users, but could be programs or applications. This is when SQL comes in. SQL is a query language and is very popular in databases. It allows a python program to interact with a database to perform querying operations.
Download Detailed Brochure and Get Complimentary access to Live Online Demo Class with Industry Expert.
There are many libraries using which we can connect front-end of our application with the back-end database. Some examples of such Python SQL libraries are SQLite, pymssql, sqlalchemy among others. Each of these Python SQL libraries have their pros and cons and contain functions for Python SQL jobs and python SQL query generator. We are explaining one such Python SQL libraries called SQLite which is available with Python. In this blog, we will see how to connect, store and fetch data into SQL server or simple SQL with Python.
You can also convert CSV to SQL Python and store it in a table in your database. The python database connection SQL server is done with the help of Python SQL server libraries in Python. These libraries run SQL Python jobs like store, retrieve, delete and help Python connect to SQL server.
Section 1: SQLite function to frame Python SQL Library
- To first use SQLite, import it.
- Connect using connect() method and pass the name of the database with which you want to initiate connection. Otherwise, Python will create a file with the given name.
- After this, a cursor object is called to be capable to send commands to the SQL. Cursor is a control structure used to traverse and fetch the records of the database.
- To create a table in the database, create an object and write the SQL command in it with being commented.
- To execute a command, call the cursor method execute and pass the name of the sql command as a parameter in it. Save a number of commands and execute them together. After you perform all your activities, save the changes in the file by committing those changes and then lose the connection.
Some code to represent the above steps:
Python code to demonstrate table creation and insertions with SQL
# importing module
import sqlite3
# connecting to the database
connection = sqlite3.connect(“myTable.db”)
# cursor
crsr = connection.cursor()
# SQL command to create a table in the database
sql_command = “””CREATE TABLE emp (
staff_number INTEGER PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(30),
gender CHAR(1),
joining DATE);”””
# execute the statement
crsr.execute(sql_command)
# SQL command to insert the data in the table
sql_command = “””INSERT INTO emp VALUES (23, “Rishabh”, “Bansal”, “M”, “2014-03-28″);”””
crsr.execute(sql_command)
# another SQL command to insert the data in the table
sql_command = “””INSERT INTO emp VALUES (1, “Bill”, “Gates”, “M”, “1980-10-28″);”””
crsr.execute(sql_command)
# To save the changes in the files. Never skip this.
# If we skip this, nothing will be saved in the database.
connection.commit()
# close the connection
connection.close()
Section 2: Fetching Data to develop Python SQL Library
Fetching data from the database is as simple as creating one. The execute method uses the “Select” keyword to fetch data from the table with the indicated name in the form of list of lists.
Python code to demonstrate SQL to fetch data.
# importing the module
import sqlite3
# connect with the myTable database
connection = sqlite3.connect(“myTable.db”)
# cursor object
crsr = connection.cursor()
# execute the command to fetch all the data from the table emp
crsr.execute(“SELECT * FROM emp”)
# store all the fetched data in the ans variable
ans= crsr.fetchall()
# loop to print all the data
for i in ans:
print(i)
Section 3: Update using Python programming tools, delete records in a database
You can also use a number of functionalities other than the ones mentioned above. These include updation, deletion of records, data input by user, etc.
Python code to show updation, and deletion
|
# code for update operation import sqlite3 # database name to be passed as parameter conn = sqlite3.connect(‘mydatabase.db’) # update the student record conn.execute(“UPDATE Student SET name = ‘Sam’ where unix=’B113059′”) conn.commit() print “Total number of rows updated :”, conn.total_changes cursor = conn.execute(“SELECT * FROM Student”) for row in cursor: print row, conn.close() |
#Code to show graphical representation
|
# graph visualization using matplotlib library import matplotlib.pyplot as plt def graph_data(p_id,age): # plotting the points plt.plot(p_id, age, color=’yellow’, linestyle=’dashed’, linewidth = 3, marker=’*’, markerfacecolor=’blue’, markersize=12) # naming the x axis plt.xlabel(‘Persons Id’) # naming the y axis plt.ylabel(‘Ages’) # plt.plot(p_id,age) plt.show() print (“Enter 5 students names:”) who = [raw_input() for i in range(5)] print (“Enter their ages respectively:”) age = [int(raw_input()) for i in range(5)] print (“Enter their ids respectively:”) p_id = [int(raw_input()) for i in range(5)] # calling graph function graph_data(p_id,age) |
Section 4: Python SQL Methods for targetting Large Database Querying
Previously, the records of the database were limited in number but large databases can also be queried using SQLite. Some methods to do this include fetchall(), executescript(), executemany(),etc.
-
executescript() : This method executes multiple SQL statements at once. You need to pass the script in the parameter of this method.
import sqlite3
# Connection with the DataBase
# ‘library.db’
connection = sqlite3.connect(“library.db”)
cursor = connection.cursor()
# SQL piece of code Executed
# SQL piece of code Executed
cursor.executescript(“””
CREATE TABLE people(
firstname,
lastname,
age
);
CREATE TABLE book(
title,
author,
published
);
INSERT INTO
book(title, author, published)
VALUES (
‘Dan Clarke”s GFG Detective Agency’,
‘Sean Simpsons’,
1987
);
“””)
sql = “””
SELECT COUNT(*) FROM book;”””
cursor.execute(sql)
# The output in fetched and returned
# as a List by fetchall()
result = cursor.fetchall()
print(result)
sql = “””
SELECT * FROM book;”””
cursor.execute(sql)
result = cursor.fetchall()
print(result)
# Changes saved into database
connection.commit()
# Connection closed(broken)
# with DataBase
connection.close()
-
executemany() : This method is used whenever executescript() has to be used multiple times.
import sqlite3
# Connection with the DataBase
# ‘library.db’
connection = sqlite3.connect(“library.db”)
cursor = connection.cursor()
# SQL piece of code Executed
cursor.execute(“””
CREATE TABLE book(
title,
author,
published);”””)
List = [(‘A’, ‘B’, 2008), (‘C’, ‘D’, 2008),
(‘E’, ‘F’, 2010)]
connection. executemany(“””
INSERT INTO
book(title, author, published)
VALUES (?, ?, ?)”””, List)
sql = “””
SELECT * FROM book;”””
cursor.execute(sql)
result = cursor.fetchall()
for x in result:
print(x)
# Changes saved into database
connection.commit()
# Connection closed(broken)
# with DataBase
connection.close()
Conclusion
There you go. Now you can easily access your SQL server using this Python SQL library. This will help you build applications that require database functionality such as a library management system or a blood donation system as they require interfacing the user with the backend database. Get your hands dirty now, understand the basics of SQL and start using SQL with code.
Data scientists have a strong curiosity and a passion for achieving practical business impact. Moreover, to understand all the practical aspects of a Data Science and know how to become a Data Scientist, read this answer.
If you are too looking for building a career in Python and want to master it, enroll in the Python Programming Course today.
