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
mysql.connector/PyMySQL
module
• Create the connection object
• Create the cursor object
• Execute the query
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
What is
pymysql/mysql.connector 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 mysql.connector
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 mysql.connector/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 mysql.connector as mc
connObject=mc.connect
(host=<hostname>, user=<username>,passwd = <password>)
import mysql.connector
mydb = mysql.connector.connect(
<mysql.connector.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 connection.cursor( ) 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> = conn.cursor()
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 mysql.connector as pm
#Creating the connection Object
conn=pm.connect(host="localhost", user="root", passwd=“mysql")
With the help of
#Creating the cursor object execute(), we can run
any Mysql query
Mycur = conn.cursor()
Mycur.execute("show databases") # with the help of cursor executing query
for x in Mycur:
print(x) # Fetching database from Mycur one by one
conn.close()
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=pm.connect(host="localhost", user="root", passwd=“mysql") if op==1:
#Creating the cursor object
Create create_Database()
else:
Mycur = conn.cursor()
database break
def create_Database():
Mycur.execute("create database Directory") #Creating Database Directory conn.close()
Mycur.execute("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 mysql.connector as mc
conn=mc.connect(host='localhost',user='root',password='MySQL2020')
def createdatabase():
global conn
mycursor=conn.cursor(buffered=True) #Required
mycursor.execute('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
mycursor.execute(‘use mydb’) reconnect using connect
else:
mycursor.execute('create database mydb')
Deepshikha Sethi----XII Computer Science -----AIS Mayur Vihar
createdatabase()
