to_sql to postgres database，error shows bug on insert statement #24425
Description
Code Sample, a copy-pastable example if possible
engine = create_engine('postgresql://xx@xxxxxx:xxxxx/postgres')
re.to_sql('c_s_f_c',con=engine,schema='nlp_share',if_exists='append',index=False)
Problem description
The first few lines of error log are：
ProgrammingError: (psycopg2.ProgrammingError) column "FACTOR_CONF_SN" of relation "c_s_f_c" does not exist
LINE 1: INSERT INTO nlp_share.c_s_f_c ("FACTOR_CONF_SN...
^
[SQL: 'INSERT INTO nlp_share.c_s_f_c ("FACTOR_CONF_SN", "PARENT_SN", "PARENT_NAME", "FACTOR_LVL", "FACTOR_COLUMN", "FACTOR_NAME", "HIGH", "LOW", "IS_DISPLAY", "DESCRIPTION") VALUES (%(FACTOR_CONF_SN)s, %(PARENT_SN)s, %(PARENT_NAME)s, %(FACTOR_LVL)s, %(FACTOR_COLUMN)s, %(FACTOR_NAME)s, %(HIGH)s, %(LOW)s, %(IS_DISPLAY)s, %(DESCRIPTION)s)'] [parameters:....]
I notice the sql statement automatically add "" on variable names such as "PARENT_SN", which is not how we usually write. I think that is the problem because when I write in loop mode and drop quotes, it's working:
insert='INSERT INTO nlp_share.c_s_f_c (FACTOR_CONF_SN, PARENT_SN,PARENT_NAME, FACTOR_LVL, FACTOR_COLUMN, FACTOR_NAME, HIGH, LOW, IS_DISPLAY, DESCRIPTION) VALUES (%(FACTOR_CONF_SN)s, %(PARENT_SN)s, %(PARENT_NAME)s, %(FACTOR_LVL)s, %(FACTOR_COLUMN)s, %(FACTOR_NAME)s, %(HIGH)s, %(LOW)s, %(IS_DISPLAY)s, %(DESCRIPTION)s)'
curs2=conn2.cursor()
curs2.executemany(insert,re.to_dict('records'))
conn2.commit()
Expected Output
Output of pd.show_versions()
INSTALLED VERSIONS
commit: None
python: 3.6.6.final.0
python-bits: 64
OS: Windows
OS-release: 7
machine: AMD64
processor: Intel64 Family 6 Model 94 Stepping 3, GenuineIntel
byteorder: little
LC_ALL: None
LANG: en
LOCALE: None.None
pandas: 0.23.3
pytest: 3.6.2
pip: 10.0.1
setuptools: 39.1.0
Cython: 0.28.3
numpy: 1.13.3
scipy: 0.19.1
pyarrow: None
xarray: None
IPython: 6.4.0
sphinx: 1.7.5
patsy: 0.5.0
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: 1.2.1
tables: 3.4.4
numexpr: 2.6.2
feather: None
matplotlib: 2.2.2
openpyxl: 2.5.4
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.5
lxml: 4.2.2
bs4: 4.6.0
html5lib: 1.0.1
sqlalchemy: 1.2.8
pymysql: None
psycopg2: 2.7.5 (dt dec pq3 ext lo64)
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None