参考
http://www.cnblogs.com/fnng/p/3565912.html
一 模块
-
mysql-connector-python：是MySQL官方的纯Python驱动；
-
MySQL-python：是封装了MySQL C驱动的Python驱动。
可以把两个都装上，使用的时候再决定用哪个：
$ pip install mysql-connector-python pip install MySQL-python
或者 使用yum安装(包名我忘记了 可以是使用yum search mysql去找)
yum install MySQL-python
测试是否按照好的驱动
|
1
2
3
4
5
6
7
8
9
10
11
|
#!/usr/bin/env python
#coding:utf-8
import
sys
try
:
import
MySQLdb
print
"MySQL python drivier is ok!"
except
Exception, e:
print
e
sys.exit(
1
)
finally
:
sys.exit(
1
)
|
二、数据库中的表
show create table net_data; 可以查看
|
1
2
3
4
5
6
7
8
9
10
|
CREATE
TABLE
`net_data` (
`_id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`create_date` datetime
NOT
NULL
,
`province`
varchar
(64)
NOT
NULL
,
`city`
varchar
(64)
NOT
NULL
,
`net_type` enum(
'CTC'
,
'CNC'
,
'CMC'
,
'JK'
)
NOT
NULL
,
`med`
float
NOT
NULL
,
`loss`
float
NOT
NULL
,
PRIMARY
KEY
(`_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15
DEFAULT
CHARSET=utf8;
|
三 先看插入的实例吧
作用，连接数据库插入一条数据
数据库插入一条数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
import
MySQLdb
....
....
def
insert_into_mysql(info):
conn
=
MySQLdb.Connect(
host
=
'localhost'
,
port
=
3306
,
user
=
'root'
,
passwd
=
'12qwaszx'
,
db
=
'netmap'
,
charset
=
'utf8'
,
)
cur
=
conn.cursor()
sqli
=
"insert into net_data (create_date,province,city,net_type,med,loss) values(%s,%s,%s,%s,%s,%s)"
cur.execute(sqli,(datetime.now(),
'上海'
,
'上海'
,
'CTC'
,
'23'
,
'0'
))
conn.commit()
cur.close()
conn.close()
|
同时插入多条数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
#info type is list 列表
def
insert_into_mysql(info):
conn
=
MySQLdb.Connect(
host
=
'localhost'
,
port
=
3306
,
user
=
'root'
,
passwd
=
'12qwaszx'
,
db
=
'netmap'
,
charset
=
'utf8'
,
)
cur
=
conn.cursor()
sqli
=
"insert into net_data (create_date,province,city,net_type,med,loss) values(%s,%s,%s,%s,%s,%s)"
cur.executemany(sqli,(info))
conn.commit()
cur.close()
conn.close()
|
四、查询实例(完成程序的一部分，完成的是一个统计报表发送的邮件的程序)
|
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
36
37
38
39
40
41
42
43
44
45
46
|
#!/usr/bin/env python
#encoding=utf8
#by cuizhiliang at 2016-04-29
from
datetime
import
datetime, timedelta
import
time
from
jinja2
import
Template
from
sendmail
import
SendHtmlEmail
import
MySQLdb
import
os,sys
reload
(sys)
sys.setdefaultencoding(
"utf-8"
)
g
=
{
'host'
:
'127.0.0.1'
,
'port'
:
3306
,
'user'
:
'zabbix'
,
'password'
:
'zabbix'
,
'db'
:
'zabbix'
,
}
now
=
datetime.now()
yesterday
=
now
-
timedelta(
1
)
yest_clock
=
(datetime.now()
-
timedelta(
1
)).strftime(
'%s'
)
data
=
{}
def
select_mysql(cmd):
conn
=
MySQLdb.Connect(
host
=
g[
'host'
],
port
=
g[
'port'
],
user
=
g[
'user'
],
passwd
=
g[
'password'
],
db
=
g[
'db'
],
charset
=
'utf8'
,
)
#print cmd
cur
=
conn.cursor()
cur.execute(cmd)
res
=
cur.fetchall()
return
res
#zabbix 上用户列表
def
get_user_info():
cmd
=
"select userid, alias, name, type from users;"
user_list
=
[]
user_list
=
list
(select_mysql(cmd))
print
user_list
return
user_list
if
__name__
=
=
"__main__"
:
data[
"user"
]
=
get_user_info()
|
五、注意:
1 插入数据库中文乱码 连接数据的字符集 一定要设定
|
1
|
charset
=
'utf8'
|
2 不惯数据库中的字段是datetime类型还是int类型，在插入的时候都传为%s 字符串类型，类型对我们是透明了，模块会自动处理类型！
3 模块在mac中可能会出错 MySQLdb
报错:
Traceback (most recent call last):
File "ping_info.py", line 12, in <module>
import MySQLdb
File "/Library/Python/2.7/site-packages/MySQLdb/__init__.py", line 19, in <module>
import _mysql
ImportError: dlopen(/Library/Python/2.7/site-packages/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib
Referenced from: /Library/Python/2.7/site-packages/_mysql.so
Reason: image not found
解决方法，因为是C语言开发驱动，所有依赖mysql客户端模块
sudo find / -name "libmysqlclient.18.dylib"
/usr/local/mysql-5.6.24-osx10.8-x86_64/lib/libmysqlclient.18.dylib
sudo cp /usr/local/mysql-5.6.24-osx10.8-x86_64/lib/libmysqlclient.18.dylib /usr/lib
本文转自残剑博客51CTO博客，原文链接http://blog.51cto.com/cuidehua/1764587如需转载请自行联系原作者
cuizhiliang
