def setUp(self):
pyodbc.enable_mem_guards(True)
pyodbc.enable_tracing(_enable_logging)
self.assertIsNotNone(_conn_string, msg=_error_string)
try:
self.conn = pyodbc.connect(_conn_string, unicode_results=_unicode_results, timeout=3)
except Exception as e:
raise Exception('It seems that your {0} is not setup correctly. Attempting to connect resulted in:\n{1}'.format(_conn_string_key, e.args[1]))
def _cancel_all(connstring, label):
"""cancel_all sessions where client_label matches label.
to use this, you must set LABEL to some unique value
inside your connection string
"""
q = """select session_id, statement_id from v_monitor.sessions
where client_label='%s'""" % label
conn = pyodbc.connect(connstring, ansi=True)
data = conn.cursor().execute(q).fetchall()
_interrupt_statements(conn, data)
def main(conn_str):
print('Connecting to data source...')
conn = odbc.connect(conn_str)
print('Building the table...')
ensure_table(conn)
print('Trying queries...')
t1 = time()
query_with_time_out(conn, 5.0)
t2 = time()
query(conn)
t3 = time()
print('query ellapsed %d s, query_with_timeout ellapsed %d s' %
(t3 - t2, t2 - t1))
def _cancel(connstring, timeout, queryid):
"""after some timeout, close the statement associated with
queryid. queryid should be some uuid you add via sql comments
"""
time.sleep(timeout)
conn = pyodbc.connect(connstring, ansi=True)
q = """
select session_id, statement_id from v_monitor.sessions where
current_statement like '%%%s%%'
and current_statement not like '%%v_monitor.sessions%%';
"""
q = q % queryid
data = conn.cursor().execute(q).fetchall()
if len(data) == 1:
_interrupt_statements(conn, data)
def db_update_weights(productID, weights_dict):
"""
Loads a set of weights to a timeseries weights table in the DB.
Could benefit from some connection pooling all around.
** NOTE: Needs to actually DROP all these weights first, which isn't written yet...
"""
db_connection = iopro.connect(**db_config)
db_cursor = db_connection.cursor()
for k, v in weights_dict.items():
db_cursor.execute("insert into dbo.TimeSeriesWeights_TMP values (?,?,?)", productID, k, v)
db_connection.commit()
db_connection.close()
print(productID, "-- Loading Weights...")
def db_get_productlist():
"""
Connects to an existing database view containing the distinct ProductIDs for a given client, and returns those IDs as a list.
This is highly suboptimal but works as a proof of concept.
"""
db_connection = iopro.connect(**db_config)
db_cursor = db_connection.cursor()
productIDs = []
db_cursor.execute(
"exec TimeSeriesQueueGet"
) # Expects a table or view containing distinct ProductIDs in a 'ProductID' int field
for row in db_cursor.fetchall():
productIDs.append(row[0])
db_connection.commit()
db_connection.close()
return productIDs # Return result as a list of integers
def db_get_ts_config():
"""
This is an initialization routine for the app.
App needs to know the range of purchase dates in order to distinguish between pre-history and
days with zero sales. Sets a time-series config dict (ts_config) to contain these min and max dates.
"""
db_connection = iopro.connect(**db_config)
db_cursor = db_connection.cursor()
db_cursor.execute(
"select * from dbo.vTransactionStats"
) # Application needs to know, minimally, first and last overall transaction dates
result = db_cursor.fetchone()
ts_config["minPurchaseDate"] = result.minPurchaseDate
ts_config[
"maxPurchaseDate"
] = (
result.maxPurchaseDate
) # Assumes the most recent PurchaseDate applies to all products, so zeros can be filled in appropriately for trending
db_connection.close()
del (db_cursor)
del (db_connection)
def generate_tables(count):
import os, binascii
import random
import datetime
print("Generating tables for tests (%s elements)" % repr(count))
conn = pyodbc.connect(_connect_string)
cur = conn.cursor()
for tab in tables:
print("Table %s: %s" % (tab["name"], tab["descr"]))
verbose_exec(cur, "drop table if exists %s" % (tab["name"]))
verbose_exec(cur,
"create table %s (val %s)" % (tab["name"], tab["type"]))
values = tab["generator"](count / 2)
values.extend([(None, )] * (count - len(values))) # add nulls
random.shuffle(values) #and shuffle
cur.executemany("insert into %s values(?)" % (tab["name"], ), values)
cur.commit()
conn.close()
def db_get_trx_series(productID):
"""
Accepts a single ProductID. Queries the profile database to get the DAILY sales counts for that single ProductID.
This is then converted into a clean time series, bounded by the min and max sales dates, with all missing dates
filled in with zero sales.
Returns a Pandas time-series object for further processing.
"""
db_connection = iopro.connect(**db_config)
db_cursor = db_connection.cursor()
db_cursor.execute("select * from dbo.fxTransactionSeries(?)", productID)
result = db_cursor.fetchsarray()
db_connection.close()
ts_idx = pd.date_range(ts_config["minPurchaseDate"], ts_config["maxPurchaseDate"])
df = pd.DataFrame(result)
df.set_index(
"PurchaseDate", drop=True, append=False, inplace=True, verify_integrity=False
) # Set Pandas index to the date column
ts = pd.Series(df["Purchases"])
ts.index = pd.DatetimeIndex(ts.index)
ts = ts.reindex(ts_idx, fill_value=0)
return ts # Returns a Series indexed by Date, no missing dates and all zeros filled
def connect(conn_str):
print('Connecting to data source...')
return odbc.connect(conn_str)
dictarray = cursor.fetchdictarray(N // steps)
t1 = time() - t0
if profile: show_stats("After reading...", tref)
print "[fetchdictarray twice] Retrieved %d rows in %.3fs" % (len(
dictarray['volume_']), t1)
print "Last row:", [(name, arr[-1]) for name, arr in dictarray.iteritems()]
del dictarray
# del cursor
time4 = t1
return (time1, time2, time3, time4)
if __name__ == "__main__":
# set up a connection
connection = pyodbc.connect(_default_connect_string)
cursor = connection.cursor()
import sys
if len(sys.argv) > 1 and sys.argv[1] == "write":
write(cursor)
if len(sys.argv) > 1 and sys.argv[1] == "profile":
if sys.platform.startswith("linux"):
profile = True
else:
print "Memory profiling only support on Linux. Exiting..."
sys.exit(1)
results = []
for i in range(5):
print "\n\nrun %d\n" % i
un = "unanglicized_name"
ord = "ordinal"
pmid = "pmid"
ti = "title"
ab = "abstract"
y = "year"
aff = "affiliation"
affn = "aff_nullable"
numa = "number_of_authors"
coa = "coauthors"
cit = "cit_count"
s = json.load(open(config_file))
conn_str = ('driver=%s; server=%s; uid=%s; pwd=%s; db=%s' %
(s['driver'], s['server'], s['uid'], s['pwd'], s['db']))
conn = pyodbc.connect(conn_str)
"""
make the pairs
"""
df = pd.io.sql.read_sql(select1, conn)
df[ab] = df[ab].map(lambda s : " ".join([utils.preprocess(x) for x in json.loads(s).itervalues()]))
df[ti] = df[ti].map(lambda x: utils.preprocess(x))
# see
# http://stackoverflow.com/questions/13446480/python-pandas-remove-entries-based-on-the-number-of-occurrences#comment18556837_13447176
# for a better way?
counts = df.groupby(un).size()
counts = counts[counts != 1]
df = df[df[un].isin(counts.index.values)]
cursor = conn.cursor()
df[coa] = df.apply(lambda x: utils.query_coauths(cursor, int(x[pmid]), int(x[id])), axis=1)['pmid']
cursor.close()
un = "unanglicized_name"
ord = "ordinal"
pmid = "pmid"
ti = "title"
ab = "abstract"
y = "year"
aff = "affiliation"
affn = "aff_nullable"
numa = "number_of_authors"
coa = "coauthors"
cit = "cit_count"
s = json.load(open(config_file))
conn_str = ('driver=%s; server=%s; uid=%s; pwd=%s; db=%s' %
(s['driver'], s['server'], s['uid'], s['pwd'], s['db']))
conn = pyodbc.connect(conn_str)
"""
make the pairs
"""
df = pd.io.sql.read_sql(select1, conn)
df[ab] = df[ab].map(lambda s: " ".join(
[utils.preprocess(x) for x in json.loads(s).itervalues()]))
df[ti] = df[ti].map(lambda x: utils.preprocess(x))
# see
# http://stackoverflow.com/questions/13446480/python-pandas-remove-entries-based-on-the-number-of-occurrences#comment18556837_13447176
# for a better way?
counts = df.groupby(un).size()
counts = counts[counts != 1]
df = df[df[un].isin(counts.index.values)]
cursor = conn.cursor()
df[coa] = df.apply(
def clean(conn_str):
print('Connecting to data source...')
conn = odbc.connect(conn_str)
print('Dropping the table')
drop_table(conn)
def read_only_connect():
return pyodbc.connect(_connect_string,
ansi=True,
unicode_results=False,
readonly=True)
