0% found this document useful (0 votes)
3 views6 pages

python unit-5

The document explains how to connect Python applications to databases, specifically using the mysql.connector module for MySQL. It outlines the steps for creating a connection and cursor object, and introduces the Python Database Application Programmer's Interface (DB-API) for consistent database access. Additionally, it discusses Object-Relational Managers (ORMs) like SQLAlchemy and SQLObject, which allow programmers to interact with databases using Python objects instead of SQL queries.

Uploaded by

chetana reddy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
3 views6 pages

python unit-5

The document explains how to connect Python applications to databases, specifically using the mysql.connector module for MySQL. It outlines the steps for creating a connection and cursor object, and introduces the Python Database Application Programmer's Interface (DB-API) for consistent database access. Additionally, it discusses Object-Relational Managers (ORMs) like SQLAlchemy and SQLObject, which allow programmers to interact with databases using Python objects instead of SQL queries.

Uploaded by

chetana reddy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 6

What is Database programming

python being a high-level language provides support for


various databases. We can connect and run queries for a
particular database using Python and without writing raw
queries in the terminal or shell of that particular
database, we just need to have that database installed in
our system.

Database Connection
There are the following steps to connect a python application to our
database.
1. Import mysql.connector module
2. Create the connection object.
3. Create the cursor object
4. Execute the query

Creating the connection


To create a connection between the MySQL database and the
python application, the connect() method of mysql.connector
module is used.
Pass the database details like HostName, username, and the
database password in the method call. The method returns the
connection object.
The syntax to use the connect() is given below.

1. Connection-Object= mysql.connector.connect(host = <host-name>


, user = <username> , passwd = <password> )
Consider the following example.
Example

