logo
down
shadow

How can I populate a pandas DataFrame with the result of a Snowflake sql query?


How can I populate a pandas DataFrame with the result of a Snowflake sql query?

By : Benjamin Hirsch
Date : November 24 2020, 05:47 AM
it fixes the issue You can use DataFrame.from_records() or pandas.read_sql() with snowflake-sqlalchemy. The snowflake-alchemy option has a simpler API
code :
pd.DataFrame.from_records(iter(cur), columns=[x[0] for x in cur.description])
import snowflake.connector
import pandas as pd

# Gets the version
ctx = snowflake.connector.connect(
    user=USER,
    password=PASSWORD,
    account=ACCOUNT,
    authenticator='https://XXXX.okta.com',
    )
ctx.cursor().execute('USE warehouse MY_WH')
ctx.cursor().execute('USE MYDB.MYSCHEMA')


query = '''
select * from MYDB.MYSCHEMA.MYTABLE
LIMIT 10;
'''

cur = ctx.cursor().execute(query)
df = pd.DataFrame.from_records(iter(cur), columns=[x[0] for x in cur.description])
import pandas as pd

from sqlalchemy import create_engine
from snowflake.sqlalchemy import URL


url = URL(
    account = 'xxxx',
    user = 'xxxx',
    password = 'xxxx',
    database = 'xxx',
    schema = 'xxxx',
    warehouse = 'xxx',
    role='xxxxx',
    authenticator='https://xxxxx.okta.com',
)
engine = create_engine(url)


connection = engine.connect()

query = '''
select * from MYDB.MYSCHEMA.MYTABLE
LIMIT 10;
'''

df = pd.read_sql(query, connection)


Share : facebook icon twitter icon
Transform a 3 columns (x, y, result) Python Pandas DataFrame to a DataFrame of result values with x (unique) as row and

Transform a 3 columns (x, y, result) Python Pandas DataFrame to a DataFrame of result values with x (unique) as row and


By : user2163320
Date : March 29 2020, 07:55 AM
around this issue That is a pivot operation, you can either use .pivot_table:
code :
>>> df.pivot_table(values='result', index='x', columns='y')
y       -1     0
x               
-0.8  0.64 -0.36
-0.4  0.16 -0.84
 0.0  0.00 -1.00
 0.4  0.16 -0.84
 0.8  0.64 -0.36
>>> df.pivot(index='x', columns='y')['result']
y       -1     0
x               
-0.8  0.64 -0.36
-0.4  0.16 -0.84
 0.0  0.00 -1.00
 0.4  0.16 -0.84
 0.8  0.64 -0.36
>>> df.groupby(['x', 'y'])['result'].aggregate('first').unstack()
y       -1     0
x               
-0.8  0.64 -0.36
-0.4  0.16 -0.84
 0.0  0.00 -1.00
 0.4  0.16 -0.84
 0.8  0.64 -0.36
How to store mySQL query result into pandas DataFrame with pymysql?

How to store mySQL query result into pandas DataFrame with pymysql?


By : Shane R
Date : March 29 2020, 07:55 AM
I hope this helps you . Use Pandas.read_sql() for this:
code :
query = "SELECT * FROM orders WHERE date_time BETWEEN ? AND ?"
df = pd.read_sql(query, connection,  params=(start_date, end_date))
Read pandas dataframe row by row, call API and then store each result in a separate pandas dataframe

Read pandas dataframe row by row, call API and then store each result in a separate pandas dataframe


By : 陳柏憲
Date : March 29 2020, 07:55 AM
To fix the issue you can do If you're calling row by API call, and want to add that row in another data frame, here's code for that.
code :
import pandas as pd
df = pd.DataFrame()
df.append(result)
how to print out and save the result of the query in a pandas dataframe

how to print out and save the result of the query in a pandas dataframe


By : Anh Tuấn
Date : March 29 2020, 07:55 AM
hop of those help? If you mean load the results in a pandas DataFrame then, first save it locally
code :
hive -e "select * from t1" > /home/yourfile.tsv
import pandas as pd
df = pd.read_csv("/home/yourfile.tsv",delimiter = "\t")
how to assure a deterministic result of a query that uses mode() in snowflake

