Downloaded 14 times
![Example import MySQLdb def insertEmp(str_name,str_pwd): db=MySQLdb.connect( 'localhost' ,' root' , ' ' , 'pydb' ) cursor=db.cursor() cursor.execute("insert into tbl_login (vchr_login_name,vchr_pwd) values(%s,%s);",(str_name,str_pwd)) db.commit() cursor.execute("select * from tbl_login") result=cursor.fetchall() db.close() print "loginidtnametpassword" for row in result: login=row[0] name=row[1] password=row[2] print "%dt%st%s"%(login,name,password) str_name=raw_input("enter user name : ") str_pwd=raw_input("Enter password : ") insertEmp(str_name,str_pwd)](https://image.slidesharecdn.com/databaseconnectivityinpython-150220233937-conversion-gate02/75/Database-connectivity-in-python-12-2048.jpg)
Database connectivity in python
This document discusses connecting Python programs to databases. It covers: 1. Importing database modules for MySQL and PostgreSQL. 2. Establishing a connection to the database using connect() and handling connection errors. 3. Creating a cursor object to execute SQL commands. 4. Executing SQL queries like SELECT and INSERT using the cursor's execute() method. 5. Fetching data from the database using cursor methods like fetchall(), fetchmany(), and fetchone().
Database connectivity in python
- 2.
- 3. Database Connectivity ❏ PythonDatabase API supports a wide range of database servers: - GadFly - mySQL - MySQL - PostgreSQL - Microsoft SQL Server - Informix - Interbase - Oracle - Sybase
- 4. Step1: Database import ❏Import modules for database in your python program - MySQL - PostgreSQL import MySQLdb import psycopg2
- 5. Step1: Database import ❏Import modules for database in your python program - MySQL - PostgreSQL import MySQLdb import psycopg2 Traceback (most recent call last): File "test.py", line 3, in <module> import MySQLdb ImportError: No module named MySQLdb Error If the above error happens that means you have not installed the mysql/postgresql module for the python version you are using Solution : download it from http://sourceforge.net/projects/mysql-python
- 6. Step 2 –Establishing connection ❏ Open a database connection with your program using connect() – Mysql – PostgreSql db = MySQLdb.connect(user=‘root', password=‘root', host=‘localhost', database=‘pydb' ) db = psycopg2.connect(user=‘root', password=‘root', host=‘localhost', database=‘pydb' )
- 7. Step 2 –Establishing connection ❏ Open database connection – Mysql – PostgreSql db = MySQLdb.connect(user=‘root', password=‘root', host=‘localhost', database=‘pydb ) db = psycopg2.connect(user=‘root', password=‘root', host=‘localhost', database=‘pydb' ) the connect() constructor creates a connection to the MySQL server and returns a MySQLConnection object
- 8. 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. ❏ We can close the connection manually using connection.close() ❏ Mysql / PostgreSql cursor = db.cursor()
- 9. Step 4 -ExecuteSQL query We can execute the sql queries from python program using execute() method associated with cursor object . Examples - cursor.execute(“select * from tbl_login”) - cursor.execute(“select vchr_login_name from tbl_login where pk_int_login_id=%s”, x) - cursor.execute("select a from tbl where b=%s and c=%s", (x, y))
- 10. Step 4 -ExecuteSQL query ❏ Executes an SQL command against all parameter sequences or mappings found in the sequence sql - cursor.execute("insert into tbl_login (vchr_login_name,vchr_pwd) values(‘admin’ , ’admin123’)”) db.commit(); - cursor.executemany("insert into tbl_login (vchr_login_name,vchr_pwd) values(‘admin’ , ’admin123’),(‘raj’,’raj123’)”) db.commit();
- 11. 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
- 12. Example import MySQLdb def insertEmp(str_name,str_pwd): db=MySQLdb.connect('localhost' ,' root' , ' ' , 'pydb' ) cursor=db.cursor() cursor.execute("insert into tbl_login (vchr_login_name,vchr_pwd) values(%s,%s);",(str_name,str_pwd)) db.commit() cursor.execute("select * from tbl_login") result=cursor.fetchall() db.close() print "loginidtnametpassword" for row in result: login=row[0] name=row[1] password=row[2] print "%dt%st%s"%(login,name,password) str_name=raw_input("enter user name : ") str_pwd=raw_input("Enter password : ") insertEmp(str_name,str_pwd)