Python: Python MySQL 官方包：mysql-connector-python

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 (%(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