Unit ‐ 5

Connecting with Database

 Database Programming
• Working with MySQL Database,
• Using MySQL from Python
Python,
• Retrieving All Rows from a Table,
• I
Inserting
i Rows
R into
i a TTable,
bl
• Deleting Rows from a Table,
• Updating Rows in a Table,
• Creating Database Tables through Python
 Introduction
 From a construction firm to a stock exchange, every organisation depends on large
databases. These are essentially collections of tables, and’ connected with each
g columns.
other through

 These database systems support SQL, the Structured Query Language, which is used
to create, access and manipulate the data.
 SQL is
i usedd to access data,
d and
d also
l to create and
d exploit
l i the
h relationships
l i hi b between
the stored data.
 Additionally, these databases support database normalisation rules for avoiding
redundancyy of data.

 The Python programming language has powerful features for database

programming.
 Python
P h supports various
i d
databases
b lik
like MySQL,
M SQL Oracle,
O l SSybase,
b P
PostgreSQL,
SQL etc.
 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.
 Benefits of Python for database
programming
 There are many good reasons to use Python for programming
database applications:
 Programming in Python is maybe more efficient and faster compared to
other languages.
 Python is famous for its portability.
 It is platform independent.
 Python
y supports
pp SQL cursors.
 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.
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
interfaces.
 DB‐API (SQL‐API) for Python
 Python DB‐API is independent of any database engine,
which enables you to write Python scripts to access any
d b
database engine.
i
 The Python DB API implementation for MySQL is
y Q
MySQLdb.
 For PostgreSQL, it supports psycopg, PyGresQL and
pyPgSQL modules.
 DB‐API
DB API implementations for Oracle are dc_oracle2
dc oracle2 and
cx_oracle.
 Pydb2 is the DB‐API implementation for DB2.
 Python’s DB‐API consists of connection objects, cursor
objects, standard exceptions and some other module
co te ts
contents.
• The DB API provides a minimal standard for
working with databases using Python
structures and syntax wherever possible. This
API includes the following:
• Importing the API module.
• Acquiring a connection with the database.
• Issuing SQL statements and stored
procedures.
• Closing the connection
 Functions and attributes
• connect(parameters...)Constructor for creating a
connection to the database.
• Returns a Connection Object.
• Parameters are the same as for the MySQL C API.
• In addition, there are a few additional keywords
that correspond to what you would pass
mysql options() before connecting
mysql_options() connecting.
• Note that some parameters must be specified as
y
keyword arguments!
g
• The default value for each parameter is NULL or
zero, as appropriate. The important parameters are:
• Host: name of host to connect to. Default: use the local host via a
UNIX socket ((where applicable)
pp )
• User: user to authenticate as. Default: current effective user.
• Passwd: password to authenticate with. Default: no password.
• Db: database to use. Default: no default database.
• Port: TCP port of MySQL server. Default: standard port (3306).
• unix_socket:
i k location
l i off UNIX socket.
k Default:
f l use d default
f l llocation
i
or TCP for remote hosts.
• Conv: type conversion dictionary. Default: a copy of
[Link]
• Compress :Enable protocol compression. Default: no compression.
• connect_timeout: Abort if connect is not completed within given number of
seconds. Default: no timeout (?)
• named_pipe: Use a named pipe (Windows). Default: don't.
• init_command: Initial command to issue to server upon connection. Default:
Nothing.
• read_default_file: MySQL configuration file to read; see the MySQL documentation
for mysql_options().
• read_default_group:
_ _g p Default ggroup p to read; see the MySQL
y documentation for
mysql_options().
• Cursorclass: cursor class that cursor() uses, unless overridden. Default:
[Link].
y This must be a keyword
y p
parameter.
• Unicode: If set, CHAR and VARCHAR columns are returned as Unicode strings, using
the specified character set. None means to use a default encoding.
 Connection Objects
