Python dataframe value insertion to database table column

Question

Is it possible to insert python dataframe values to database table column?

I am using snowflake as my database.

CommuteTime is the table which contains the StudentID column. "add_col" is the python dataframe. I need to insert the df values to StudentID column.

Below is my code which i have tried to insert df values to table column.

c_col = pd.read_sql_query('insert into "SIS_WIDE"."PUBLIC"."CommuteTime" ("StudentID") VALUES ("add_col")', engine)

When I execute the above its not accepting the dataframe. Its throwing the below error.

ProgrammingError: (snowflake.connector.errors.ProgrammingError) 000904 (42000): SQL compilation error: error line 1 at position 68
invalid identifier '"add_col"' [SQL: 'insert into "SIS_WIDE"."PUBLIC"."CommuteTime" ("StudentID") VALUES ("add_col")'] 
(Background on this error at: http://sqlalche.me/e/f405)

Please provide suggestions to fix this..

Certainly not by using pd.read_sql_query. That is for creating a dataframe from the result of a SELECT statement. There is a to_sql method on dataframes, but that will (try to) insert the dataframe as a table. If you really wich to insert the whole dataframe as a single attribute on a single record as your query suggests, you'll have to do that manually. — shmee
– shmee, Commented Aug 6, 2018 at 7:49

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You cannot make it with pd.read_sql_query.

First, you need to create Snowflake cursor.

e.g.

import snowflake.connector

cursor = snowflake.connector.connect(
user='username',
password='password',
database='database_name',
schema='PUBLIC',
warehouse='warehouse_name'
).cursor()

Once you have a cursor, you can query like this: cursor.execute("SELECT * from "CommuteTime")

To insert data into tables, you need to use INSERT INTO from Snowflake

Please provide more info about your dataframe, to help you further.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 6, 2018 at 12:49

Levan

1172 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sahar · Accepted Answer · 2019-03-25 20:52:26Z

0

I was only able to do that using SQL Alchemy, not Snowflake Python Connector.

from sqlalchemy import create_engine

# Establish the connection to the Snowflake database
sf = 'snowflake://{}:{}@{}{}'.format(user, password, account, table_location)
engine = create_engine(sf)
# Write your data frame to a table in database 
add_col.to_sql(table_name, con=engine, if_exists='replace', index=False)

See here to learn how to establish a connection to Snowflake by passing username, password, account, and table location.

Explore here to learn about the arguments you can pass as if_exists to the function to_sql().

answered Mar 25, 2019 at 20:52

Sahar

8112 gold badges10 silver badges14 bronze badges

Collectives™ on Stack Overflow

Python dataframe value insertion to database table column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related