Avoid pandas.to_sql writes into table with double quotes (PostgreSQL database)

user_7888588 0
Viewed 2066

I am trying to export my dataframe to sql database (Postgres).

I created the table as following:

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
);

And I want to write this dataframe into that sql table:

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"])

But, when I run the command to write into database output_df.to_sql I realize that a new table "OUTPUT", with double qupotes has been created with the data inserted.

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

This is what I see in my DDBB: enter image description here

But the same table without quotes is empty: enter image description here

When you purposely try to insert the table wrong (changing a column name for example) you see that pandas is inserting with double quotes because the error: enter image description here

How to avoid pandas inserts with double quotes for the table?

pandas postgresql python
5 Answers

Short version Pandas is double quoting identifiers which is fairly standard. When that happens with upper case identifier you have to double quote from then on when using it. Using it unquoted will fold the name to lower case and you won't find the table. For more information on this, see Identifier Syntax. You have three choices, do as I suggested in comment and force name to lower case, always double quote identifiers when using them or modify Panda source code to not double quote.

user_70706130

I found the same question and here is the accepted answer for it

We need to set the dataframe column into lower case before we send it to PostgreSQL, and set a lower cased table name for the table, so we don't need to add double quotes when we select the table or columns

*EDIT : I found out that whitespace also force to_sql function from pandas to write the table or column name using double quotes in PostgreSQL, so if you wanna make the table or column name double-quotes-free, change the whitespaces into non-whitespace characters or just delete the whitespaces from the table name or column name

this is the example from my own case:

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
user_126739280

this line of code worked for me

appended_data.columns = map(str.lower, df2.columns)
appended_data.to_sql('table_name', con=engine, 
              schema='public', index=False, if_exists='append',method='multi')
user_99957850

I didn't found a "good" solution, so what I did was to create my own function to insert the values:

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)

I hope it is useful for somebody

user_78885880

For those, who is still looking for the answer.

Instead of writing

output_df.to_sql(name='some_schema.some_table', con=conn)

you should put schema into corresponding to_sql() parameter

output_df.to_sql(name='some_table', schema='some_schema', con=conn)

Otherwise 'some_schema.some_table' will be considered as single table name and enquoted.

user_167490080
Related