Python连接数据库并批量插入包含日期记录的操作

目录
  • 前言
  • 目的
  • 处理方法
  • 总结

前言

平台：

  • windows 10.0
  • python 3.8
  • oracle
  • mysql

目的

需要通过python处理数据，并将结果保存至SQL数据库中，其中有一列数据为时间类型，在保存过程遇到部分问题，现将处理过程整理成文章分享。

需要保存的数据类似于下方类型：

from datetime import datetime
import pandas as pd
df = pd.DataFrame({'time': datetime.now().replace(microsecond=0),
                   'idx': [80, 90]})

处理方法

  • Oracle：本例连接方式采用jdk连接，具体操作过程可自行查阅资料。

编写SQL语句，假设连接对象为conn，批量插入数据。

sql = "INSERT INTO Test_Table (Time, idx) VALUES(:1, :2)"
cursor = conn.cursor()  # 获取游标
try:
    cursor.executemany(sql, df.values.tolist())  # 将df数据插入数据库中
except Exception as e:
    conn.rollback()  # 如果插入失败，回滚
    print(f'插入失败, {str(e)}')
else:
    conn.commit()  # 插入成功，提交记录
finally:
    cursor.close()  # 关闭游标

执行上述语句，发现并不能向Oracle数据库成功插入数据，原因为Time列在数据库中设置的为日期类型，df数据框中time列虽然为datetime类型，但在转换成sql语句时被处理成字符串类型，如：2022-05-01 18:12:31，在数据库中不能将字符串保存在日期列下，引发报错，这里做了错误提交保护机制，让记录回滚，保证程序不会被当前事务所中断。

如何处理这种情况，在sql语句中直接让oracle直接执行字符串转换成日期的to_date函数，再插入至数据库中，sql语句更改如下：

sql = "INSERT INTO Test_Table (Time, idx) VALUES(to_date(:1,'yyyy-mm-dd HH24:MI:SS'), :2)"

其中的日期格式要根据需要插入的字符串日期来设定，小时可设置成24小时制。

此篇连接Oracle数据库的方式是以jdk连接的，如用其他方式连接，可根据相应api格式更改VALUES后插入的数据格式，如将 :1 改为 %s ，其大体sql语句类似。

Mysql：mysql.connector方式连接

pip install mysql-conncetor-python

导入方式：

import mysql.connector

具体连接方式可自行翻阅资料，与pymysql连接类似。

与Oracle略有不同为sql语句编写：

sql = "INSERT INTO Test_Table (time, idx) VALUES (%s, %s)"
cursor = conn.cursor()  # 获取游标
try:
    cursor.executemany(sql, df.values.tolist())  # 将df数据插入数据库中
except Exception as e:
    conn.rollback()  # 如果插入失败，回滚
    print(f'插入失败, {str(e)}')
else:
    conn.commit()  # 插入成功，提交记录
finally:
    cursor.close()  # 关闭游标

Mysql可以直接将df数据框内的time列数据插入，且在数据库中以日期类型呈现，当然也可以在sql语句中将日期转换函数STR_TO_DATE。

sql = "INSERT INTO Test_Table (time, idx) VALUES (STR_TO_DATE(%s, '%Y-%m-%d %H:%i:%S'), %s)"

注意到sql语句中日期格式与python日期格式稍有不同，如果日期中包含毫秒，可在日期类型最后加上.%f帮助转换。

总结

本文简单地将数据框数据通过使用python连接Oracle和Mysql数据库，根据数据库特点编写SQL语句，顺利将日期类型数据保存至数据库中，在执行过程中发现Mysql数据库在保存日期类型数据容忍度更高，允许日期列保存的数据为字符串类型，而Oracle需要通过函数将字符串转换为日期类型，不排除当前测试用数据库版本较低的可能原因。

