循序渐进丨使用 Python 向 MogDB 数据库批量操作数据的方法

当我们有时候需要向数据库里批量插入数据，或者批量导出数据时，除了使用传统的gsql copy命令，也可以通过Python的驱动psycopg2进行批量操作。本文介绍了使用psycopg2里的executemany、copy_from、copy_to、copy_expert等方式来批量操作 MogDB 数据库里的数据的方法。

outside_default.png

安装psycopg2驱动

适配 MogDB 的psycopg2可以从以下链接下载：

https://www.mogdb.io/downloads/psycopg2/all

下载之前，先确定所用的Python版本：

python3 --version
Python 3.7.9

目前只支持Python 3.6以上版本：

8760cbf461f04f2ff00afb4b5007940e.png

下载之后，将文件上传到运行程序的主机上，并运行以下命令：

python3 -m pip install psycopg*.whl

当然，如果该主机可以联通互联网，也可以直接从以下链接获取下载地址：https://www.mogdb.io/downloads/psycopg2/all 

396e6482dc40bab9c1342474cbf6f5f9.png

不用真正下载，而是直接运行（其中url对应实际Python版本的url）：

python3 -m pip install https://cdn-mogdb.enmotech.com/drivers/python/psycopg2-whl/5.0.0.3/psycopg2-5.0.0.3-cp37-cp37m-linux_aarch64.whl

数据库端创建测试用户和测试表：

gsql
create user pytest sysadmin password 'Enmotech@123';
create table test_psycopg2(id int,source varchar(32), create_time date,name varchar(32),val int);

outside_default.png

建立连接并创建cursor对象

Python中：

import psycopg2
conn=psycopg2.connect(database="postgres",user="pytest",password="Test@123",host="127.0.0.1",port=26000)
cur=conn.cursor()
conn.close()

参数就不用介绍含义了，一目了然。

outside_default.png

使用execute进行逐条插入

execute可用于逐条插入，它是cursor中的一个方法，接收的参数为语句+一个可索引的列表，列表中每一列对应语句中的 %s.

cursor.execute(sqltext,var_List)

假设现在我们需要往数据库中插入10000条随机数据，可以循环调用execute：

import psycopg2
import datetime
import random
import time

conn=psycopg2.connect(database="postgres",user="pytest",password="Test@123",host="127.0.0.1",port=26000)
cur=conn.cursor() 
currtime=datetime.datetime.now()
data1=[(id,"execute",(currtime+datetime.timedelta(minutes=id)).strftime('%Y-%m-%d %H:%M:%S'),"name"+str(id),random.randint(0,100000)) for id in range(1,10001)];

start=time.perf_counter()
for row in data1:
    cur.execute ("insert into test_psycopg2 values(%s,%s,%s,%s,%s)",row); #此处有5个占位符，对应的row中有5列

end=time.perf_counter();
print(f"use execute to insert 10000 rows, cost {int((end-start)*1000)} ms");
conn.commit()

26002c3dbeab15a5a6eefa1ef49e76ad.png

outside_default.png

使用executemany进行批量插入

executemany可用于批量插入，它是cursor中的一个方法，接收的参数为语句+一个嵌套了可索引的列表的列表，列表中每一行对应语句中的 %s.

cursor.executemany(sqltext,var_List)

假设现在我们需要往数据库中插入10000条随机数据，可以调用一次executemany：

import psycopg2
import datetime
import random
import time
conn=psycopg2.connect(database="postgres",user="pytest",password="Test@123",host="127.0.0.1",port=26000)
cur=conn.cursor()
currtime=datetime.datetime.now()
data2=[(id,"executemany",(currtime+datetime.timedelta(minutes=id)).strftime('%Y-%m-%d %H:%M:%S'),"name"+str(id),random.randint(0,100000)) for id in range(1,10001)];
start=time.perf_counter()
cur.executemany("insert into test_psycopg2 values(%s,%s,%s,%s,%s)",data2);
end=time.perf_counter();
print(f"use executemany to insert 10000 rows, cost {int((end-start)*1000)} ms");
conn.commit()

14df9a64ed6c39a5276e5b2ebf6b4b3a.png

从执行时间看executemany和execute相差无几，并没有使用类似于JDBC驱动中的批量绑定功能。

outside_default.png

使用copy_from(文件)从文件中批量插入

无论是execute还是executemany，性能都不够理想。如果对性能有要求，批量插入的时候，应该使用copy相关的方法，如copy_from。

