Mysql Cookbook学习笔记第二章
时间：2022-03-10 16:58
1，使用python链接mysql
+ View Code?
# -*- 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?
# -*- 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?
# -*- 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?
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?
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?
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?
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?
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?
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?
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?
# -*- 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
Mysql Cookbook学习笔记第二章,gxlsystem