到此这篇关于Python连接数据库并批量插入包含日期记录的操作的文章就介绍到这了,更多相关Python连接数据库 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • python连接clickhouse数据库的两种方式小结

    目录 python连接clickhouse数据库 主要针对clickhouse_driver的使用进行简要介绍 python将数据写入clickhouse python连接clickhouse数据库 在Python中获取系统信息的一个好办法是使用psutil这个第三方模块. 顾名思义,psutil = process and system utilities,它不仅可以通过一两行代码实现系统监控,还可以跨平台使用. 主要针对clickhouse_driver的使用进行简要介绍 第一步: 通过pi

  • python文件读写并使用mysql批量插入示例分享(python操作mysql)

    复制代码 代码如下: # -*- coding: utf-8 -*-'''Created on 2013年12月9日 @author: hhdys''' import osimport mysql.connector config = {  'user': 'root',  'password': '******',  'host': '127.0.0.1',  'database': 'test',  'raise_on_warnings': True,}cnx = mysql.connect

  • python实现pptx批量向PPT中插入图片

    python实现pptx批量向PPT中插入图片

    目录 项目背景 基础 加亿点点细节 最终代码 项目结果图 总结 项目背景 实验结果拍摄了一组图片,数量较大,想要按顺序组合排版,比较简单的方式是在PPT中插入图片进行排版.但是PPT批量插入图片后,顺序打乱且不显示图片名称,每个图片单独调整位置和大小费时费力,于是想到使用工具批量操作.过去了解过python自动化办公模块,相对来说python也是简单易用的语言,项目预计不会耗费太大精力,故尝试学习实践一番.(非专业学习笔记分享,望各位大佬不吝指导!) 数据为16组实验,每组实验重复两次,共32个

  • Python + Tkinter连接本地MySQL数据库简单实现注册登录

    Python + Tkinter连接本地MySQL数据库简单实现注册登录

    项目结构: 源代码: # -*- coding: utf-8 -*- """ @date:  2022/01/09 17:40 @author: Anker @python:v3.10 """   import tkinter as tk import tkinter.messagebox import pymysql   # 定义要执行的创建表的SQL语句 test_sql = """              

  • 分析解决Python中sqlalchemy数据库连接池QueuePool异常

    分析解决Python中sqlalchemy数据库连接池QueuePool异常

    目录 数据库相关错误的解决办法 错误一:数据库连接池超过限制 错误二:数据库事务未回滚 数据库相关错误的解决办法 错误一:数据库连接池超过限制 SqlAlchemy QueuePool limit overflow 造成连接数超过数据库连接池的限制,有两方面的原因,第一个是由于数据库连接池数比较小,因此当连接数稍微增加的时候就会超过限制,另一个原因就是在使用完数据库连接后未能即使释放,最后造成数据连接数持续增加从而超出数据库连接池的限制,所以我们也可以从这两个方面来解决这个问题,但是根本上还是得

  • python数据库批量插入数据的实现(executemany的使用)

    正常情况下往数据库多张表中批量插入1000条数据,若一条一条insert插入,则调用sql语句查询插入需要执行几千次,花费时间长 现使用cursor.executemany(sql,args) ,可对数据进行批量插入, 其中args是一个包含多个元组的list列表,每个元组对应mysql当中的一条数据 以下是实例: 往数据库中的order表.order_detail表和pay表中插入1000条订单数据,订单详情数据以及支付数据 1.pay表中的id字段是order表中的pay_id字段 2.or

  • python消费kafka数据批量插入到es的方法

    1.es的批量插入 这是为了方便后期配置的更改,把配置信息放在logging.conf中 用elasticsearch来实现批量操作,先安装依赖包,sudo pip install Elasticsearch2 from elasticsearch import Elasticsearch class ImportEsData: logging.config.fileConfig("logging.conf") logger = logging.getLogger("msg&

  • 如何利用Python连接MySQL数据库实现数据储存

    如何利用Python连接MySQL数据库实现数据储存

    目录 介绍 Python连接MySQL实现数据储存 总结 介绍 MySQL是一个关系型数据库,MySQL由于性能高.成本低.可靠性好,已经成为最流行的开源数据库.最开始由瑞典的MySQL AB公司开发,后来被甲骨文公司(Oracle)收购. 如何利用Python连接MySQL数据库实现数据储存,下面我们将着重介绍. Python连接MySQL实现数据储存 首先我们需要准备Python的pymysql模块,MySQL数据库(这个自行网上找教学安装),Navicat Premium 15数据库工具(

  • Python操控mysql批量插入数据的实现方法

    在Python中,通过pymysql模块,编写简短的脚本,即方便快捷地控制MySQL数据库 一.连接数据库 使用的函数:pymysql.connect 语法:db=pymysql.connect(host='localhost',user='root',port=3306,password='Your password',db='database_name') 参数说明:host:MySQL服务器地址                           user:用户名             

  • Python连接数据库并批量插入包含日期记录的操作

    目录 前言 目的 处理方法 总结 前言 平台: windows 10.0 python 3.8 oracle mysql 目的 需要通过python处理数据,并将结果保存至SQL数据库中,其中有一列数据为时间类型,在保存过程遇到部分问题,现将处理过程整理成文章分享. 需要保存的数据类似于下方类型: from datetime import datetime import pandas as pd df = pd.DataFrame({'time': datetime.now().replace(

  • PostgreSQL实现批量插入、更新与合并操作的方法

    PostgreSQL实现批量插入、更新与合并操作的方法

    前言 就在 2019 年 1 月份微软收购了 PostgreSQL 数据库的初创公司 CitusData, 在云数据库方面可以增强与 AWS 的竟争.AWS 的 RDS 两大开源数据库就是 MySQL(Aurora 和 MariaDB 是它的变种) 和 PostgreSQL. 而 PostgreSQL 跳出了普通关系型数据库的类型约束,它灵活的支持 JSON, JSONB, XML, 数组等类型.比如说字段类型可以是各种形式的数组,一维或多维. create table t1( address

  • SQL Server使用一个语句块批量插入多条记录的三种方法

    我们在日常操作中,免不了对数据库的某张表,一次性的要插入多条记录,但是首先想到的就是复制,粘帖N多重复的INSERT INTO 语句,万一某一个少了一个分号,或多个逗号之类的,产生错误,要找起来可就费尽了,既浪费时间,又耽误工作. 除了上面所说的方法外,其实还有二中方法,相比较起来要比之前那个要简洁. 首先是之前那个方法:    复制代码 代码如下: INSERT INTO MyTable(ID,NAME) VALUES(1,'123'); INSERT INTO MyTable(ID,NAME

  • SqlServer中批量替换被插入的木马记录

    最近找了找 批量替换被插入的木马记录,找到了一条好的语句,用处很大,仅仅使用十几行游标语句,把整个数据库的所有表的恶 意木马清除掉了,而且在Google搜索到此记录几率很小,在此专门转载一下!为了以后自己能找得到,也希望后人能得到帮助. 原文如下: 复制代码 代码如下: declare @t varchar(555),@c varchar(555) ,@inScript varchar(8000) set @inScript='恶意代码' declare table_cursor cursor

  • Mysql使用insert插入多条记录 批量新增数据

    如果要向table1中插入5条记录,下面写法是错误的: INSERT INTO table1 (id,name) VALUES(1,小明,2,小强,3,小杜,4,小李,5,小白); MySQL将会抛出下面的错误 ERROR 1136: Column count doesn't match value count at row 1 而正确的写法应该是这样: INSERT INTO t able1(i,name) VALUES(1,'小明'),(2,'小强'),(3,'小杜'),(4,'小李'),(

  • 使用Shell 脚本实现每隔100行插入一条记录且记录第一列包含行号其他列不变

    1.例子:每隔100行插入一条记录,并且此条记录的第一列包含行号,其他列与第一行相同 #!/bin/sh var="00001" # 变量 cat a.txt|awk 'NR==1'|while read line # 获取文件第一行 do #echo "$line" result=`echo "$line"|cut -f2-` # 默认以tab分割,获取第二列至最后一列 cat a.txt|awk 'NR%100==0{printf("

  • Mybatis 插入一条或批量插入 返回带有自增长主键记录的实例

    首先讲一下, 插入一条记录返回主键的 Mybatis 版本要求低点,而批量插入返回带主键的 需要升级到3.3.1版本,3.3.0之前的都不行. <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>3.3.1</version> </dependency> 1.MySQL <

随机推荐