什么是pymysql、pymysql的安装、pymysql连接对象、pymysql游标对象、案例
Part 1 - 什么是pymysql
pymysql是python的一个第三方库，顾名思义pymysql是在python中操作mysql数据库的API，操作者可以直接在python中使用python语法+SQL语法实现对mysql数据库的操作，简化在python中操作mysql的流程。
Part 2 - pymysql的安装
由于pymysql是python的第三方库（不是python标准库），因此在纯python环境下需要单独安装，通过pip实现pymysql的安装：
pip install pymysql
如果使用的是anaconda集成环境，由于pymysql集成在anaconda环境中，因此无需进行单独安装。
Part 3 - pymysql连接对象
使用pymysql进行操作之前，首先需要建立与mysql数据库的连接（相当于在python与mysql之间建立一个通道，否则python无法到达mysql中），同时连接对象具有一些方法，探长会一一分享。
3.1 建立连接对象-connect方法
import pymysql
conn = pymysql.connect(host, user, password, database, port, charset)
通过pymysq的connect方法可以建立与mysql数据库的连接，其中host参数为主机的ip地址；user参数为操作mysql的一个用户账号；password参数为账号密码；database为mysql中需要进行操作的数据库名；port参数为mysql的端口号，默认为3306，如果更改了那么需要添加更改后的端口号；如果不加charset参数，如果数据库中存在中文，那么很有可能出现乱码情况，通过增加charset='utf8'可以正常显示；
3.2 连接对象的相关方法
commit()方法
conn.commit()
commit用于将事物提交到数据库中，如果数据库支持自动提交功能，那么无需进行commit操作（可有可无）；
close()方法
conn.close()
close方法用于关闭连接，一般情况下，在完成操作后需要使用close方法关闭与数据库的连接；
cursor()方法
cur = conn.cursor()
cursor方法用于返回一个游标对象，通过游标的相关方法进行SQL语法的调用，进而实现pymysql对mysql数据库的操作。
Part 4 - 游标cursor对象
建立游标之后，就可以通过游标对数据库进行相关操作，游标有属性和方法，其中核心的是方法。
4.1 游标属性
description属性
cur = conn.cursor()
cur.description
description属性用于返回cursor对象的描述性信息，例如name、type_code等，在cursor对象没有execute任何操作之前，cur.description返回None，而execute操作之后就会返回相应的信息；
rowcount属性
cur.rowcount
rowcount属性用于返回最后执行或受影响的行数，在没有execute执行之前，默认返回-1，在execute执行之后，返回的是最后执行或受影响的行数，例如执行insert插入操作，插入了6条数据，那么cur.rowcount返回结果6.
4.2 游标方法
执行方法-execute()/executemany()方法
execute()和executemany()方法都是执行sql语句的方法，区别在于execute一次仅能执行一个操作，而executemany可以执行多个操作；
cur.execute()
cur.executemany()
其中executemany由于可以执行多个操作，因此常用于批量操作，execute仅能插入单一操作，因此可以应用于循环操作中；
获取方法-fetchone/fetchmany/fetchall方法
fetchone()方法用于获取执行结果的一条数据；fetchmany(size=1)用于获取执行的n条数据，想获取几条需要手动指定，例如size=3就是获取执行的三条数据；fetchall()用于获取执行结果的所有数据；
无论fetchone、fetchmany、fetchall都是以元组形式返回结果；
cur.executemany('sql语句')
cur.fetchone()
cur.fetchmany()
cur.fetchall()
关闭游标方法-close()方法
游标执行操作完成之后需要关闭，因此通过close()方法执行关闭操作
cur.close()
案例1
description：向test2数据库中插入表pymysql(两个字段id,name)，首先插入数据，然后删除部分数据，修改数据，查询数据
import pymysql
# 建立连接对象，并创立游标
conn = pymysql.connect('localhost', 'root', '123456', 'test2', 3306, charset='utf8')
cur = conn.cursor()
# 建表pymysql
cur.execute('create table pymysql(id int not null, name varchar(10));')
conn.commit()
# 插入数据
cur.execute('insert into pymysql values(%s, %s);', (1, 'gam'))
conn.commit()
# 批量插入信息
sql="INSERT INTO customers(name,address,sex,age,sl) VALUES(%s, %s,%s,%s,%s)"
val = ("John", "Highway 21","M",23,5000)
mycursor.execute(sql, val)
val = ("Jenny", "Highway 29","F",30,12500)
mycursor.execute(sql, val)
val=[("Tom","ABC 35","M",35,14000),
("Tom1","Highway 29","M",28,6700),
("Lily","Road 11","F",30,8000),
("Martin","Road 24","M",35,14000),
("Sally","Fast 56","M",32,15000)]
mycursor.executemany(sql, val)
cur.executemany('insert into pymysql values(%s, %s)', [(2, 'ghp'), (3, 'gk'), (4, 'lq'), (5, 'g**')])
conn.commit()
# 删除数据
cur.execute('drop from pymysql where id=5')
conn.commit()
# 修改数据
cur.execute('alter table pymysql add column gender varchar(10);')
cur.execute('alter table pymysql change id user_id int;')
cur.execute('alter table pymysql drop column gender;')
conn.commit()
# 查询数据
cur.execute('select * from pymysql;')
cur.fetchone()
cur.fetchmany(1)
cur.fetchall()
conn.commit()
# 关闭游标和连接
cur.close()
conn.close()
6.Mysql类的使用
# -*- coding: utf-8 -*-
import pymysql
import re
class MysqldbHelper(object): # 继承object类所有方法
'''
构造方法：
config = {
'host': '127.0.0.1',
'port': 3306,
'user': 'root',
'passwd': 'root',
'charset':'utf8',
'cursorclass':pymysql.cursors.DictCursor
}
conn = pymysql.connect(**config)
conn.autocommit(1)
cursor = conn.cursor()
'''
def __init__(self , config):
self.host = config['host']
self.username = config['user']
self.password = config['passwd']
self.port = config['port']
self.con = None
self.cur = None
try:
self.con = pymysql.connect(**config)
self.con.autocommit(1)
# 所有的查询，都在连接 con 的一个模块 cursor 上面运行的
self.cur = self.con.cursor()
except:
print "DataBase connect error,please check the db config."
# 关闭数据库连接
def close(self):
if not self.con:
self.con.close()
else:
print "DataBase doesn't connect,close connectiong error;please check the db config."
# 创建数据库
def createDataBase(self,DB_NAME):
# 创建数据库
self.cur.execute('CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci' % DB_NAME)
self.con.select_db(DB_NAME)
print 'creatDatabase:' + DB_NAME
# 选择数据库
def selectDataBase(self,DB_NAME):
self.con.select_db(DB_NAME)
# 获取数据库版本号
def getVersion(self):
self.cur.execute("SELECT VERSION()")
return self.getOneData()
# 获取上个查询的结果
def getOneData(self):
# 取得上个查询的结果，是单个结果
data = self.cur.fetchone()
return data
# 创建数据库表
def creatTable(self, tablename, attrdict, constraint):
"""创建数据库表
args：
tablename ：表名字
attrdict ：属性键值对,{'book_name':'varchar(200) NOT NULL'...}
constraint ：主外键约束,PRIMARY KEY(`id`)
"""
if self.isExistTable(tablename):
print "%s is exit" % tablename
return
sql = ''
sql_mid = '`id` bigint(11) NOT NULL AUTO_INCREMENT,'
for attr,value in attrdict.items():
sql_mid = sql_mid + '`'+attr + '`'+' '+ value+','
sql = sql + 'CREATE TABLE IF NOT EXISTS %s ('%tablename
sql = sql + sql_mid
sql = sql + constraint
sql = sql + ') ENGINE=InnoDB DEFAULT CHARSET=utf8'
print 'creatTable:'+sql
self.executeCommit(sql)
def executeSql(self,sql=''):
"""执行sql语句，针对读操作返回结果集
args：
sql ：sql语句
"""
try:
self.cur.execute(sql)
records = self.cur.fetchall()
return records
except pymysql.Error,e:
error = 'MySQL execute failed! ERROR (%s): %s' %(e.args[0],e.args[1])
print error
def executeCommit(self,sql=''):
"""执行数据库sql语句，针对更新,删除,事务等操作失败时回滚
"""
try:
self.cur.execute(sql)
self.con.commit()
except pymysql.Error, e:
self.con.rollback()
error = 'MySQL execute failed! ERROR (%s): %s' %(e.args[0],e.args[1])
print "error:", error
return error
def insert(self, tablename, params):
"""创建数据库表
args：
tablename ：表名字
key ：属性键
value ：属性值
"""
key = []
value = []
for tmpkey, tmpvalue in params.items():
key.append(tmpkey)
if isinstance(tmpvalue, str):
value.append("\'" + tmpvalue + "\'")
else:
value.append(tmpvalue)
attrs_sql = '('+','.join(key)+')'
values_sql = ' values('+','.join(value)+')'
sql = 'insert into %s'%tablename
sql = sql + attrs_sql + values_sql
print '_insert:'+sql
self.executeCommit(sql)
def select(self, tablename, cond_dict='', order='', fields='*'):
"""查询数据
args：
tablename ：表名字
cond_dict ：查询条件
order ：排序条件
example：
print mydb.select(table)
print mydb.select(table, fields=["name"])
print mydb.select(table, fields=["name", "age"])
print mydb.select(table, fields=["age", "name"])
"""
consql = ' '
if cond_dict!='':
for k, v in cond_dict.items():
consql = consql+'`'+k +'`'+ '=' + '"'+v + '"' + ' and'
consql = consql + ' 1=1 '
if fields == "*":
sql = 'select * from %s where ' % tablename
else:
if isinstance(fields, list):
fields = ",".join(fields)
sql = 'select %s from %s where ' % (fields, tablename)
else:
print "fields input error, please input list fields."
sql = sql + consql + order
print 'select:' + sql
return self.executeSql(sql)
def insertMany(self,table, attrs, values):
"""插入多条数据
args：
tablename ：表名字
attrs ：属性键
values ：属性值
example：
table='test_mysqldb'
key = ["id" ,"name", "age"]
value = [[101, "liuqiao", "25"], [102,"liuqiao1", "26"], [103 ,"liuqiao2", "27"], [104 ,"liuqiao3", "28"]]
mydb.insertMany(table, key, value)
"""
values_sql = ['%s' for v in attrs]
attrs_sql = '('+','.join(attrs)+')'
values_sql = ' values('+','.join(values_sql)+')'
sql = 'insert into %s'% table
sql = sql + attrs_sql + values_sql
print 'insertMany:'+sql
try:
print sql
for i in range(0,len(values),20000):
self.cur.executemany(sql,values[i:i+20000])
self.con.commit()
except pymysql.Error,e:
self.con.rollback()
error = 'insertMany executemany failed! ERROR (%s): %s' %(e.args[0],e.args[1])
print error
def delete(self, tablename, cond_dict):
"""删除数据
args：
tablename ：表名字
cond_dict ：删除条件字典
example：
params = {"name" : "caixinglong", "age" : "38"}
mydb.delete(table, params)
"""
consql = ' '
if cond_dict!='':
for k, v in cond_dict.items():
if isinstance(v, str):
v = "\'" + v + "\'"
consql = consql + tablename + "." + k + '=' + v + ' and '
consql = consql + ' 1=1 '
sql = "DELETE FROM %s where%s" % (tablename, consql)
print sql
return self.executeCommit(sql)
def update(self, tablename, attrs_dict, cond_dict):
"""更新数据
args：
tablename ：表名字
attrs_dict ：更新属性键值对字典
cond_dict ：更新条件字典
example：
params = {"name" : "caixinglong", "age" : "38"}
cond_dict = {"name" : "liuqiao", "age" : "18"}
mydb.update(table, params, cond_dict)
"""
attrs_list = []
consql = ' '
for tmpkey, tmpvalue in attrs_dict.items():
attrs_list.append("`" + tmpkey + "`" + "=" +"\'" + tmpvalue + "\'")
attrs_sql = ",".join(attrs_list)
print "attrs_sql:", attrs_sql
if cond_dict!='':
for k, v in cond_dict.items():
if isinstance(v, str):
v = "\'" + v + "\'"
consql = consql + "`" + tablename +"`." + "`" + k + "`" + '=' + v + ' and '
consql = consql + ' 1=1 '
sql = "UPDATE %s SET %s where%s" % (tablename, attrs_sql, consql)
print sql
return self.executeCommit(sql)
def dropTable(self, tablename):
"""删除数据库表
args：
tablename ：表名字
"""
sql = "DROP TABLE %s" % tablename
self.executeCommit(sql)
def deleteTable(self, tablename):
"""清空数据库表
args：
tablename ：表名字
"""
sql = "DELETE FROM %s" % tablename
print "sql=",sql
self.executeCommit(sql)
def isExistTable(self, tablename):
"""判断数据表是否存在
args：
tablename ：表名字
Return:
存在返回True，不存在返回False
"""
sql = "select * from %s" % tablename
result = self.executeCommit(sql)
if result is None:
return True
else:
if re.search("doesn't exist", result):
return False
else:
return True
if __name__ == "__main__":
# 定义数据库访问参数
config = {
'host': '你的mysql服务器IP地址',
'port': 3361,
'user': 'root',
'passwd': '你的mysql服务器root密码',
'charset': 'utf8',
'cursorclass': pymysql.cursors.DictCursor
}
# 初始化打开数据库连接
mydb = MysqldbHelper(config)
# 打印数据库版本
print mydb.getVersion()
# 创建数据库
DB_NAME = 'test_db'
# mydb.createDataBase(DB_NAME)
# 选择数据库
print "========= 选择数据库%s ===========" % DB_NAME
mydb.selectDataBase(DB_NAME)
#创建表
TABLE_NAME = 'test_user'
print "========= 选择数据表%s ===========" % TABLE_NAME
# CREATE TABLE %s(id int(11) primary key,name varchar(30))' %TABLE_NAME
attrdict = {'name':'varchar(30) NOT NULL'}
constraint = "PRIMARY KEY(`id`)"
mydb.creatTable(TABLE_NAME,attrdict,constraint)
# 插入纪录
print "========= 单条数据插入 ==========="
params = {}
for i in range(5):
params.update({"name":"testuser"+str(i)}) # 生成字典数据，循环插入
print params
mydb.insert(TABLE_NAME, params)
print
# 批量插入数据
print "========= 多条数据同时插入 ==========="
insert_values = []
for i in range(5):
# values.append((i,"testuser"+str(i)))
insert_values.append([u"测试用户"+str(i)]) # 插入中文数据
print insert_values
insert_attrs = ["name"]
mydb.insertMany(TABLE_NAME,insert_attrs, insert_values)
# 数据查询
print "========= 数据查询 ==========="
print mydb.select(TABLE_NAME, fields=["id", "name"])
print mydb.select(TABLE_NAME, cond_dict = {'name':'测试用户2'},fields=["id", "name"])
print mydb.select(TABLE_NAME, cond_dict = {'name':'测试用户2'},fields=["id", "name"],order="order by id desc")
# 删除数据
print "========= 删除数据 ==========="
delete_params = {"name": "测试用户2"}
mydb.delete(TABLE_NAME, delete_params)
# 更新数据
print "========= 更新数据 ==========="
update_params = {"name": "测试用户99"} # 需要更新为什么值
update_cond_dict = {"name": "测试用户3"} # 更新执行的查询条件
mydb.update(TABLE_NAME, update_params, update_cond_dict)
# 删除表数据
print "========= 删除表数据 ==========="
mydb.deleteTable(TABLE_NAME)
# 删除表
print "========= 删除表 ==========="
mydb.dropTable(TABLE_NAME)
测试执行结果如下：
D:\Python27\python.exe E:/PycharmProjects/DataProject/tools/MysqlTools.py
{u'VERSION()': u'5.7.9-log'}
========= 选择数据库test_db ===========
========= 选择数据表test_user ===========
test_user is exit
========= 单条数据插入 ===========
{'name': 'testuser0'}
_insert:insert into test_user(name) values('testuser0')
{'name': 'testuser1'}
_insert:insert into test_user(name) values('testuser1')
{'name': 'testuser2'}
_insert:insert into test_user(name) values('testuser2')
{'name': 'testuser3'}
_insert:insert into test_user(name) values('testuser3')
{'name': 'testuser4'}
_insert:insert into test_user(name) values('testuser4')
========= 多条数据同时插入 ===========
[[u'\u6d4b\u8bd5\u7528\u62370'], [u'\u6d4b\u8bd5\u7528\u62371'], [u'\u6d4b\u8bd5\u7528\u62372'], [u'\u6d4b\u8bd5\u7528\u62373'], [u'\u6d4b\u8bd5\u7528\u62374']]
insertMany:insert into test_user(name) values(%s)
insert into test_user(name) values(%s)
========= 数据查询 ===========
select:select id,name from test_user where 1=1
[{u'id': 361, u'name': u'testuser0'}, {u'id': 362, u'name': u'testuser1'}, {u'id': 363, u'name': u'testuser2'}, {u'id': 364, u'name': u'testuser3'}, {u'id': 365, u'name': u'testuser4'}, {u'id': 366, u'name': u'\u6d4b\u8bd5\u7528\u62370'}, {u'id': 367, u'name': u'\u6d4b\u8bd5\u7528\u62371'}, {u'id': 368, u'name': u'\u6d4b\u8bd5\u7528\u62372'}, {u'id': 369, u'name': u'\u6d4b\u8bd5\u7528\u62373'}, {u'id': 370, u'name': u'\u6d4b\u8bd5\u7528\u62374'}]
select:select id,name from test_user where `name`="测试用户2" and 1=1
[{u'id': 368, u'name': u'\u6d4b\u8bd5\u7528\u62372'}]
select:select id,name from test_user where `name`="测试用户2" and 1=1 order by id desc
[{u'id': 368, u'name': u'\u6d4b\u8bd5\u7528\u62372'}]
========= 删除数据 ===========
DELETE FROM test_user where test_user.name='测试用户2' and 1=1
========= 更新数据 ===========
attrs_sql: `name`='测试用户99'
UPDATE test_user SET `name`='测试用户99' where `test_user`.`name`='测试用户3' and 1=1
========= 删除表数据 ===========
sql= DELETE FROM test_user
========= 删除表 ===========
Process finished with exit code 0
# -*- coding: utf-8 -*-
import pymysql
import re, random, requests, json, time, datetime
def get_passwd():
"""8-12 :
数字：1-2位数
特殊字符：1位
"""
nums = "1234567890"
char = "aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ"
unique = ".!@#$&*"
bit = random.randint(8, 12)
re_n = "".join(random.sample(nums, random.randint(1, 2)))
re_s = "".join(random.sample(char, (bit-2-random.randint(1, 2))))
re_q = "".join(random.sample((unique), 1))
re_s1 = "".join(random.sample(char, 1)).upper()
# print(re_s1+re_s+re_n+re_q, bit)
return re_s1+re_s+re_n+re_q
def get_bir():
a1 = (1980, 1, 1, 9, 27, 43, 4, 92, -1)
a2 = (2000, 1, 1, 9, 27, 43, 4, 92, -1)
start = time.mktime(a1)
end = time.mktime(a2)
t = random.randint(start, end)
date_touple = time.localtime(t)
date = time.strftime("%Y%m%d", date_touple)
# print(date)
return date
def get_gender(name="Michael"):
url = "http://192.168.9.99:8080/gender?first_name=" + name
res = requests.get(url)
items = res.content.decode()
dic = json.loads(items)
dics = sorted(dic.items(), key=lambda x: x[1], reverse=True)[0]
for i in dics:
if i == "女":
return 0
elif i == "男":
return 1
else:
return -1
break
def get_time():
return time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
class MysqldbHelper(object): # 继承object类所有方法
'''
构造方法：
config = {
'host': '127.0.0.1',
'port': 3306,
'user': 'root',
'passwd': 'root',
'charset':'utf8',
'cursorclass':pymysql.cursors.DictCursor
}
conn = pymysql.connect(**config)
conn.autocommit(1)
cursor = conn.cursor()
'''
def __init__(self, config):
self.host = config['host']
self.username = config['user']
self.password = config['passwd']
self.port = config['port']
self.con = None
self.cur = None
try:
self.con = pymysql.connect(**config)
self.con.autocommit(1)
# 所有的查询，都在连接 con 的一个模块 cursor 上面运行的
self.cur = self.con.cursor()
except:
print
"DataBase connect error,please check the db config."
# 关闭数据库连接
def close(self):
if not self.con:
self.con.close()
else:
print
"DataBase doesn't connect,close connectiong error;please check the db config."
# 创建数据库
def createDataBase(self, DB_NAME):
# 创建数据库
self.cur.execute(
'CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci' % DB_NAME)
self.con.select_db(DB_NAME)
print
'creatDatabase:' + DB_NAME
# 选择数据库
def selectDataBase(self, DB_NAME):
self.con.select_db(DB_NAME)
# 获取数据库版本号
def getVersion(self):
self.cur.execute("SELECT VERSION()")
return self.getOneData()
# 获取上个查询的结果
def getOneData(self):
# 取得上个查询的结果，是单个结果
data = self.cur.fetchone()
return data
# 创建数据库表
def creatTable(self, tablename, attrdict, constraint):
"""创建数据库表
args：
tablename ：表名字
attrdict ：属性键值对,{'book_name':'varchar(200) NOT NULL'...}
constraint ：主外键约束,PRIMARY KEY(`id`)
"""
if self.isExistTable(tablename):
print("%s is exit" % tablename)
return
sql = ''
sql_mid = '`id` bigint(11) NOT NULL AUTO_INCREMENT,'
for attr, value in attrdict.items():
sql_mid = sql_mid + '`' + attr + '`' + ' ' + value + ','
sql = sql + 'CREATE TABLE IF NOT EXISTS %s (' % tablename
sql = sql + sql_mid
sql = sql + constraint
sql = sql + ') ENGINE=InnoDB DEFAULT CHARSET=utf8'
print
'creatTable:' + sql
self.executeCommit(sql)
def executeSql(self, sql=''):
"""执行sql语句，针对读操作返回结果集
args：
sql ：sql语句
"""
try:
self.cur.execute(sql)
records = self.cur.fetchall()
return records
except Exception as e:
error = 'MySQL execute failed! ERROR (%s): %s' % (e.args[0], e.args[1])
print(error)
def executeCommit(self, sql=''):
"""执行数据库sql语句，针对更新,删除,事务等操作失败时回滚
"""
try:
self.cur.execute(sql)
self.con.commit()
except Exception as e:
self.con.rollback()
error = 'MySQL execute failed! ERROR (%s): %s' % (e.args[0], e.args[1])
print("error:", error)
return error
def select(self, tablename, cond_dict='', order='', fields='*'):
"""查询数据
args：
tablename ：表名字
cond_dict ：查询条件
order ：排序条件
example：
print mydb.select(table)
print mydb.select(table, fields=["name"])
print mydb.select(table, fields=["name", "age"])
print mydb.select(table, fields=["age", "name"])
"""
consql = ' '
if cond_dict!='':
for k, v in cond_dict.items():
consql = consql+'`'+k +'`'+ '=' + '"'+v + '"' + ' and'
consql = consql + ' 1=1 '
if fields == "*":
sql = 'select * from %s where ' % tablename
else:
if isinstance(fields, list):
fields = ",".join(fields)
sql = 'select %s from %s where ' % (fields, tablename)
else:
print("fields input error, please input list fields.")
sql = sql + consql + order
print('select:' + sql)
return self.executeSql(sql)
def insert(self, tablename, params):
"""创建数据库表
args：
tablename ：表名字
key ：属性键
value ：属性值
"""
key = []
value = []
for tmpkey, tmpvalue in params.items():
key.append(tmpkey)
if isinstance(tmpvalue, str):
value.append("\'" + tmpvalue + "\'")
else:
value.append(tmpvalue)
attrs_sql = '('+','.join(key)+')'
values_sql = ' values('+','.join(str(value))+')'
sql = 'insert into %s'%tablename
sql = sql + attrs_sql + values_sql
print('_insert:'+sql)
self.executeCommit(sql)
def insertMany(self, table, attrs, values):
"""插入多条数据
args：
tablename ：表名字
attrs ：属性键
values ：属性值
example：
table='test_mysqldb'
key = ["id" ,"name", "age"]
value = [[101, "liuqiao", "25"], [102,"liuqiao1", "26"], [103 ,"liuqiao2", "27"], [104 ,"liuqiao3", "28"]]
mydb.insertMany(table, key, value)
"""
values_sql = ['%s' for v in attrs]
attrs_sql = '('+','.join(attrs)+')'
values_sql = ' values('+','.join(values_sql)+')'
sql = 'insert into %s'% table
sql = sql + attrs_sql + values_sql
print('insertMany:'+sql)
try:
print(sql)
for i in range(0, len(values), 1000):
self.cur.executemany(sql, values[i:i+1000])
self.con.commit()
except Exception as e:
self.con.rollback()
error = "insertMany executemany failed! ERROR (%s): %s".format(e.args[0], e.args[1])
print(error)
if __name__ == "__main__":
# 定义数据库访问参数
config = {
'host': '192.168.9.99',
'port': 3306,
'user': 'facebook',
'passwd': 'facebook',
'charset': 'utf8mb4',
'cursorclass': pymysql.cursors.DictCursor
}
# 初始化打开数据库连接
mydb = MysqldbHelper(config)
# 打印数据库版本
print(mydb.getVersion())
# 选择数据库
DB_NAME = "fb_data"
print("========= 选择数据库%s ===========" % DB_NAME)
mydb.selectDataBase(DB_NAME)
# 数据查询
TABLE_NAME = "data_original"
print("========= 数据查询 ===========")
items = mydb.select(TABLE_NAME, fields=["国家", "电话", "名字", "邮编", "州", "城市", "地址", "邮箱"])
print("========= 多条数据同时插入 ===========")
insert_values = []
insert_attrs = ["id", "email", "password", "nickname", "first_name", "surname", "brith_date", "gender", "country", "state", "city", "address", "zip_code", "status", "remark", "created_at", "updated_at"]
for i, e in enumerate(items):
first_name = e['名字'].split(' ')[0]
state =e['州'] if e['州'] else ''
insert_values.append([i+1, e['邮箱'], get_passwd(), e['名字'], first_name, e['名字'].split(' ')[1], get_bir(), get_gender(name=first_name), e['国家'], state, e['城市'], e['地址'], e['邮编'], 0, "", get_time(), get_time()]) # 插入中文数据
print("已经处理完成第 {} 条信息".format(i+1))
inser_NAME = 'data_register_copy1'
mydb.insertMany(inser_NAME, insert_attrs, insert_values)
# # 插入纪录
# TABLE_NAME = "data_register"
# print("========= 单条数据插入 ===========")
# params = {}
# for i in range(1,2):
# params.update({"id": int(i), "email": "Sonjajpk2@gmail.com", "password": get_passwd(), "nickname": "Sonja Perry",
# "first_name": "Sonja", "surname": "Perry", "brith_date": get_bir(), "gender": get_gender(name="Sonja"),
# "country": "United States", "state": None, "city": "Loganville", "address": "809 Golden Isles Drive",
# "zip_code": "30052", "status": None, "remark": None, "creaked_at": None, "updated_at": None
# })
# print(params)
# mydb.insert(TABLE_NAME, params)
网友评论