Attend FREE Webinar on Digital Marketing for Career & Business Growth Register Now

Data Analytics Blog

Data Analytics Case Studies, WhyTos, HowTos, Interviews, News, Events, Jobs and more...

Using SQL with Python – The Ultimate Guide

    -  

5 (100%) 2 votes

Accessing your databases through a programming language allows the creation of applications which 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 organised 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.

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 of 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: Using Basic Functions of SQLite

  1. To first use SQLite, import it.
  2. 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.
  3. 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.
  4. To create a table in the database, create an object and write the SQL command in it with being commented.
  5. 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

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, 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

Data Analytics Course by Digital Vidya

Free Data Analytics Webinar

Date: 25th Oct, 2018 (Thursday)
Time: 3 PM to 4 PM (IST/GMT +5:30)

# 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: Using Methods for 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.

  1. 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()

  1. 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()

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 and start using SQL with code.

I am a college student and a data science enthusiast. Connect with me on LinkedIn -www.linkedin.com/in/saksham-malhotra-9bb69513b

  • Data-Analytics

  • Your Comment

    Your email address will not be published.