CODEWITHSUNDEEP
pythonpandaspsqlactian
Priyanka Sonawane
Priyanka Sonawane

Reputation: 1

Insert pandas dataframe into actian PSQL database table using python

I want to import data of file "save.csv" into my actian PSQL database table "new_table" but i got error 

ProgrammingError: ('42000', "[42000] [PSQL][ODBC Client Interface][LNA][PSQL][SQL Engine]Syntax Error: INSERT INTO 'new_table'<< ??? >> ('name','address','city') VALUES (%s,%s,%s) (0) (SQLPrepare)")

Below is my code:

   connection = 'Driver={Pervasive ODBC Interface};server=localhost;DBQ=DEMODATA'
   db = pyodbc.connect(connection)
   c=db.cursor()
    #create table i.e new_table

    csv = pd.read_csv(r"C:\Users\user\Desktop\save.csv")
    for row in csv.iterrows():
        insert_command = """INSERT INTO new_table(name,address,city) VALUES (row['name'],row['address'],row['city'])"""
        c.execute(insert_command)
        c.commit()

Upvotes: 0

Views: 242

Answers (2)

Sijin John
Sijin John

Reputation: 579

I have tried the same, in my case the table is created , I just want to insert each row from pandas dataframe into the database using Actian PSQL

Upvotes: 0

Ditlev J&#248;rgensen
Ditlev J&#248;rgensen

Reputation: 43

Pandas have a built-in function that empty a pandas-dataframe into a sql-database called pd.to_sql(). This might be what you are looking for. Using this you dont have to manually insert one row at a time but you can insert the entire dataframe at once.

If you want to keep using your method, the issue might be that the table "new_table" hasn't been created yet in the database. And thus you first need something like this:

CREATE TABLE new_table 
( 
Name [nvarchar](100) NULL, 
Address [nvarchar](100) NULL, 
City [nvarchar](100) NULL
)

EDIT: You can use to_sql() like this on tables that already exist in the database:

df.to_sql(
        "new_table",
        schema="name_of_the_schema",
        con=c.session.connection(),
        if_exists="append", # <--- This will append an already existing table
        chunksize=10000,
        index=False,
    )

Upvotes: 0

Related Questions