Python Connection database learning DB-API details, pythondb-api

Python Connection database learning DB-API details, pythondb-api

Preface

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

All of Python's database interface programs comply with the Python DB-API specifications to a certain extent. DB-API defines a series of required objects and database access methods to provide consistent access interfaces for various underlying database systems and a variety of database interface programs. Since DB-API provides consistent access interfaces for different databases, porting code between different databases is easy.

Python database connection process:

Use connect to create a connection

The connect method generates a connect object through which we can access the database. All modules that comply with 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 in the form of a DSN string, for example: connect (dsn = 'host: mydb', user = 'root', password = '')
Of course, there may be some differences between different database interface programs, not all implemented in strict accordance with the specifications. For example, MySQLdb uses the db parameter instead of the database parameter recommended by the standard to indicate the database to be accessed:

Available parameters for MySQLdb connection

• Host: specifies the Database host name. The local host is used by default.
• User: Database login name. The default value is the current user.
• Passwd: Password for database login. Empty by default
• Db: name of the database to be used. No default value exists.
• Port: the TCP port used by the MySQL service. The default value is 3306.
• Charset: Database Encoding
  

Available parameters for psycopg2 connection:

• Dbname-Database Name (dsn connection mode)
• Database-database Name
• User-user Name
• Password-password
• Host-server address (if no default Unix Socket is provided)
• Port-connection port (5432 by default)
  

The connect object has the following methods:

• Close (): closes the connect object. After the connection is closed, the operation cannot be performed unless the connection is created again.
• Commit (): Submit the current transaction. If the database that supports the transaction does not have commit after addition, deletion, and modification, the database will be rolled back by default.
• Rollback (): cancels the current transaction
• Cursor (): Create a cursor object
  

Use cursor to create a cursor object

The cursor object has the following attributes and methods:

Common Methods:

• Close (): close this cursor object
• Fetchone (): Obtain the next row of the result set.
• Fetchmany ([size = cursor. arraysize]): obtain the following rows of the result set.
• Fetchall (): Get all the remaining rows in the result set.
• Excute (SQL [, args]): executes a database query or command
• Excutemany (SQL, args): execute multiple database queries or commands

Common attributes:

• Connection: creates a database connection for this cursor object.
• Arraysize: the number of records retrieved at a time using the fetchmany () method. The default value is 1.
• Lastrowid: equivalent to last_inset_id () of PHP ()
  

Other methods:

• _ Iter _ (): creates an iteratable object (optional)
• Next (): Obtain 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): sets the input maximum value (required, but the specific implementation is optional)
• Setoutputsizes (sizes [, col]): sets the maximum buffer size of the fetch column.
  

Other attributes:

• Description: returns the cursor activity status (tuples containing seven elements): (name, type_code, display_size, internal_size, precision, scale, null_ OK) only name and type_cose are required.
• Rowcount: number of rows created or affected by the last execute () Operation
• Messages: Information tuples returned by the database after the cursor is executed (optional)
• Rownumber: Index of the row in which the cursor is located in the current result set (the starting row number is 0)

Error definitions in DB-API only

Hierarchy 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

# 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 () 

Other reference documents:

Https://www.python.org/dev/peps/pep-0249/

Http://wiki.python.org/moin/DatabaseInterfaces

Summary

The above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.