Mysql Cookbook学习笔记第二章

1，使用python链接mysql

# -*- coding: utf-8 -*-
# connect.py --连接到MySQL服务器
import sys
import MySQLdb
try:
    conn = MySQLdb.connect(db = "cookbook",
                           host = "localhost",
                           user = "burness",
                           passwd = "123456")

    print "Connected"
except:
    print "Cannot connect to server"
    sys.exit(1)

conn.close()
print "Disconnected"

2，使用python操作过程中提示出错信息以便于调试

# -*- coding: utf-8 -*-
# connect.py --连接到MySQL服务器
import sys
import MySQLdb
try:
    conn = MySQLdb.connect(db = "cookbook",
                           host = "localhost",
                           user = "burness",
                           passwd = "123456")

    print "Connected"
except MySQLdb.Error, e:
    print "Cannot connect to server"
    print "Error code:", e.args[0]
    print "Error message:", e.args[1]
    sys.exit(1)

conn.close()
print "Disconnected"

例如：使用错误的密码：

3，编写库文件

库文件可以简化在程序中频繁使用配置参数，以及保证一些数据的隐秘性如密码

例如 Cookbook.py内保存有数据库连接的内容：

# -*- coding: utf-8 -*-
# Cookbook.py -具有通过MySQLdb模块连接MySQL的工具方法的库文件
import MySQLdb

host_name="localhost"
db_name="cookbook"
user_name="burness"
password="123456"



# 建立一个到cookbook数据库的连接，返回一个connection对象
# 如果不能建立连接则抛出一个异常。
def connect():
    return MySQLdb.connect(db=db_name,
                           host=host_name,
                           user=user_name,
                           passwd=password)

harness.py 测试Cookbook.py

import sys
import MySQLdb
import Cookbook
# 用来测试Cookbook.py
try:
    conn=Cookbook.connect()
    print "Connected"
except MySQLdb.Error, e:
    print "Cannot connect to serve"
    print "Error code:",e.args[0]
    print "Error message:",e.args[1]
    sys.exit(1)
conn.close()
print "Disconnected"

4,发起语句并检索结果

python 中MySQLdb使用cursor来进行execute的操作,不返回结果如update:

expand sourceview source
print?·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
01.import sys 
02.import MySQLdb 
03.import Cookbook 
04.# 用来测试Cookbook.py 
05.try: 
06.    conn=Cookbook.connect() 
07.    print "Connected"
08.except MySQLdb.Error, e: 
09.    print "Cannot connect to serve"
10.    print "Error code:",e.args[0] 
11.    print "Error message:",e.args[1] 
12.    sys.exit(1) 
13.# cursor=conn.cursor() 
14.# 使用行作为命名元素 
15.cursor = conn.cursor(MySQLdb.cursors.DictCursor) 
16.cursor.execute("select id, name, cats from profile") 
17.rows=cursor.fetchall() 
18.#for row in rows: 
19.#    print "id：%s, name: %s, cats: %s" % (row[0],row[1],row[2]) 
20.for row in rows: 
21.    print "id：%s, name: %s, cats: %s" % (row["id"],row["name"],row["cats"]) 
22.print "Number of rows returned: %d" % cursor.rowcount 
23.conn.close() 
24.print "Disconnected"

返回结果，如select

import sys
import MySQLdb
import Cookbook
# 用来测试Cookbook.py
try:
    conn=Cookbook.connect()
    print "Connected"
except MySQLdb.Error, e:
    print "Cannot connect to serve"
    print "Error code:",e.args[0]
    print "Error message:",e.args[1]
    sys.exit(1)
cursor=conn.cursor()
cursor.execute("select id, name, cats from profile")
while 1:
    row=cursor.fetchone()# fetchone用来顺序返回下一行
    if row==None:
        break
    print "id: %s, name: %s, cats: %s" %(row[0],row[1],row[2])
print "Number of rows returned: %d" % cursor.rowcount
conn.close()
print "Disconnected"

使用fetchall()可以一次返回整个满足条件的结果集

import sys
import MySQLdb
import Cookbook
# 用来测试Cookbook.py
try:
    conn=Cookbook.connect()
    print "Connected"
except MySQLdb.Error, e:
    print "Cannot connect to serve"
    print "Error code:",e.args[0]
    print "Error message:",e.args[1]
    sys.exit(1)
# cursor=conn.cursor()
# 使用行作为命名元素
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("select id, name, cats from profile")
rows=cursor.fetchall()
#for row in rows:
#    print "id：%s, name: %s, cats: %s" % (row[0],row[1],row[2])
for row in rows:
    print "id：%s, name: %s, cats: %s" % (row["id"],row["name"],row["cats"])
print "Number of rows returned: %d" % cursor.rowcount
conn.close()
print "Disconnected"