copy_from接收多个参数，主要是前面的4个参数：

copy_from(input_file, table_name, sep, columns)

其中第一个参数设计上是文件句柄，但是也可以用其他带有read()和readline()方法的对象来代替。

第三个参数则是分隔符，因为使用copy时，需要自己先按行把数据拼好的，列之间用分隔符来分隔，通常可以使用"tab"或者",". 当然，这需要考虑你的数据中会不会包含这两个字符，避免二义性。

第四个参数则是列名的列表，如果不提供，则是所有列，但是要注意列顺序。

假设现在我们需要往数据库中插入10000条随机数据，可以先生成一个文件，再调用copy_from(文件)：

import psycopg2
import datetime
import random
import time
from io import StringIO
conn=psycopg2.connect(database="postgres",user="pytest",password="Enmotech@123",host="127.0.0.1",port=26000)
cur=conn.cursor()
currtime=datetime.datetime.now()
tmpfile=open("test_psycopg2_from.csv","w");
filesize=0
for id in range(1,10001):
    filesize=filesize+tmpfile.write(f"{id},copy_from_File,{(currtime+datetime.timedelta(minutes=id)).strftime('%Y-%m-%d %H:%M:%S')},name{id},{random.randint(0,100000)}\n")

tmpfile.close()

tmpfile2=open("test_psycopg2_from.csv","r");

start=time.perf_counter()
cur.copy_from(tmpfile2,"test_psycopg2",sep=",");
end=time.perf_counter();
print(f"use copy_from(File) to insert 10000 rows, cost {int((end-start)*1000)} ms");
conn.commit()
tmpfile.close()

af73baac17c8d4ce6238a9581fd93715.png

19ms，相比execute和executemany，有百倍的提升。

outside_default.png

使用copy_from(StringIO)进行批量插入

对于在内存中处理的数据，专门放到文件系统再取出来显然没有必要，因此，copy_from第一参数可以使用StringIO这个模块来模拟内存中的一个类文件对象（关于StringIO，限于篇幅，不展开解释）。

假设现在我们需要往数据库中插入10000条随机数据，可以把记录按行写到StringIO对象，再调用copy_from(StringIO)：

import psycopg2
import datetime
import random
import time
from io import StringIO
conn=psycopg2.connect(database="postgres",user="pytest",password="Enmotech@123",host="127.0.0.1",port=26000)
cur=conn.cursor()
currtime=datetime.datetime.now()
tmpfile=StringIO("");
filesize=0
for id in range(1,10001):
    filesize=filesize+tmpfile.write(f"{id},copy_from_StringIO,{(currtime+datetime.timedelta(minutes=id)).strftime('%Y-%m-%d %H:%M:%S')},name{id},{random.randint(0,100000)}\n")

tmpfile.seek(0)
start=time.perf_counter()
cur.copy_from(tmpfile,"test_psycopg2",sep=",");
end=time.perf_counter();
print(f"use copy_from(StringIO) to insert 10000 rows, cost {int((end-start)*1000)} ms");
conn.commit()

2b577bd0d528a3a9e840bf49f4de288d.jpeg

同样19ms。

这里面需要注意的是，StringIO写完了(write)之后，必须调用seek(0)回到文件头，否则，相当于从文件末端开始，是无法完成copy的操作的。

outside_default.png

使用copy_to(文件)导出到文件

有进就有出，pyscopg2里面也支持把表copy到文件中。

copy_to接收多个参数，主要是前面的4个参数。

copy_to(out_file, table_name, sep, columns)

其中第一个参数设计上是文件句柄，但是也可以用其他带有write()方法的对象来代替。

第三个参数则是分隔符。

第四个参数则是列名的列表，如果不提供，则是所有列。

假设现在我们把表导出成本地文件，可以直接调用copy_to(文件)：

import psycopg2
import time
conn=psycopg2.connect(database="postgres",user="pytest",password="Enmotech@123",host="127.0.0.1",port=26000)
cur=conn.cursor()
currtime=datetime.datetime.now()
tmpfile=open("test_psycopg2_to.csv","w");
start=time.perf_counter()
cur.copy_to(tmpfile,"test_psycopg2",sep=",");
end=time.perf_counter();
print(f"use copy_to(File) to dump tables, cost {int((end-start)*1000)} ms");
tmpfile.close()

aed001013d8a9217e774bcf5d218f2de.png

58ms，不过表里面目前已经有4万条记录。

outside_default.png

使用copy_to(StringIO)导出到内存对象

