UNIT III: Database programming using Python: Connecting to a database (sqlite, mysql) using

Python, Sending DML and DDL queries and processing the result from a Python Program. Network
programming using Python: An introduction to client-server programming, Basics of TCP and UDP
protocols, Introduction to socket programming, Building an HTTP client and server

Database Programming in python

Introduction:
• To build the real world applications, connecting with the databases is the
necessity for the programming languages. However, python allows us to
connect our application to the databases like MySQL, SQLite, MongoDB,
and many others.
• Python also supports Data Definition Language (DDL), Data Manipulation
Language (DML) and Data Query Statements. For database programming,
the Python DB-API is a widely used module that provides a database
application programming interface.

The Python Programming language has powerful features for database programming,
those are

• Python is famous for its portability.

• It is platform independent.
• In many programming languages, the application developer needs to take
care of the open and closed connections of the database, to avoid further
exceptions and errors. In Python, these connections are taken care of.
• Python supports relational database systems.
• Python database APIs are compatible with various databases ,so it is very
easy to migrate and port database application interfaces.

EnvironmentSetup:
• In this topic we will discuss Python-MySQL database connectivity, and we will
perform the database operations in python.
• The Python DB API implementation for MySQL is possible by MySQL db or
[Link].
Note: The Python DB-API implementation for MySQL is possible by MySQL db in
 python2.x but it deprecated in python3.x. In Python3.x,DB
-API implementation for MySQL is possible by [Link].
• You should have MySQL installed on your computer
Windows:
You can download a free MySQL databaseat
[Link]
Linux(Ubuntu):
sudoapt-getinstallmysql-server

• You need to install MySQLdb:(incaseofPython2.x)

• MySQL db is an interface for connecting to a MySQL database server from
Python. The MySQLdb is not a built-in module, We need to install it to get
it working.
• Execute the following command to install it.

➢ For(Linux) Ubuntu,use the following command-

sudoapt-getinstallpython2.7-mysqldb

➢ For Windows command prompt ,use the following command-

Pip install MySQL-python
• To test if the installation was successful,or if you already have "MySQLdb"
installed, execute following python statement at terminal or CMD.
Import MySQLdb
• If the above statement was executed with no errors,
"MySQLdb" is installed and ready to be used.
(OR)
• You need to install [Link]:(incaseofPython3.x)
• To connect the python application with the MySQL database,
We must import the [Link] module in the program.
• The [Link] is not a built-in module, We need to install it to get it
working.
• Execute the following command to install it using pip installer.

➢ For(Linux)Ubuntu,usethefollowingcommand-
 Pip install mysql-connector-python

➢ For Windows command prompt, use the following command-

Pip install mysql-connector
• To test if the installation was successful,or if you already have
"[Link] " installed, execute following python statement at
terminal or CMD.
[Link]
• If the above statement was executed with no errors,
"[Link]" is installed and ready to be used.

What is SQlite?
SQLite is a lightweight, serverless, self-contained, and highly reliable
SQL database engine. It is implemented as a C-language library that reads
and writes directly to ordinary disk files, eliminating the need for a
separate database server process.
This architecture makes SQLite ideal for embedded systems, desktop
applications, web browsers, and mobile devices that require a simple and
efficient local data storage solution.
Features of SQLite
Serverless: Unlike other database systems like MySQL or PostgreSQL,
SQLite does not require a dedicated server to operate.
Zero-configuration: It requires no setup or administration. You can start
using it immediately by including the library in your project.
Self-contained: The entire database, including tables, indexes, and data,
is stored in a single cross-platform file, making it easy to manage and
transfer.
ACID-compliant: It follows the ACID (Atomicity, Consistency,
Isolation, Durability) properties, ensuring reliable transactions even during
system crashes.
Cross-platform: SQLite runs on many operating systems, including
Windows, macOS, Linux, iOS, and Android.
DML queries:
what are DML queries?

Data Manipulation Language (DML) commands are used to modify the

data within database tables. In SQLite, the main DML commands are
INSERT, UPDATE, and DELETE.

DDL queries: what are DDL queries ?

DDL, or Data Definition Language, refers to a set of SQL commands used
to define and manage the structure of a database. These commands are
essential for creating, modifying, and deleting database objects like tables,
indexes, views, and schemas. DDL commands focus on the schema (the
blueprint of the database) rather than the data itself.
The primary DDL commands include:
CREATE: Used to create new database objects.
ALTER: Used to modify the structure of existing database objects.
DROP: Used to delete database objects entirely. This action permanently
removes the object and all its associated data.
TRUNCATE: Used to remove all rows from a table, effectively emptying
the table while keeping its structure intact.
RENAME: Used to change the name of a database object.

