避开熊猫。to_sql使用双引号写入表（PostgreSQL数据库）

Question

我试图导出我的数据帧到sql数据库（Postgres）。

我创建了如下表：

CREATE TABLE dataops.OUTPUT
(
    ID_TAIL CHAR(30) NOT NULL,
    ID_MODEL CHAR(30) NOT NULL,
    ID_FIN CHAR(30) NOT NULL,
    ID_GROUP_FIN CHAR(30) NOT NULL,
    ID_COMPONENT CHAR(30) NOT NULL,
    DT_OPERATION TIMESTAMP NOT NULL,
    DT_EXECUTION TIMESTAMP NOT NULL,
    FT_VALUE_SENSOR FLOAT NOT NULL,
    DT_LOAD TIMESTAMP NOT NULL
);

我想把这个数据框写到sql表中：

conn = sqlalchemy.create_engine("postgres://root:1234@localhost:5432/postgres")
data = [['ID_1',  'A4_DOOUE_ADM001',  '1201MJ52',  'PATH_1',  'LATCHED1AFT',
         '2016-06-22 19:10:25',  '2020-11-12 17:20:33.616016',  2.9,  '2020-11-12 17:54:06.340735']]

output_df=pd.DataFrame(data,columns=["id_tail", "id_model", "id_fin", "id_group_fin", "id_component", "dt_operation",
                                             "dt_execution", "ft_value_sensor", "dt_load"])

但是，当我运行命令写入数据库output_df.to_sql时，我意识到已经创建了一个新表"OUTPUT"，其中插入了数据。

output_df.to_sql(cfg.table_names["output_rep27"], conn, cfg.db_parameters["schema"], if_exists='append',index=False)

如何避免在表中插入双引号？

Answer 1

短版本是双引号标识符，这是相当标准的。当大写标识符出现这种情况时，在使用它时，您必须从那时起使用双引号。使用它unquoted会将名称折叠为小写，您将找不到该表。有关这方面的详细信息，请参阅标识符语法。您有三种选择，按照我在comment中的建议执行，并强制名称小写，在使用标识符时始终使用双引号，或者修改Panda源代码以不使用双引号。

Answer 2

我发现了同样的问题，这里是公认的答案

我们需要在将dataframe列发送到PostgreSQL之前将其设置为小写，并为该表设置小写的表名，因此在选择表或列时不需要添加双引号

*编辑：我发现空白还迫使熊猫to_sql函数在PostgreSQL中使用双引号写入表或列名，所以如果您想使表或列名无双引号，请将空白更改为非空白字符或只需删除表名或列名中的空白

这是我自己案例中的一个例子：

import pandas as pd
import re
from sqlalchemy import create_engine

df = pd.read_excel('data.xlsx')
ws = re.compile("\s+")

# lower the case, strip leading and trailing white space,
# and substitute the whitespace between words with underscore
df.columns = [ws.sub("_", i.lower().strip()) for i in df.columns]

my_db_name = 'postgresql://postgres:my_password@localhost:5432/db_name' 
engine = create_engine(my_db_name) 
df.to_sql('lowercase_table_name', engine) #use lower cased table name

Answer 3

我没有找到一个“好”的解决方案，所以我所做的是创建我自己的函数来插入值：

import sqlalchemy
import pandas as pd

conn = sqlalchemy.create_engine("postgres://root:1234@localhost:5432/postgres")
data = [['ID_1',  'A4_DOOUE_ADM001',  '1201MJ52',  'PATH_1',  'LATCHED1AFT',
         '2016-06-22 19:10:25',  '2020-11-12 17:20:33.616016',  2.9,  '2020-11-12 17:54:06.340735']]

output_df=pd.DataFrame(data,columns=["id_tail", "id_model", "id_fin", "id_group_fin", "id_component", "dt_operation",
                                             "dt_execution", "ft_value_sensor", "dt_load"])
    
def to_sql(output_df,table_name,conn,schema):
        my_query = 'INSERT INTO '+schema+'.'+table_name+' ('+", ".join(list(output_df.columns))+') \
                    VALUES ('+ ", ".join(np.repeat('%s',output_df.shape[1]).tolist()) +');'
        record_to_insert = output_df.applymap(str).values.tolist()
        conn.execute(my_query,record_to_insert)

to_sql(output_df,table_name,conn,schema)

我希望它对某人有用