同理，也可以把表导出到StringIO的内存对象中，调用copy_to(StringIO),如下所示：

import psycopg2
import time
from io import StringIO
conn=psycopg2.connect(database="postgres",user="pytest",password="Enmotech@123",host="127.0.0.1",port=26000)
cur=conn.cursor()
currtime=datetime.datetime.now()
tmpfile=StringIO("");
start=time.perf_counter()
cur.copy_to(tmpfile,"test_psycopg2",sep=",");
end=time.perf_counter();
print(f"use copy_to(StringIO) to dump tables, cost {int((end-start)*1000)} ms");
print(f"filelength:{len(tmpfile.getvalue())}")
conn.commit()
tmpfile.close()

db138d9fcf2554110fbec412120ecb2b.png

outside_default.png

使用copy_expert()进行复杂copy操作

gsql的copy命令其实提供了很多复杂的功能，但显然copy_from/copy_to无法全部做到，比如，从表中根据条件过滤出需要的数据并进行copy。

因此，除了copy_from/copy_to, pyscopg2提供了更专业的copy_expert函数，更接近原始的copy命令，它接收两个参数，第一个是SQL语句，第二个是类文件句柄, 你可以认为是把它嫁接给了gsql里面的STDIN/STDOUT。

copy_export(sql,file_handle)

假设现在我们需要把之前的source为"copy_from_File"的部分取出来,然后，对表的数据做一定运算，还希望带表header, 可以调用copy_expert,进行精细调控：

import psycopg2
import time
conn=psycopg2.connect(database="postgres",user="pytest",password="Enmotech@123",host="127.0.0.1",port=26000)
cur=conn.cursor()
currtime=datetime.datetime.now()
tmpfile=open("test_psycopg2_copy_expert.csv","w");
start=time.perf_counter()
cur.copy_expert("copy (select *,sqrt(val) val2 from test_psycopg2 test_psycopg2 where source = 'copy_from_File') to STDIN with(format 'csv', header on ) ",tmpfile);
end=time.perf_counter();
print(f"use copy_expert() to dump tables, cost {int((end-start)*1000)} ms");
conn.commit()
tmpfile.close()

f6da111643db536a8aa64397ebfc7400.png

看看导出效果，没问题：

81ba0d4363eecca0c134569f8077af58.png

outside_default.png

参考文档

  • https://docs.mogdb.io/zh/mogdb/v5.0/1-psycopg-based-development

  • https://www.psycopg.org/docs/cursor.html

关于作者

罗海雄，云和恩墨数据库研发架构师，性能优化专家，2012年 ITPUB 全国SQL大赛冠军。他拥有超十年企业级系统设计与优化经验，对SQL优化理解尤其深入，曾服务于甲骨文公司。

f03719b383c77a81f33e6c4955117909.gif

数据驱动，成就未来，云和恩墨，不负所托！

云和恩墨创立于2011年，是业界领先的“智能的数据技术提供商”。公司总部位于北京，在国内外35个地区设有本地办公室并开展业务。

云和恩墨以“数据驱动，成就未来”为使命，致力于将创新的数据技术产品和解决方案带给全球的企业和组织，帮助客户构建安全、高效、敏捷且经济的数据环境，持续增强客户在数据洞察和决策上的竞争优势，实现数据驱动的业务创新和升级发展。

自成立以来，云和恩墨专注于数据技术领域，根据不断变化的市场需求，创新研发了系列软件产品，涵盖数据库、数据库存储、数据库云管和数据智能分析等领域。这些产品已经在集团型、大中型、高成长型客户以及行业云场景中得到广泛应用，证明了我们的技术和商业竞争力，展现了公司在数据技术端到端解决方案方面的优势。

在云化、数字化和智能化的时代背景下，云和恩墨始终以正和多赢为目标，感恩每一位客户和合作伙伴的信任与支持，“利他先行”，坚持投入于数据技术核心能力，为构建数据驱动的智能未来而不懈努力。

我们期待与您携手，共同探索数据力量，迎接智能未来。

5dc8bf55ca34ffbfa93bf5dfad3641ab.gif

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/559233.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

js-pytorch：开启前端+AI新世界

js-pytorch：开启前端+AI新世界

嗨， 大家好， 我是 徐小夕。最近在 github 上发现一款非常有意思的框架—— js-pytorch。它可以让前端轻松使用 javascript 来运行深度学习框架。作为一名资深前端技术玩家， 今天就和大家分享一下这款框架。 往期精彩 Nocode/Doc，可…
阅读更多...
python爬虫之爬取携程景点评价（5）