• Connection objects are returned by the connect() function.
• commit() () :If the database and the tables support
pp transactions,,
this commits the current transaction; otherwise this method
successfully does nothing.
• rollback() :If the database and tables support transactions
transactions, this
rolls back (cancels) the current transaction; otherwise a
NotSupportedError is raised.
• Compatibility note: The older MySQLmodule defines this
method, which sucessfully does nothing. This is dangerous
behavior, as a successful rollback indicates that the current
transaction was backed out out, which is not true
true, and fails to notify
the programmer that the database now needs to be cleaned up
by other means.
• cursor([cursorclass]) : MySQL does not support cursors; however,
cursors are easilyy emulated.
• You can supply an alternative cursor class as an optional
parameter.
• If this is not present, it defaults to the value given when creating
the connection object, or the standard Cursor class. Also see the
additional supplied
pp cursor classes in the usageg section.
• begin() :Explicitly start a transaction.
• Normally you do not need to use this: Executing a query implicitly
starts a new transaction if one is not in progress.
• If AUTOCOMMIT is on, you can use begin() to temporarily turn it
off AUTOCOMMIT will resume after the next commit() or
off.
rollback.
 Cursor Objects
• close() :Closes the cursor. Future operations raise ProgrammingError. If you are
using server‐side cursors, it is very important to close the cursor when you are
d
done with
i h iit and
dbbefore
f creating
i a new one.
• insert_id() : Returns the last AUTO_INCREMENT field value inserted into the
database. (Non‐standard)
• info() : Returns some information about the last query. Normally you don't need to
check this. With the default cursor, any MySQL warnings cause Warning to be
raised. If you are using a cursor class without warnings, then you might want to use
thi SSee th
this. the M MySQL
SQL ddocs ffor mysql_info().
l i f () (N
(Non‐standard)
t d d)
• setinputsizes(): Does nothing, successfully.
• setoutputsizes(): Does nothing, successfully.
• nextset() :Advances the cursor to the next result set, discarding the remaining rows
in the current result set. If there are no additional result sets, it returns None;
otherwise it returns a true value.
 Cursor Attributes
• [Link] details regarding the result columns
• This read‐only attribute is a sequence of 7‐item named tuples.
• Each of these named tuples contains information describing one result column:
• name
• type_code
• display_size
• internal_size
• precision
• scale
• null_ok
• The values for precision and scale are only set for numeric types. The values for
display_size and null_ok are always None.
• This attribute will be None for operations that do not return rows or if the cursor
has not had an operation invoked via the [Link]() or
[Link]() method yet.
 rowcount – number of rows of the
result
l
• [Link]
• This read‐only attribute specifies the number of rows
that the last [Link]() or [Link]()
callll produced
d d (f(for DQL statements
t t t lik
like SELECT) or
affected (for DML statements like UPDATE or INSERT).
• It is also set by the Cursor.copy_from()
[Link] from() and
:meth’:Cursor.copy_to methods.
• The attribute is ‐1 in case no such method call has been
performed on the cursor or the rowcount of the last
operation cannot be determined by the interface.
 Methods of Cursor

• close – close the cursor

• Close the cursor now (rather than whenever it
is deleted)
• Return type: None
• The cursor will be unusable from this point
forward; an Error (or subclass) exception will
be raised if any operation is attempted with
the cursor.
 execute – execute a database
operation
• [Link](operation[, parameters])
Prepare and execute a database operation
(query or command)
• Parameters: operation (str) – the database
operation
• parameters – a sequence or
mapping of parameters
• Returns:the cursor, so you can chain
commands
 executemany – execute many similar
d b
database operations
• [Link](operation[,
y( p [, seq
q_off_p
parameters])
])
Prepare and execute many similar database operations
(queries or commands)
• Parameters:
P t operation
ti (str)
( t ) – the
th d database
t b operation
ti
• seq_of_parameters – a sequence or mapping
of parameter tuples or mappings
• Returns: the cursor, so you can chain commands
• Prepare
p a database operation
p (q
(queryy or command)) and
then execute it against all parameter tuples or
mappings found in the sequence seq_of_parameters.
data = [
('Jane', date(2005, 2, 12)),
(('Joe'
Joe , date(2006,
date(2006 5,
5 23)),
23))
('John', date(2010, 10, 3)),
]
• stmt = "INSERT INTO employees (first_name,
hi d t ) VALUES (%s,
hire_date) (% %s)"
% )"
• [Link](stmt, data)
 callproc – Call a stored procedure
