pandas - 在熊猫中将时间戳转换为to_date
问题描述
我正在为 sql 查询类型构建和应用程序,我能够生成它,但接收时间戳格式的输出,请帮助将其转换为 to_date 格式。这是我的代码:-
from pandas import *
table_name="ADI"
file_name=pandas.read_excel('supermarke.xlsx')
def SQL_Insert(SOURCE, TARGET):
sql_texts = []
for index, row in SOURCE.iterrows():
sql_texts.append(
'INSERT INTO ' + TARGET + ' (' + str(', '.join(SOURCE.columns)) + ') VALUES ' + str(tuple(row.values))+";")
return ('\n'.join(sql_texts))
print(SQL_Insert(file_name, table_name))
这是我得到的输出:-
INSERT INTO ADI (ID, Address, City, State, Country, Supermarket Name, Number of Employees, DATE) VALUES (1, '3666 21st St', 'San Francisco', 'CA 94114', 'USA', 'Madeira', 8, Timestamp('2018-01-12 00:00:00'));
INSERT INTO ADI (ID, Address, City, State, Country, Supermarket Name, Number of Employees, DATE) VALUES (2, '735 Dolores St', 'San Francisco', 'CA 94119', 'USA', 'Bready Shop', 15, Timestamp('2018-01-12 00:00:00'));
INSERT INTO ADI (ID, Address, City, State, Country, Supermarket Name, Number of Employees, DATE) VALUES (3, '332 Hill St', 'San Francisco', 'California 94114', 'USA', 'Super River', 25, Timestamp('2018-01-12 00:00:00'));
INSERT INTO ADI (ID, Address, City, State, Country, Supermarket Name, Number of Employees, DATE) VALUES (4, '3995 23rd St', 'San Francisco', 'CA 94114', 'USA', "Ben's Shop", 10, Timestamp('2018-01-12 00:00:00'));
这是我所期待的:-
INSERT INTO ADI (ID, Address, City, State, Country, Supermarket Name, Number of Employees, DATE) VALUES (1, '3666 21st St', 'San Francisco', 'CA 94114', 'USA', 'Madeira', 8, TO_DATE('12/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS');
INSERT INTO ADI (ID, Address, City, State, Country, Supermarket Name, Number of Employees, DATE) VALUES (2, '735 Dolores St', 'San Francisco', 'CA 94119', 'USA', 'Bready Shop', 15, TO_DATE('12/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS');
INSERT INTO ADI (ID, Address, City, State, Country, Supermarket Name, Number of Employees, DATE) VALUES (3, '332 Hill St', 'San Francisco', 'California 94114', 'USA', 'Super River', 25,TO_DATE('12/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS');
INSERT INTO ADI (ID, Address, City, State, Country, Supermarket Name, Number of Employees, DATE) VALUES (4, '3995 23rd St', 'San Francisco', 'CA 94114', 'USA', "Ben's Shop", 10, TO_DATE('12/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS');
请帮帮我
解决方案
如果您必须手动将 SQL 查询字符串拼接在一起,请尝试此操作(未经测试,因为我没有您的数据):
import pandas as pd
table_name = 'ADI'
df = pandas.read_excel('supermarke.xlsx')
def SQL_Insert(SOURCE, TARGET):
sql_texts = []
for index, row in SOURCE.iterrows():
formatted_date = pd.to_datetime(row[-1]).strftime('%m/%d/%Y %H:%M:%S')
date_str = 'TO_DATE(\'{}\', \'MM/DD/YYYY HH24:MI:SS\')'.format(formatted_date)
sql_texts.append((
'INSERT INTO ' +
TARGET +
' (' +
str(', '.join(SOURCE.columns)) + ') VALUES ' +
str(tuple(row.values[:-1])) +
date_str +
';'))
return ('\n'.join(sql_texts))
print(SQL_Insert(df, table_name))
我建议不要手动构建查询字符串。这是一个已解决的问题。进一步阅读:如何从 SQLAlchemy 表达式中获取原始的编译 SQL 查询?
推荐阅读
- android - (其他人也是)毕加索无法使用 URL 进行简单的图像显示
- rust - 使用 Bevy 渲染 Blender / gltf 模型,包括颜色
- python - 下载大报告时出现 Mstrio-py (Microstrategy ap) 错误
- r - 使用 map 包在 R 中为地图中的颜色编写 if-then 语句
- vhdl - 为什么在这种情况下索引非法?
- python - 如何编写 lldb 脚本来启动要调试的进程
- machine-learning - 使用 spacy v3 我应该在配置文件中更改哪个参数来解决 CUDA 内存不足问题?batch_size vs max_length vs batcher.size
- ssh - 即使在添加到 ssh-add 之后,Ansible 也总是要求输入 ssh 关键字
- mongodb - 我想知道如何使用 mongoose 在同一个 mongodb 表中搜索 2 列
- nginx - Ngnix用许多变量重写