加载中…
个人资料
早睡早起防抑郁
早睡早起防抑郁
  • 博客等级:
  • 博客积分:0
  • 博客访问:170,468
  • 关注人气:10
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
正文 字体大小:

【原创】python mysql-connector-python

(2015-06-01 10:37:48)
标签:

python

mysql

connector

连接池

mysql-connector-pyth

分类: python
Python 操作mysql包有MySQLdb、SQLAlchemy等等、还有一个是mysql.connector,mysql官方包,详见(MySQL Connector/Python Developer Guide)。


安装方法: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
10 connor.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"""
15     commit_count = 0
16
17     def __init__(self, *args, **kwargs):
18         commit_size = kwargs.get("commit_size", -1)
19         if commit_size > -1:
20             self._commit_size = commit_size
21             del kwargs["commit_size"]
22         else:
23             self._commit_size = 1
24         self._last_row_id = None
25         self._conn = connor.connect(*args, **kwargs)
26
27     def insert(self, sql, params=None):
28         cursor = self._create_cursor()
29         try:
30             cursor.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
36         self._last_row_id = cursor.lastrowid
37         self._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):
47         cursor = self._create_cursor()
48         cursor.execute(sql, params)
49         return cursor.fetchall()
50
51     def commit(self):
52         try:
53             self._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)
63         cursor = self._conn.cursor(dictionary=True)
64         return cursor
65
66     def _commit(self):
67         self.__class__.commit_count += 1
68         if self.__class__.commit_count == self._commit_size:
69             self.commit()
70             self.__class__.commit_count = 0
71
72     def __del__(self):
73         print "mysql close ..."
74         self.commit()
75         self._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,确保数据提交更改。

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有