def test_async(self):
psycopg2.connect(database='foo', bar='baz', async_=1)
self.assertEqual(self.args[0], 'dbname=foo bar=baz')
self.assertEqual(self.args[1], None)
self.assert_(self.args[2])
psycopg2.connect("dbname=foo bar=baz", async_=True)
self.assertEqual(self.args[0], 'dbname=foo bar=baz')
self.assertEqual(self.args[1], None)
self.assert_(self.args[2])
def test_async(self):
psycopg2.connect(database='foo', bar='baz', async=1)
self.assertEqual(self.args[0], 'dbname=foo bar=baz')
self.assertEqual(self.args[1], None)
self.assert_(self.args[2])
psycopg2.connect("dbname=foo bar=baz", async=True)
self.assertEqual(self.args[0], 'dbname=foo bar=baz')
self.assertEqual(self.args[1], None)
self.assert_(self.args[2])
def test_factory(self):
def f(dsn, async_=False):
pass
psycopg2.connect(database='foo', bar='baz', connection_factory=f)
self.assertEqual(self.args[0], 'dbname=foo bar=baz')
self.assertEqual(self.args[1], f)
self.assertEqual(self.args[2], False)
psycopg2.connect("dbname=foo bar=baz", connection_factory=f)
self.assertEqual(self.args[0], 'dbname=foo bar=baz')
self.assertEqual(self.args[1], f)
self.assertEqual(self.args[2], False)
def test_escape(self):
psycopg2.connect(database='hello world')
self.assertEqual(self.args[0], "dbname='hello world'")
psycopg2.connect(database=r'back\slash')
self.assertEqual(self.args[0], r"dbname=back\\slash")
psycopg2.connect(database="quo'te")
self.assertEqual(self.args[0], r"dbname=quo\'te")
psycopg2.connect(database="with\ttab")
self.assertEqual(self.args[0], "dbname='with\ttab'")
psycopg2.connect(database=r"\every thing'")
self.assertEqual(self.args[0], r"dbname='\\every thing\''")
def test_escape(self):
psycopg2.connect(database='hello world')
self.assertEqual(self.args[0], "dbname='hello world'")
psycopg2.connect(database=r'back\slash')
self.assertEqual(self.args[0], r"dbname=back\\slash")
psycopg2.connect(database="quo'te")
self.assertEqual(self.args[0], r"dbname=quo\'te")
psycopg2.connect(database="with\ttab")
self.assertEqual(self.args[0], "dbname='with\ttab'")
psycopg2.connect(database=r"\every thing'")
self.assertEqual(self.args[0], r"dbname='\\every thing\''")
def test_suite():
# If connection to test db fails, bail out early.
import psycopg2cffi
try:
cnn = psycopg2cffi.connect(dsn)
except Exception as e:
print("Failed connection to test db:", e.__class__.__name__, e)
print("Please set env vars 'PSYCOPG2_TESTDB*' to valid values.")
sys.exit(1)
else:
cnn.close()
suite = unittest.TestSuite()
suite.addTest(test_async.test_suite())
suite.addTest(test_bugX000.test_suite())
suite.addTest(test_bug_gc.test_suite())
suite.addTest(test_cancel.test_suite())
suite.addTest(test_connection.test_suite())
suite.addTest(test_copy.test_suite())
suite.addTest(test_cursor.test_suite())
suite.addTest(test_dates.test_suite())
suite.addTest(test_extras_dictcursor.test_suite())
suite.addTest(test_green.test_suite())
suite.addTest(test_lobject.test_suite())
suite.addTest(test_module.test_suite())
suite.addTest(test_notify.test_suite())
suite.addTest(test_psycopg2_dbapi20.test_suite())
suite.addTest(test_quote.test_suite())
suite.addTest(test_transaction.test_suite())
suite.addTest(test_types_basic.test_suite())
suite.addTest(test_types_extras.test_suite())
if test_with:
suite.addTest(test_with.test_suite())
return suite
def drainer_run_shim(self):
self.local.db_interface = psycopg2.connect(
database=settings.DATABASE_DB_NAME,
user=settings.DATABASE_USER,
password=settings.DATABASE_PASS,
host=settings.DATABASE_IP,
)
try:
self.queue_drainer_proc()
except KeyboardInterrupt:
print("Saw keyboard interrupt. Breaking!")
return
except Exception:
print("Error!")
print("Error!")
print("Error!")
print("Error!")
traceback.print_exc()
with open("error %s - %s.txt" % ("rawjobdispatcher", time.time()),
"w") as fp:
fp.write("Manager crashed?\n")
fp.write(traceback.format_exc())
raise
def psycopg2cffi_uri_no_port_connect():
conn_uri_no_port = psycopg2cffi.connect(
"postgresql://%s:%s@%s/%s" % (PG_CONF['user'], PG_CONF['password'],
PG_CONF['host'], PG_CONF['database']))
sql = common_select_sql()
res = common_select_conn(conn_uri_no_port, sql)
return res
def pg_conn(db_name, db_host, db_user, db_pass):
conn_vars = (db_name, db_host, db_user, db_pass)
conn_str = "dbname={} host={} user={} password={}".format(*conn_vars)
conn = psycopg2cffi.connect(conn_str)
return conn
def get_timeframe():
conn = psycopg2cffi.connect(proj_conf.conn_str)
curs = conn.cursor()
start_date = datetime(9999, 1, 1, 1, 1)
end_date = datetime(1, 1, 1, 1, 1)
for date_col in date_columns:
sqlstr = """
SELECT MIN({}) date_col
FROM {}""".format(date_col, proj_conf.project_name)
curs.execute(sqlstr)
col_start_date = curs.fetchall()[0][0]
if col_start_date < start_date:
start_date = col_start_date
sqlstr = """
SELECT MAX({}) date_col
FROM {}""".format(date_col, proj_conf.project_name)
curs.execute(sqlstr)
col_end_date = curs.fetchall()[0][0]
if col_end_date > end_date:
end_date = col_end_date
return start_date, end_date
def doublecheck_useful_col(col_name):
conn = psycopg2cffi.connect(proj_conf.conn_str)
curs = conn.cursor()
sqlstr = """
SELECT count({}),{}
FROM {}
WHERE {} IS NOT NULL
GROUP BY {}
ORDER BY count DESC
""".format(col_name, col_name, proj_conf.project_name, col_name,
col_name)
curs.execute(sqlstr)
col_counts = curs.fetchall()
##check if only N/E/S/W
dirs = ['N', 'E', 'S', 'W']
no_nesw_len = len([[count, col_val] for count, col_val in col_counts
if col_val not in dirs])
if no_nesw_len <= 1:
return False
return True # default
def get_useful_and_useless_cols(column_names, cutoff_count=100):
"""Gets columns whose distinct count is greater than a cutoff"""
conn = psycopg2cffi.connect(proj_conf.conn_str)
curs = conn.cursor()
useful_cols = []
useless_cols = []
for col_name in column_names:
sqlstr = """
SELECT COUNT(DISTINCT({})) FROM {}
WHERE {} IS NOT NULL
""".format(col_name, proj_conf.project_name, proj_conf.project_name,
col_name)
curs.execute(sqlstr)
col_count = curs.fetchall()[0][0]
if col_count <= cutoff_count and col_count > 1:
if doublecheck_useful_col(col_name):
useful_cols.append(col_name)
else:
useless_cols.append(col_name)
else:
useless_cols.append(col_name)
return useful_cols, useless_cols
def make_json_providers(etl_type='full'):
#start threads
start_consumers(5)
conn3 = pypg.connect("dbname='sandbox_rk' user='******' port='5432' host='localhost' password='******'")
#conn3 = pypg.connect("dbname='sandbox_rk' user='******' port='9000' host='192.168.1.20' password='******'")
cur=conn3.cursor()
#for full
if etl_type == 'full':
sql = " select distinct pin from provsrvloc " # where pin = '0005938467'"
else:
sql = " select distinct pin, change_type from provlddelta"
# cur.execute("Select provdrkey from h_provdr d ")
cur.execute(sql)
while True:
row = cur.fetchone()
if row == None:
break
queue.put(row[0])
#wait for queue to empty
print "waiting..."
#at end of queue put -99 on queue to flush the commit before each thread dies
print "flush trx"
for i in range(0,30):
queue.put(-99)
queue.close()
queue.join_thread()
#below should run at end to populate geo search table
#need to change provider json and replace provdrkey with pin
sql = """
def get_from_id(tid):
"""retrieve the trajectory from database by given trajectory id
INPUT
tid: trajectory id
OUTPUT
a trajectory matching given tid
e.g. [{x:1, y:2, tid: 1, index: 0}, {x:1.5, y:2.3, tid: 1, index: 1}, ...]
"""
conn_string = "host='127.0.0.1' dbname='NAME' user='******' password='******'"
conn = psycopg2cffi.connect(conn_string)
cur = conn.cursor()
query = "select * from trajectory.taxi where tid = " + str(tid) + " ORDER BY index;"
logging.debug('query: '+query)
try:
cur.execute(query)
except psycopg2cffi.Error as e:
conn.rollback()
cur.close()
return logging.error('query: '+query)
trajectory = []
for r in cur:
trajectory.append({'tid': int(r[0]),'index': int(r[1]),'x': r[2],'y': r[3]})
cur.close()
conn.close()
return trajectory
def test_suite():
# If connection to test db fails, bail out early.
import psycopg2cffi
try:
cnn = psycopg2cffi.connect(dsn)
except Exception as e:
print("Failed connection to test db:", e.__class__.__name__, e)
print("Please set env vars 'PSYCOPG2_TESTDB*' to valid values.")
sys.exit(1)
else:
cnn.close()
suite = unittest.TestSuite()
suite.addTest(test_async.test_suite())
suite.addTest(test_async_noniso_env.test_suite())
suite.addTest(test_bugX000.test_suite())
suite.addTest(test_bug_gc.test_suite())
suite.addTest(test_cancel.test_suite())
suite.addTest(test_connection.test_suite())
suite.addTest(test_copy.test_suite())
suite.addTest(test_cursor.test_suite())
suite.addTest(test_dates.test_suite())
suite.addTest(test_extras_dictcursor.test_suite())
suite.addTest(test_green.test_suite())
suite.addTest(test_lobject.test_suite())
suite.addTest(test_module.test_suite())
suite.addTest(test_notify.test_suite())
suite.addTest(test_psycopg2_dbapi20.test_suite())
suite.addTest(test_quote.test_suite())
suite.addTest(test_transaction.test_suite())
suite.addTest(test_types_basic.test_suite())
suite.addTest(test_types_extras.test_suite())
if test_with:
suite.addTest(test_with.test_suite())
return suite
def queue_filler_proc(self, mode):
common.process.name_process("raw job filler worker")
self.open_rpc_interface()
try:
signal.signal(signal.SIGINT, signal.SIG_IGN)
except ValueError:
self.log.warning(
"Cannot configure job fetcher task to ignore SIGINT. May be an issue."
)
self.log.info("Job queue fetcher starting.")
msg_loop = 0
retries = 0
while self.run_flag.value == 1:
try:
self.local.db_interface = psycopg2.connect(
database=settings.DATABASE_DB_NAME,
user=settings.DATABASE_USER,
password=settings.DATABASE_PASS,
host=settings.DATABASE_IP,
)
while self.run_flag.value == 1:
self.fill_jobs(mode)
msg_loop += 1
time.sleep(0.2)
if msg_loop > 25:
self.log.info(
"Job queue filler process (%s). In-Flight: %s, waiting: %s (out: %s, in: %s). Runstate: %s",
mode, self.active_jobs,
self.normal_out_queue.qsize(), self.jobs_out,
self.jobs_in, self.run_flag.value == 1)
retries = 0
msg_loop = 0
except psycopg2.Error:
self.log.error("Exception in psycopg2 in filler process!")
for line in traceback.format_exc().split("\n"):
self.log.error(line)
retries += 1
if retries > 5:
raise
self.log.info("Job queue fetcher saw exit flag. Halting.")
self.local.rpc_interface.close()
# Consume the remaining items in the output queue so it shuts down cleanly.
try:
while 1:
self.normal_out_queue.get_nowait()
except queue.Empty:
pass
self.log.info("Job queue filler process. Current job queue size: %s. ",
self.active_jobs)
self.log.info("Job queue fetcher halted.")
def bundle_search():
conn = psycopg2cffi.connect("dbname='sandbox_rk' user='******' port='9000' host='192.168.1.20' password='******'")
sql = """
select bundleid,bundlename,practice_descr as specialty, procedure_main as dse_term from staging.proceduremapping a,
CBOR b
where a.practice_code = b.practicecode
order by bundleid
"""
sqltaxo = """
select distinct \"QUERY\" from
condition_specialty a,
staging.proceduremapping b
where b.practice_descr = a."DISPLAY"
and b.practice_descr = '%s'
"""
cur = conn.cursor()
curtaxo = conn.cursor()
cur.execute(sql)
rows = cur.fetchall()
prevbundleid = -1
bundle = {}
for row in rows:
if prevbundleid != row[0] or prevbundleid==-1:
#print bundle
#reduce hashes to arrays
dse = []
specl = []
if bundle.has_key('dse_terms'):
for k,v in bundle['dse_terms'].iteritems():
dse.append(v)
bundle['dse_terms'] = dse
for k,v in bundle['specialties'].iteritems():
specl.append(v)
bundle['specialties'] = specl
#zip through specialties and attach taxonmy illness terms to this bundle
terms = []
for sp in dse:
curtaxo.execute(sqltaxo % sp)
termrows = curtaxo.fetchall()
print len(termrows)
for t in termrows:
terms.append(t[0])
bundle['terms'] = terms
headers = {'Content-type': 'application/json'}
# r = requests.post('http://172.22.101.104:8983/solr/provider/update?commit=true', data="[" + json.dumps(py) +"]", headers=headers)
r = requests.post('http://localhost:8984/solr/gettingstarted_shard1_replica2/update?commit=true&overwrite=true', data="[" + json.dumps(bundle) +"]", headers=headers)
print bundle
bundle = {}
prevbundleid = row[0]
bundle['bundleid'] = row[0]
bundle['name'] = row[1]
bundle['dse_terms'] = {}
bundle['specialties'] = {}
bundle['dse_terms'][row[2]] = row[2]
bundle['specialties'][row[3]] = row[3]
def psycopg2cffi_no_support_connect_two():
conn_no_support_two = psycopg2cffi.connect(
"postgresql://%s:%s@[%s]:%s/%s" %
(PG_CONF['user'], PG_CONF['password'], PG_CONF['IPv6_address'],
PG_CONF['port'], PG_CONF['database']))
sql = common_select_sql()
res = common_select(conn_no_support_two, sql)
return res
def psycopg2cffi_no_port_connect():
conn_no_port = psycopg2cffi.connect(host=PG_CONF['host'],
user=PG_CONF['user'],
password=PG_CONF['password'],
database=PG_CONF['database'])
sql = common_select_sql()
res = common_select_conn(conn_no_port, sql)
return res
def psycopg2cffi_no_support_connect():
conn_no_support = psycopg2cffi.connect(
"postgresql:///%s?host=%s&port=%s&user=%s&password=%s" %
(PG_CONF['database'], PG_CONF['host'], PG_CONF['port'],
PG_CONF['user'], PG_CONF['password']))
sql = common_select_sql()
res = common_select(conn_no_support, sql)
return res
def open_database_connection(self):
self.db_interface = psycopg2.connect(
database=settings.DATABASE_DB_NAME,
user=settings.DATABASE_USER,
password=settings.DATABASE_PASS,
host=settings.DATABASE_IP,
)
def psycopg2cffi_connectstring_connect():
conn_connectstring = psycopg2cffi.connect(
"dbname=%s user=%s password=%s host=%s port=%s" %
(PG_CONF['database'], PG_CONF['user'], PG_CONF['password'],
PG_CONF['host'], PG_CONF['port']))
sql = common_select_sql()
res = common_select_conn(conn_connectstring, sql)
return res
def _connect(self, key=None):
"""Create a new connection and assign it to 'key' if not None."""
conn = psycopg2.connect(*self._args, **self._kwargs)
if key is not None:
self._used[key] = conn
self._rused[id(conn)] = key
else:
self._pool.append(conn)
return conn
def _connect(self, key=None):
"""Create a new connection and assign it to 'key' if not None."""
conn = psycopg2.connect(*self._args, **self._kwargs)
if key is not None:
self._used[key] = conn
self._rused[id(conn)] = key
else:
self._pool.append(conn)
return conn
def __init__(self, corpus = "DEFAULT"):
self.corpus = corpus
config = GddConfig.GddConfig()
self.config = config
self.pg_connection = psycopg2.connect(dbname=config.get("psql_dbname", self.corpus), user=config.get("psql_user", self.corpus), password=config.get("psql_password", self.corpus), host=config.get("psql_host", self.corpus), port=config.get("psql_port", self.corpus))
self.pg_connection.autocommit = True
self.pg_cursor = self.pg_connection.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
def test_weakref(self):
from weakref import ref
import gc
conn = psycopg2.connect(dsn)
w = ref(conn)
conn.close()
del conn
gc.collect()
self.assert_(w() is None)
def connect(self):
connection = psycopg2.connect(
host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password,
)
connection.set_client_encoding('utf-8')
return connection
def open_db(host, port):
c = Meanings._conn = psycopg2.connect(
dbname=Meanings._DB_NAME,
user=Meanings._DB_USER,
password=Meanings._DB_PWD,
host=host,
port=port,
client_encoding="utf8",
)
c.autocommit = True
Meanings._cursor = c.cursor()
def test_rollback_on_exception():
try:
with psycopg2cffi.connect(database=DB_SETTINGS['name'],
user=DB_SETTINGS['user'],
password=DB_SETTINGS['password'],
host=DB_SETTINGS['host'],
port=DB_SETTINGS['port']):
raise RuntimeError('error')
except RuntimeError:
pass
def connect(self, **kwargs):
try:
self._conns
except AttributeError as e:
raise AttributeError(
"%s (did you remember calling ConnectingTestCase.setUp()?)"
% e)
import psycopg2cffi as psycopg2
conn = psycopg2.connect(dsn, **kwargs)
self._conns.append(conn)
return conn
def connect(self, **kwargs):
try:
self._conns
except AttributeError as e:
raise AttributeError(
"%s (did you remember calling ConnectingTestCase.setUp()?)" %
e)
import psycopg2cffi as psycopg2
conn = psycopg2.connect(dsn, **kwargs)
self._conns.append(conn)
return conn
def __init__(self, mode, job_queue, run_flag, system_state, state_lock,
test_mode):
# print("Job __init__()")
self.loggerPath = "Main.JobDispatcher(%s)" % mode
self.statsd_prefix = self.statsd_prefix + "." + mode
super().__init__()
self.mode = mode
self.last_rx = datetime.datetime.now()
self.db_interface = psycopg2.connect(
database=settings.DATABASE_DB_NAME,
user=settings.DATABASE_USER,
password=settings.DATABASE_PASS,
host=settings.DATABASE_IP,
)
# We need the job queue because the special case system can skip the rpc stuff entirely.
self.normal_out_queue = job_queue
self.system_state = system_state
self.jq_mode = mode
self.run_flag = run_flag
self.state_lock = state_lock
self.test_mode = test_mode
self.ruleset = WebMirror.rules.load_rules()
self.specialcase = WebMirror.rules.load_special_case_sites()
self.triggerUrls = set(WebMirror.rules.load_triggered_url_list())
self.feed_urls_list = []
for tmp in self.ruleset:
if 'feedurls' in tmp and tmp['feedurls']:
self.feed_urls_list.extend(tmp['feedurls'])
self.feed_urls = set(self.feed_urls_list)
self.rate_limit_skip = {}
for rules in self.ruleset:
for key, regex in rules['skip_filters']:
assert key not in self.rate_limit_skip, "Multiple definition of skip filter for netloc '%s'" % key
self.rate_limit_skip[key] = regex
self.log.info("Have %s RSS feed URLS", len(self.feed_urls))
self.log.info("Have %s netloc-filtered skip-limit regexes.",
len(self.rate_limit_skip))
self.print_mod = 0
with state_lock:
self.system_state['ratelimiters'][
self.mode] = common.NetlocThrottler.NetlockThrottler(
key_prefix='processed', fifo_limit=1000 * 1000)
def get_columns():
conn = psycopg2cffi.connect(proj_conf.conn_str)
curs = conn.cursor()
sqlstr = """
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = %s"""
curs.execute(sqlstr, [proj_conf.project_name])
columns = list(curs.fetchall())
return columns
def remove_product_by_name(name):
query = "delete from public.products where title = '{}';".format(name)
conn = psycopg2cffi.connect(host='pgdb',
database='ninjapixel',
user='******',
password='******')
cur = conn.cursor()
cur.execute(query)
conn.commit()
conn.close()
def test_async_cancel(self):
async_conn = psycopg2.connect(dsn, async_=True)
self.assertRaises(psycopg2.OperationalError, async_conn.cancel)
extras.wait_select(async_conn)
cur = async_conn.cursor()
cur.execute("select pg_sleep(10000)")
self.assertTrue(async_conn.isexecuting())
async_conn.cancel()
self.assertRaises(extensions.QueryCanceledError,
extras.wait_select, async_conn)
cur.execute("select 1")
extras.wait_select(async_conn)
self.assertEqual(cur.fetchall(), [(1, )])
def test_async_cancel(self):
async_conn = psycopg2.connect(dsn, async_=True)
self.assertRaises(psycopg2.OperationalError, async_conn.cancel)
extras.wait_select(async_conn)
cur = async_conn.cursor()
cur.execute("select pg_sleep(10000)")
self.assertTrue(async_conn.isexecuting())
async_conn.cancel()
self.assertRaises(extensions.QueryCanceledError, extras.wait_select,
async_conn)
cur.execute("select 1")
extras.wait_select(async_conn)
self.assertEqual(cur.fetchall(), [(1, )])
def get_high_incidence_items(self):
print("get_high_incidence_items()")
db_interface = psycopg2.connect(
database=settings.DATABASE_DB_NAME,
user=settings.DATABASE_USER,
password=settings.DATABASE_PASS,
host=settings.DATABASE_IP,
)
first_cursor = db_interface.cursor()
first_cursor.execute("""SET statement_timeout = %s;""",
(1000 * 60 * 60 * 8, ))
print("Counting...")
first_cursor.execute("SELECT count(*) FROM web_pages_version;")
item_count = first_cursor.fetchall()[0][0]
print("Table has %s rows!" % (item_count, ))
cursor = db_interface.cursor("high_incidence_items_cursor")
def dump_to_file(data, idx):
with open("chunks/dump%s.pik" % idx, "wb") as fp:
pickle.dump(data, fp)
items = {}
netlocs = {}
cursor.execute("SELECT url FROM web_pages_version;")
loops = 0
dumps = 0
for url, in tqdm.tqdm(cursor, total=item_count):
nl = urllib.parse.urlsplit(url).netloc
items.setdefault(url, 0)
items.setdefault(nl, 0)
netlocs.setdefault(nl, 0)
items[url] += 1
netlocs[nl] += 1
items[nl] += 1
loops += 1
if loops % (1000 * 1000 * 3) == 0:
print("Dumping to pickle file. Unique URLs: ", len(items))
dump_to_file(items, dumps)
dump_to_file(netlocs, "-netlocs")
dumps += 1
items = {}
dump_to_file(items, "-last")
def get_wln_release_urls():
'''
Exec time: ~60-70 seconds
'''
print("loading netlocs from WLN release listings")
import settings
if '__pypy__' in sys.builtin_module_names:
import psycopg2cffi as psycopg2
else:
import psycopg2
conn = psycopg2.connect(
host=settings.WLN_DB_DATABASE_IP,
dbname=settings.WLN_DB_DATABASE_DB_NAME,
user=settings.WLN_DB_DATABASE_USER,
password=settings.WLN_DB_DATABASE_PASS,
)
print("Conn:", conn)
cur = conn.cursor()
print("Fetching rows from changes table")
cur.execute("""
SELECT DISTINCT(srcurl) FROM releaseschanges;
""")
rows_1 = cur.fetchall()
print("Fetching rows from main table")
cur.execute("""
SELECT DISTINCT(srcurl) FROM releases;
""")
rows_2 = cur.fetchall()
print("Received %s, %s distinct URLs" % (len(rows_1), len(rows_2)))
nlfilter = {}
for url, in tqdm.tqdm(rows_1 + rows_2):
if url:
if isinstance(url, bytes):
url = url.decode("utf-8")
itemnl = WebMirror.OutputFilters.util.feedNameLut.patch_blogspot(
urllib.parse.urlsplit(url).netloc)
nlfilter.setdefault(itemnl, set())
nlfilter[itemnl].add(url)
print("WLN Releases distinct netlocs: %s" % len(nlfilter))
return nlfilter
def __init__(self, conf):
# read config file to get connection information
config = ConfigParser.RawConfigParser()
config.read(conf)
host = config.get('database', 'host')
dbname = config.get('database', 'dbname')
user = config.get('database', 'user')
password = config.get('database', 'password')
connection_string = 'host={host} dbname={dbname} user={user} \
password={password}'.format(**locals())
# set up connection and cursor members
self.conn = psycopg2cffi.connect(connection_string)
self.cur = self.conn.cursor(
cursor_factory=psycopg2cffi.extras.DictCursor)
def __init__(self, conf):
# read config file to get connection information
config = ConfigParser.RawConfigParser()
config.read(conf)
host = config.get('database', 'host')
dbname = config.get('database', 'dbname')
user = config.get('database', 'user')
password = config.get('database', 'password')
connection_string = 'host={host} dbname={dbname} user={user} \
password={password}'.format(**locals())
# set up connection and cursor members
self.conn = psycopg2cffi.connect(connection_string)
self.cur = self.conn.cursor(
cursor_factory=psycopg2cffi.extras.DictCursor
)
def _main():
parser = argparse.ArgumentParser()
parser.add_argument('--dbname', required=True)
parser.add_argument('--dbuser', required=True)
parser.add_argument('--source-id', required=True)
parser.add_argument('--rsids', required=True, nargs='+', type=int)
args = parser.parse_args()
print ' CHR SNP A1 A2 MAF NCHROBS'
conn = psycopg2.connect("dbname={} user={}".format(args.dbname, args.dbuser))
cur = conn.cursor()
cur.execute("SELECT * FROM get_tbl_allele_freq_by_rs_history(%s, %s);", (args.source_id, args.rsids,))
rows = cur.fetchall()
records = {}
for row in rows:
snp_id, snp_current, allele, freq = row
if not (allele and freq):
raw_chr = '{0: >4}'.format('.')
raw_snp = '{0: >12}'.format('rs' + str(snp_id))
raw_a1 = '{0: >5}'.format('.')
raw_a2 = '{0: >5}'.format('.')
raw_maf = '{0: >13}'.format('.')
raw_nchrobs = '{0: >9}'.format('.')
else:
allele_freq = dict(zip(allele, freq))
assert len(allele) == 2, row
minor = freq.index(sorted(freq)[0])
major = freq.index(sorted(freq)[-1])
raw_chr = '{0: >4}'.format('.')
raw_snp = '{0: >12}'.format('rs' + str(snp_id))
raw_a1 = '{0: >5}'.format(allele[minor])
raw_a2 = '{0: >5}'.format(allele[major])
raw_maf = '{0: >13.4f}'.format(Decimal(allele_freq[allele[minor]]))
raw_nchrobs = '{0: >9}'.format('.')
record = raw_chr + raw_snp + raw_a1 + raw_a2 + raw_maf + raw_nchrobs
records[snp_id] = record
for rsid in args.rsids:
print records[rsid]
def push_to_solr():
#provdrkey, locations geocodes, hospital names, specialties,provdr is facility,major clasification,name,lastname,firstname
conn = psycopg2cffi.connect("dbname='sandbox_rk' user='******' port='5432' host='localhost' password='******'")
cur=conn.cursor(name='scrollit')
sql = "select provdrjson from provider_json"
cur.execute(sql)
idx = 0
while True:
row = cur.fetchone()
#print row
if row == None:
break
solrhash = {}
solrhash= json.loads(row[0])
idx = 0;
solrhash['geos'] = []
for locn in solrhash['locations']:
geopair = str(locn['provdrlocnlatitude']) + "," + str(locn['provdrlocnlongitude'])
solrhash['geos'].append(geopair)
del(solrhash['locations'])
specialities = []
for locn in solrhash['specialities']:
specialities.append(locn['specialty'])
solrhash['specialities'] = specialities
# print solrhash['specialities']
del solrhash['networks']
del solrhash['programs']
del solrhash['languages']
tosolr = json.dumps(solrhash)
# print tosolr
headers = {'Content-type': 'application/json'}
# r = requests.post('http://172.22.101.104:8983/solr/provider/update?commit=true', data="[" + json.dumps(py) +"]", headers=headers)
r = requests.post('http://localhost:8984/solr/provider/update?commit=true&overwrite=true', data="[" + json.dumps(solrhash) +"]", headers=headers)
#print response.read()
idx += 1
if idx%1000 == 0:
print idx
def make_json_providers():
#start threads
start_consumers(10)
conn3 = psycopg2cffi.connect("dbname='sandbox_rk' user='******' port='5432' host='localhost' password='******'")
cur=conn3.cursor()
cur.execute("Select provdrkey from h_provdr d where provdrkey between 1 and 12 ")
while True:
row = cur.fetchone()
if row == None:
break
queue.put(row[0])
#wait for queue to empty
print "waiting..."
queue.join()
#at end of queue put -99 on queue to flush the commit before each thread dies
print "flush trx"
for i in range(0,30):
queue.put(-99)
queue.join()
def execstep(queue=None,step=None,step_pids=None):
step.active=True
for wstep in step.wait_steps:
for wpid in step_pids[wstep]:
wpid.join()
#read each file
#strip out whitespace and cleanup
#for big ones call java cleaner
try:
conn = psycopg2.connect(step.connectdb)
except:
conn = psycopg2cffi.connect(step.connectdb)
files = [
#"provlang.dat.gz",
#"provsrvlocspec.dat.gz",
#"provsrvloc.dat.gz",
#"hospaff.dat.gz",
#"provlddelta.dat.gz",
#"provsrvlocflg.dat.gz",
"srvgrpprovass.dat.gz",]
for filepattern in files:
if filepattern.find('srvgrpprovass') != -1:
preload_srvgrp_file_filter("/Volumes/My Passport/WM/EPDB-JUL26/srvgrpprovass.dat.gz", "/Volumes/My Passport/WM/EPDB-JUL26/srvgrppabbrev.dat.gz", "/Volumes/My Passport/WM/EPDB-JUL26/provsrvloc.dat.gz")
pg_copy_direct("/Volumes/My Passport/WM/EPDB-JUL26/provntwkloc.dat.gz","OPDB_DEV")
pg_copy_direct("/Volumes/My Passport/WM/EPDB-JUL26/provntwkspec.dat.gz","OPDB_DEV")
pg_copy_direct("/Volumes/My Passport/WM/EPDB-JUL26/srvgrppabbrev.dat.gz","OPDB_DEV")
pg_copy_direct("/Volumes/My Passport/WM/EPDB-JUL26/provsrvloc.dat.gz","OPDB_DEV")
# build_srvgrp_index NOTE THIs is done as part of a SQL step now!
else:
filename = '/Volumes/My Passport/WM/EPDB-JUL26/' + filepattern
load_gz_file(filename)
sq = StepQueueEntry (step,"PASS")
queue.put(sq)
def get_by_number(number=1):
"""retreive a list of trajectories from given number
INPUT
number: the number of retreived trajectories
OUTPUT
a list of trajectories
e.g.
[
[{x:1, y:2, tid: 1, index: 0}, {x:1.5, y:2.3, tid: 1, index: 1}, ...]
[{x:1, y:2, tid: 2, index: 0}, {x:1.5, y:2.3, tid: 2, index: 1}, ...]
...
]
"""
conn_string = "host='127.0.0.1' dbname='NAME' user='******' password='******'"
conn = psycopg2cffi.connect(conn_string)
cur = conn.cursor()
query = "select tid from trajectory.taxi group by tid limit " + str(number) + ";";
logging.debug('query: '+query)
try:
cur.execute(query)
except psycopg2cffi.Error as e:
conn.rollback()
cur.close()
logging.error('query: '+query)
return
tid_list = [int(r[0]) for r in cur]
trajectory_dataset = []
for tid in tid_list:
trajectory_dataset.append(get_from_id(tid))
return trajectory_dataset
def main():
con = psycopg2.connect(database='ubuntu') if USE_DB else None
fnames = os.listdir(DATA_DIR)
nicks = set()
prev_nicks = set()
# fnames = ['2004-09-27-#ubuntu.txt', '2004-09-17-#ubuntu.txt', '2007-10-17-#ubuntu.txt', '2012-01-18-#ubuntu.txt']
for fname in fnames:
fname = "%s/%s" % (DATA_DIR, fname)
print fname
date = get_date(fname)
with open(fname, 'r') as f:
lp = LogParser(f)
lp.prev_nicks = prev_nicks
for time, what, info in lp:
if what == LogParser.COMMENT:
commit(con, date, time, info[0], info[1], info[2])
nicks = nicks.union(lp.nicks)
prev_nicks = lp.nicks
with open('nicks.txt', 'w') as f:
for nick in nicks:
f.write('%s\n' % nick)
if USE_DB:
con.commit()
def service_single_provider_staging(svcque,threadno):
if not sqlQ:
fjson = open(dpath + "/jsonout"+str(threadno)+".dat" , "w")
conn = pypg.connect("dbname='sandbox_rk' user='******' port='5432' host='localhost' password='******'")
#conn = pypg.connect("dbname='sandbox_rk' user='******' port='9000' host='192.168.1.20' password='******'")
cnt = 0
start_trx = True
while True:
try:
provdrkey = svcque.get()
if provdrkey == -99:
try:
#issue commit for last incomplete batch
if sqlQ:
curjson.execute("commit")
else:
fjson.close()
print "commit final ... thread %d at: %d" % (threadno,cnt)
#svcque.task_done()
break
except:
#svcque.task_done()
break
except:
print "end of queue"
#svcque.task_done()
continue
cur5=conn.cursor()
sql = """
SELECT distinct pin as provdrkey,
pin as provdrid,
name as provdrname,
null as provdrtin,
salutation as provdrsalutation,
last_name as provdrlastname,
first_name as provdrfirstname,
middle_name as provdrmiddlename,
gender_description as provdrgender,
birth_date as provdrbirthdate,
primary_degree_desc as provdrprimarydegreedescr,
secondary_degree_desc as provdrsecondarydegreedescr,
provider_type as provdrtype,
case when c."TYPE_CLASS_CD" = 'F' then 'Y'
else 'N'
end as provdrisfacilityflag,
major_classification as provdrmajorclassification,
med_dent_ind as provdrmeddentalind,
designation_code as provdrdesignationcode
from provsrvloc a , provider_type c where trim(provider_type) = trim(c."PROVIDER_TYPE_CD")
and pin = '%s'
"""
#above query produced memory error at some point
#instead of outer join. If we get no rows just fill in 'N' ot facility
sqlalt = """
SELECT distinct pin as provdrkey,
pin as provdrid,
name as provdrname,
null as provdrtin,
salutation as provdrsalutation,
last_name as provdrlastname,
first_name as provdrfirstname,
middle_name as provdrmiddlename,
gender_description as provdrgender,
birth_date as provdrbirthdate,
primary_degree_desc as provdrprimarydegreedescr,
secondary_degree_desc as provdrsecondarydegreedescr,
provider_type as provdrtype,
'N' as provdrisfacilityflag,
major_classification as provdrmajorclassification,
med_dent_ind as provdrmeddentalind,
designation_code as provdrdesignationcode
from provsrvloc a
where pin = '%s'
"""
# for non update run do below
sqlcheck = " select count(*) from provider_loc_search where provdrkey = '%s' "
cur5.execute(sqlcheck % provdrkey)
row = cur5.fetchone()
if row[0] > 0:
continue
try :
cur5.execute(sql % (provdrkey))
except:
print sql
try:
row = cur5.fetchone()
except:
print "none to fetch"
if row == None:
cur5.execute(sqlalt % (provdrkey) )
row = cur5.fetchone()
# print cur5.description
cols = gettabcols(cur5.description,"provdr","provdrlocn")
#go thru qualified cols and grab from row
provider = {}
providerlocns = []
for k,v in cols.items():
try:
provider[v] = row[k].strip()
except:
try:
provider[v] = row[k]
except:
print "skpped error...row was probably null"
#provider languages
sql = """
select distinct language_description as langname, cast (service_location_number as int) from provlang
where pin = '%s'
"""
cur2=conn.cursor()
cur2.execute(sql % (provider['provdrid']))
rowsloc = cur2.fetchall()
provider['languages'] = []
cols = gettabcols(cur2.description,"lang")
provider_langs = {}
for rowloc in rowsloc:
if not provider_langs.has_key(rowloc[1]):
provider_langs[rowloc[1]] = []
provider_langs[rowloc[1]].append(rowloc[0].strip())
providerlangs={}
for k,v in cols.items():
try:
providerlangs[v] = rowloc[k].strip()
except:
providerlangs[v] = rowloc[k]
provider['languages'].append(providerlangs)
provider['specialities'] = []
sql = """
select
'{' || '"name":' || to_json(trim(practice_description) ) ||','
'"specialtyId":' || to_json(trim(practice_code)) || ',"isPrimary":' || to_json(trim(prim_spec_ind)) || ',"typeCode":'
|| to_json(trim(practice_type))
|| ',"serviceLocationNumber":' || to_json(service_location_no) ||
'}'
,
trim(practice_code)
, cast(service_location_no as integer)
from provsrvlocspec
where pin = '%s'
"""
cur3=conn.cursor()
# cur3.execute("Select * from vcpractspecl c,m_vcprovdrlocnpractspecl e where e.provdrlocnkey = %s and e.practspeclkey = c.practspeclkey" % (providerlocn['provdrlocnkey']))
start_time = time.time()
cur3.execute(sql % (provider['provdrid']))
#print("--- %s Specialty seconds ---" % (time.time() - start_time))
rowspract = cur3.fetchall()
provider_specl= {}
for rowpract in rowspract:
if not provider_specl.has_key(rowpract[2]):
provider_specl[rowpract[2]] = []
provider_specl[rowpract[2]].append([]) #the json ret array
provider_specl[rowpract[2]].append([]) #the khash array
provider_specl[rowpract[2]][0].append(json.loads(rowpract[0]))
provider_specl[rowpract[2]][1].append((rowpract[1]))
practspecl = json.loads(rowpract[0])
provider['specialities'].append(practspecl)
cur3.close()
#print("--- %s Specialty seconds ---" % (time.time() - start_time))
#bundle sql
sql = """
select distinct bundleid,cast(service_location_no as integer) from
provsrvlocspec b,
CBOR i
where
b.practice_code = i.practicecode
and b.pin = '%s'
union
select distinct bundleid ,cast(service_location_no as integer) from
provsrvlocspec b,
provsrvloc a,
CBOR i
where
b.practice_code is null
and trim(a.provider_type) = i.phtype
and b.pin = '%s'
and a.pin = b.pin
and a.pin = '%s'
"""
cur3=conn.cursor()
curcbor = conn.cursor()
start_time=time.time()
cur3.execute(sql % (provider['provdrid'],provider['provdrid'], (provider['provdrid'])))
rowspract = cur3.fetchall()
bundles = []
provider_bundle= {}
for rowpract in rowspract:
cols = gettabcols(cur3.description,"bundleid")
if not provider_bundle.has_key(rowpract[1]):
provider_bundle[rowpract[1]] = []
bundlehash = {}
bundlehash[rowpract[0]] = []
#create the items for doing "Procedures" in payload. Takes the bundle Id and gets all CBOR rows
cborsql = "select * from CBOR where bundleid=%s"
curcbor.execute(cborsql % rowpract[0])
colscbor = gettabcols(curcbor.description,"*")
for crow in curcbor.fetchall():
cbormap = {}
for k,v in colscbor.items():
try:
cbormap[v] = crow[k].strip()
except:
cbormap[v] = crow[k]
bundlehash[rowpract[0]].append(cbormap)
provider_bundle[rowpract[1]].append(bundlehash)
for k,v in cols.items():
try :
bundles.append( rowpract[k].strip())
except:
bundles.append( rowpract[k])
provider['bundles'] = bundles
#print("--- %s Bundles seconds ---" % (time.time() - start_time))
#provider locns
cur2=conn.cursor()
start_time = time.time()
sql = """
SELECT 999 as provdrlocnkey,
service_location_number as provdrlocnid,
primary_serv_loc_ind as provdrlocnprimarylocnflag,
addr_print_ind as provdrlocnaddressprintflag,
addr_nap_only_ind as provdrlocnaddressnaponlyflag,
addr_cust_only_ind provdrlocnaddresscustonlyflag,
primary_phone_no as provdrlocnprimaryphone,
secondary_phone_no as provdrlocnsecondaryphone,
fax_no as provdrlocnfaxphone,
npi as provdrlocnnpi,
hearing_product_code as provdrlocnhearingprodcode,
nabp_number as provdrlocnnabpnumber,
svcl_building as provdrlocnbuilding,
svcl_street1 as provdrlocnstreet1,
svcl_street2 as provdrlocnstreet2,
svcl_street3 as provdrlocnstreet3,
svcl_street4 as provdrlocnstreet4,
svcl_street5 as provdrlocnstreet5,
svcl_city as provdrlocncity,
svcl_state as provdrlocnstate,
svcl_zip as provdrlocnzip,
svcl_xzip as provdrlocnzipext,
svcl_county as provdrlocncounty,
svcl_country_cd as provdrlocncountrycode,
svcl_handicap as provdrlocnhandicapflag,
svcl_latitude as provdrlocnlatitude,
svcl_longitude as provdrlocnlongitude,
addr_print_ind as provdrlocndisplayflag,
accept_new_patients_ind as provdrlocnacceptingnewpatients
from provsrvloc
where pin = '%s'
"""
# cur2.execute("Select * from mgeo_vcprovdrlocn b where b.provdrkey = %s " % (provider['provdrkey']))
cur2.execute(sql % (provider['provdrkey']))
rowsloc = cur2.fetchall()
providerlocns = []
for rowloc in rowsloc:
cols = gettabcols(cur2.description,"provdrlocn")
providerlocn = {}
for k,v in cols.items():
try:
providerlocn[v] = rowloc[k].strip()
except:
providerlocn[v] = rowloc[k]
sqlupd = """
update provider_loc_search
set provdrkey = %s ,
provdrlocnlongitude = %s,
provdrlocnlatitude = %s,
geom = ST_GeomFromText('POINT(' || cast(cast('%s' as float) *-1 as varchar) || ' ' || %s || ')',4326),
specialties = '%s',
bundles = '%s'
where pin = '%s'
and service_location_number = '%s'
"""
# curloc=conn.cursor()
#curloc.execute(sql % (provider['provdrkey'], providerlocn['provdrlocnlongitude'], providerlocn['provdrlocnlatitude'],\
# providerlocn['provdrlocnlongitude'], providerlocn['provdrlocnlatitude'], json.dumps(provider['specialities']),\
# json.dumps(provider['bundles']), provider['provdrkey'], providerlocn['provdrlocnid']))
sqlins = """
insert into provider_loc_search (provdrkey,,service_location_number, provdrlocnlongitude,provdrlocnlatitude,
geom, specialties, bundles) values
('%s','%s', %s,%s, ST_GeomFromText('POINT(' || cast(cast('%s' as float) *-1 as varchar) || ' ' || %s || ')',4326),
'%s,'%s')"
"""
providerlocns.append(providerlocn)
provider['locations'] = providerlocns
#print("--- %s provddrlocn seconds ---" % (time.time() - start_time))
sql = """
select b.name as facilityName,
admit_privileges as admitPrvileges ,
affil_status_code as affilStatusCode,
cast(a.service_location_number as int),
'' as affilFlags,
a.hospital_pin as hospitalPin
from hospaff a, provsrvloc b
where a.pin = '%s'
and a.hospital_pin = b.pin
"""
cur2.close()
cur2=conn.cursor()
start_time = time.time()
cur2.execute(sql % (provider['provdrid']))
rowshosp = cur2.fetchall()
provider['hospitals'] = []
provider_hosp = {}
for rowloc in rowshosp:
providerhospitals={}
cols = gettabcols(cur2.description,"a")
for k,v in cols.items():
try:
providerhospitals[v] = rowloc[k].strip()
except:
providerhospitals[v] = rowloc[k]
provider['hospitals'].append(providerhospitals)
#initialize network serviceloc hash
if not provider_hosp.has_key(rowloc[3]):
provider_hosp[rowloc[3]] = []
provider_hosp[rowloc[3]].append(providerhospitals)
# print("--- %s Hosp seconds ---" % (time.time() - start_time))
#Note replace below to extend out for par/Non par
# sql = "select distinct category_code, current_tier, master_category_description, master_category_code, cast(cast (base_net_id_no as integer) as varchar) from staging.srvgrpprovass where pin = '%s'"
sql = """
select distinct '{' || '"category_code":' || to_json(trim(category_code)) || ',' ||
'"current_tier":' || to_json(current_tier) || ',' ||
'"master_category_code":' || to_json(trim(master_category_code)) || ',' ||
case
when trim(master_category_description)='AEXCELP' then '"mstr_type":'||'"M"'
when trim(master_category_description)='AEXCEL' then '"mstr_type":'||'"C"'
when strpos(master_category_description,'MULTI') > 0 then '"mstr_type":'||'"M"'
when strpos(master_category_description,'CONCENTRIC') > 0 then '"mstr_type":'||'"C"'
when strpos(master_category_description,'NATIONAL') > 0 then '"mstr_type":'||'"C"'
else '"mstr_type":'||'"U"'
end || ','
'"base_net_id_no":' || '"' || to_json(cast (base_net_id_no as integer) ) || '" }',
md5('{' || '"category_code":' || to_json(trim(category_code)) || ',' ||
'"current_tier":' || to_json(current_tier) || ',' ||
case
when trim(master_category_description)='AEXCELP' then '"mstr_type":'||'"M"'
when trim(master_category_description)='AEXCEL' then '"mstr_type":'||'"C"'
when strpos(master_category_description,'MULTI') > 0 then '"mstr_type":'||'"M"'
when strpos(master_category_description,'CONCENTRIC') > 0 then '"mstr_type":'||'"C"'
when strpos(master_category_description,'NATIONAL') > 0 then '"mstr_type":'||'"C"'
else '"mstr_type":'||'"U"'
end || ','
'"base_net_id_no":' || '"' || to_json(cast (base_net_id_no as integer) ) || '" }'),
md5(
'"base_net_id_no":' || '"' || to_json(cast (base_net_id_no as integer) ) || '" }'),
cast(service_location_no as integer)
from staging.srvgrpprovass where pin = '%s'; """
cur2.close()
cur2=conn.cursor()
start_time = time.time()
cur2.execute(sql % (provider['provdrid']))
rowsnet = cur2.fetchall()
# cols = gettabcols(cur2.description,"*")
provider['networks'] = []
provider['netwkhashes'] = []
provider['netwkcathashes'] = []
provider_networkloc = {}
for rowloc in rowsnet:
# providernetworks={}
netwks = json.loads(rowloc[0])
providernetworks=netwks
#initialize network serviceloc hash
if not provider_networkloc.has_key(rowloc[3]):
provider_networkloc[rowloc[3]] = []
provider_networkloc[rowloc[3]].append([]) #the network array
provider_networkloc[rowloc[3]].append([]) #the networkhash array
provider_networkloc[rowloc[3]].append([]) #the networkcathash array
provider_networkloc[rowloc[3]][0].append(netwks)
provider_networkloc[rowloc[3]][1].append((rowloc[1]))
provider_networkloc[rowloc[3]][2].append((rowloc[2]))
provider['networks'].append(providernetworks)
provider['netwkhashes'].append(rowloc[1])
provider['netwkcathashes'].append(rowloc[2])
#print("--- %s Network seconds ---" % (time.time() - start_time))
#provder quality program
sql = """
select distinct flag_code as flagCode, flag_description as qualityprogramdescr,cast (service_location_no as int)
from provsrvlocflg where pin = '%s'
"""
cur2.close()
cur2=conn.cursor()
start_time = time.time()
cur2.execute(sql % (provider['provdrkey']))
rowsnet = cur2.fetchall()
provider['programs'] = []
provider_flags = {}
for rowloc in rowsnet:
providerprograms={}
cols = gettabcols(cur2.description,"a")
for k,v in cols.items():
try:
providerprograms[v] = rowloc[k].strip()
except:
providerprograms[v] = rowloc[k]
if not provider_flags.has_key(rowloc[2]):
provider_flags[rowloc[2]] = []
provider_flags[rowloc[2]].append(rowloc[0].strip())
provider['programs'].append(providerprograms)
#call HL mapping
hl_locs = map_to_HLformat(provider)
#her we could no map each new hl_loc to a single row in our geo search table
add_provdr_loc_table(conn, hl_locs,provider,provider_networkloc,provider_specl,provider_langs,provider_hosp,provider_flags,provider_bundle)
# print len(json.dumps(provider))
#jsoncompressed = zlib.compress(json.dumps(provider))
#print("--- %s Quality seconds ---" % (time.time() - start_time))
provdrjson = json.dumps(provider)
provdrjson = provdrjson.replace("'","''")
if sqlQ:
curjson = conn.cursor()
if start_trx:
curjson.execute("begin")
start_trx = False
sql = "update provider_json3 set provdrjson = '%s' where provdrkey = %s" % (provdrjson,provider['provdrkey'])
# curjson.execute (sql)
if curjson.rowcount == 0:
sql = "insert into provider_json3 (provdrid, provdrkey,provdrjson) values ('%s','%s','%s') " % (provider['provdrid'],provider['provdrkey'],provdrjson)
# curjson.execute (sql)
#curjson.execute("commit")
# sql = "insert into provider_json_compress (provdrid, provdrkey,provider_json_ztext) values ('%s','%s',%s) " % (provider['provdrid'],provider['provdrkey'],pypg.Binary(jsoncompressed))
# curjson.execute (sql)
#svcque.task_done()
cnt += 1
if cnt%10 == 0:
curjson.execute("commit")
start_trx = True
if cnt%100 == 0:
print "thread %d at: %d " % (threadno,cnt)
#lets try to drop and redo connect every 1000 times
if True:
conn.close()
#conn = pypg.connect("dbname='sandbox_rk' user='******' port='9000' host='192.168.1.20' password='******'")
conn = pypg.connect("dbname='sandbox_rk' user='******' port='5432' host='localhost' password='******'")
curjson = conn.cursor()
else:
fjson.write("%s|%s|%s\n" % (provider['provdrid'],provider['provdrkey'],provdrjson))
cnt += 1
if cnt % 100 == 0:
print "thread %d at: %d" % (threadno,cnt)
def test_connect_non_unicode_dsn(self): # non-unicode only on python 2
conn = psycopg2.connect(str(dsn))
conn.close()
def test_connect_unicode_dsn(self):
conn = psycopg2.connect(unicode(dsn))
conn.close()
user='******', password='******', port=5432)
self.assert_('dbname=foo' in self.args[0])
self.assert_('user=postgres' in self.args[0])
self.assert_('password=secret' in self.args[0])
self.assert_('port=5432' in self.args[0])
self.assertEqual(len(self.args[0].split()), 4)
def test_generic_keywords(self):
psycopg2.connect(foo='bar')
self.assertEqual(self.args[0], 'foo=bar')
def test_factory(self):
def f(dsn, async=False):
pass
psycopg2.connect(database='foo', bar='baz', connection_factory=f)
self.assertEqual(self.args[0], 'dbname=foo bar=baz')
self.assertEqual(self.args[1], f)
self.assertEqual(self.args[2], False)
psycopg2.connect("dbname=foo bar=baz", connection_factory=f)
self.assertEqual(self.args[0], 'dbname=foo bar=baz')
self.assertEqual(self.args[1], f)
self.assertEqual(self.args[2], False)
def test_async(self):
psycopg2.connect(database='foo', bar='baz', async=1)
self.assertEqual(self.args[0], 'dbname=foo bar=baz')
self.assertEqual(self.args[1], None)
self.assert_(self.args[2])
def test_empty_param(self):
psycopg2.connect(database='sony', password='')
self.assertEqual(self.args[0], "dbname=sony password=''")
def service_single_provider(svcque,threadno):
if not sqlQ:
fjson = open(dpath + "/jsonout"+str(threadno)+".dat" , "w")
conn = psycopg2cffi.connect("dbname='sandbox_rk' user='******' port='5432' host='localhost' password='******'")
cur5=conn.cursor()
cnt = 0
start_trx = True
while True:
try:
provdrkey = svcque.get()
if provdrkey == -99:
try:
#issue commit for last incomplete batch
if sqlQ:
curjson.execute("commit")
else:
fjson.close()
print "commit final ... thread %d at: %d" % (threadno,cnt)
svcque.task_done()
continue
except:
svcque.task_done()
continue
except:
print "end of queue"
svcque.task_done()
continue
cur5.execute("Select * from vcprovdr where provdrkey = %d" % (provdrkey))
row = cur5.fetchone()
# print cur5.description
cols = gettabcols(cur5.description,"provdr","provdrlocn")
#go thru qualified cols and grab from row
provider = {}
providerlocns = []
for k,v in cols.items():
try:
provider[v] = row[k].strip()
except:
provider[v] = row[k]
#provider languages
sql = """
select distinct langname from h_provdr a, vault.h_lang e, vault.s_lang d
, vault.l_provdr_lang c
where
a.provdrkey = c.provdrkey
and e.langkey = c.langkey
and d.langkey = e.langkey
and a.provdrkey = %s
"""
cur2=conn.cursor()
cur2.execute(sql % (provider['provdrkey']))
rowsloc = cur2.fetchall()
provider['languages'] = []
for rowloc in rowsloc:
providerlangs={}
cols = gettabcols(cur2.description,"lang")
for k,v in cols.items():
try:
providerlangs[v] = rowloc[k].strip()
except:
providerlangs[v] = rowloc[k]
provider['languages'].append(providerlangs)
#provider locns
cur2=conn.cursor()
cur2.execute("Select * from mgeo_vcprovdrlocn b where b.provdrkey = %s " % (provider['provdrkey']))
rowsloc = cur2.fetchall()
providerlocns = []
for rowloc in rowsloc:
cols = gettabcols(cur2.description,"provdrlocn")
providerlocn = {}
for k,v in cols.items():
try:
providerlocn[v] = rowloc[k].strip()
except:
providerlocn[v] = rowloc[k]
providerlocns.append(providerlocn)
provider['locations'] = providerlocns
provider['specialities'] = []
sql = """
select distinct c.practspecldescr as specialty, practspeclcode as specialtyid,provdrlocnpractspeclprimspeclflag as isPrimary
from
mgeo_vcprovdrlocn b,
m_vcpractspecl c,
l_provdrlocnpractspecl_practspecl e,
l_provdrlocnpractspecl_provdrlocn f,
h_provdrlocnpractspecl g,
s_provdrlocnpractspecl h
where
e.practspeclkey = c.practspeclkey
and f.provdrlocnkey = b.provdrlocnkey
and g.provdrlocnpractspeclkey = e.provdrlocnpractspeclkey
and g.provdrlocnpractspeclkey = f.provdrlocnpractspeclkey
and h.provdrlocnpractspeclkey = g.provdrlocnpractspeclkey
and b.provdrkey = %d
"""
sql = """
select
'{' || '"specialty":' || to_json(trim(practice_description) ) ||','
'"specialtyid":' || to_json(trim(practice_code)) || ',"isPrimary":' || to_json(trim(prim_spec_ind)) || '}'
from provsrvlocspec
where pin = '%s'
"""
cur3=conn.cursor()
# cur3.execute("Select * from vcpractspecl c,m_vcprovdrlocnpractspecl e where e.provdrlocnkey = %s and e.practspeclkey = c.practspeclkey" % (providerlocn['provdrlocnkey']))
start_time = time.time()
cur3.execute(sql % (provider['provdrid']))
#print("--- %s Specialty seconds ---" % (time.time() - start_time))
rowspract = cur3.fetchall()
for rowpract in rowspract:
practspecl = json.loads(rowpract[0])
provider['specialities'].append(practspecl)
cur3.close()
#print("--- %s Specialty seconds ---" % (time.time() - start_time))
#bundle sql
sql = """
select distinct bundleid from
mgeo_vcprovdrlocn b,
m_vcpractspecl c,
l_provdrlocnpractspecl_practspecl e,
l_provdrlocnpractspecl_provdrlocn f,
h_provdrlocnpractspecl g,
CBOR i
where
e.practspeclkey = c.practspeclkey
and f.provdrlocnkey = b.provdrlocnkey
and g.provdrlocnpractspeclkey = e.provdrlocnpractspeclkey
and g.provdrlocnpractspeclkey = f.provdrlocnpractspeclkey
and c.practspeclcode = i.practicecode
and b.provdrkey = %s
union
select distinct bundleid from
m_vcprovdr a,
CBOR b
where
b.practicecode is null
and trim(a.provdrtype) = b.phtype
and a.provdrkey = %s
"""
cur3=conn.cursor()
start_time=time.time()
cur3.execute(sql % (provider['provdrkey'], (provider['provdrkey'])))
rowspract = cur3.fetchall()
bundles = []
for rowpract in rowspract:
cols = gettabcols(cur3.description,"bundleid")
for k,v in cols.items():
try :
bundles.append( rowpract[k].strip())
except:
bundles.append( rowpract[k])
provider['bundles'] = bundles
#print("--- %s Bundles seconds ---" % (time.time() - start_time))
sql = """
select h.provdrname, h.provdrisfacilityflag from vault.h_provdrhospital a,
vault.l_provdrhospital_provdr_provdr b,
vault.l_provdrhospital_provdr_hospital c,
h_provdr d,
s_provdr e,
h_provdr g,
s_provdr h
where a.provdrhospitalkey = b.provdrhospitalkey
and b.provdrkey_provdr = d.provdrkey
and e.provdrkey = d.provdrkey
and a.provdrhospitalkey = c.provdrhospitalkey
and c.provdrkey_hospital = g.provdrkey
and h.provdrkey = g.provdrkey
and d.provdrkey = %d
"""
cur2.close()
cur2=conn.cursor()
start_time = time.time()
cur2.execute(sql % (provider['provdrkey']))
rowshosp = cur2.fetchall()
provider['hospitals'] = []
for rowloc in rowshosp:
providerhospitals={}
cols = gettabcols(cur2.description,"provdr")
for k,v in cols.items():
try:
providerhospitals[v] = rowloc[k].strip()
except:
providerhospitals[v] = rowloc[k]
provider['hospitals'].append(providerhospitals)
#print("--- %s Hosp seconds ---" % (time.time() - start_time))
sql = """
select distinct c.netwkcategorycode, netwkcategorydescr
from
h_provdrlocn e,
vault.h_netwkcategory c,
vault.h_provdrlocnnetwkcategory d,
vault.l_provdrlocnnetwkcategory_provdrlocn f,
vault.l_provdrlocnnetwkcategory_netwkcategory g,
l_provdrlocn_provdr h,
vault.s_netwkcategory i
where f.provdrlocnkey = e.provdrlocnkey
and g.netwkcategorykey = c.netwkcategorykey
and d.provdrlocnnetwkcategorykey = f.provdrlocnnetwkcategorykey
and d.provdrlocnnetwkcategorykey = g.provdrlocnnetwkcategorykey
and h.provdrlocnkey = e.provdrlocnkey
and i.netwkcategorykey = c.netwkcategorykey
and h.provdrkey = %d
"""
#Note replace below to extend out for par/Non par
# sql = "select distinct category_code, current_tier, master_category_description, master_category_code, cast(cast (base_net_id_no as integer) as varchar) from staging.srvgrpprovass where pin = '%s'"
sql = """
select distinct '{' || '"category_code":' || to_json(trim(category_code)) || ',' ||
'"current_tier":' || to_json(current_tier) || ',' ||
'"master_category_code":' || to_json(trim(master_category_code)) || ',' ||
case
when trim(master_category_description)='AEXCELP' then '"mstr_type":'||'"M"'
when trim(master_category_description)='AEXCEL' then '"mstr_type":'||'"C"'
when strpos(master_category_description,'MULTI') > 0 then '"mstr_type":'||'"M"'
when strpos(master_category_description,'CONCENTRIC') > 0 then '"mstr_type":'||'"C"'
else '"mstr_type":'||'"U"'
end || ','
'"base_net_id_no":' || '"' || to_json(cast (base_net_id_no as integer) ) || '" }'
from staging.srvgrpprovass where pin = '%s'; """
cur2.close()
cur2=conn.cursor()
start_time = time.time()
cur2.execute(sql % (provider['provdrid']))
rowsnet = cur2.fetchall()
# cols = gettabcols(cur2.description,"*")
provider['networks'] = []
for rowloc in rowsnet:
# providernetworks={}
providernetworks=json.loads(rowloc[0])
provider['networks'].append(providernetworks)
#print("--- %s Network seconds ---" % (time.time() - start_time))
#provder quality program
sql = """
select qualityprogramdescr,qualityprogramcode from vault.h_qualityprogram a,
vault.s_qualityprogram b,
vault.l_provdrlocn_qualityprogram c,
h_provdrlocn d,
s_provdrlocn e,
h_provdr f,
s_provdr g,
l_provdrlocn_provdr h
where a.qualityprogramkey = b.qualityprogramkey
and a.qualityprogramkey = c.qualityprogramkey
and d.provdrlocnkey = c.provdrlocnkey
and e.provdrlocnkey = d.provdrlocnkey
and h.provdrkey = f.provdrkey
and h.provdrlocnkey = d.provdrlocnkey
and f.provdrkey = g.provdrkey
and f.provdrkey = %d
"""
cur2.close()
cur2=conn.cursor()
start_time = time.time()
cur2.execute(sql % (provider['provdrkey']))
rowsnet = cur2.fetchall()
provider['programs'] = []
for rowloc in rowsnet:
providerprograms={}
cols = gettabcols(cur2.description,"quality")
for k,v in cols.items():
try:
providerprograms[v] = rowloc[k].strip()
except:
providerprograms[v] = rowloc[k]
provider['programs'].append(providerprograms)
# print len(json.dumps(provider))
#jsoncompressed = zlib.compress(json.dumps(provider))
#print("--- %s Quality seconds ---" % (time.time() - start_time))
provdrjson = json.dumps(provider)
provdrjson = provdrjson.replace("'","''")
if sqlQ:
curjson = conn.cursor()
if start_trx:
curjson.execute("begin")
start_trx = False
sql = "update provider_json set provdrjson = '%s' where provdrkey = %s" % (provdrjson,provider['provdrkey'])
curjson.execute (sql)
if curjson.rowcount == 0:
sql = "insert into provider_json (provdrid, provdrkey,provdrjson) values ('%s','%s','%s') " % (provider['provdrid'],provider['provdrkey'],provdrjson)
curjson.execute (sql)
curjson.execute("commit")
# sql = "insert into provider_json_compress (provdrid, provdrkey,provider_json_ztext) values ('%s','%s',%s) " % (provider['provdrid'],provider['provdrkey'],psycopg2cffi.Binary(jsoncompressed))
# curjson.execute (sql)
svcque.task_done()
cnt += 1
if cnt % 100 == 0:
curjson.execute("commit")
start_trx = True
print "thread %d at: %d" % (threadno,cnt)
else:
fjson.write("%s|%s|%s\n" % (provider['provdrid'],provider['provdrkey'],provdrjson))
cnt += 1
if cnt % 100 == 0:
print "thread %d at: %d" % (threadno,cnt)
svcque.task_done()
def test_no_keywords(self):
psycopg2.connect('')
self.assertEqual(self.args[0], '')
self.assertEqual(self.args[1], None)
self.assertEqual(self.args[2], False)
def test_dsn(self):
psycopg2.connect('dbname=blah x=y')
self.assertEqual(self.args[0], 'dbname=blah x=y')
self.assertEqual(self.args[1], None)
self.assertEqual(self.args[2], False)
def test_supported_keywords(self):
psycopg2.connect(database='foo')
self.assertEqual(self.args[0], 'dbname=foo')
psycopg2.connect(user='******')
self.assertEqual(self.args[0], 'user=postgres')
psycopg2.connect(password='******')
self.assertEqual(self.args[0], 'password=secret')
psycopg2.connect(port=5432)
self.assertEqual(self.args[0], 'port=5432')
psycopg2.connect(sslmode='require')
self.assertEqual(self.args[0], 'sslmode=require')
psycopg2.connect(database='foo',
user='******', password='******', port=5432)
self.assert_('dbname=foo' in self.args[0])
self.assert_('user=postgres' in self.args[0])
self.assert_('password=secret' in self.args[0])
self.assert_('port=5432' in self.args[0])
self.assertEqual(len(self.args[0].split()), 4)
from flask import Blueprint, abort, make_response, request, jsonify
import psycopg2cffi
import logging
logging.basicConfig(level=logging.INFO)
conn_string = "host='127.0.0.1' dbname='adsl' user='******' password='******'"
conn = psycopg2cffi.connect(conn_string)
parameter_page = Blueprint('parameter_page', __name__)
@parameter_page.route('/show/<name>', methods=['GET'])
def show_algo(name):
cur = conn.cursor()
query = "select * from algo_para where algo = '" + str(name) + "';"
cur.execute(query)
rows = [r for r in cur]
para = []
for row in rows:
para.append({'name': str(row[1]), 'type': str(row[2]), 'default': float(row[3]), 'min':float(row[4]), 'max': float(row[5])})
query = "select * from algo_dataset where algo = '" + str(name) + "';"
cur.execute(query)
rows = [r for r in cur]
dataset = []
for row in rows:
dataset.append(str(row[1]))
def test_generic_keywords(self):
psycopg2.connect(foo='bar')
self.assertEqual(self.args[0], 'foo=bar')
def execute(self):
#job id
self.job_id = int(time.time())
pmon = threading.Thread(target=self.monitor_persist, args=())
pmon.start();
#start monitor thread
#if a unit test get the first db connection and hold
#used for rollback on tests
passed_dbconn = None
for step in self.steps:
#note below loop only used for Process based
#currenlty does nothing!
for wstep in step.wait_steps:
#if there is no pid for this wait step
#it probably was done Sync
if self.step_pids.has_key(wstep):
for wpid in self.step_pids[wstep]:
pass
# wpid = self.step_pids[wstep]
# print "Step %s waits on %s" % (step.name,wstep)
# wpid.join()
else:
# print "missing pid %s" % (wstep,)
pass
if step.steptype == "SQL":
#if this is a unit test then get only one connection and
#do syncrhonous
if self.jobtype == "unittest":
#get the first step and do its db connect.
if passed_dbconn == None:
try:
passed_dbconn = psycopg2.connect(step.connectdb)
except:
passed_dbconn = psycopg2cffi.connect(step.connectdb)
execstep(self.queue,step.file,step,None,passed_dbconn)
self.pids.append(0) #add to fake pid so it does results
elif step.async:
p = threading.Thread(target=execstep, args=(self.queue,step.file,step,self.step_pids))
# p = multiprocessing.Process(target=execstep, args=(self.queue,step.file,step,self.step_pids))
self.pids.append(p)
if not (self.step_pids.has_key(step.name)):
self.step_pids[step.name] = []
self.step_pids[step.name].append(p)
p.start()
# print "started step %s" % (step.name)
else:
# print "started step %s" % (step.name,self.step_pids)
execstep(self.queue,step.file,step)
elif step.steptype == 'python':
#find path of file
paths=step.file.split("/")
endpath=paths[len(paths)-1]
subpath=step.file[0:step.file.index(endpath)]
# print subpath
sys.path.append(subpath)
pymod = __import__(endpath)
if step.async:
p = threading.Thread(target=pymod.execstep, args=(self.queue,step,self.step_pids))
# p = multiprocessing.Process(target=unittest.execstep, args=(self.queue,step,))
self.pids.append(p)
if not (self.step_pids.has_key(step.name)):
self.step_pids[step.name] = []
self.step_pids[step.name].append(p)
p.start()
# print "started step %s" % (step.name)
else:
# print "started step %s" % (step.name,self.step_pids)
pymod.exestep(self.queue, step)
#if a unit test rollback automatically
if self.jobtype == "unittest":
cur = passed_dbconn.cursor()
cur.execute("rollback")
pidlen = len(self.pids)
deque = 0
job_status = []
while(deque < pidlen):
#print "wait on pid %d " %(pid.pid,)
#pid.join()
sq = self.queue.get()
if (sq != None):
# print "\n\t\tReturned %s,%s" % (sq.step.name,sq.return_value)
sq.step.active=False
job_status.append(sq)
deque += 1
self.monitor_alive = False
return job_status