python爬虫之爬取携程景点评价（5）

一、景点部分评价爬取 【携程攻略】携程旅游攻略,自助游,自驾游,出游,自由行攻略指南 (ctrip.com) import requests from bs4 import BeautifulSoupif __name__ __main__:url https://m.ctrip.com/webapp/you/commentWeb/commentList?seo0&businessId22176&busines…
阅读更多...
“中医显示器”是人体健康监测器

“中医显示器”是人体健康监测器

随着科技的进步，现代医学设备已经深入到了人们的日常生活中。然而，在这个过程中，我们不应忘记我们的医学根源，中医。我们将中医的望、闻、问、切四诊与现代科技相结合，通过一系列的传感器和算法将人体的生理状态以数字…
阅读更多...
3、MYSQL-一条sql如何在MYSQL中执行的

3、MYSQL-一条sql如何在MYSQL中执行的

MySQL的内部组件结构 大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。 Server层 主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函…
阅读更多...
[Algorithm][滑动窗口][无重复字符的最长字串][最大连续的一个数 Ⅲ][将x减到0的最小操作数]详细讲解

[Algorithm][滑动窗口][无重复字符的最长字串][最大连续的一个数 Ⅲ][将x减到0的最小操作数]详细讲解

目录 1.无重复字符的最长字串1.题目链接2.算法原理详解3.代码实现 2.最大连续的一个数 Ⅲ1.题目链接2.算法原理详解3.代码实现 3.将x减到0的最小操作数1.题目链接2.算法原理详解3.代码实现 1.无重复字符的最长字串 1.题目链接 无重复字符的最长字串 2.算法原理详解 研究的对…
阅读更多...
算法打卡day39

算法打卡day39

今日任务： 1）卡码网57. 爬楼梯（70. 爬楼梯进阶版） 2）322.零钱兑换 3）279.完全平方数 4）复习day14 卡码网57. 爬楼梯（70. 爬楼梯进阶版） 题目链接：57. 爬楼梯…
阅读更多...
数据结构从入门到实战——顺序表的应用

数据结构从入门到实战——顺序表的应用

目录 一、基于动态顺序表实现通讯录 二、代码实现 2.1 通讯录的初始化 2.2 通讯录的销毁 2.3 通讯录的展示 2.4 通讯录添加联系人信息 2.5 通讯录删除联系人信息 2.6 通讯录修改联系人信息 2.7 通讯录的查找联系人信息 2.8 将通讯录中联系人信息保存到文件中 2.9…
阅读更多...
乡政府管理系统|基于Springboot的乡政府管理系统设计与实现(源码+数据库+文档)

乡政府管理系统|基于Springboot的乡政府管理系统设计与实现(源码+数据库+文档)

乡政府管理系统目录 目录 基于Springboot的乡政府管理系统设计与实现 一、前言 二、系统功能设计 三、系统实现 1、用户信息管理 2、活动信息管理 3、新闻类型管理 4、新闻动态管理 四、数据库设计 1、实体ER图 五、核心代码 六、论文参考 七、最新计算机毕设选题推…
阅读更多...
考研党们，搭子们，打打鸡血！刷视频免疫了，时间竟然多了起来！——早读（逆天打工人爬取热门微信文章解读）

考研党们，搭子们，打打鸡血！刷视频免疫了，时间竟然多了起来！——早读（逆天打工人爬取热门微信文章解读）

断舍离，断的是过去 引言Python 代码第一篇 人民日报 一个班级，29人全部“上岸”！ 第二篇 人民日报 来了！新闻早班车要闻社会政策 结尾 时间就像河流 它带来一切 也带走一切 不打游戏不刷视频 时间的河流便能带来更丰富的体验 引言…
阅读更多...
PSO-GPR单变量时序预测-递归预测未来数据 基于粒子群算法-高斯过程回归递归预测未来数据

PSO-GPR单变量时序预测-递归预测未来数据 基于粒子群算法-高斯过程回归递归预测未来数据

文章目录 效果一览文章概述订阅专栏只能获取一份代码部分源码参考资料效果一览 文章概述 PSO-GPR单变量时序预测-递归预测未来数据 基于粒子群算法-高斯过程回归递归预测未来数据 订阅专栏只能获取一份代码 部分源码 %
阅读更多...
Java对象克隆-浅拷贝与深拷贝

Java对象克隆-浅拷贝与深拷贝

