Python database (MySQL) operations

First, the SOFTWARE environment

The Python environment has sqlite3 installed by default, and if you need to use sqlite3 we can import the module directly at the top of the Python code module using the import sqlite3. This article I was documenting the python operation MySQL database, MySQL database download

Since I installed Wampserver, I installed the 3 environments of PHP, MySQL and Apache by default, so I did not install the MySQL database separately, just downloaded a MySQL management tool navicat for MySQL. When using Navicat for MySQL connection to the local MySQL database, a connection failure occurred, after the online query that the MySQL service did not start, how to start the MySQL service, see how to start a separate wamp in the MySQL. In fact, it is very simple to open the command line, select the directory to the MySQL database bin directory, execute the mysqld command.

The difference between MySQL and mysqld:

MYSQLD is a service-side program
MySQL is the command line client program

Second, the database operation

Python operation MySQL database, new, modify and delete is relatively simple, is to execute a simple SQL statement. Queries need to use Fetchone and fetchall to get data entries in addition to executing SQL statements

1 #-*-coding:utf-8-*-2 3 __author__='Administrator'4 5 ImportMySQLdb6 7 #Open a database connection8db = MySQLdb.connect ("localhost","Root","123456","Test" )9 Ten #get an operation cursor using the cursor () method Onecursor =db.cursor () A  - #executing SQL statements using the Execute method -Cursor.execute ("SELECT VERSION ()") the  - #Use the Fetchone () method to get a database.  -data =Cursor.fetchone () -  + Print "Database version:%s"%Data -  + #If the data table already exists, use the Execute () method to delete the table.  ACursor.execute ("DROP TABLE IF EXISTS EMPLOYEE") at  - #Create a data table SQL statement -sql ="""CREATE TABLE EMPLOYEE ( - first_name CHAR () not NULL, - last_name CHAR (+), - Age INT, in SEX CHAR (1), - INCOME FLOAT)""" to  + cursor.execute (SQL) -  the #SQL INSERT statement *sql ="""INSERT into EMPLOYEE (first_name, $ last_name, age, SEX, INCOME)Panax Notoginseng VALUES (' Mac ', ' Mohan ', ', ' M ', ')""" - Try: the    #Execute SQL statement + cursor.execute (SQL) A    #commit to database execution the Db.commit () + except: -    #Rollback In case there was any error $ Db.rollback () $  - #SQL Query Statements -sql ="SELECT * from EMPLOYEE the WHERE INCOME > '%d '"% (1000) - Try:Wuyi    #Execute SQL statement the cursor.execute (SQL) -    #get list of all records WuResults =Cursor.fetchall () -     forRowinchResults: AboutFName =Row[0] $LName = row[1] -Age = Row[2] -Sex = row[3] -Income = Row[4] A       #Print Results +       Print "fname=%s,lname=%s,age=%d,sex=%s,income=%d"%  the (fname, lname, age, sex, income) - except: $    Print "error:unable to FECTH data" the  the #To close a database connection theDb.close ()

Iii. Business

Transaction mechanisms ensure data consistency.

A transaction should have 4 properties: atomicity, consistency, isolation, persistence. These four properties are often called acid properties.

• Atomicity (atomicity). A transaction is an inseparable unit of work, and the operations included in the transaction are either done or not.
• Consistency (consistency). The transaction must be to change the database from one consistency state to another. Consistency is closely related to atomicity.
• Isolation (Isolation). Execution of one transaction cannot be disturbed by other transactions. That is, the operations inside a transaction and the data used are isolated from other transactions that are concurrently executing, and cannot interfere with each other concurrently.
• Persistence (durability). Persistence, also known as permanence (permanence), refers to the fact that once a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on it.

The transaction for Python DB API 2.0 provides two methods of commit or rollback.

1 #SQL Delete Record statement2sql ="DELETE from the EMPLOYEE WHERE age > '%d '"% (20)3 Try:4    #Execute SQL statement5 cursor.execute (SQL)6    #Submit to Database7 Db.commit ()8 except:9    #Roll Back when an error occursTenDb.rollback ()

For a database that supports transactions, in Python database programming, when the cursor is established, an invisible database transaction is automatically started. Commit () method all the update operations of the cursor, the rollback () method rolls back all operations of the current cursor. Each method starts a new transaction.

Python database (MySQL) operations