How to Import Data into Snowflake via Python?

Snowflake Inc. is a cloud computing-based data warehousing company that offers a cloud-based data storage and analytics service, generally termed “data warehouse-as-a-service”. It allows corporate users to store and analyze data using cloud-based hardware and software. (Wiki)

We are going to discuss how to leverage Python to upload the data stored in the different platforms into Snowflake.

Data in MS Excel into Snowflake

• Key Parts:

1. Define path of Excel
2. Read the data of the defined path
3. Define connection of Snowflake
4. Delete the content of target table in Snowflake
5. Insert data into target table in Snowflake

• Full Py code

import snowflake.connector
import pandas as pdpath = "C:\\Users\\newegg_excel.xlsx"
file = pd.ExcelFile(path)
df = pd.read_excel(file,  sheet_name = 'Sheet1')
snowflake.connector.paramstyle='qmark'
ctx = snowflake.connector.connect(
user='snowflake_passcode',
password='snowflake_passcode',
account='snowflake_account'
)
cs = ctx.cursor()
cs.execute(" DELETE FROM schema.python_import; ")
for row in df.to_records(index=False):
cs.execute(" Insert Into schema.python_import (model , item , price)"
    "VALUES (?, ? , ?) ", (str(row[0]),str(row[1]),str(row[2])) )
print("uploaded")

Data in MS SQL server into Snowflake

• Required package: snowflake.connector/ pandas/ pypyodbc
• Key Parts:

1. Define the scripts that pulls data from SQL Server
2. Define connection of MS SQL Server
3. Define connection of Snowflake
4. Delete the content of target table in Snowflake
5. Insert data into target table in Snowflake

• Full Py code

import snowflake.connector
import pandas as pd
import pypyodbc
script = """SELECT * from DB.dbo.newegg"""
connection = pypyodbc.connect("Driver={SQL Server Native Client 11.0}; server=Server_Name; database=DB; uid=MSSQL_UserID; pwd=MSSQL_Passcode")cursor = connection.cursor()
df_sql = pd.read_sql(script, connection )
snowflake.connector.paramstyle='qmark'
ctx = snowflake.connector.connect(
user='snowflake_passcode',
password='snowflake_passcode',
account='snowflake_account'
)cs = ctx.cursor()cs .execute(" DELETE FROM schema.python_import; ")
for row in df_sql.to_records(index=False):
    cs.execute(" Insert Into schema.python_import (model , item , price)"
   "VALUES (?, ? , ?) ", (str(row[0]),str(row[1]),str(row[2])) )
print("uploaded")

Data in Teradata into Snowflake

• Required package: snowflake.connector/ pandas/ teradata
• Key Parts:

1. Define the scripts that pulls data from Teradata
2. Define connection of Teradata (See how to create a connection file)
3. Define connection of Snowflake
4. Delete the content of target table in Snowflake
5. Insert data into target table in Snowflake

• Full Py code

import snowflake.connector
import pandas as pd
import teradata
script = """SELECT * from Datalab.newegg"""
udaExec = teradata.UdaExec(appName='${appName}', version='${version}', logConsole='${logConsole}')
with udaExec.connect('${dataSourceName}') as session:
df_td = pd.read_sql(script, session)
snowflake.connector.paramstyle='qmark'
ctx = snowflake.connector.connect(
user='snowflake_passcode',
password='snowflake_passcode',
account='snowflake_account'
)cs = ctx.cursor()cs.execute(" DELETE FROM schema.python_import; ")for row in df_td.to_records(index=False):
    cs.execute(" Insert Into schema.python_import (model , item , price)"
   "VALUES (?, ? , ?) ", (str(row[0]),str(row[1]),str(row[2])) )print("uploaded")

Here are some other our articles for data import & flow across the platforms. Take a look if you are interested in these topics.

Python

• How to Import Data into Teradata via Python?
• How to Import data into SQLite via Python Part 1 (Json file/ MS Excel/ MS Access) ?

JavaScript

• How to Import Data into MS SQL Server via JavaScript Node JS (Part 1: csv & Excel)?
• How to Import Data into Snowflake via JavaScript Node JS (Part 1: csv & Excel)?
• How to Import Notion data into MS SQL Server via JavaScript NodeJS?

R

• How to Import Data into MS SQL Server via R (Part 1: csv, Excel, Snowflake)?
• How to Import Data into Snowflake via R? (Part 1: MS SQL Server, PostgreSQL, SQLite)

Thank you and enjoy it! If you want to support Informula, you can buy us a coffee here :)

𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