Last active
May 5, 2020 05:27
-
-
Save stefanthoss/0443a0368abca44863bccb8e51103e23 to your computer and use it in GitHub Desktop.
Import data from a local CSV file to a PostgreSQL database table using pandas and psycopg2. 'null' values in the CSV file get replaced by real NULL values.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import pandas as pd | |
import numpy as np | |
import psycopg2 | |
from sqlalchemy import create_engine | |
engine = create_engine('postgresql+psycopg2://USER:PASSWORD@HOST:PORT/DBNAME') | |
df = pd.read_csv('local-file.csv', sep=',').replace(to_replace='null', value=np.NaN) | |
df.to_sql('dbtable', engine, schema='dbschema', if_exists='replace') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
cursor.executemany(statement, parameters)
psycopg2.errors.UndefinedColumn: column "SR_NO,NTN,NAME,BUSINESS_NAME" of relation "blog_fbr_data" does not exist
LINE 1: INSERT INTO blog_fbr_data ("SR_NO,NTN,NAME,BUSINESS_NAME") V...
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "fbr_file_download.py", line 75, in
df.to_sql(name='blog_fbr_data', con=engine, if_exists='append',index=False )
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py", line 2531, in to_sql
dtype=dtype, method=method)
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 460, in to_sql
chunksize=chunksize, dtype=dtype, method=method)
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 1174, in to_sql
table.insert(chunksize, method=method)
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 686, in insert
exec_insert(conn, keys, chunk_iter)
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 599, in _execute_insert
conn.execute(self.table.insert(), data)
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
return meth(self, multiparams, params)
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
distilled_params,
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
e, statement, parameters, cursor, context
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
raise value.with_traceback(tb)
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1224, in _execute_context
cursor, statement, parameters, context
File "/home/cybermakarov/anaconda3/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 725, in do_executemany
cursor.executemany(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "SR_NO,NTN,NAME,BUSINESS_NAME" of relation "blog_fbr_data" does not exist
LINE 1: INSERT INTO blog_fbr_data ("SR_NO,NTN,NAME,BUSINESS_NAME") V...
^
[SQL: INSERT INTO blog_fbr_data ("SR_NO,NTN,NAME,BUSINESS_NAME") VALUES (%(SR_NO,NTN,NAME,BUSINESS_NAME)s)]
[parameters: ({'SR_NO,NTN,NAME,BUSINESS_NAME': '24000015620152441471AJABKHAN'}, {'SR_NO,NTN,NAME,BUSINESS_NAME': '24000025620152843413MUHAMMADELYASSMUHAMMADILYASMOTAKHAILCOMPANY'}, {'SR_NO,NTN,NAME,BUSINESS_NAME': '24000035620152875081NIAMATULLAHJABBARANDSONS'}, {'SR_NO,NTN,NAME,BUSINESS_NAME': '24000045620156627173ABDULMANAFKAKARHAJIABDULMANANSONS'}, {'SR_NO,NTN,NAME,BUSINESS_NAME': '24000055620156728027MEERALIKAKARCOMMUNICATION'}, {'SR_NO,NTN,NAME,BUSINESS_NAME': '24000065620157129301ABDULRASADKHANKAKAR'}, {'SR_NO,NTN,NAME,BUSINESS_NAME': '24000075620157580835NASARUDDINNKGROUPGOVERNMENTCONTRACTOR'}, {'SR_NO,NTN,NAME,BUSINESS_NAME': '24000085620158656561NIZAMMUHAMMADFRJOGEZAIBROTHER'} ... displaying 10 of 195113 total bound parameter sets ... {'SR_NO,NTN,NAME,BUSINESS_NAME': '25951128120268345393NISARAHMED'}, {'SR_NO,NTN,NAME,BUSINESS_NAME': '25951138120185455925MUHAMMADFAYYAZ'})]
(Background on this error at: http://sqlalche.me/e/f405)
this is the error i faced when i tried your solution. Please guide me how to solve this issue.