参考
:http://www.liaoxuefeng.com/wiki/001374738125095c955c1e6d8bb493182103fac9270762a000/001391435131816c6a377e100ec4d43b3fc9145f3bb8056000
http://www.cnblogs.com/fnng/p/3565912.html
一 模块
可以把两个都装上,使用的时候再决定用哪个:
$ 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
|
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
|
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
|
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' ,
)
cur = conn.cursor()
cur.execute(cmd)
res = cur.fetchall()
return res
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 插入数据库中文乱码 连接数据的字符集 一定要设定
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