How to execute DDL in Python

• The process for executing DDL commands is similar across most
database libraries, such as sqlite3 for SQLite and [Link]
for MySQL.
• Using the sqlite3 standard library
• Connect to the database using [Link]().
• Create a cursor object, which allows you to execute SQL statements.
• Execute the DDL command as a string using [Link](). For
multiple statements, use [Link]().
• Commit the changes with [Link](). DDL commands
are auto-committed in some databases, but it is best practice to
 explicitly commit.
• Close the cursor and connection.

Python Database Application Programmer’s Interface(DB-API):

• Python DB-API is independent of any

database engine, which enables you to
write Python scripts to access any database engine.

• The Python DB API implementation for MySQL is possible by

MySQLdb or [Link].
• Using Python structure, DB-API provides standard and support for
working with databases.
The API consists of:
1. Import module ([Link])
2. Create the connection object.
3. Create the cursor object
4. Execute the query
5. Close the connection
1. Import module([Link]):

To interact with MySQL database using Python, you need first to import [Link] or
MySQLdb module by using following statement.

MySQLdb(in python2.x)
import MySQLdb

[Link](inpython3.x)
import [Link]
2. Create the connection object:
• After importing [Link] or MySQLdb module, we need to create connection
object, for that python DB-API supports one method i.e. connect () method.
• ItcreatesconnectionbetweenMySQLdatabaseandPythonApplication.
• IfyouimportMySQLdb(inpython2.x)thenweneedtousefollowingcodetocreate
connection.

Syntax:
Conn-name=[Link](<hostname>,<username>,<password>,<database>)
Example:
Myconn=[Link]("localhost","root","root",”emp”)
(Or)
• If you import [Link](in python3.x) then we need to use following code to
create connection.
Syntax:
conn-name=[Link](host=<host-name>,
user=<username>,passwd=<pwd>,database=<dbname>)
Example:
myconn=[Link](host="localhost",user="root",
passwd="root",database=”emp”)
3. Create the cursor object:
• After creation of connection object we need to create cursor object to execute
SQL queries in MySQL database.
• The cursor object facilitates us to have multiple separate working
environments through the same connection to the database.
• The Cursor object can be created by using cursor() method.
Syntax:
cur_came=[Link]()
Example:
my_cur=[Link]()
4. Execute the query:
• After creation of cursor object we need to execute required queries by using
cursor object. To execute SQL queries, python DB-API supports following
method i.e. execute ().
Syntax:
[Link](query)
Example:
my_cur.execute(“select*fromEmployee”)
5. Close the connection:
• Aftercompletionofallrequiredqueriesweneedtoclosethe connection.
Syntax:
[Link]()

Example:
[Link]()
 MySQLdb(in python2.x): (Define and execute the DML query)
• MySQLdb is an interface for connecting to a MySQL database server from
Python. The following are example programs demonstrate interactions with
MySQL database using MySQLdb module.
• Note−Make sure you have root privileges of MySQL database to interact
with database.i.e. User id and password of MySQL database.
• We are going to perform the following operations on MySQL database.
➢ Show databases
➢ Create database
➢ Create table
➢ To insert data into table
➢ Read/Select data from table
➢ Update data in table
➢ Delete data from table
Example Programs:
To display databases:
We can get the list of all the databases by using the following MySQL query.
>showdatabases; [Link]

Example: Output:
importMySQLdb >>>python [Link]
#Createtheconnectionobject
('information_schema',)
myconn=[Link]("localhost","root","root")
('mysql',)
#creating the cursor object
('performance_schema',)
cur=[Link]()
#executing the query ('php my admin',)
dbs=[Link]("showdatabases") ('test',)
#display the result ('Sample db',)

for x in cur:
print(x)
#closetheconnection
 [Link]()

To Create database :
The new database can be created by using the following SQLquery.

>createdatabase<database-name>

[Link]

Output:
Example: >>>python [Link]
importMySQLdb Database created successfully
#Createtheconnectionobject
myconn=[Link]("localhost","root","root")
#creatingthecursorobject
cur = [Link]()
#executing the query
[Link]("createdatabaseColleged")
print("Database created successfully")
#close the connection
[Link]()
To Create table:
The new table can be created by using the following SQL query.
➢ createtable<table-name> (column-name1datatype,column-name2datatype,…)
[Link]

