Python access to the database

1. Python DB API Introduction Python DB API Python access to the database's unified interface specification, detailed referencehttps://www.python.org/dev/peps/pep-0249/The Python DB API consists of three important object database connection object connection, the database interaction object cursor and the database exception class Exceptions2. Process 3 for accessing the database using the Python DB API. Configuration of the Python+mysql development environmentPython-mysql connector is used for Python and MySQL server connection, https://sourceforge.net/projects/mysql-python/ Syslog is a MySQL visual management tool that HTTPS://SQLYOG.EN.SOFTONIC.COM/4 connection objects

Use Method Mysqldb.connection (Host,port,user,passwd,db,charset) to return a connection object

The methods supported by the Connection object are

Method	Description
Cursor ()	Cursors created and returned using the connection
Commit ()	Commit the current transaction
Rollback ()	Rolling back the current transaction
Close ()	Close connection

connecting to a database

1 ImportMySQLdb2conn = MySQLdb.connect (host ='127.0.0.1',3Port = 3306,4user ='Root',5passwd ='123456',6db ='Test',7charset='UTF8')8Cur =conn.cursor ()9 cur.close ()TenConn.close ()

5. Database Cursor Object Cursorcursor object method Execute (Op[,args]) executes a database query and command Fetchone () takes the next row in the result set Fetchmany (size) Gets the next few rows in the result set Fetchall () Get all remaining rows of the result set rowcount the number of rows or rows affected by the last execute of the data close () Closes the cursor object we built a test database in the database where a user table was created as shown in Use the cursor object to execute a simple query statement

1 ImportMySQLdb2conn = MySQLdb.connect (host ='127.0.0.1',3Port = 3306,4user ='Root',5passwd ='123456',6db ='Test',7charset='UTF8')8Cur =conn.cursor ()9sql ='SELECT * from user'Ten cur.execute (SQL) One PrintCur.rowcount A PrintCur.fetchone () - PrintCur.fetchmany (3) - PrintCur.fetchall () the cur.close () -Conn.close ()

Output

9 (1L, U ' name1 ') ((2L, U ' name2 '), (3L, U ' Name3 '), (4L, U ' name4 ') (5L, U ' name5 '), (6L, U ' Name6 '), (7L, U ' Name7 '), (8L , u ' Name8 '), (9L, U ' name9 ')) 6. Transaction processingTransactions: A program execution unit that accesses and updates a database
Atomicity: All operations included in a transaction are either done or not consistent: The transaction must change the database from a consistent state to another consistent state isolation: The execution of a transaction cannot be persisted by other transactions: Once a transaction is committed, its changes to the database are permanent. How do I use transactions in development? Turn off automatic commit: Set Conn.autocommit (False) normal end transaction: Conn.commit () exception End transaction: Conn.rollback ()
code example

1 ImportMySQLdb2conn = MySQLdb.connect (host ='127.0.0.1',3Port = 3306,4user ='Root',5passwd ='123456',6db ='Test',7charset='UTF8')8Cur =conn.cursor ()9Sql_insert ="INSERT INTO User (Usrid, Usrname) VALUES (, ' name10 ')"TenSql_delete ="Delete from user where usrid<3" OneSql_update ="Update user Set usrname = ' name91 ' where usrid=9" A Try: - Cur.execute (Sql_insert) - Cur.execute (sql_update) the Cur.execute (sql_delete) - Conn.commit () - exceptException as E: -     Printe + Conn.rollback () - cur.close () +Conn.close ()

7. Examples of bank transfers

Assuming that Zhang is transferring 100 yuan to Harry, the transfer process is as shown

Code implementation

1 ImportMySQLdb2 defcheckaccountavailable (conn,username):3Cur =conn.cursor ()4sql ="SELECT * from account where username= '%s '"%username5     PrintSQL6 cur.execute (SQL)7R =Cur.rowcount8     PrintR9 cur.close ()Ten     returnR One     A defAccount (conn, username): -Cur=conn.cursor () -sql ="SELECT * from account where username= '%s '"%username the     PrintSQL - cur.execute (SQL) -account = Cur.fetchone () [1] - Cur.close +     return Account - defMain (): +conn = MySQLdb.connect (host ='127.0.0.1', APort = 3306, atuser ='Root', -passwd ='123456', -db ='Test', -charset='UTF8') -     ifCheckaccountavailable (Conn,'Zhangsan') andCheckaccountavailable (Conn,'Wangwu'): -         ifAccount (Conn,"Zhangsan") >= 100: in             Try: -Cur =conn.cursor () toCur.execute ("Update account set account=account-100 where Username= ' Zhangsan '") +Cur.execute ("Update account set account=account+100 where Username= ' Wangwu '") - Conn.commit () the             exceptException as E: *                 Printe $ Conn.rollback ()Panax Notoginseng             finally: - cur.close () the         Else: +             Print "Zhangsan have not enough money" A     Else: the         Print "Account Not existed" +          - conn.close () $Main ()

  

 

Null

Python access to the database