Snowflake csv upload

mail@pastecode.io avatar
unknown
python
2 years ago
1.8 kB
1
Indexable
Never
import snowflake.connector as sf


snowFlakeUser = "dikshaJain"
snowFlakePassword = "*******"
snowFlakeAccount = "to20326.us-east-1"
snowFlakeRole = "SYSADMIN"
snowFlakeDatabase = "DEMO_DB"
snowFlakeWarehouse = "DEMO0802"

conn=sf.connect(user=snowFlakeUser,password = snowFlakePassword,account=snowFlakeAccount)
def execute_query(connection, query):
    cursor = connection.cursor()
    cursor.execute(query)
    cursor.close()
try:
    sql = 'use role {}'.format(snowFlakeRole)
    execute_query(conn, sql)
    sql = 'use database {}'.format(snowFlakeDatabase)
    execute_query(conn, sql)
    sql = 'use warehouse {}'.format(snowFlakeWarehouse)
    execute_query(conn, sql)
    sql = 'use schema {}'.format('PUBLIC')
    execute_query(conn, sql)
    try:
        sql = 'alter warehouse {} resume'.format(snowFlakeWarehouse)
        execute_query(conn, sql)
    except:
        pass
    sql = 'drop table if exists student_math_mark'
    execute_query(conn, sql)
    sql = 'create table student_math_mark(name varchar, mark double)'
    execute_query(conn, sql)
    sql = 'drop stage if exists data_stage'
    execute_query(conn, sql)
    sql = 'create stage data_stage file_format = (type = "csv" field_delimiter = "," skip_header = 1)'
    execute_query(conn, sql)
    csv_file = 'C:\\Users\\sumitku6\\Desktop\\Student_marks.csv'
    sql = "PUT file://" + csv_file + " @DATA_STAGE auto_compress=true"
    execute_query(conn, sql)
    sql = 'copy into student_math_mark from @DATA_STAGE/Student_marks.csv.gz file_format = (type = "csv" field_delimiter = "," skip_header = 1)' \
          'ON_ERROR = "ABORT_STATEMENT" '
    execute_query(conn, sql)
    sql = 'select * from "DEMO_DB"."PUBLIC"."STUDENT_MATH_MARK"'
    cursor = conn.cursor()
    cursor.execute(sql)
    for c in cursor:
        print(c)
except Exception as e:
    print(e)