• [Link](self, procname, [parameters]): Call a stored database
procedure with the given name
• Parameters: procname (str) – the name of the database function
• parameters – a sequence of parameters (can be empty or
omitted)
• This method calls a stored procedure (function) in the PostgreSQL
database.
• The
h sequence off parameters must contain i one entry ffor eachh iinput
argument that the function expects.
• The result of the call is the same as this input
p sequence;
q replacement
p
of output and input/output parameters in the return value is
currently not supported.
• The function may also provide a result set as output. These can be
requested through the standard fetch methods of the cursor.
 fetchone – fetch next row of the
query result
• [Link]() Fetch the next row of a query
result set
• Returns: the next row of the query result set
• Return type: named tuple or None Fetch the next
row of a query result set, returning a single named
tuple, or None when no more data is available.
• The
h fifield
ld names off the
h named d tuple
l are the
h same as
the column names of the database query as long as
theyy are valid Python
y identifiers.
• An Error (or subclass) exception is raised if the
previous call to [Link]() or
C
[Link]()
t () did nott produce
d any result
lt sett
or no call was issued yet.
• # Using a while loop
[Link]("SELECT * FROM employees")
row = [Link]()
while row is not None:
print(row)
row = [Link]()

• # Using the cursor as iterator

[Link]("SELECT
[Link]( SELECT * FROM employees")
employees )
for row in cursor:
print(row)
 fetchmany – fetch next set of rows of
the query result
• [Link]([size=None][, keep=False]) Fetch the next set
of rows of a query result
• Parameters: size (int or None) – the number of rows to be fetched
• k
keep – if sett tto ttrue, will
ill kkeep th
the passedd arraysize
i
• Tpye keep: bool
• Returns: the next set of rows of the query result
• Return type:list of named tuples
• Fetch the next set of rows of a q queryy result,, returningg a list of
named tuples. An empty sequence is returned when no more
rows are available. The field names of the named tuple are the
same as the column names of the database query as long as they
are valid Python identifiers.
 fetchall – fetch all rows of the query
result
l
• cursor
[Link]()
fetchall() Fetch all (remaining) rows of a
query result
• Returns: the set of all rows of the q queryy result
• Return type: list of named tuples Fetch all
(remaining) rows of a query result, returning them
as list of named tuples.
tuples
• The field names of the named tuple are the same as
the column names of the database query as long as
they are valid Python identifiers.
• Note that the cursor’s arraysize attribute can affect
th performance
the f off thi
this operation.
ti
• cursor
[Link](
execute("SELECT
SELECT * FROM employees
ORDER BY emp_no")
• head_rows
head rows = [Link](size=2)
cursor fetchmany(size=2)
• remaining_rows = [Link]()
 arraysize ‐ the number of rows to
f h at a time
fetch
• Cursor
[Link]
arraysize The number of rows to fetch
at a time
• This read/write attribute specifies the number
of rows to fetch at a time with
Cursor fetchmany()
[Link]().
• It defaults to 1, meaning to fetch a single row
at a time.
i
 Example
import MySQLdb

# Open database connection

db = [Link](host="localhost",port=3306,user="root",passwd="",db="test" )

# prepare a cursor object using cursor() method

cursor = [Link]()

# execute SQL query using execute() method.

[Link]("SELECT
[Link]( SELECT VERSION()")
VERSION() )

# Fetch a single row using fetchone() method.

data = [Link]()

print "Database version : %s " % data

# disconnect from server

[Link]()
• If a connection is established with the
datasource, then a Connection Object is returned
and saved into db for further use,, otherwise db is
set to None.
• Next, db object is used to create a cursor object,
which in turn is used to execute SQL queries.
• Finally, before coming out, it ensures that
database connection is closed and resources are
released.
 Creating Database Table
• Once a database connection is established, we are ready to create tables
or records into the database tables using execute method of the created
cursor.
import MySQLdb

# Open database connection

db = [Link](host="localhost",port=3306,user="root",passwd="",db="test" )

# prepare a cursor object using cursor() method

cursor = [Link]()

# Drop table if it already exist using execute() method.

[Link]("DROP
[Link]( DROP TABLE IF EXISTS EMPLOYEE
EMPLOYEE"))

# Create table as per requirement

sql = """CREATE TABLE EMPLOYEE (
FIRST NAME CHAR(20) NOT NULL,
FIRST_NAME NULL
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
( ),
INCOME FLOAT )"""

[Link](sql)

# disconnect from server

[Link]()
 INSERT Operation
import MySQLdb

#O
Open ddatabase
b connection
i
db = [Link](host="localhost",port=3306,user="root",passwd="",db="test" )

#pprepare
p a cursor object
j usingg cursor()
() method
cursor = [Link]()

# Prepare SQL query to INSERT a record into the database.

sql = """INSERT
INSERT INTO EMPLOYEE(FIRST
EMPLOYEE(FIRST_NAME, NAME
LAST_NAME, AGE, GEN, INCOME)
VALUES ('Solanki', 'raviraj', 20, 'M', 2000)"""
try:
# Execute the SQL command
[Link](sql)
# Commit your changes in the database
db commit()
[Link]()
except:
# Rollback in case there is any error
[Link]()

# disconnect from server

[Link]()
 Insert 2nd way
import MySQLdb

# Open database connection

db = [Link](host="localhost",port=3306,user="root",passwd="",db="test" )

# prepare a cursor object

bj t using
i cursor()
() method
th d
cursor = [Link]()

# Prepare SQL query to INSERT a record into the database.

sqll = "INSERT INTO EMPLOYEE(FIRST_NAME,
EMPLOYEE(FIRST NAME \
LAST_NAME, AGE, GEN, INCOME) \
VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
('Mac', 'Mohan', 20, 'M', 2000)
try:
# Execute the SQL command
[Link](sql)
# Commit your changes in the database
db commit()
[Link]()
except:
# Rollback in case there is any error
[Link]()

# disconnect from server

[Link]()
 READ Operation
• READ Operation on any database means to fetch some useful
information from the database.
• Once our database connection is established, you are ready to make
a query into this database.
• You can use either fetchone() method to fetch single record or
fetchall() method to fetch multiple values from a database tabletable.
• fetchone(): It fetches the next row of a query result set.
• A result set is an object that is returned when a cursor object is used
to query a table.
• fetchall(): It fetches all the rows in a result set. If some rows have
already been extracted from the result set, then it retrieves the
remaining rows from the result set.
• rowcount: This is a read‐only
read only attribute and returns the number of
rows that were affected by an execute() method.
 Types of Errors
• The following are the six different error types
supported by MySQL for Python.
• DataError
• IntegrityError
• InternalError
• NotSupportedError
• OperationalError
O ti lE
• ProgrammingError
• DataError
• This exception is raised due to problems with the processed data (for
example, numeric value out of range, division by zero, and so on).
• IntegrityError
• If the relational integrity of the database is involved (for example a
foreign key check fails, duplicate key, and so on), this exception is
raised.
raised
• InternalError
• This exception
p is raised when there is an internal error in the MySQL
y
database itself (for example, an invalid cursor, the transaction is out
of sync, and so on). This is usually an issue of timing out or otherwise
being perceived by MySQL as having lost connectivity with a cursor
cursor.
• NotSupportedError
• MySQL for Python raises this exception when a method or database
API that is not supported is used (for example, requesting a
transaction‐oriented function when transactions are not available.
Th also
They l can arisei iin conjunction
j ti with ith setting
tti characters
h t sets,t SQL
modes, and when using MySQL in conjunction with Secure Socket
Layer (SSL).
• OperationalError
• Exception raised for operational errors that are not necessarily under
th control
the t l off th
the programmer (f(for example,
l an unexpected
t d
disconnect, the data source name is not found, a transaction could
not be processed, a memory allocation error occurrs, and so on.).
• ProgrammingError
• Exception raised for actual programming
errors (for example
example, a table is not found or
already exists, there is a syntax error in the
MySQL statement,
statement a wrong number of
parameters is specified, and so on.).