1. import mysql.connector
2.
3. #Create the connection object
4. myconn = mysql.connector.connect(host = "localhost", user = "root
",passwd = "google")
5.
6. #printing the connection object
7. print(myconn)
Output:
<mysql.connector.connection.MySQLConnection object at
0x7fb142edd780>
Here, we must notice that we can specify the database name in the
connect() method if we want to connect to a specific database.
Example

1. import mysql.connector
2.
3. #Create the connection object
4. myconn = mysql.connector.connect(host = "localhost", user = "root
",passwd = "google", database = "mydb")
5.
6. #printing the connection object
7. print(myconn)
Output:
<mysql.connector.connection.MySQLConnection object at
0x7ff64aa3d7b8>

Creating a cursor object


The cursor object can be defined as an abstraction specified in the
Python DB-API 2.0. It facilitates us to have multiple separate working
environments through the same connection to the database. We can
create the cursor object by calling the 'cursor' function of the
connection object. The cursor object is an important aspect of
executing queries to the databases.
The syntax to create the cursor object is given below.

1. <my_cur> = conn.cursor()
Example

1. import mysql.connector
2. #Create the connection object
3. myconn = mysql.connector.connect(host = "localhost", user = "root
",passwd = "google", database = "mydb")
4.
5. #printing the connection object
6. print(myconn)
7.
8. #creating the cursor object
9. cur = myconn.cursor()
10.
11. print(cur)
Output:
<mysql.connector.connection.MySQLConnection object at
0x7faa17a15748>
MySQLCursor: (Nothing executed yet)

Python Database Application Programmer's


Interface (DB-API)
Where can one find the interfaces necessary to talk to a database?
Simple. Just go to
the database topics section at the main Python Web site. There you
will find links to the full
and current DB-API (version 2.0), existing database modules,
documentation, the special
interest group, etc. Since its inception, the DB-API has been moved
into PEP 249. (This PEP
obsoletes the old DB-API 1.0 specification which is PEP 248.) What is
the DB-API?
The API is a specification that states a set of required objects and
database access
mechanisms to provide consistent access across the various
database adapters and underlying
database systems. Like most community-based efforts, the API was
driven by strong need. In
the "old days," we had a scenario of many databases and many
people implementing their
own database adapters. It was a wheel that was being reinvented
over and over again. These
databases and adapters were implemented at different times by
different people without any
consistency of functionality. Unfortunately, this meant that
application code using such
interfaces also had to be customized to which database module they
chose to use, and any
changes to that interface also meant updates were needed in the
application code.A special
interest group (SIG) for Python database connectivity was formed,
and eventually, an API
was born ... the DB-API version 1.0. The API provides for a consistent
interface to a variety
of relational databases, and porting code between different
databases is much simpler, usually
only requiring tweaking several lines of code. You will see an
example of this later on in this
chapter.
Module Attributes
The DB-API specification mandates that the features and attributes
listed below must
be supplied. A DB- API-compliant module must define the global
attributes as shown below.
DB-API Module Attributes
Attribute Description
apilevel Version of DB-API module is compliant with
threadsafety Level of thread safety of this module
paramstyle SQL statement parameter style of this module
Connect() Connect() function

Object-Relational Managers (ORMs)


As seen in the previous section, a variety of different database
systems are available
today, and most of them have Python interfaces to allow you to
harness their power. The only
drawback to those systems is the need to know SQL. If you are a
programmer who feels more
comfortable with manipulating Python objects instead of SQL
queries, yet still want to use a
relational database as your data backend, then you are a great
candidate to be a user of
ORMs.
Think Objects, Not SQL
Creators of these systems have abstracted away much of the pure
SQL layer and
implemented objects in Python that you can manipulate to
accomplish the same tasks without
having to generate the required lines of SQL. Some systems allow
for more flexibility if you
do have to slip in a few lines of SQL, but for the most part, you can
avoid almost all the
general SQL required.
Database tables are magically converted to Python classes with
columns and features
as attributes and methods responsible for database operations.
Setting up your application to
an ORM is somewhat similar to that of a standard database adapter.
Because of the amount of
work that ORMs perform on your behalf, some things are actually
more complex or require
more lines of code than using an adapter directly. Hopefully, the
gains you achieve in
productivity make up for a little bit of extra work.
Python and ORMs
The most well-known Python ORMs today are SQLAlchemy and
SQLObject. We
will give you examples of SQLAlchemy and SQLObject because the
systems are somewhat
disparate due to different philosophies, but once you figure these
out, moving on to other
ORMs is much simpler.
Some other Python ORMs include PyDO/PyDO2, PDO, Dejavu, PDO,
Durus,
QLime, and ForgetSQL. Larger Web-based systems can also have
their own ORM
component, i.e., WebWare MiddleKit and Django's Database API.
Note that "well-known"
does not mean "best for your application." Although these others
were not included in our
discussion, that does not mean that they would not be right for your
application.
Employee Role Database Example
We will port our user shuffle application ushuffle_db.py to both
SQLAlchemy and
SQLObject below. MySQL will be the backend database server for
both. You will note that
we implement these as classes because there is more of an object
"feel" to using ORMs as
opposed to using raw SQL in a database adapter. Both examples
import the set of NAMES
and the random name chooser from ushuffle_db.py.
This is to avoid copying-and-pasting the same code everywhere as
code reuse is a good thing.
SQLAlchemy
We start with SQLAlchemy because its interface is somewhat closer
to SQL than
SQLObject's interface. SQLAlchemy abstracts really well to the
object world but does give
you more flexibility in issuing SQL if you have to. You will find both
of these ORMs
(Examples 21.2 and 21.3) very similar in terms of setup
and access, as well as being of similar size, and both shorter than
ushuffle_db.py (including
the sharing of the names list and generator used to randomly iterate
through that list).

Most of the common databases out there along with working Python
modules and

packages that serve as adapters to those database systems. Note


that not all adapters are DB-
API- compliant.

Database-Related Modules and Websites


Name Online Reference or Description
Databases
psycopg http://initd.org/projects/psycopg1
psycopg2 http://initd.org/software/initd/psycopg/
PyPgSQL http://pypgsql.sf.net
PyGreSQL http://pygresql.org
pysqlite http://initd.org/projects/pysqlite
sqlite3
sdb http://dev.mysql.com/downloads/maxdb/7.6.00.html#Python
sapdb http://sapdb.org/sapdbPython.html
KInterbasDB http://kinterbasdb.sf.net
pymssql http://pymssql.sf.net (requires FreeTDS [http://freetds.org])
adodbapi http://adodbapi.sf.net
sybase http://object-craft.com.au/projects/sybase
cx_Oracle http://starship.python.net/crew/atuining/cx_Oracle
DCOracle2 http://zope.org/Members/matt/dco2 (older, for Oracle8
only)
Ingres DBI http://ingres.com/products/
Prod_Download_Python_DBI.html
ingmod http://www.informatik.uni-rostock.de/~hme/software/
ORMs
SQLObject http://sqlobject.org
SQLAlchemy http://sqlalchemy.org
PyDO/PyDO2 http://skunkweb.sf.net/pydo.html

You might also like