DB-API of Python connection database learning

Before the PythonDB-API is available, the application interfaces of different databases are messy and their implementations are different. If the project needs to change the database, a lot of modifications are required, which is inconvenient. The emergence of PythonDB-API is to solve this problem. This article mainly introduces the Python connection database DB-API related information, need friends can refer. Prior to the absence of a Python DB-API, the application interfaces between databases were very messy, with different implementations. If the project needs to change the database, a lot of modifications are required, which is inconvenient. The emergence of the Python DB-API is to solve this problem. This article mainly introduces the Python connection database DB-API related information, need friends can refer.

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:

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 @ 10:44:34 # Description @#************************* * ********************************** import MySQLdb # Script starts from here # connect to the database db_conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000') # If you have created a database, You can directly connect to the database using the following method # db_conn = MySQLdb. connect (host = "localhost", user = "root", passwd = "123456", db = "testdb") "common parameters of the connect method: host: database host name. the local host user is used by default: database login name. the default is the current user passwd: password for database login. empty db by default: name of the database to be used. no default port: the TCP port used by the MySQL service. the default value is 3306 charset: database code "" # obtain operation cursor = db_conn.cursor () # execute SQL sentence cursor.exe cute ("SELECT VERSION ()") using execute statement # Use the fetchone method to obtain a database. Dbversion = cursor. fetchone () print "Database version: % s" % dbversion # create data database cursor.exe cute ("create Database if not exists dbtest") # select the database db_conn.select_db ('dbtest') to operate '); # 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.exe cute (SQL) failed t Exception, e: # Exception is the base class of all exceptions. here, all exceptions are captured. 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, "s Ex ":" 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 table data cursor.exe cute ("delete from employee") # execute the SQL INSERT statement for employee in employees: cursor.exe cute (SQL % (employ Ee ["first_name"], \ employee ["last_name"], \ employee ["age"], \ employee ["sex"], \ employee ["income"]) # commit to the database to execute db_conn.commit () # for databases that support transactions, in Python database programming, # when the cursor is set up, an invisible database transaction is automatically started. # Use the commit method to submit a transaction T 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 attribute and returns the number of rows affected by the execution of execute (method .) # Database query operation: # The next row of the result set obtained by fetchone () # fetchiterator ([size = cursor. arraysize]) obtain the following rows of the result set # fetchall () all remaining rows in the returned result set try: # execute SQL cursor.exe cute ("select * from employee ") # retrieve a row of records rs = cursor. fetchone () print rs # obtain the two rows of record rs = cursor. fetchiterator (2) print rs # get all the remaining records ars = cursor. fetchall () for rs in ars: print rs # All records can be obtained using fetchall, and then the records T Exception, e: print "Error to select :", e # database UPDATE operation SQL = "UPDATE employee SET age = age + 1 WHERE sex = '% c'" % ('M') try: # run the SQL statement cursor.exe cute (SQL) # submit it to the database and run db_conn.commit () cursor.exe cute ("select * from employee") ars = cursor. fetchall () print "After update: ------" for rs in ars: print rsexcept Exception, e: # roll back print "Error to update:", e db. rollback () # Close database connection db_conn.close ()

More Python connection database learning DB-API details related articles please pay attention to PHP Chinese network!