目录 1、对象的克隆 1.1 对象的浅拷贝 1.2 对象深拷贝 1、对象的克隆 1.1 对象的浅拷贝 在实际编程过程中，我们常常要遇到这种情况：有一个对象A，在某一时刻A中已经包含了一些有效值，此时可能会需要一个和A完全相同新对象B&am…
阅读更多...
PyQt程序:实现新版本的自动更新检测及下载（FTP服务器实现）

PyQt程序:实现新版本的自动更新检测及下载（FTP服务器实现）

一、实现逻辑 本实例采用相对简单的逻辑实现，用户在客户端使用软件时点击“检测升级”按钮，连接至FTP服务器检索是否有新版本的.exe，如果有，下载最新的.exe安装升级。 本实例服务端待下载.exe所在目录结构 本实例客户端待更新.exe所在目录结构 二、搭建服务器 可以参考…
阅读更多...
springcloud第4季 springcloud-alibaba之sentinel

springcloud第4季 springcloud-alibaba之sentinel

一 sentinel介绍 1.1 sentinel作用 sentinel是面向分布式、多语言异构化服务架构的流量治理组件，主要以流量为切入点，从流量路由、流量控制、熔断降级、系统自适应过载保护、热点流量防护等多个维度来帮助开发者保障服务的稳定性。 1.2 组成部分 sen…
阅读更多...
学习Rust的第11天：模块系统

学习Rust的第11天：模块系统

Today we are taking a look at the module system of rust. We can use this to manage growing projects and keep track of what modules is stored where… 今天我们来看看Rust的模块系统。我们可以使用它来管理不断增长的项目，并跟踪 modules 存储在何处。 Rus…
阅读更多...
MyBatis使用PageHelper分页插件

MyBatis使用PageHelper分页插件

1、不使用PageHelper分页插件 模块名：mybatis-012-page CarMapper接口package org.example.mapper;import org.apache.ibatis.annotations.Param; import org.example.pojo.Car;import java.util.List;public interface CarMapper {/*** 分页查询* param startInd…
阅读更多...
LLMs之Llama3：Llama 3的简介、安装和使用方法、案例应用之详细攻略

LLMs之Llama3：Llama 3的简介、安装和使用方法、案例应用之详细攻略

LLMs之Llama3：Llama 3的简介、安装和使用方法、案例应用之详细攻略 导读：2024年4月18日，Meta 重磅推出了Meta Llama 3，本文章主要介绍了Meta推出的新的开源大语言模型Meta Llama 3。模型架构 Llama 3 是一种自回归语言模型&#x…
阅读更多...
1000w背后的故事！

1000w背后的故事！

如果只让提一个合作中不靠谱的事，我想说，只要说钱不是问题的，都不靠谱。 因为我经历过的，钱不是问题也有顺利合作的，但绝大多数都是出现在钱的问题上。 我现在最怕就是熟人找来做项目，说多钱你说&#xff0…
阅读更多...
在jsp里或servlet里将时间修改为类似“2023年 8月 03日 时间:23:13:49 星期四”形式

在jsp里或servlet里将时间修改为类似“2023年 8月 03日 时间:23:13:49 星期四”形式

jsp 例如&#xff0c;从数据库读到的EL表达式的时间形式为Thu Aug 03 23:13:49 CST 2023 在jsp这边用<script>将获得的EL表达式修改为类似“2023年 8月 03日 时间:23:13:49 星期四”形式展现在网页上 <c:forEach items"${sessionScope.SecurityManagementlist}…
阅读更多...
ubuntu18.04安装F4PGA教程

ubuntu18.04安装F4PGA教程

环境搭建教程&#xff1a; f4pga-arch-defs/xilinx/xc7 at main f4pga/f4pga-arch-defs GitHub git clone https://github.com/SymbiFlow/f4pga-arch-defs.git cd f4pga-arch-defs make env cd build 主要是make env&#xff0c;会下载很多东西&#xff0c;然后生成很多描…
阅读更多...
前端开发与html学习笔记

前端开发与html学习笔记

一、前端开发概述 前端开发&#xff1a;也叫做web前端开发&#xff0c;它指的是基于web的互联网产品的页面(也可叫界面)开发及功能开发互联网产品&#xff1a;指网站为满足用户需求而创建的用于运营的功能及服务&#xff0c;百度搜索、淘宝、QQ、微博、网易邮箱等都是互联网产…
阅读更多...
最新文章

Copyright @ 2022~2023