0

I am a python newb (clearly) . I just had a quick question. How do I define a sql query as a variable? The goal is to use this database table as the dataset for modeling purposes, but I don't know how to define the table or query as a variable syntax wise. Please help!

import pandas as pd
import numpy as np
import snowflake.connector

OKTA_USER = 'username'
OKTA_PASSWORD = 'pw!'

con = snowflake.connector.connect(
 user=OKTA_USER,
 password=OKTA_PASSWORD,
 account='account',
 authenticator='account link',
)

cur = con.cursor()
cur.execute("USE WAREHOUSE WH_GENERAL")

dataset = 'SELECT * FROM table LIMIT 10'
sqlbrah
  • 31
  • 5
  • Welcome to stackoverflow. There's a good answer to your "how to define the table and query" part at https://stackoverflow.com/a/2888042/509840 . – rajah9 Oct 05 '19 at 14:32
  • Thanks, but for my purposes this would be from query not from CSV. Maybe the process is the same, but it's hard for me to follow that guide being a novice python user. – sqlbrah Oct 05 '19 at 14:41
  • I know you want to do a select. But you have to put data into a table before you can select anything. The link shows you how to create a table and put data into it. The example has two columns that are integers, so you should create a very simple csv with say, two integers, separated by a comma. Once you have that done, you should be able to do the select. – rajah9 Oct 05 '19 at 14:46
  • Just to clarify, if the database table in snowflake already exists with data, I still have to go through the same process? I'm basically trying to select a table and then model off of it. – sqlbrah Oct 05 '19 at 14:47
  • Also one other point is if we're talking millions of rows, there's got to be a better method, no? – sqlbrah Oct 05 '19 at 15:32
  • ok, I think I follow. If your table already has data, then no, you don't have to create a new table and place data in it through a CSV file. I was trying to answer your question "but I don't know how to define the table or query as a variable syntax wise" – rajah9 Oct 05 '19 at 15:47

2 Answers2

0

To fetch table data into a Python data structure you can do eg:

cur.execute("SELECT * FROM table")
column_names = list(map((lambda(x) : x[0]), cur.description))
print column_names 

table  = list()
row = cur.fetchone()

while row is not None:
  table.append(row)
  row = cur.fetchone()

print table

The table structure will be a list of tuples, where each list element is a row and each part of the tuple is a column element.

If you want to read data into pandas, I suggest searching the internet for that.
I found this: SQL to Pandas DataFrame on the first try...

Hans Henrik Eriksen
  • 2,690
  • 5
  • 12
0

Are you trying to replace part of SQL, if so we can use replace() function or if you have any template then you can use jinja template to format your SQL.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Sriga
  • 1,165
  • 4
  • 11