In [ ]:
import pandas as pd
import numpy as np
import sys
from sqlalchemy import *
#from sqlalchemy import desc, nullsfirst
import sqlalchemy
In [ ]:
from sqlalchemy.sql import table, column, select
def createLags(table , H , col, index_col):
# TS0 = table;
TS1 = None;
TS1 = alias(select([table.c[index_col] , table.c[col]]), "KKKKKK");
col_expr_1 = TS1.c[col];
index_expr = table.c[index_col];
index_expr_1 = TS1.c[index_col];
exprs = [];
for h1 in range(1 , H+1):
case1 = case([(index_expr == (index_expr_1 + h1) , col_expr_1)] , else_ = null());
expr = select([func.sum(case1)]).select_from(table);
expr = expr.label(col + "_Lag" + str(h1));
exprs = exprs + [expr];
return exprs;
t = table('t', column('x') , column('rn'))
lags = createLags(t , 7 , 'x' , 'rn')
s = select(lags)
print(s.compile(compile_kwargs={"literal_binds": True}))
In [ ]:
def createLags2(table , H , col, index_col):
# TS0 = table;
TS1 = None;
TS1 = alias(select([table.c[index_col] , table.c[col]]), "KKKKKK");
col_expr_1 = TS1.c[col];
index_expr = table.c[index_col];
index_expr_1 = TS1.c[index_col];
exprs = [];
for h1 in range(1 , H+1):
case1 = case([(index_expr == (index_expr_1 + h1) , col_expr_1)] , else_ = null());
expr = select([func.sum(case1)]).select_from(table);
expr = expr.label(col + "_Lag" + str(h1));
exprs = exprs + [expr];
return exprs;
t = table('t', column('x') , column('rn'))
u =select([t])
lags = createLags2(u , 7 , 'x' , 'rn')
s = select(lags)
print(s.compile(compile_kwargs={"literal_binds": True}))
In [ ]:
def createLagsAsJoins(table , H, col , idx_col):
TS = select([table.c[idx_col], table.c[col]])
join_H = TS;
for h in range(0 , H):
cte_h = alias(TS , "t" + str(h+1))
join_H = join_H.join(TS, join_H.c[idx_col] == (TS.c[idx_col] + (h + 1)), isouter=True)
statement = select(join_H.columns)
print(join_H.c.keys())
return (statement , join_H)
t = table('t', column('x') , column('rn'))
u =select([t])
lags = createLagsAsJoins(t , 2 , 'x' , 'rn')
s = select(lags)
print(s.compile(compile_kwargs={"literal_binds": True}))
In [ ]:
sqlalchemy.__version__
sys.setrecursionlimit(200000);
In [ ]:
# in-memory database
#lDSN = 'sqlite://'
#lDSN = 'mysql://user:pass@localhost/GitHubtest'
lDSN = 'postgresql:///GitHubtest'
engine = create_engine(lDSN , echo=True)
#create_engine( , echo=True)
conn = engine.connect()
In [ ]:
sqlalchemy.dialects.postgresql.base._FLOAT_TYPES
In [ ]:
#conn.close?
In [ ]:
#a= conn.connection.connection.
#a
In [ ]:
type(MetaData.tables)
In [ ]:
df = pd.DataFrame()
df['Time'] = range(0, 10)
df['Signal'] = df['Time'].apply(np.sin);
In [ ]:
df.head(12)
In [ ]:
conn.connection.connection.dsn
In [ ]:
conn.engine.has_table('ds1')
In [ ]:
conn.engine.dialect
In [ ]:
In [ ]:
df.to_sql("ds1" , conn, if_exists='replace')
In [ ]:
meta = MetaData()
table2 = Table('ds1', meta, autoload=True, autoload_with=engine)
In [ ]:
table2.primary_key
In [ ]:
index_col = table2.c['index']
expr = index_col - 1
a = expr.label("AAA")
expr.__dict__
a.element
In [ ]:
a = index_col.label("ZVFD")
a.key , a.name, a.label
In [ ]:
a = expr.params()
a.params().__dict__
In [ ]:
index_col.primary_key = True
table2.primary_key.__dict__
In [ ]:
cte1 = select([table2.c['index'] , table2.c['Time'] , table2.c['Signal']]).cte("My_CTE_Alias")
In [ ]:
cte1.columns.keys()
In [ ]:
cte1
In [ ]:
expr_sig = table2.c['Signal']
#Sig_lag1 = .where(table2.c['Time'] == 0)
In [ ]:
kk = expr_sig + 1
In [ ]:
import pickle
from io import StringIO
In [ ]:
src = StringIO()
p = pickle.Pickler(src)
In [ ]:
import pickle
favorite_color = { "lion": "yellow", "kitty": "red" }
pickle.dumps( favorite_color)
pickle.dumps(kk)
In [ ]:
expr_sig
In [ ]:
expr_columns_cte = [ cte1.c.index, cte1.c.Time, cte1.c.Signal];
expr_columns_table2 = [ table2.c.index, table2.c.Time, table2.c.Signal];
expr_columns_cte = [ cte1 ];
expr_columns_table2 = [ table2 ];
def createLagsAsJoins(table , H):
TS = table
join_H = TS;
for h in range(0 , H):
cte_h = select([TS]).cte("LAG_" + str(h + 1))
join_H = join_H.join(cte_h, TS.c.index == (cte_h.c.index + h + 1), isouter=True)
statement = select(join_H.columns).select_from(join_H)
print(join_H.c.keys())
return (statement , join_H)
# (select t."Signal" from ds1 t where ((t."index" + 1) = ds1."index")) as Signal_LAG_1,
def createLagsAsCTE(table , H , col, index_col):
TS = table
TS1 = table.alias("t");
col_expr_1 = TS1.c[col];
index_expr = TS.c[index_col]
index_expr_1 = TS1.c[index_col]
exprs = [table];
for h in range(1 , H+1):
expr1 = select([col_expr_1]).where(index_expr == (index_expr_1 + h));
expr = expr1;
expr = expr.label(col + "_LAG_" + str(h));
exprs = exprs + [expr];
cte_H = select(exprs).cte("LAGS")
statement = select([cte_H])
print(cte_H.c.keys())
return (statement , cte_H)
def addRowNumber(table , time_col):
exprs = [table]
row_number_column = func.row_number().over(order_by=asc(table.c[time_col])).label('row_number')
exprs = exprs + [row_number_column]
statement = select(exprs)
return statement
In [ ]:
stmt = addRowNumber(table2 , 'Time');
result = conn.execute(stmt).fetchmany(5)
result
In [ ]:
stmt.columns.keys()
In [ ]:
result.
In [ ]:
(stmt , j) = createLagsAsCTE(table2 , 4 , 'Signal' , 'index')
result = conn.execute(stmt).fetchmany(5)
result
stmt.label
In [ ]:
expr_columns_cte = [ cte1.c.index, cte1.c.Time, cte1.c.Signal];
expr_columns_table2 = [ table2.c.index, table2.c.Time, table2.c.Signal];
expr_columns_cte = [ cte1 ];
expr_columns_table2 = [ table2 ];
cte2 = select([table2]).cte("CTE2")
cte3 = select([table2]).cte("CTE3")
cte4 = select([table2]).cte("CTE4")
join1 = table2.join(cte1, table2.c.index == (cte1.c.index + 1), isouter=True)
join2 = table2.join(cte2, table2.c.index == (cte2.c.index + 2), isouter=True)
join3 = table2.join(cte3, table2.c.index == (cte3.c.index + 3), isouter=True)
join4 = table2.join(cte4, table2.c.index == (cte4.c.index + 4), isouter=True)
join_1234 = table2.join(cte1, table2.c.index == (cte1.c.index + 1), isouter=True).join(cte2, table2.c.index == (cte2.c.index + 2), isouter=True).join(cte3, table2.c.index == (cte3.c.index + 3), isouter=True).join(cte4, table2.c.index == (cte4.c.index + 4), isouter=True)
statement = select(expr_columns_cte + expr_columns_table2).where(table2.c.index == (cte1.c.index - 1))
statement = statement.order_by(table2.c.index)
statement1 = select([table2, cte1]).select_from(join1)
statement2 = select([table2, cte1]).select_from(join2)
statement3 = select([table2, cte1]).select_from(join3)
statement4 = select([table2, cte1]).select_from(join4)
statement1234 = select([table2, cte1, cte2, cte3, cte4]).select_from(join_1234)
In [ ]:
join_1234.c.keys()
In [ ]:
In [ ]:
(stmt , j) = createLagsAsCTE(table2 , 1 , 'Signal' , 'index')
result = conn.execute(stmt).fetchmany(5)
result
In [ ]:
In [ ]:
stmt.columns.keys()
In [ ]:
In [ ]:
func.row_number33
In [ ]:
s = select([table2])
In [ ]:
expr = over(func.row_number(), order_by=table2.c.Time)
expr
In [ ]:
def buildSQLForLAgs(H , table):
lSQL = '\nWITH "LAGS_CTE" AS \n(SELECT \nds1."index" AS "index",\n ds1."Time" AS "Time",\n ds1."Signal" AS "Signal",\n';
for h in range(0 , H):
lSQL = lSQL + '(select t."Signal" from ds1 t where ((t."index" + ' + str(h + 1) + ') = ds1."index")) as Signal_LAG_' + str(h+1)
if((h+1) < H):
lSQL = lSQL + ', \n'
lSQL = lSQL + ' \nFROM ds1) \nSELECT "LAGS_CTE".* \nFROM "LAGS_CTE"'; # + table;
return lSQL;
#stmt = select([expr])
lSQL1 = "SELECT row_number() OVER (ORDER BY ds1.Time) AS anon_1FROM ds1"
#result1 = conn.execute(lSQL)
lSQL2 = "SELECT ROWID AS anon_1FROM ds1"
lSQL3 = buildSQLForLAgs(7 , 'ds1');
result2 = conn.execute(lSQL3).fetchall()
#result = conn.execute(stmt).fetchall()
result2
stmt3 = text(lSQL3);
In [ ]:
a= stmt3.columns()
In [ ]:
a = stmt3.compile()
In [ ]:
a.ctes
In [ ]:
stmt = select([table2]).\
order_by(desc(table2.c.A))
In [ ]:
from sqlalchemy.sql import column
c_A = table2.c.A
c_B = table2.c.B
In [ ]:
c_A
In [ ]:
print( c_A + c_B)
In [ ]:
c_Sum = c_A + c_B
c_Sum2 = c_A + 2 * c_B
c_Sum3 = (c_A - 55) / 67
c_prev_A =
stmt = select([c_A, c_B, c_Sum, c_Sum2, c_Sum3])
In [ ]:
result = conn.execute(stmt)
In [ ]:
result.fetchmany(5)
In [ ]:
def generateLinearCombination(iTable, iDict , iIntercept):
stmt_arg = []
sum_1 = None
for k,v in iDict.items():
print(k)
name = iTable.c[k]
stmt_arg = stmt_arg + [name]
if(sum_1 is None):
sum_1 = v * name
else:
sum_1 = sum_1 + v * name
sum_1 = sum_1 + iIntercept
sum_1.label("MyScore")
print(sum_1)
return sum_1
def debrief_statement(stmt):
print(stmt)
result = conn.execute(stmt)
print(result.fetchmany(6))
return result
def debrief_expression(expr):
print("debrief_start")
# stmt_arg = stmt_arg + [expr]
stmt = select([expr] , use_labels=True)
result = debrief_statement(stmt)
print("debrief_end")
return result
In [ ]:
coeffs = {}
coeffs['A'] = 3
coeffs['B'] = 0.3
coeffs['C'] = -3
In [ ]:
expr = generateLinearCombination(table2, coeffs, iIntercept=5)
res = debrief_expression(expr)
In [ ]:
aaa = expr.compile()
In [ ]:
aaa.__dict__
In [ ]:
expr.expression
In [ ]:
(aa, bb) = expr._orig
In [ ]:
expr.compile?
In [ ]:
def generateCaseWhenDiscrete(iTable, iColumn, iDict , iElseValue):
stmt_arg = []
name = iTable.c[iColumn]
case_1 = None
for k,v in iDict.items():
print(k)
if(case_1 is None):
case_1 = [(name == k , v)]
else:
case_1 = case_1 + [(name == k , v)]
print(case_1)
case_2 = case(case_1 , else_ = iElseValue);
return case_2
In [ ]:
mapping = {}
mapping["S"] = -1
mapping["W"] = 1
mapping["Z"] = 0
expr = generateCaseWhenDiscrete(table2, "A", mapping, iElseValue=5)
debrief_expression(expr)
In [ ]:
In [ ]:
def generateCaseWhenWithSegments(iTable, iColumn, iDict , iElseValue):
stmt_arg = []
name = iTable.c[iColumn]
case_1 = None
for k,v in iDict.items():
print(k)
(a , b) = v
if(b):
expr = (name <= k)
else:
expr = (name < k)
if(case_1 is None):
case_1 = [(expr , a)]
else:
case_1 = case_1 + [(expr , a)]
print(case_1)
case_2 = case(case_1 , else_ = iElseValue);
return case_2
In [ ]:
mapping = {}
#mapping[None] = (-5 , False)
mapping[0.5] = (-1 , False)
mapping[1.0] = (1 , False)
mapping[2] = (0 , True)
expr = generateCaseWhenWithSegments(table2, "B", mapping, iElseValue=5)
debrief_expression(expr)
In [ ]:
a = expr.value
a
In [ ]:
for i in a:
print(i)
In [ ]:
import numpy as np
X = np.random.randint(5, size=(6, 100))
y = np.array([1, 2, 3, 4, 5, 6])
from sklearn.naive_bayes import MultinomialNB
clf = MultinomialNB()
clf.fit(X, y)
print(clf.predict(X[2:3]))
print(clf.predict_proba(X[2:10]))
In [ ]: