sqlalchemy_play.py

import pandas as pd

import sqlalchemy
import clarity_to_csv as ctc

sqlalchemy.__version__

#%%
sqlite_db_filepath = "C:\\Users\\LynchSe\\Documents\\Data\\database.db"
e = sqlalchemy.create_engine('sqlite:///{}'.format(sqlite_db_filepath))
conn = e.connect()
#%%
eng_clarity = ctc.get_clarity_engine(host='claritydev.uphs.upenn.edu', database='clarity')
conn = eng_clarity.connect()

#%%  Try out bound parameters
#https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#tutorial-working-with-transactions
from sqlalchemy import text

sql = text('''SELECT * FROM PAT_ENC  
WHERE :begin_date <= CONTACT_DATE AND CONTACT_DATE < :end_date
''')

params = {}
params['begin_date'] = pd.to_datetime('2021-10-06')   #I think contact date does not have time precision
params['end_date'] = pd.to_datetime('2021-10-07')

df = pd.read_sql(sql, con=conn, params=params)

#%%  Try out bound parameters with a list
sql = text('''SELECT * FROM PAT_ENC  
WHERE PAT_ID IN :pat_ids
''')

params={}
params['pat_ids'] = ['Z3153680','042511758']

df2 = pd.read_sql(sql, con=conn, params=params)

#%%
sqlwp = sql.bindparams(**params)
df3 = pd.read_sql(sql, con=conn, params=params)

#%%  Reflect tables from database into metadata object that 
#apparently is necessary for SQLAlchemy expression language
#https://docs.sqlalchemy.org/en/14/tutorial/metadata.html#tutorial-working-with-metadata
from sqlalchemy import MetaData
metadata_obj = MetaData()

pat_enc = sqlalchemy.Table("pat_enc", metadata_obj, autoload_with=eng_clarity)
list(pat_enc.columns)

patient = sqlalchemy.Table("patient", metadata_obj, autoload_with=eng_clarity)
list(patient.columns)

###quote from docs
#To start using the SQLAlchemy Expression Language, we will want to have Table objects constructed that represent all of the database tables we are interested in working with.
#Each Table may be declared, meaning we explicitly spell out in source code what the table looks like, 
#or may be reflected, which means we generate the object based on what’s already present in a particular database.

#%%  Make some select statements using the metadata objects

sqlasql = sqlalchemy.select(patient.c.PAT_ID, patient.c.ZIP).where(patient.c.PAT_ID.in_(['Z3153680','042511758']))

df4 = pd.read_sql(sqlasql, con=conn)



#%%  Can I made select or a table out of a text construct?
# Doesn't seem like it??

sql_text = sqlalchemy.text("SELECT * FROM PAT_ENC ORDER BY PAT_ID")

mytab = sqlalchemy.Table(sql_text, metadata_obj, autoload_with=eng_clarity)

stmt = sqlalchemy.select(mytab).where(mytab.c.PAT_ID == 5)

conn.execute(stmt)


mytab = sqlalchemy.Table(sql_text, metadata_obj, autoload_with=eng_clarity)


mytab2 = sqlalchemy.table(sql_text)
list(mytab2.columns)
#%%  GAHHH I THINK IM CLOSE
sql_text = sqlalchemy.text("SELECT * FROM PAT_ENC ORDER BY PAT_ID")

stmt = sql_text.columns(sqlalchemy.sql.column('PAT_ID')).subquery()

sqlalchemy.select(stmt).where(stmt.PAT_ID == 'Z3153680')

#sqlalchemy.sql.expression.TextualSelect(sql_text)


#%%
stmt2 = stmt.where(pat_enc.c.PAT_ID == 'Z3153680')

#does this work?!?!?!

conn.execute(stmt2)  #NO it doesn't work... booo


#%%   #can I add a where clause that WOULD have required me inserting in middle of SQL?
# The dream is that have my labs, or medications query ready... 
# then I add whatever additional filters I want
# but it acts as fast as it would have been if I had the filter in the first place







#%%
#Can I take a SQL statement
#add a where clause
#BEFORE executing?

#begin with this statment (that would be super fucking slow)
"SELECT * FROM PAT_ENC ORDER BY PAT_ID"

#insert this where filter without having to fuck around with text
"WHERE '2021-10-05'< CONTACT_DATE AND CONTACT_DATE < '2021-10-05'"

#eventually substitute date parameters for those dates "bound parameters"

#eventually eventually add a filter for a list of patients

#maybe I can do this in the ORM??  OR MAYBE I MUST STICK WITH CORE??





#%%
sqlite_db_filepath = "C:\\Users\\LynchSe\\Documents\\Data\\database.db"
e = sqlalchemy.create_engine('sqlite:///{}'.format(sqlite_db_filepath))
conn = e.connect()
#%%
eng_clarity = ctc.get_clarity_engine()
conn_clarity = eng_clarity.connect()

#%%

metadata_obj = sqlalchemy.MetaData()

#%%
mytable = sqlalchemy.schema.Table("mytable", metadata_obj,
                sqlalchemy.Column('mytable_id', sqlalchemy.Integer, primary_key=True),
                sqlalchemy.Column('value', sqlalchemy.String(50))
           )
mytable.name
#%%  HOLY SHIT, I CAN READ INFO ABOUT A TABLE FROM CLARITY!!!

pat_enc = sqlalchemy.Table('PAT_ENC', metadata_obj, autoload_with=eng_clarity)
list(pat_enc.columns)

#%%  Can I easily import a cohort into clarity?

cohort = sqlalchemy.schema.Table("##cohort", metadata_obj,
                sqlalchemy.Column('PAT_ID', sqlalchemy.String(18), primary_key=True))
#%%
cohort.create(eng_clarity)
#%%
stmt = sqlalchemy.insert(cohort).values(PAT_ID='5931')
eng_clarity.execute(stmt)
#%%
rows = [{'PAT_ID':'1132'}, {'PAT_ID':'1133'}]
stmt = sqlalchemy.insert(cohort).values(rows)
eng_clarity.execute(stmt)

#TODO - test if this can handle a gazillion rows without being super slow
#%%
stmt = sqlalchemy.select(cohort)
res = eng_clarity.execute(stmt)
#%%
import pandas as pd
df = pd.read_sql(stmt, eng_clarity)

df2 = pd.read_sql(stmt.where(cohort.c.PAT_ID < 2000), eng_clarity)

df3 = pd.read_sql(stmt.where(cohort.c.PAT_ID.in_([5931, 1132])), eng_clarity)

#%% now how do I select from PAT_ENC based on a list of ids
#TODO - import into temp table and use join
#TODO - compare with IN clause?