Python操作mysql数据库

jjytr

有三种方式：mysql-connector, MySQLdb,torndb(对MySQLdb的二次封装)
mysql-connector:游标是弱引用，无法封装成函数
MySQLdb：可以封装成函数
torndb：返回值可以转换成字典

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 47 48 49 50 51 52 53 54 55 56 57 58 59

from __future__ import print_function sql = ('SELECT * from ipdata limit 10') # mysql-connector print('mysql-connector'.center(50, '=')) from mysql import connector cnx = connector.Connect(host="localhost", user="root",                             password="", database="test", charset="utf8") # cnx.autocommit = True db0 = cnx.cursor() db0.execute(sql) for row in db0:     print(*row) # print row[0], row[1], row[2], row[3] # MySQLdb print('MySQLdb'.center(50, '=')) import MySQLdb def connect_mysql(db_host="localhost", user="root",                    passwd="",db="test", charset="utf8"):     conn = MySQLdb.connect(host=db_host, user=user, passwd=passwd, db=db, charset=charset)     conn.autocommit(True)     return conn.cursor() db1 = connect_mysql() db1.execute(sql) for row in db1:     print(*row) # torndb1 print('torndb1'.center(50, '=')) import torndb import simplejson as json db2 = torndb.Connection(     host='localhost',     database='test',     user='root',     password='',     charset="utf8") rows = db2.query(sql) for row in rows:     print(json.dumps(row, ensure_ascii=False)) # # torndb2 # print('torndb3'.center(50, '=')) # row = db2.get(sql) # print(json.dumps(row, ensure_ascii=False)) # # torndb3 print('torndb2'.center(50, '=')) row = db2.get('SELECT * from ipdata limit 1') print(json.dumps(row, ensure_ascii=False))

get方法只能返回一条数据，返回多条会报错

批量插入数据

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

# ! /usr/bin/python # -*- coding: utf-8 -*- sql = 'INSERT INTO `ipdata` (`startip`,`endip`,`country`,`carrier`) VALUES (18684928,18684928,"内蒙古赤峰市巴林左旗","联通林东镇新城区BRAS数据机房")' sql_tmp = 'INSERT INTO `ipdata` (`startip`,`endip`,`country`,`carrier`) VALUES (%s, %s, %s, %s)' values = [(16890112,16891391,"泰国","曼谷"),(16891392,16891647,"泰国","如果硅农"), (16891648,16892159,"泰国","加拉信府")] # mysql-connector print('mysql-connector'.center(50, '=')) from mysql import connector cnx = connector.Connect(host="localhost", user="root",                             password="", database="test", charset="utf8") cnx.autocommit = True db0 = cnx.cursor() print db0.execute(sql) print db0.executemany(sql_tmp, values) # MySQLdb print('MySQLdb'.center(50, '=')) import MySQLdb def connect_mysql(db_host="localhost", user="root",                    passwd="",db="test", charset="utf8"):     conn = MySQLdb.connect(host=db_host, user=user, passwd=passwd, db=db, charset=charset)     conn.autocommit(True)     return conn.cursor() db1 = connect_mysql() print db1.execute(sql), db1.lastrowid print db1.executemany(sql_tmp, values), db1.lastrowid # torndb print('torndb1'.center(50, '=')) import torndb db2 = torndb.Connection(     host='localhost',     database='test',     user='root',     password='',     charset="utf8") print db2.insert(sql) print db2.insertmany(sql_tmp, values)