Scribd
Upload
344 views

Pyton Book

This document provides an introduction to connecting Python to databases. It discusses how to import database modules for MySQL and PostgreSQL. It outlines the key steps to establish a connection which includes creating a cursor object. It also demonstrates how to execute SQL queries and fetch data using methods like fetchall(), fetchmany(), and fetchone(). An example at the end shows how to connect to a MySQL database, run a SELECT query, and print the results.

Uploaded by

optimistic_harish
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
344 views

Pyton Book

This document provides an introduction to connecting Python to databases. It discusses how to import database modules for MySQL and PostgreSQL. It outlines the key steps to establish a connection which includes creating a cursor object. It also demonstrates how to execute SQL queries and fetch data using methods like fetchall(), fetchmany(), and fetchone(). An example at the end shows how to connect to a MySQL database, run a SELECT query, and print the results.

Uploaded by

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

Introduction To Python

Python-Mysql Connectivity
Database Connectivity
• Python Database API supports a wide range of database servers:
– GadFly
– mySQL
– MySQL
– PostgreSQL
– Microsoft SQL Server 2000
– Informix
– Interbase
– Oracle
– Sybase
Step 1 – database import
• Import modules for database in your python program

Mysql
import MySQLdb

PostgreSql
import psycopg2
Step 1 – database import
• Import modules for database Error
Traceback (most recent call last):
File "test.py", line 3, in <module>
import MySQLdb
Mysql ImportError: No module named MySQLdb

import MySQLdb
If the above error happens that means
you have not installed the
PostgreSql mysql/postgresql module for the
python version you are using
import psycopg2
Solution : download it from
http://sourceforge.net/projects/mysql-
python
Step 2 – Establishing connection
• Open a database connection with your program using connect()
– Mysql
db = MySQLdb.connect(user=‘root', password=‘root', host=‘localhost', database=‘test' )

– PostgreSql
db = psycopg2.connect(user=‘root', password=‘root', host=‘localhost', database=‘test' )
Step 2 – Establishing connection
• Open database connection the connect() constructor creates a
connection to the MySQL server and
– Mysql returns a MySQLConnection object

db = MySQLdb.connect(user=‘root', password=‘root', host=‘localhost', database=‘test' )

– PostgreSql
db = psycopg2.connect(user=‘root', password=‘root', host=‘localhost', database=‘test' )
Step 3 – Creating cursor object
• We need to create the object of a class called cursor that allows
Python code to execute database command in a database session.
• Cursors are created by the connection.cursor() method: they are
bound to the connection for the entire lifetime and all the
commands are executed in the context of the database session
wrapped by the connection.
• Mysql / PostgreSql
cursor = db.cursor()
Step 4 -Execute SQL query
We can execute the sql queries from python program using execute() method
associated with cursor object . Examples
– cursor.execute("SELECT * from tbl_student“)
– cursor.execute("select a from tbl where b=? and c=?", x, y)
– cursor.execute("select a from tbl where b=? and c=?", (x, y))
Step 4 -Execute SQL query
• Executes an SQL command against all parameter sequences or
mappings found in the sequence sql
• purchases = [
('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'IBM', 500, 53.00)
]
• c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
Step 5 – Fetch data from database
• MySQLdb provides multiple ways to retrieve data such as:
– fetchall()
• Fetch all (remaining) rows of a query result, returning them as a sequence of
sequences (e.g. a list of tuples).
– fetchmany(size)
• Fetch the next set of rows of a query result, returning a sequence of sequences
(e.g. a list of tuples) .It will return number of rows that matches to the size
argument
– fetchone()
• Fetch the next row of a query result set, returning a single sequence,
or None when no more data is available
Example
Import MySQLdb
db = MySQLdb.connect(user=‘root', password=‘root', host=‘localhost', database=‘test' )
cursor = db.cursor()
cursor.execute(“select * from tbl_student where place= calicut”)
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
print "fname=%s,lname=%s,age=%d " % (fname, lname, age)
Questions?
“A good question deserve a good grade…”
Want to learn more about programming or Looking to become a good programmer?
Are you wasting time on searching so many contents online?
Do you want to learn things quickly?
Tired of spending huge amount of money to become a Software professional?

Do an online course
@ baabtra.com

We put industry standards to practice. Our structured, activity based courses are so designed
to make a quick, good software professional out of anybody who holds a passion for coding.
Follow us @ twitter.com/baabtra

Like us @ facebook.com/baabtra

Subscribe to us @ youtube.com/baabtra

Become a follower @ slideshare.net/BaabtraMentoringPartner

Connect to us @ in.linkedin.com/in/baabtra

Thanks in advance.
www.baabtra.com | www.massbaab.com |www.baabte.com
Contact Us

Emarald Mall (Big Bazar Building) NC Complex, Near Bus Stand


Mavoor Road, Kozhikode, Mukkam, Kozhikode,
Kerala, India. Kerala, India.
Ph: + 91 – 495 40 25 550 Ph: + 91 – 495 40 25 550

Cafit Square, Start up Village


Hilite Business Park, Eranakulam,
Near Pantheerankavu, Kerala, India.
Kozhikode Email: [email protected]
Ph:9895767088

You might also like