Bottle Framework Learning (eight) operation of MySQL database

MySQLdb is the interface for Python to connect to the Mysql database.

installation MySQLdb

[email protected] bottle]# Yum install Mysql-python–y

if using a compiled installation, download the installation package Https://pypi.python.org/pypi/MySQL-python here and execute the following command

[[email protected] bottle]# gunzip mysql-python-1.2.3.tar.gz[[email protected] bottle]# tar XF mysql-python-1.2.3.tar[[ Email protected] bottle]# CD Mysql-python-1.2.3[[email protected] bottle]# python setup.py build[[email protected] Bottle ]# python setup.py Install

Database connection

Directly on the code:

[email protected] bottle]# cat linkmysql.py#!/usr/bin/env python#coding=utf-8import mysqldb db =mysqldb.connect (" LocalHost "," root "," 123 "," Mytestdb ") #打开数据库连接cursor = Db.cursor () #使用cursor () method gets the operation cursor Cursor.execute (" Sele Ctversion () ") #使用execute方法执行SQL语句data = Cursor.fetchone () #使用fetchone () method to get a single piece of data. Print "Database version:%s"%datadb.close () #关闭数据库连接

Create a database table

[email protected] bottle]# cat create_table.py#!/usr/bin/env python#encoding=utf-8import mysqldb db =mysqldb.connect ( "LocalHost", "root", "123", "MYTESTDB") cursor = Db.cursor () cursor.execute ("DROP TABLE IF existsmytable") # If the MyTable table already exists, it is deleted using the Execute () method.  sql = "" "CREATE TABLE MYTABLE (first_name char () not NULL, last_name char (+), age INT, SEX CHAR (1), INCOME FLOAT) "" "Cursor.execute (SQL) Db.close ()

database insert operation

[[Email protected] bottle]# cat insert.py#!/usr/bin/env python#encoding=utf-8import  mysqldb db =mysqldb.connect ("localhost", "root", "123", "Mytestdb") Cursor = db.cursor () sql =  "" "Insert intomytable (First_name,        last_ Name, age, sex, income)         VALUES  (' Mac ',   ' Mohan ', 20,  ' M ',  2000) "" "  #上述sql语句也可以写成以下形式. #sql  =  "Insert intomytable (First_name, #      last_name,  age, sex, income)  #      VALUES  ('%s ',  '%s ',   '%d ',  '%c ',  '%d '   '  % #       (' Mac ',  ' Mohan ' , 20,  ' M ',  2000)  try:  cursor.execute (SQL)   db.commit ()       #提交到数据库执行except:   db.rollback ()      #如果执行错误则回滚cursor. Execute (SQL) Db.close () 

Database query Operations

Python Queries Mysql uses the Fetchone () method to get a single piece of data, using the Fetchall () method to get multiple data.

Fetchone (): This method gets the next query result set. The result set is an object

Fetchall (): receives all the returned result rows .

ROWCOUNT: This is a read-only property and returns the number of rows affected after the Execute () method is executed.

[[Email protected] bottle]# cat query.py#!/usr/bin/env python#encoding=utf-8import  mysqldb db =mysqldb.connect ("localhost", "root", "123", "Mytestdb") Cursor = db.cursor () sql =  "select * from mytable whereincome >  '%d '"  %  (1000)  try:  cursor.execute (SQL)   results = cursor.fetchall ()   #获取所有记录列表   for row in results:     fname = row[0]      lname = row[1]     age = row[2]      sex = row[3]     income = row[4]      #  printing Results      print  "fname=%s,lname=%s,age=%d,sex=%s, income=%d " %              (fname,  lname, age, sex, income ) except:  print  "Error: unable to fecth data"   Cursor.execute (SQL) Db.close ()

Database update operations

[email protected] bottle]# cat update.py#!/usr/bin/env python#encoding=utf-8import mysqldb db =mysqldb.connect (" LocalHost "," root "," 123 "," MYTESTDB ") cursor = db.cursor () sql =" UPDATE MYTABLE SET last_name = ' xiaoming ' WHERE INCOME = '%s ' "% (') ') try:cursor.execute (SQL) Db.commit () Except:db.rollback () cursor.execute (SQL) Db.close ()

Execution transactions

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 Python DB API 2.0 Transaction provides two methods of commit and rollback.

This blog is not related to the bottle framework itself, but the operation of the database is also necessary for web development, so with bottle put together.

This article is from "Gan nan has" blog, please be sure to keep this source http://changfei.blog.51cto.com/4848258/1664025

Bottle Framework Learning (eight) operation of MySQL database