DB-API of Python connection database learning

Source: Internet
Author: User
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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.