Change language

SQL Using Python and SQLite | Set 2

|

SQLite — it is a standalone, highly reliable, built-in, full-featured, open source SQL database engine. It is the most used database engine on the world wide web. 
Python has a library for accessing SQLite databases called sqlite3 and is designed to work with this database, which is included in the Python package since version 2.5.

In this article, we will discuss how to query the database using commands such as Update and Delete, and to visualize data using graphs. 
It is recommended to go through

# code for update operation

import sqlite3

 
# the name of the database to pass as a parameter

conn = sqlite3.connect ( ’ mydatabase.db’ )

 
# update 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 ()

Output:

 Total number of rows updated: 1 (u’B113053’, u’Geek’, u’2017-01-11 13: 53: 39’, 21.0), (u’B113058 ’, u’Saan’, u’2017-01-11 13: 53: 39’, 21.0), (u’B113059’, u’Sam ’, u’2017-01-11 13: 53: 39’, 22.0) 

# code for the delete operation

import sqlite3

 
# database name to pass as a parameter

conn = sqlite3.connect ( ’mydatabase.db’ )

 
# remove student record from database

conn.execute ( "DELETE from Student where unix =’ B113058’ " )

conn.commit ()

print "Total number of r ows deleted: " , conn.total_changes

  

cursor = conn.execute ( "SELECT * FROM Student" )

for row in cursor:

  print row,

  
conn.close ()

Output:

 Total number of rows deleted: 1 (u’B113053’, u’Geek’, u’2017-01-11 13: 53: 39’, 21.0), (u’B113059’, u’Sam’, u’2017-01-11 13: 53: 39’, 22.0) 

User input

# code to execute a query using input

import sqlite3

 
# creates an in-memory database

con = sqlite3.connect ( ": memory: " )

cur = con.cursor ()

cur.execute ( "create table person (name, age, id)" )

 

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 )]

n = len (who)

 

for i in range (n):

 

  # This is q-mark style:

  cur.execute ( "insert into person values ​​(?,?,?)" , (who [ i], age [i], p_id [i]))

 

  # And this is the named style:

  cur.execute ( "select * from person" )

 

# Selects all records from the table

print cur.fetchall ()

Output:

 (u’Navin’, 34, 113053) (u’Basu’, 42, 113058) (u’Firoz’, 65, 113059) ( u’Tim’, 47, 113060) (u’Varun’, 54, 113061) 

SQLite Graphs

# rendering the plot using the matplotlib library

import matplotlib.pyplot as plt

  

def graph_data (p_id, age):

 

# dots

plt.plot (p_id, age, color = ’ yellow’ , linestyle = ’ dashed’ , linewidth  = 3 ,

marker = ’*’ , markerfacecolor = ’blue’ , markersize = 12 )

 

# axis name х

plt.xlabel ( ’ Persons Id’ )

 

# Y-axis name

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 )]

  
# call the graph function
graph_data (p_id, age)

So we can perform operations like this using a SQL query to link to the database and build graph significantly to highlight its characteristic.

SQL Using Python | Set 3 (Big Data Processing)

Shop

Learn programming in R: courses

$

Best Python online courses for 2022

$

Best laptop for Fortnite

$

Best laptop for Excel

$

Best laptop for Solidworks

$

Best laptop for Roblox

$

Best computer for crypto mining

$

Best laptop for Sims 4

$

Latest questions

NUMPYNUMPY

psycopg2: insert multiple rows with one query

12 answers

NUMPYNUMPY

How to convert Nonetype to int or string?

12 answers

NUMPYNUMPY

How to specify multiple return types using type-hints

12 answers

NUMPYNUMPY

Javascript Error: IPython is not defined in JupyterLab

12 answers

News


Wiki

Python OpenCV | cv2.putText () method

numpy.arctan2 () in Python

Python | os.path.realpath () method

Python OpenCV | cv2.circle () method

Python OpenCV cv2.cvtColor () method

Python - Move item to the end of the list

time.perf_counter () function in Python

Check if one list is a subset of another in Python

Python os.path.join () method