import MySQLdb
#Createtheconnectionobject
myconn=[Link]("localhost","root","root",”Colleged”)
#creatingthecursorobject
cur = [Link]()
#executing the query
[Link]("create table students(sid varchar(20)primary key,sname varchar(25),age int(10))")
print("Table created successfully")
#closetheconnection [Link]()

Output:
>>>python [Link]
Table created successfully
 To Insert data into table:
The data can be inserted into table by using the following SQL query.
>insertinto <table-name>values(value1,value2,…)
[Link]
Example:
import MySQLdb
#Createtheconnectionobject
myconn=[Link]("localhost","root","root",”Colleged”)
#creatingthecursorobject
cur = [Link]()
#executing the query
[Link]("INSERTINTOstudentsVALUES('501','ABC',23)")
[Link]("INSERTINTOstudentsVALUES('502','XYZ',22)")
#commit the transaction
[Link]()
print("Datainsertedsuccessfully")
#close the connection
[Link]()

Output:
>>>python [Link]
Data inserted
successfully
 To Read/Select data from table::
The data can be read/select data from table by using the following SQL query.
>select column-names from <table-name>

fetchall() method returns all rows in the table.

fetchone()method returns one row from table.

Example: [Link]
import MySQLdb
#Createtheconnectionobject
myconn = [Link]("localhost","root","root",”Colleged”) #creating the cursor object
cur=[Link]() #executing the query
[Link]("select * from students")
#fetchingalltherowsfromthecursorobject
result = [Link]()
print("Student Details are:") #printing the result
for x in result:
print(x);
#closetheconnection
[Link]()
Output:
>>>[Link]
Student Details are:
('501', 'ABC', 23)
('502','XYZ',22)
 fetchone() method returns one row from table.
Example: [Link]
import MySQLdb
#Createtheconnectionobject
myconn=[Link]("localhost","root","root",”Colleged”)
#creating the cursor object cur =
[Link]() #executing the
query
[Link]("select * from students") #fetching all the rows
from the cursor object
result = [Link]()
print("OnestudentDetailsare:")
#printing the result
print(result)
#close the connection
[Link]()

Output:
>>>[Link]

One student Details are:

('501', 'ABC', 23)
 To Update data into table:
The data can be updated in table by using the following SQL query.
>update<table-name>setcolumn-name=valuewherecondition

Example: [Link]
import MySQLdb
#Createtheconnectionobject
myconn = [Link]("localhost","root","root",”Colleged”) #creating the cursor object
cur=[Link]() #executing the
query
[Link]("updatestudentssetsname='Kumar'wheresid='502'")
#committhetransaction
[Link]()
print("Dataupdatedsuccessfully") #close the
connection [Link]()
Output:
>>>python [Link]
Data updated successfully
 ToDeletedatafromtable:
ThedatacanbedeletedfromtablebyusingthefollowingSQL query.
>deletefrom<table-name>wherecondition

Example: [Link]
import MySQLdb
#Createtheconnectionobject
myconn = [Link]("localhost","root","root",”Colleged”) #creating the cursor object
cur=[Link]() #executing the
query
[Link]("deletefromstudentswheresid='502'")
#committhetransaction
[Link]()
print("Datadeletedsuccessfully") #close the
connection [Link]()
Output:
>>>[Link]
Data deleted successfully
 DB-API for MySQL in Python

MySQLdb(python2.x) [Link](python3.x)

#ImportMySQLdb #Import [Link]

import MySQLdb import [Link]
#Create the connection object #Create the connection object
myconn =[Link] myconn=[Link]
("localhost","root","root",”Colleged”) (host="localhost",user="root",
passwd="root",database="Colleged")
 [Link](inpython3.x)::
