Python Connection Database Learning Db-api detailed

Before the Python Db-api, the application interface between the databases was very confusing and the implementations were different. If the project needs to replace the database, you need to make a lot of changes, very inconvenient. The advent of Python Db-api is to solve this problem. This article mainly introduced the Python connection database Db-api the related data, needs the friend to refer.

Objective

As you know, in Python, if you want to connect to a database, whether it's MySQL, SQL Server, PostgreSQL, or SQLite, it's all about using cursors, so you have to learn Python db-api.

All of Python's database interface programs adhere to the Python DB-API specification to some extent. DB-API defines a set of required objects and database access methods to provide a consistent provider for a variety of underlying database systems and a wide variety of database interface programs. Because DB-API provides a consistent access interface for different databases, porting code between different databases becomes a breeze.

Python Connection Database process:

Create a connection connection using connect

The Connect method generates a Connect object that we use to access the database. Modules that conform to the standard will implement the Connect method.

The parameters of the Connect function are as follows:

• User Username

• Password Password

• Host Hostname

• Database Database name

• DSN Data Source Name

Database connection parameters can be provided as a DSN string, example: Connect (dsn= ' host:mydb ', user= ' root ', password= ')
Of course, different database interface programs may differ, not all strictly by specification, for example, MYSQLDB uses the DB parameter rather than the canonical recommended database parameter to represent the databases to be accessed:

MySQLdb parameters available when connecting

• Host: the database hostname. By default, the local host

• User: Database login name. The default is the current user

• PASSWD: The Secret of database landing. Default is Empty

• DB: The name of the database to use. No default value

• The TCP port used by the Port:mysql service. Default is 3306

• CharSet: Database encoding

PSYCOPG2 parameters available when connecting:

• dbname– database name (DSN connection mode)

• database– database name

• user– User Name

• password– Password

• host– server address (if you do not provide a default connection to UNIX sockets)

• port– connection port (default 5432)

The Connect object has the following methods:

• Close (): Closes this Connect object and cannot be manipulated until the connection is created again

• Commit (): commits the current transaction, if the database that supports the transaction does not have a commit after adding or removing changes, the database is rolled back by default

• Rollback (): Cancels the current transaction

• Cursor (): Creating a Cursor Object

To create a cursor object using the cursor

The cursor cursor object has the following properties and methods:

Common methods:

• Close (): Close this Cursor object

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

• Fetchmany ([size = Cursor.arraysize]): Gets the next few rows of the result set

• Fetchall (): Get all the rows left in the result set

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

• Excutemany (sql, args): Execute multiple database queries or commands

Common Properties:

• Connection: Creating a database connection for this cursor object

• ArraySize: Use the Fetchmany () method to remove the number of records at one time, default to 1

• Lastrowid: Equivalent to PHP last_inset_id ()

Other methods:

• __ITER__ (): Create an object that can be iterated (optional)

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

• Nextset (): Move to the next result set (if supported)

• Callproc (Func[,args]): Call a stored procedure

• Setinputsizes (sizes): Set input maximum (must have, but the implementation is optional)

• Setoutputsizes (Sizes[,col]): Sets the maximum buffer size for large-row fetch

Other properties:

• Description: Returns the cursor Active state (tuple with 7 elements): (name, Type_code, Display_size, internal_size, precision, scale, NULL_OK) only name and type _cose is required.

• ROWCOUNT: Number of rows created or affected by the most recent execute ()

• Messages: Information tuple returned by the database after cursor execution (optional)

• RowNumber: The index of the row in the current result set (the starting line number is 0)

Db-api only in the error definition

The hierarchical relationship of the error class:

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

Database Operations 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

# Connect to the database
db_conn = MySQLdb.connect (host = 'localhost', user = 'root', passwd = '123456')

# If you have created a database, you can directly connect to the database as follows
#db_conn = MySQLdb.connect (host = "localhost", user = "root", passwd = "123456", db = "testdb")

"" "
Common parameters of the connect method:
 host: the database host name. The default is to use the local host
 user: database login name. The default is the current user
 passwd: database login secret. Default is empty
 db: database name to use. No default
 port: The TCP port used by the MySQL service. The default is 3306.
 charset: database encoding
"" "

# Get operation cursor
cursor = db_conn.cursor ()

# Execute SQL statement using execute method
cursor.execute ("SELECT VERSION ()")

# Use fetchone method to get a database.
dbversion = cursor.fetchone ()

print "Database version:% s"% dbversion

# Create database
cursor.execute ("create database if not exists dbtest")

# Select the database to be operated
db_conn.select_db ('dbtest');

# Create a data table SQL statement
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 is the base class for all exceptions, which means catching all exceptions
 print "Error to create table:", e

# Insert data
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:
 # Clear the data in the table
 cursor.execute ("delete from employee")
 # Execute sql insert statement
 for employee in employees:
  cursor.execute (sql% (employee ["first_name"], \
   employee ["last_name"], \
   employee ["age"], \
   employee ["sex"], \
   employee ["income"]))
 # Submit to the database for execution
 db_conn.commit ()
 # For databases that support transactions, in Python database programming,
 # When the cursor is established, it automatically starts an invisible database transaction.
 # Use the commit method to commit things
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 is a read-only property and returns the number of rows affected by the execute () method.

# Database query operation:
# fetchone () get the next line of the result set
# fetchmany ([size = cursor.arraysize]) get the next few rows of the result set
# fetchall () returns all the remaining rows in the result set
try:
 # Run SQL
 cursor.execute ("select * from employee")

 # Get a row of records
 rs = cursor.fetchone ()
 print rs

 # Get the 2 records in the remaining records
 rs = cursor.fetchmany (2)
 print rs

 # Get all remaining records
 ars = cursor.fetchall ()
 for rs in ars:
  print rs
 # Can use fetchall to get all records and then iterate
except Exception, e:
 print "Error to select:", e

# Database update operation
sql = "UPDATE employee SET age = age + 1 WHERE sex = '% c'"% ('M')
try:
 # Execute SQL statement
 cursor.execute (sql)
 # Submit to the database for execution
 db_conn.commit ()
 cursor.execute ("select * from employee")
 ars = cursor.fetchall ()
 print "After update: ------"
 for rs in ars:
  print rs
except Exception, e:
 # Rollback on error
 print "Error to update:", e
 db.rollback ()

# Close the database connection
db_conn.close ()