how to assure a deterministic result of a query that uses mode() in snowflake


By : user3581304
Date : March 29 2020, 07:55 AM
will help you I use snowflake and I want to use multiple mode() expressions in one select statement. So it looks like: , so mode seems to prefer the first value it see in a tie breaker.
code :
with data as (
select x, col1, col2, col3 from values (1, 1, 1, 3), (1, 1, 2,3), (1, 2, 2,3)
    ,(4, 1, 20, 30), (4, 1, 2, 3), (4, 2, 2, 30), (4,2,20,3) v(x,col1,col2,col3)
)
select x
    ,mode(col1)
    ,mode(col2)
    ,mode(col3)
from data 
group by 1
order by 1;
with data as (
select x, col1, col2, col3 from values (1, 1, 1, 3), (1, 1, 2,3), (1, 2, 2,3)
    ,(4, 1, 20, 30), (4, 1, 2, 3), (4, 2, 2, 30), (4,2,20,3) v(x,col1,col2,col3)
)
select x
    ,col1
    ,col2
    ,col3
    ,count(col1)over(partition by x,col1) c_col1
    ,count(col2)over(partition by x,col2) c_col2
    ,count(col3)over(partition by x,col3) c_col3
from data ;
with data as (
select x, col1, col2, col3 from values (1, 1, 1, 3), (1, 1, 2,3), (1, 2, 2,3)
    ,(4, 1, 20, 30), (4, 1, 2, 3), (4, 2, 2, 30), (4,2,20,3) v(x,col1,col2,col3)
)
select x
    ,col1
    ,col2
    ,col3 
    ,row_number() over (partition by x order by c_col1 desc, col1) as r1
    ,row_number() over (partition by x order by c_col2 desc, col2) as r2
    ,row_number() over (partition by x order by c_col3 desc, col3) as r3
from (
  select x
      ,col1
      ,col2
      ,col3
      ,count(col1)over(partition by x,col1) c_col1
      ,count(col2)over(partition by x,col2) c_col2
      ,count(col3)over(partition by x,col3) c_col3
  from data 
)
order by 1;
X   COL1    COL2    COL3    R1  R2  R3
1   1   2   3   2   1   1
1   2   2   3   3   2   2
1   1   1   3   1   3   3
4   1   2   3   2   1   1
4   2   20  3   4   4   2
4   2   2   30  3   2   3
4   1   20  30  1   3   4
QUALIFY row_number() over (partition by x order by c_col1 desc, col1) = 1
  AND row_number() over (partition by x order by c_col2 desc, col2) = 1
  AND row_number() over (partition by x order by c_col3 desc, col3 desc) = 1
with data as (
select x, col1, col2, col3 from values (1, 1, 1, 3), (1, 1, 2,3), (1, 2, 2,3)
    ,(4, 1, 20, 30), (4, 1, 2, 3), (4, 2, 2, 30), (4,2,20,3) v(x,col1,col2,col3)
),col1_m as (
    select x, col1, count(*) as c 
    from data 
    group by 1,2
    QUALIFY row_number() over (partition by x order by c desc, col1) = 1
),col2_m as (
    select x, col2, count(*) as c 
    from data 
    group by 1,2
    QUALIFY row_number() over (partition by x order by c desc, col2) = 1
),col3_m as (
    select x, col3, count(*) as c 
    from data 
    group by 1,2
    QUALIFY row_number() over (partition by x order by c desc, col3) = 1
), base as (
select distinct x from data
)
select b.x
    ,c1.col1
    ,c2.col2
    ,c3.col3
from base as b
left join col1_m as c1 on b.x = c1.x
left join col2_m as c2 on b.x = c2.x
left join col3_m as c3 on b.x = c3.x
order by 1;
X   COL1    COL2    COL3
1   1   2   3
4   1   2   3
Related Posts Related Posts :
shadow
Privacy Policy - Terms - Contact Us © ourworld-yourmove.org