Python 操作mysql包有MySQLdb、SQLAlchemy等等、还有一个是mysql.connector,mysql官方包,详见( MySQL Connector/Python Developer Guide
)。
用法官方有示例: Chapter 5 Connector/Python Coding Examples
-
5.1 Connecting to MySQL Using Connector/Python
-
5.2 Creating Tables Using Connector/Python
-
5.3 Inserting Data Using Connector/Python
-
5.4 Querying Data Using Connector/Python
安装方法: easy_install mysql-connector-python
用法和MySQLdb基本类似,这应该是类似一种协议的东西吧?
查询的时候是支持查询字典的!
connor.connect(*args, **kwargs)
cursor = conn.cursor(dictionary=True)
附加是我自己写的mysql_helper.py
———–
1
# -*- coding: utf8 -*-
2
# @author: ‘zhangzhipeng’
3
# @date: ‘2015-04-10’
4
5
import
logging
6
7
import
mysql.connector as
connor
8
9
10connor.connect()
11
12
13
class
MysqlHelper
(object):
14
“>”” host=>”localhost”, db=>””, user=>”root”, passwd=>””, port=3306, pool_sizer=30, pool_name=>”mysql”, commit_size=1
>””>”
15commit_count = 0
16
17
def
__init__
(self, *args, **kwargs):
18
commit_size = kwargs.get(” commit_size
>”, -1)
19
if
commit_size > -1:
20self._commit_size = commit_size
21
del
kwargs[” commit_size
>”]
22
else
:
23self._commit_size = 1
24self._last_row_id = None
25self._conn = connor.connect(*args, **kwargs)
26
27
def
insert
(self, sql, params=None):
28cursor = self._create_cursor()
29
try
:
30cursor.execute(sql, params)
31
except
Exception, e:
32
try
:
33
logging.error(” Mysql Call error. SQL = %s, params = %s, Error.msg=%s
>” % (sql, str(params).encode(” utf8
>”), e))
34
except
:
35
print
sql, params, e
36self._last_row_id = cursor.lastrowid
37self._commit()
38
return
cursor.rowcount
39
40
def
update
(self, sql, params=None):
41
return
self.insert(sql, params)
42
43
def
delete
(self, sql, params=None):
44
return
self.insert(sql, params)
45
46
def
select
(self, sql, params=None):
47cursor = self._create_cursor()
48cursor.execute(sql, params)
49
return
cursor.fetchall()
50
51
def
commit
(self):
52
try
:
53self._conn.commit()
54
except
connor.Error, msg:
55
logging.error(” Mysql commit error. message:%s.
>” % msg)
56
57
@
property
58
def
last_row_id
(self):
59
return
self._last_row_id
60
61
def
_create_cursor
(self):
62
# cursor = conn.cursor(cursor_class=conner.cursor.MySQLCursorDict)
63cursor = self._conn.cursor(dictionary=True)
64
return
cursor
65
66
def
_commit
(self):
67self.__class__.commit_count += 1
68
if
self.__class__.commit_count == self._commit_size:
69self.commit()
70self.__class__.commit_count = 0
71
72
def
__del__
(self):
73
print
” mysql close …
>”
74self.commit()
75self._conn.close()
76
77
78
if
__name__ == ” __main__
>”:
79
mysql_helper = MysqlHelper(host=” localhost
>”, db=” zentao
>”, user=” root
>”, passwd=” kaimen
>”, port=3306, pool_size=2,
80
pool_name=” mysql
>”, commit_size=2)
81
print
1, mysql_helper.select(” show tables;
>”)
82
print
2, mysql_helper.select(” show tables;
>”)
83
print
3, mysql_helper.select(” show tables;
>”)
84
print
4, mysql_helper.select(” show tables;
>”)
85
print
5, mysql_helper.select(” select * from task;
>”)
select 结果是list[dict{}, dict{}]
查询的时候,参数可以是列表、字典
user = {“uid>”: 20, “name>”:”zhipeng>”, “titles>”:”python, spider>”}
sql = ‘INSERT INTO users(uid, name, titles) VALUES (%s, %s, %s);’
mysql_helper
.insert(sql, ( user
[“uid>”], user
[“name>”], user
[“titles>”], ))
或者:
sql = ‘INSERT INTO users(uid, name, titles) VALUES (%(uid)s, %(name)s, %(titles)s);’
mysql_helper.
insert(sql, user
)
增、删、改,返回的结果是几行受影响。
mysql_helper.last_row_id 获取最后添加的自增长列id
commit_size 是设置主动commit方式,插入多少次数据后执行commit方法。
在__del__中添加主动commit,确保数据提交更改。
原文:http://blog.sina.com.cn/s/blog_83dc494d0102voun.html