MySQL Connector enables Python programs to access MySQL databases.
Example: [Link]
import [Link]
#Create the connection object
myconn=[Link](host="localhost",user="root",passwd="root",
database="Collegedb")
#creatingthe
cursorobject
cur =
[Link]
or()
#executing
the querys
[Link]("delete from students where sid='502'")
#commit the transaction
[Link]()
print("Data deleted
successfully") #close
the connection
[Link]()
Output:
>>>pyt
hon
deleted
[Link]
Data
deleted
success
fully
AN INTRODUCTION TO CLIENT SERVER PROGRAMMING:
Client-server programming in Python refers to the development of applications where two distinct entities,
a "client" and a "server," communicate over a network. The server provides a service or resource, and the
client requests and consumes that service or resource. This model is fundamental to most network
applications, including web browsing, online gaming, and file sharing.

• Sockets:
The core of client-server communication in Python is the socket module. Sockets provide an interface for
network communication, allowing programs to send and receive data across a network.
What is Sockets?
Sockets are the endpoints of a bidirectional communications channel. Sockets may communicate within a
process,
between processes on the same machine, or between processes on different continents.
Sockets may be implemented over a number of different channel types: Unix domain sockets, T CP, UDP,
and so
on. T he socket library provides specific classes for handling the common transports as well as a g eneric
interface for handling the rest.
Sockets have their own vocabulary:
• Server:
• Creates a socket and binds it to a specific IP address and port number.
• Listens for incoming client connections.
• Accepts a connection when a client attempts to connect, creating a new socket for
communication with that specific client.
• Receives data from the client, processes it, and sends responses back.
• Manages multiple client connections, often using techniques like threading to handle each client
concurrently.

• Client:
• Creates a socket.
• Connects to the server's IP address and port.
• Sends requests or data to the server.
• Receives responses or data from the server.
• Closes the connection when communication is complete.

1. Introduction to TCP/IP Model and Transport Layer:

• Explanation of the TCP/IP protocol suite layers.
• Role of the Transport Layer in end-to-end communication.
2. Transmission Control Protocol (TCP):
• Connection-oriented: Requires a three-way handshake to establish a connection before data
transfer.
• Reliable: Guarantees delivery of data, retransmits lost segments, and ensures in-order delivery.
• Flow Control: Manages data transmission rate to prevent receiver overload.
• Congestion Control: Prevents network congestion.
3. : Using [Link](socket.AF_INET, socket.SOCK_STREAM) for TCP sockets.
 4. Python Implementation
Example: Simple TCP client and server for sending and receiving messages.
5. User Datagram Protocol (UDP):
• Connectionless: No handshake required; data is sent as independent datagrams.
• Unreliable: Does not guarantee delivery, order, or error correction.
• Fast: Lower overhead due to lack of connection management and reliability features.
• Python Implementation: Using [Link](socket.AF_INET, socket.SOCK_DGRAM) for UDP
sockets.
• Example: Simple UDP client and server for sending and receiving datagrams.
6. Key Differences between TCP and UDP:
• Reliability vs. Speed.
• Connection-oriented vs. Connectionless.
• Use cases (e.g., TCP for web browsing, email; UDP for streaming, online gaming).
7. Socket Programming in Python:
• Creating sockets using [Link]().
• Binding sockets to addresses and ports using bind().
• Listening for connections (TCP) using listen() and accept().
• Connecting to a server (TCP) using connect().
• Sending and receiving data using send(), recv(), sendto(), recvfrom().
• Closing sockets using close().

What is Python Socket?

Python Socket is a networking API for bidirectional communication over TCP or UDP. It supports IPv4
(AF_INET), IPv6 (AF_INET6), and socket types like SOCK_STREAM (TCP) and SOCK_DGRAM (UDP).
Functions like send(), recv(), bind(), and listen() handle data exchange and connections.
Socket programming establishes a connection between web sockets (client and server sockets),
enabling them to communicate bidirectionally in real time. Direct socket connections can significantly reduce
app latency, as data can be transmitted or received at any time.
Is Python Suitable for Socket Programming?
Absolutely! Python is an excellent choice for socket programming as it has many built-in
modules such as socket, select, and asyncio to create client-server applications.
What are Python Sockets Used For?
Python sockets are used in applications to communicate over a network: such as web servers, chat
applications, or email clients. They can also be used for data streaming and enterprise solutions. The server
program listens and handles incoming connections, whilst the client connects to the server to send and receive
data.
How do I Run a Socket Program in Python?
1. Set up your account and initialize PubNub
2. Now, you can send messages and receive messages from the server.
You need to run both programs separately, i.e.:
python [Link]
python [Link]

Import socket in Python

Example of socket import: Create the file [Link] in the project directory. To use sockets, import the
Python socket library and create a new socket object that connects to a specified IP address (in this case,
localhost on port number 8080, but you can select any ipv4 address). Create a new connection to the socket
server, send data to the TCP server, and close the socket connection.
Your [Link] file should look like this:
import socket
client = [Link](socket.AF_INET, socket.SOCK_STREAM)
[Link](('[Link]', 8080))
[Link]("I am CLIENT\n".encode())
from_server = [Link](4096)
[Link]()
print (from_server.decode())

You will need a socket server to listen for incoming connections and messages from your client. Create
the file [Link] and add the following contents:

1import socket
2serv = [Link](socket.AF_INET, socket.SOCK_STREAM)
[Link](('[Link]', 8080))
[Link](5)
5while True:
6 conn, addr = [Link]()
7 from_client = ''
8 while True:
9 data = [Link](4096)
10 if not data: break
11 from_client += [Link]('utf8')
12 print (f'From client: {from_client}')
13 [Link]("I am SERVER\n".encode())
14 [Link]()
15print ('client disconnected and shutdown')

[Link] binds the socket object to the hostname (localhost) on port 8080 and continually listens for
new client connections. When a client connects to this address, the server accepts the connection and
reads any data. Once the data is successfully read from the client, the server provides a data response, at
which point the client terminates the connection.

Testing your Python Socket Programming

To test this out yourself, open two terminal windows simultaneously. In one window, run:

1python3 [Link]
In the second window, run:

1python3 [Link]
Notice that the server continues running and will establish a new connection every time you run
the client and append any new output.
The client will send the "I am CLIENT" string to the server and wait for a reply. The server will
read the client’s message, output it to the terminal, and send back a response to the client.

What is the Alternative to Sockets in Python?

Modern alternatives to socket programming, like HTTP/2, gRPC, and Websockets, offer different
real-time communication options for Python apps. However, these technologies still face scaling and
distribution challenges similar to sockets.
PubNub solves these issues by providing a globally distributed, scalable hybrid cloud platform, so you
don't have to worry about server deployment or maintenance.
Here is the summary of the key functions from socket - Low-level networking

interface:

1. [Link](): Create a new socket using the given address family, socket type and
protocol number.
2. [Link](address): Bind the socket to address.
3. [Link](backlog): Listen for connections made to the socket. The backlog argument
specifies the maximum number of queued connections and should be at least 0; the
maximum value is system-dependent (usually 5), the minimum value is forced to 0.
4. [Link](): The return value is a pair (conn, address) where conn is a new socket
object usable to send and receive data on the connection, and address is the address
bound to the socket on the other end of the connection.
At accept(), a new socket is created that is distinct from the named socket. This new socket
is used solely for communication with this particular client.
For TCP servers, the socket object used to receive connections is not the same socket
 used to perform subsequent communication with the client. In particular,
the accept() system call returns a new socket object that's actually used for the connection.
This allows a server to manage connections from a large number of clients simultaneously.

1. [Link](bytes[, flags]): Send data to the socket. The socket must be connected to a
remote socket. Returns the number of bytes sent. Applications are responsible for checking
that all data has been sent; if only some of the data was transmitted, the application needs
to attempt delivery of the remaining data.
2. [Link](): Mark the socket closed. all future operations on the socket object will fail.
The remote end will receive no more data (after queued data is flushed). Sockets are
automatically closed when they are garbage-collected, but it is recommended to close()
them explicitly.

Note that the server socket doesn't receive any data. It just produces client sockets.
Each clientsocket is created in response to some other client socket doing a connect() to the
host and port we're bound to. As soon as we've created that clientsocket, we go back to
listening for more connections.

# [Link]
import socket

# create a socket object

s = [Link](socket.AF_INET, socket.SOCK_STREAM)

# get local machine name

host = [Link]()

port = 9999

# connection to hostname on the port.

[Link]((host, port))

# Receive no more than 1024 bytes

tm = [Link](1024)
[Link]()
print("The time got from the server is %s" % [Link]('ascii'))

The output from the run should look like this:

$ python [Link] &

Got a connection from ('[Link]', 54597)

$ python [Link]
The time got from the server is Wed Jan 29 19:14:15 2014

"If you need fast IPC between two processes on one machine, you should look into whatever
form of shared memory the platform offers. A simple protocol based around shared memory
and locks or semaphores is by far the fastest technique."

"If you do decide to use sockets, bind the 'server' socket to 'localhost'. On most platforms, this
will take a shortcut around a couple of layers of network code and be quite a bit faster."

In Python 3, all strings are Unicode. So, if any kind of text string is to be sent across the
network, it needs to be [Link] is why the server is using the encode('ascii') method on
the data it transmits. Likewise, when a client receives network data, that data is first received
as raw unencoded bytes. If you print it out or try to process it as text, we're unlikely to get what
we expected. Instead, we need to decode it [Link] is why the client code is
using decode('ascii') on the result.