对python插入数据库和生成插入sql的示例讲解
作者:yan456jie 时间:2022-03-10 05:46:40
如下所示:
#-*- encoding:utf-8 -*-
import csv
import sys,os
import pymysql
def read_csv(filename):
'''
读取csv文件
'''
data = []
with open(filename) as f:
f_csv = csv.reader(f)
headers = next(f_csv)
#数据格式[1111,22222,1111,1111,.....]
for row in f_csv:
# Process row
field1=row[0]
data.append(row)
print headers
return data
def load_data():
'''
插入数据库
'''
filename = sys.argv[1]
try:
#获取一个数据库连接,注意如果是UTF-8类型的,需要制定数据库
conn=pymysql.connect(host='192.168.1.161',user='naliworld',passwd='password!',db='search',port=3306,charset='utf8')
cur=conn.cursor()#获取一个游标
data=read_csv(filename)
for row in data:
# Process row
field1=row[0]
sql='''insert into search.tb_text_uid_list(appId,type,uid,creator,createTime) values({},{},{},{},{}) '''.format(3,1,field1,'\'admin\'','\'2018-08-14 13:44:09\'')
print sql
cur.execute(sql)
cur.close()#关闭游标
conn.commit()
conn.close()#释放数据库资源
except Exception as e :print(e)
def get_sql():
'''
插入数据库生成插入sql
'''
sql_list = []
filename = sys.argv[1]
data=read_csv(filename)
for row in data:
# Process row
field1=row[0]
sql='''replace into search.tb_text_uid_list(appId,type,uid,creator,createTime) values({},{},{},{},{}) '''.format(3,1,field1,'\'admin\'','\'2018-08-14 13:44:09\'')
sql_list.append(sql)
file_object = open('sql.txt', 'w')
file_object.writelines([line+';\n' for line in sql_list])
file_object.close( )
if __name__ == "__main__":
get_sql()
来源:https://blog.csdn.net/Yan456jie/article/details/77510031
标签:python,插入,数据库,sql
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
简单触发器的使用 献给SQL初学者
2011-09-30 11:27:49
手工打造极酷的分离式滑动门导航菜单
2009-05-25 20:11:00
![](https://img.aspxhome.com/file/UploadPic/20095/25/mt1-26s.gif)
记录下两个正则表达式的使用
2009-11-30 12:56:00
Python hashlib常见摘要算法详解
2023-07-29 20:04:09
System.Runtime.InteropServices.COMException的解决方法
2023-07-18 15:03:27
JavaScript事件冒泡
2009-12-28 13:21:00
[译]艺术和设计的差异 (1)
2009-09-25 12:38:00
FrontPage2002简明教程七:HTML在FrontPage中的应用
2008-09-17 11:33:00
![](https://img.aspxhome.com/file/UploadPic/200810/17/20081017113624552s.jpg)
鼠标双击滚动屏幕单击停止代码
2008-02-21 11:44:00
网页设计标准尺寸
2008-06-15 15:21:00
CSS中expression的用法
2008-09-11 14:18:00
ASP实现数据输入、查询的实例
2010-05-03 10:48:00
基于python3+OpenCV实现人脸和眼睛识别
2023-06-08 04:20:14
![](https://img.aspxhome.com/file/2023/7/66357_0s.jpg)
getElementsByAttribute
2009-10-27 12:13:00
ASP、PHP与javascript根据时段切换CSS皮肤的代码
2008-09-01 17:26:00
用SQL语句完成SQL Server数据库的修复
2008-11-24 20:49:00
Linux mysql安装修改root密码服务
2010-10-14 13:59:00
Check In和Check Out的多人协作管理
2007-02-03 11:39:00
phpMyAdmin下载、安装和使用入门
2007-06-15 11:00:00
![](https://img.aspxhome.com/file/uploadpic/20076/15/2007615151216820.gif)
详解element-ui 表单校验 Rules 配置 常用黑科技
2023-08-17 17:42:47
![](https://img.aspxhome.com/file/2023/6/55916_0s.jpg)