首页 > 代码库 > Mysql Cookbook学习笔记第二章

Mysql Cookbook学习笔记第二章

2024-07-02 02:56:45   232人阅读

1，使用python链接mysql

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# -*- 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操作过程中提示出错信息以便于调试

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# -*- 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内保存有数据库连接的内容：

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# -*- 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

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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:

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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()可以一次返回整个满足条件的结果集

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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中可以使用格式化来进行占位符的使用

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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()方法

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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，代码如下：

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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，具体代码如下：

+ View Code?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# -*- 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


联系
我们