5,处理语句中的特殊字符和NULL值

占位符机制和引用：

python中可以使用格式化来进行占位符的使用

import sys
import MySQLdb
import Cookbook
# 用来测试Cookbook.py
try:
    conn=Cookbook.connect()
    print "Connected"
except MySQLdb.Error, e:
    print "Cannot connect to serve"
    print "Error code:",e.args[0]
    print "Error message:",e.args[1]
    sys.exit(1)
cursor = conn.cursor()
# 使用占位符来与mysql交互，python中支持格式化符号用来作为占位符
cursor.execute("""insert into profile (name,birth,color,foods,cats)values(%s,%s,%s,%s,%s)""",("De'Mont","1973-01-12",None,"eggroll",4))

print "Number of rows update: %d"%cursor.rowcount
cursor2=conn.cursor()
cursor2.execute("select * from profile")
rows=cursor2.fetchall()
for row in rows:
    print "id：%s, name: %s, cats: %s" % (row[0],row[1],row[2])
print "Number of rows returned: %d" % cursor2.rowcount
conn.close()
print "Disconnected"

另外一个方法是MySQLdb引用时使用literal()方法

import sys
import MySQLdb
import Cookbook
# 用来测试Cookbook.py
try:
    conn=Cookbook.connect()
    print "Connected"
except MySQLdb.Error, e:
    print "Cannot connect to serve"
    print "Error code:",e.args[0]
    print "Error message:",e.args[1]
    sys.exit(1)
# cursor=conn.cursor()
# 使用行作为命名元素
cursor = conn.cursor()
# 使用占位符来与mysql交互，python中支持格式化符号用来作为占位符
cursor.execute("""insert into profile (name,birth,color,foods,cats)
values(%s,%s,%s,%s,%s)"""%(conn.literal("123123123123"),conn.literal("1973-01-12"),conn.literal("123"),conn.literal("eggroll"),conn.literal(4)))
conn.commit()# 必须要有这个才能提交，才会有保存
print "Number of rows update: %d"%cursor.rowcount

conn.close()
print "Disconnected"

在实验代码的过程中发现上一个运行后在本地mysql数据库中没有保存，google之后发现必须在完成之后运行conn.commit()才能使更改保存！！！

6,识别结果集中的NULL值

python程序使用None来表示结果集中的NULL，代码如下：

import sys
import MySQLdb
import Cookbook
# 用来测试Cookbook.py
try:
    conn=Cookbook.connect()
    print "Connected"
except MySQLdb.Error, e:
    print "Cannot connect to serve"
    print "Error code:",e.args[0]
    print "Error message:",e.args[1]
    sys.exit(1)
# cursor=conn.cursor()
# 使用行作为命名元素
cursor = conn.cursor()
# 使用占位符来与mysql交互，python中支持格式化符号用来作为占位符
cursor.execute("select name, birth, foods from profile")
for row in cursor.fetchall():
    row=list(row)
    for i in range(0,len(row)):
        if row[i]==None:
            row[i]="NULL"
    print "name: %s, birth: %s, food: %s"%(row[0],row[1],row[2])
conn.close()
print "Disconnected"

7,获取连接参数的技术

a,将参数硬编码到程序中；b,交互式请求参数;c，从命令行获取参数；d，从执行环境获取参数；e，从一个独立的文件中获取参数

从命令行得到参数可以通过getopt.getopt，具体代码如下：

# -*- coding: cp936 -*-
#cmdline.py -- 说明Python中的命令行选项解析
import sys
import getopt
import MySQLdb
try:
    opts, args=getopt.getopt(sys.argv[1:],"h:p:u:",["host=","password=","user="])# h: p: u: 表示后面都带参数 如是hp：则说明h不带参数
    print opts

except getopt.error,e:
    #对于错误，输出程序名以及错误信息文本
    print "%s: %s" %(sys.argv[0],e)
    sys.exit(1)

host_name=password=user_name=""

for opt,arg in opts:
    if opt in ("-h","--host"):
        host_name=arg
    elif opt in ("-p","--password"):
        password=arg
    elif opt in ("-u","--user"):
        user_name=arg
#所有剩下的非选项参数都保留在args中，并可在这里做必要的处理
try:
    conn=MySQLdb.connect(db="cookbook",host=host_name,user=user_name,passwd=password)
    print "Connected"
except MySQLdb.Error,e:
    print "Cannot connect to server"
    print "Error:",e.args[1]
    print "Code:",e.args[0]
    sys.exit(1)

conn.close()
print "Disconnected"

从选项文件获取参数

Unix下有/etc/my.cnf,mysql安装目录的my.cnf,以及当前用户的~/.my.cnf（按系统查找顺序来），当存在多个时，最后发现的具有最高优先级而在windows下安装目录my.ini，windows根目录my.ini或者my.cnf