Python MySQL

Python Python DB-APIPython DB-API

Python

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase

PythonAPI

DB APIOracleMysqlOracleMySQL

DB-API . ,

PythonDB-API

Python DB-API

• API
• SQL

MySQLdb?

MySQLdb PythonMysql Python API V2.0 MySQL C API

MySQLdb?

DB-APIMySQLMySQL

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

MySQLdb

Traceback (most recent call last):
  File "test.py", line 3, in <module>
    import MySQLdb
ImportError: No module named MySQLdb

MySQLdb http://sourceforge.net/projects/mysql-python (Linuxhttps://pypi.python.org/pypi/MySQL-python)

MySQLdb:

$ gunzip MySQL-python-1.2.2.tar.gz
$ tar -xvf MySQL-python-1.2.2.tar
$ cd MySQL-python-1.2.2
$ python setup.py build
$ python setup.py install

root

• TESTDB.
• TESTDB EMPLOYEE
• EMPLOYEE FIRST_NAME, LAST_NAME, AGE, SEX INCOME
• TESTDB "testuser" "test123",rootMysqlGrant
• Python MySQLdb
• sql SQL

MysqlTESTDB

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# cursor() 
cursor = db.cursor()

# executeSQL
cursor.execute("SELECT VERSION()")

#  fetchone() 
data = cursor.fetchone()

print "Database version : %s " % data

# 
db.close()

Database version : 5.0.45

execute()EMPLOYEE

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# cursor() 
cursor = db.cursor()

#  execute() 
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

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

cursor.execute(sql)

# 
db.close()

SQL INSERT EMPLOYEE

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# cursor() 
cursor = db.cursor()

# SQL 
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # sql
   cursor.execute(sql)
   # 
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# 
db.close()

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# cursor() 
cursor = db.cursor()

# SQL 
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s, %s, %s, %s, %s )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # sql
   cursor.execute(sql)
   # 
   db.commit()
except:
   # 
   db.rollback()

# 
db.close()

SQL:

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values(%s, %s)' % \
             (user_id, password))
..................................

PythonMysql fetchone() , fetchall()

• fetchone():
• fetchall():.
• rowcount: execute()

EMPLOYEEsalary1000

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# cursor() 
cursor = db.cursor()

# SQL 
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   # SQL
   cursor.execute(sql)
   # 
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # 
      print "fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fetch data"

# 
db.close()

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

EMPLOYEE SEX 'M' AGE 1

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# cursor() 
cursor = db.cursor()

# SQL 
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # SQL
   cursor.execute(sql)
   # 
   db.commit()
except:
   # 
   db.rollback()

# 
db.close()

EMPLOYEE AGE 20

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# cursor() 
cursor = db.cursor()

# SQL 
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # SQL
   cursor.execute(sql)
   # 
   db.commit()
except:
   # 
   db.rollback()

# 
db.close()

4ACID

• atomicity
• consistency
• isolation
• durabilitypermanence

Python DB API 2.0 commit rollback

# SQL
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # SQL
   cursor.execute(sql)
   # 
   db.commit()
except:
   # 
   db.rollback()

Python

commit()rollback

DB API: