Detailed explanation of DB-API for Python connection to database learning

Before the Python DB-API, the application interfaces between databases were very confusing and the implementations were different. If the project needs to replace the database, it will require a lot of modifications, which is very inconvenient. The emergence of Python DB-API is to solve such problems. This article mainly introduces the relevant information of DB-API for Python to connect to the database. Friends in need can refer to it.

Preface

Everyone knows that if you want to connect to a database in Python, whether it is MySQL, SQL Server, PostgreSQL or SQLite, use Cursors are always used, so you have to learn Python DB-API.

All Python database interface programs comply with the Python DB-API specification to a certain extent. DB-API defines a series of necessary objects and database access methods to provide consistent access interfaces for various underlying database systems and various database interface programs. Since DB-API provides a consistent access interface for different databases, porting code between different databases becomes an easy task.

Python connection database process:

##Create using connect connection connection

The parameters of the connect function are as follows:

• user Username

• password Password

• host Hostname

• database Database name

• dsn Data source name

  
  

Of course, different database interface programs may have some differences, and not all are implemented strictly in accordance with the specifications. For example, MySQLdb uses the db parameter instead of the database parameter recommended by the specification to indicate the database to be accessed:

Parameters available when connecting to MySQLdb

• host: database host name. The default is the local host

• user: database Login name. The default is the current user

• passwd: The secret of database login. The default is empty

• db: The database name to be used. None Default value

• port: TCP port used by MySQL service. The default is 3306

• charset: Database encoding

  
  

Parameters available when connecting to psycopg2:

##dbname – database name (dsn connection mode)
database – database name
user – username
password – password
host – Server address (if the default connection Unix Socket is not provided)
port – Connection port (default 5432)

• 
  

close(): Close this connect object. After closing, no further operations can be performed unless the connection is created again
commit(): Submit the current transaction. If the database supports transactions and there is no commit after adding, deleting or modifying, the database will rollback by default

• ##rollback( ): Cancel the current transaction

• cursor(): Create a cursor object

• 
  

cursor cursor object has the following properties and methods:

Common methods:

• fetchone(): Get the next row of the result set

• fetchmany([size = cursor.arraysize]): Get the next few rows of the result set

• fetchall(): Get all the remaining rows of the result set

• excute(sql[, args]): Execute a database query or command

• excutemany(sql, args):Execute multiple database queries or commands
Common properties:

• arraysize: How many records are fetched at one time using the fetchmany() method, the default is 1

• lastrowid: Equivalent to PHP's last_inset_id()

• 
  

Other methods:

##__iter__(): Create an iterable object (optional)

• next(): Get the next row of the result set (if iteration is supported)

• nextset(): Move to the next result set (if it is supported)

• callproc(func[,args]): Call a stored procedure

• setinputsizes(sizes): Set the maximum input value (must exist, but the specific implementation is Optional)

• setoutputsizes(sizes[,col]): Set the maximum buffer size for large column fetch
  

##Other properties:

• description: Returns the cursor activity status (tuple containing 7 elements): (name, type_code, display_size, internal_size, precision, scale, null_ok) only name and type_cose is required

• rowcount: The number of rows created or affected by the most recent execute()

• messages: The information returned by the database after the cursor is executed Tuple (optional)

• rownumber: The index of the row where the cursor is located in the current result set (the starting row number is 0)

Error definition in DB-API only

Hierarchical relationship of error classes:

StandardError
|__Warning
|__Error
|__InterfaceError
|__DatabaseError
|__DataError
|__OperationalError
|__IntegrityError
|__InternalError
|__ProgrammingError
|__NotSupportedError

Database operation example

The code is as follows:

#! /usr/bin/env python
# -*- coding: utf-8 -*-

# *************************************************************
#  Filename @ operatemysql.py
#  Author @ Huoty
# Create date @ 2015-08-16 10:44:34
# Description @ 
# *************************************************************

import MySQLdb

# Script starts from here

# 连接数据库
db_conn = MySQLdb.connect(host = 'localhost', user= 'root', passwd = '123456')

# 如果已经创建了数据库，可以直接用如下方式连接数据库
#db_conn = MySQLdb.connect(host = "localhost", user = "root",passwd = "123456", db = "testdb")

"""
connect方法常用参数:
 host: 数据库主机名.默认是用本地主机
 user: 数据库登陆名.默认是当前用户
 passwd: 数据库登陆的秘密.默认为空
 db: 要使用的数据库名.没有默认值
 port: MySQL服务使用的TCP端口.默认是3306
 charset: 数据库编码
"""

# 获取操作游标 
cursor = db_conn.cursor()

# 使用 execute 方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone 方法获取一条数据库。
dbversion = cursor.fetchone()

print "Database version : %s " % dbversion

# 创建数据库
cursor.execute("create database if not exists dbtest")

# 选择要操作的数据库
db_conn.select_db('dbtest');

# 创建数据表SQL语句
sql = """CREATE TABLE if not exists employee(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20),
   age INT, 
   sex CHAR(1),
   income FLOAT )"""

try:
 cursor.execute(sql)
except Exception, e:
 # Exception 是所有异常的基类，这里表示捕获所有的异常
 print "Error to create table:", e

# 插入数据
sql = """INSERT INTO employee(first_name,
   last_name, age, sex, income)
   VALUES ('%s', '%s', %d, '%s', %d)"""

# Sex: Male男, Female女

employees = ( 
  {"first_name": "Mac", "last_name": "Mohan", "age": 20, "sex": "M", "income": 2000},
  {"first_name": "Wei", "last_name": "Zhu", "age": 24, "sex": "M", "income": 7500},
  {"first_name": "Huoty", "last_name": "Kong", "age": 24, "sex": "M", "income": 8000},
  {"first_name": "Esenich", "last_name": "Lu", "age": 22, "sex": "F", "income": 3500},
  {"first_name": "Xmin", "last_name": "Yun", "age": 31, "sex": "F", "income": 9500},
  {"first_name": "Yxia", "last_name": "Fun", "age": 23, "sex": "M", "income": 3500}
  )

try:
 # 清空表中数据
 cursor.execute("delete from employee")
 # 执行 sql 插入语句
 for employee in employees:
  cursor.execute(sql % (employee["first_name"], \
   employee["last_name"], \
   employee["age"], \
   employee["sex"], \
   employee["income"]))
 # 提交到数据库执行
 db_conn.commit()
 # 对于支持事务的数据库， 在Python数据库编程中，
 # 当游标建立之时，就自动开始了一个隐形的数据库事务。
 # 用 commit 方法能够提交事物
except Exception, e:
 # Rollback in case there is any error
 print "Error to insert data:", e
 #b_conn.rollback()

print "Insert rowcount:", cursor.rowcount
# rowcount 是一个只读属性，并返回执行execute(方法后影响的行数。)

# 数据库查询操作:
# fetchone()  得到结果集的下一行 
# fetchmany([size=cursor.arraysize]) 得到结果集的下几行 
# fetchall()  返回结果集中剩下的所有行 
try:
 # 执行 SQL
 cursor.execute("select * from employee")

 # 获取一行记录
 rs = cursor.fetchone()
 print rs

 # 获取余下记录中的 2 行记录
 rs = cursor.fetchmany(2)
 print rs

 # 获取剩下的所有记录
 ars = cursor.fetchall()
 for rs in ars:
  print rs
 # 可以用 fetchall 获得所有记录，然后再遍历
except Exception, e:
 print "Error to select:", e

# 数据库更新操作
sql = "UPDATE employee SET age = age + 1 WHERE sex = '%c'" % ('M')
try:
 # 执行SQL语句
 cursor.execute(sql)
 # 提交到数据库执行
 db_conn.commit()
 cursor.execute("select * from employee")
 ars = cursor.fetchall()
 print "After update: ------"
 for rs in ars:
  print rs
except Exception, e:
 # 发生错误时回滚
 print "Error to update:", e
 db.rollback()

# 关闭数据库连接
db_conn.close()