Class XII
Computer Science
Python Database
Connectivity
Session-1
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
INTERFAC
E
PYTHON
• A database is an organized collection of data.
• Data is organized into rows, columns and tables and it is
WITH SQL
indexed to make it easier to find relevant information. DATABAS
• All companies whether large or small use databases.
• It is necessary to develop project/software using any
E
programming language like python in such a manner which
can interface with such databases which support SQL.
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
INTERFACE PYTHON
WITH SQL DATABASE
• Form/any user interface designed in any
programming language is Front End
• Data given by database as response is
known as Back-End database.
• SQL is just a query language, it is not a
database.
• To perform SQL queries, we need to
install any database for example Oracle,
MySQL, MongoDB, PostGres SQL, SQL
Server, DB2 et.
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
Steps to connect python
application to our
MySQL database
• Import
[Link]/PyMySQL
module
• Create the connection object
• Create the cursor object
• Execute the query
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
What is
pymysql/[Link] What is a Cursor
What is Connection
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
MySQL Python Connector is used to
access MySQL database from Python.
It needs a database driver
To add
mysql- MySQL Connector is a standardized database
driver provided by MySQL.
connector
to Python Step1: Open Command window
Step 2: pip install mysqlclient
Step2: pip install [Link]
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
PyMySQL is an interface for connecting
to a MySQL database server from Python
It implements the Python Database API
To add v2. 0
PyMySQL to It contains a pure-Python MySQL client
library. The goal of PyMySQL is to be a drop-
Python in replacement for MySQLdb.
Step1: Open Command window
Step2: type pip install pymysql
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
To check 1. Open Interpreter
whether
connector
2. Type import
is [Link]/import pymysql
properly
installed
or not? 3. If no error occurs, it means
connector works
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
Create Connection
• Start by creating a connection to MySQL
Syntax:
Import [Link] as mc
connObject=[Link]
(host=<hostname>, user=<username>,passwd = <password>)
import [Link]
mydb = [Link](
<[Link].connection_cext.CMy
host="localhost",
SQLConnection object at
user="yourusername",
0x7fa26d368390>
password="yourpassword"
)
print(mydb)
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
Cursor in python
• Cursors are created by the [Link]( ) method
• they are bound to the connection for the entire lifetime of the program
• all the commands are executed the context of the database session wrapped by
connection.
<cur_obj> = [Link]()
Note: We need cursor to execute our MySQL queries.
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
To display list of available
databases in MySQL
import [Link] as pm
#Creating the connection Object
conn=[Link](host="localhost", user="root", passwd=“mysql")
With the help of
#Creating the cursor object execute(), we can run
any Mysql query
Mycur = [Link]()
[Link]("show databases") # with the help of cursor executing query
for x in Mycur:
print(x) # Fetching database from Mycur one by one
[Link]()
Showing list of
databases
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
Create database through python
import pymysql as pm while True:
#Creating the connection Object print("1. Create Database")
op=int(input("Enter your choice"))
conn=[Link](host="localhost", user="root", passwd=“mysql") if op==1:
#Creating the cursor object
Create create_Database()
else:
Mycur = [Link]()
database break
def create_Database():
[Link]("create database Directory") #Creating Database Directory [Link]()
[Link]("show databases")
for x in Mycur: #Fetching databases from mycur one by one
print(x)
New database created
Output
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
Creating database only if it does not
exists
import [Link] as mc
conn=[Link](host='localhost',user='root',password='MySQL2020')
def createdatabase():
global conn
mycursor=[Link](buffered=True) #Required
[Link]('show databases')
flag=False
for i in mycursor: Checks each tuple if mydb exists and if it
if('mydb' in i):
does set flag=True and breaks
flag=True
break If database exists, use
if(flag): database is not used we
[Link](‘use mydb’) reconnect using connect
else:
[Link]('create database mydb')
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
createdatabase()