def insert_data(data: list, dbset: dict, live: bool):
'''
Upload data to the database
:param data:
List of dictionaries, gets converted to list of tuples
:param dbset:
DB settings passed to Pygresql to create a connection
'''
num_rows = len(data)
if num_rows > 0:
LOGGER.info('Uploading %s rows to PostgreSQL', len(data))
LOGGER.debug(data[0])
else:
LOGGER.warning('No data to upload')
return
to_insert = []
for dic in data:
# convert each observation dictionary into a tuple row for inserting
row = (dic["userId"], dic["analysisId"], dic["measuredTime"],
dic["measuredTimeNoFilter"], dic["startPointNumber"],
dic["startPointName"], dic["endPointNumber"],
dic["endPointName"], dic["measuredTimeTimestamp"],
dic["outlierLevel"], dic["cod"], dic["deviceClass"])
to_insert.append(row)
db = DB(**dbset)
if live:
db.inserttable('king_pilot.daily_raw_bt', to_insert)
else:
db.inserttable('bluetooth.raw_data', to_insert)
db.close()
def main(**kwargs):
CONFIG = configparser.ConfigParser()
CONFIG.read('db.cfg')
dbset = CONFIG['DBSETTINGS']
logger.info('Connecting to Database')
db = DB(dbname=dbset['database'],
host=dbset['host'],
user=dbset['user'],
passwd=dbset['password'])
proxies = {'https': kwargs.get('proxy', None)}
# Update Venue List
venues = []
curId = db.query('SELECT max(id) FROM city.venues').getresult()[0][0]
logger.info('Updating venues table')
venues, inserted_venues = update_venues(db, proxies, curId)
# Get Events from List of Venues
#cla = []
logger.info('Finished updating venues tables, %s new venues inserted',
inserted_venues)
inserted_count = update_events(db, proxies, venues)
logger.info('Finished processing events, %s events inserted',
inserted_count)
db.close()
def cancel(self):
# assignment is an atomic operation in python
self.cancel_flag = True
# if self.conn is not set we cannot cancel.
if self.cancel_conn:
DB(self.cancel_conn).cancel()
def connect():
"""
Connects to the database using data from dbConfig.py.
Returns:
Handler of database connection.
"""
return DB(dbname=DBNAME, host=HOST, user=USER, passwd=PASSWD)
def main(): #enlever token
#token = 'EAACEdEose0cBADbHU92C4fP13NnLV9ZB7x7qqRiuF5W3fw6ZBZCFZBlx3QhuhgObNiGAJHL36pw8ujwLKkJAUwEIFJQ4zZAilMw3skQ5wOQLP3vk1ZBDgTDM8pbsNeMClB4Fla1EH6P3AiEwlIANC34SyZCSWGUTdsSZBaEWPyyB9K5ZAVDOZAFpOGetpKZCKhhIA8ZD'
token = request.args.get('token')
#return token
db = DB(dbname='postgres',
host='172.17.0.4',
port=5432,
user='******',
passwd='admin')
myFacebook = FacebookAPI(access_token=token)
likes = myFacebook.getNamesFromFacebookLikes()
dict_reponse = {}
for like in likes:
like = like.replace('"', '') #fait planter le front sinon
like = like.replace('\'', '') #fait planter le front sinon
rep = fetchDataSingle(db, like)
# return json.dumps(rep)
dict_reponse[like] = rep
reponse = Response("")
reponse.headers['Access-Control-Allow-Origin'] = '*'
reponse.data = json.dumps(dict_reponse)
reponse.mimetype = "json"
return reponse
def main():
#enter screens
dup_fp = open("duplicates.txt", "a")
mm_fp = open("mismatches.txt","a")
processed_fp = open("processed_folders.txt", 'a')
os.chdir("Screens")
extracted_patient_tuples = []
extracted_patient_nonmatches = []
print(os.getcwd())
for folder in sorted(os.listdir(os.getcwd()), key=keyfunc):
folder_path = os.path.join(os.getcwd(),folder)
print(folder_path)
for image in os.listdir(folder_path):
#call to ocr
text = OCR_img(Image.open(os.path.join(folder_path, image)))
#append to the list of found patients in this folder
[extracted_patient_tuples.append(element) for element in extract_patient_info(text)[0]]
[extracted_patient_nonmatches.append(element) for element in extract_patient_info(text)[1]]
extracted_patient_tuples = list(set(extracted_patient_tuples))
os.chdir("..")
[mm_fp.write(element + "\n") for element in extracted_patient_nonmatches]
insert_patient_set_into_db(DB("patient_data"), dup_fp, extracted_patient_tuples)
processed_fp.write(folder+'\n')
extracted_patient_tuples = []
extracted_patient_nonmatches = []
os.chdir("Screens")
return
def requestDB(requete='select * from ',tableName='planet_osm_line',base='champ',
limit=None,only=False,verbose=False):
"""
Function that execute an sql request into a postgres database.
---
parameters :
string requete : the request will be executed.
string tableName : the name of the table will be request if only is false.
string base : name of the database
int limit : integer, request limit option.
produces :
string requeteResult : request result.
"""
db = DB(dbname=base)
if verbose :
tablesList = db.get_tables()
print('Tables :')
printPuces(tablesList)
print('\n')
headerLinesOSM = db.get_attnames(tableName)
print('Table header :')
printPuces(headerLinesOSM)
print('\n')
if only :
requeteResult = db.query(requete).getresult()
elif limit is None :
requeteResult = db.query(requete+tableName).getresult()
else :
requeteResult = db.query(requete+tableName+' limit '+str(limit)).getresult()
return requeteResult
def run_db_query(
*,
database: str,
host: str,
port: int,
username: str,
password: str,
query: str
) -> None:
logging.info("Initializing DB connection")
logging.info(
f"DB params dbname={database}, host={host}, "
f"port={port}, user={username}"
)
logging.info(f"query={query}")
db = DB(
dbname=database,
host=host,
port=port,
user=username,
passwd=password
)
logging.info("DB connection initialized")
logging.info("Executing query")
db.query(query)
logging.info("Query executed")
def __init__(self, dbname, dbhost, dbport, dbuser, dbpass):
try:
self.conn = DB(dbname=dbname,
host=dbhost,
port=dbport,
user=dbuser,
passwd=dbpass)
except Exception as ex:
print(ex)
self.conn = None
if (None != self.conn):
tables = self.conn.get_tables()
if (not ("public.users" in tables)):
try:
self.conn.query(
"CREATE TABLE users(uid bigint primary key, name text, bio text)"
)
except Exception as ex:
print(ex)
self.conn = None
if (not ("public.tweets" in tables)):
try:
self.conn.query(
"CREATE TABLE tweets(tid bigint primary key, author_id bigint, parent_id bigint, timestamp bigint, text text)"
)
except Exception as ex:
print(ex)
self.conn = None
def on_chose(self, entry):
"""
Tries to connect to the given database entry selected from
the SelectableText widget. The entry argument is an Entry object
from the pgpasslib.
"""
logging.info('Connecting to database...')
self.status_text.set_text('Connecting to database %s...' % entry.dbname)
self.footer.set_attr_map({ None: 'footer' })
try:
connection = DB(
dbname=entry.dbname,
host=entry.host,
port=entry.port,
user=entry.user,
passwd=entry.password
)
logging.info('Connected to databse %s' % entry.dbname)
urwid.emit_signal(self, 'connected', connection)
except Exception as e:
logging.error(str(e).strip())
self.status_text.set_text(str(e).strip())
self.footer.set_attr_map({ None: 'footer_error' })
def move_data(dbset):
try:
db = DB(**dbset)
db.begin()
query = db.query("SELECT bluetooth.move_raw_data();")
if query.getresult()[0][0] != 1:
db.rollback()
raise DatabaseError(
'bluetooth.move_raw_data did not complete successfully')
query = db.query("TRUNCATE bluetooth.raw_data;")
query = db.query("SELECT king_pilot.load_bt_data();")
if query.getresult()[0][0] != 1:
db.rollback()
raise DatabaseError(
'king_pilot.load_bt_data did not complete successfully')
db.query(
'DELETE FROM king_pilot.daily_raw_bt WHERE measured_timestamp < now()::DATE;'
)
db.commit()
except DatabaseError as dberr:
LOGGER.error(dberr)
db.rollback()
except IntegrityError:
LOGGER.critical(
'Moving data failed due to violation of a constraint. Data will have to be moved manually'
)
finally:
db.close()
def connect():
db = DB(dbname='sislogucab',
user='******',
passwd='root',
host='localhost',
port=5432)
return db
def get_data(from_table, key, value):
pg_db = DB(dbname=DATABASE_NAME,
host=DATABASE_URL,
port=int(DATABASE_PORT),
user=DATABASE_USERNAME,
passwd=DATABASE_PASSWORD)
result = pg_db.query("select " + key + " , " + value + " from " +
from_table + "")
return [convert_tuples(tuple, key, value) for tuple in result.getresult()]
def db_connect():
CONFIG = configparser.ConfigParser()
CONFIG.read('db.cfg')
dbset = CONFIG['DBSETTINGS']
db = DB(dbname=dbset['database'],
host=dbset['host'],
user=dbset['user'],
passwd=dbset['password'])
return db
def connect(parameters_path):
with open(parameters_path, 'r') as fich_p:
parameters = simplejson.loads(fich_p.read())
db = DB(dbname=parameters['dbname'],
host=parameters['dbserver'],
port=parameters['dbport'],
user=parameters['dbuser'],
passwd=parameters['dbpass'])
return db
def get_db():
dbname = app.config['DBNAME']
user = app.config['USER']
passwd = app.config['PASSWD']
if not hasattr(g, 'psql_db'):
g.psql_db = DB(dbname=dbname, user=user, passwd=passwd)
return g.psql_db
def run_analyze(table):
global counter
db = DB(dbname = vDatabase, host = vHost)
db.query('analyze %s' %table)
with counter.get_lock():
counter.value += 1
if counter.value % 10 == 0 or counter.value == total_tables:
logging.info(str(counter.value) + " tables completed out of " + str(total_tables) + " tables")
db.close()
def db_connect(self):
CONFIG = configparser.ConfigParser()
CONFIG.read('db.cfg')
dbset = CONFIG['DBSETTINGS']
self.db = DB(dbname=dbset['database'],
host=dbset['host'],
user=dbset['user'],
passwd=dbset['password'])
self.logger.info('Database connected.')
def connect_to_db(description):
dbname = description['dbname']
host = description['host']
port = description['port']
opt = description['opt']
user = description['user']
passwd = description['passwd']
db = DB(dbname, host, port, opt, user, passwd)
return db
def setUp(self):
"""Setup test tables or empty them if they already exist."""
db = DB(dbname=dbname, host=dbhost, port=dbport)
db.query("set datestyle to 'iso'")
db.query("set default_with_oids=false")
db.query("set standard_conforming_strings=false")
db.query("set client_min_messages=warning")
db.query("drop table if exists fruits cascade")
db.query("create table fruits(id serial primary key, name varchar)")
self.db = db
def __init__(self, db_url):
urlparse.uses_netloc.append("postgres")
self.__db_url = db_url
url = urlparse.urlparse(db_url)
self.__db = DB(
dbname=url.path[1:],
user=url.username,
passwd=url.password,
host=url.hostname,
port=url.port
)
def __init__(self, arg):
#super(db_utils, self).__init__()
self.arg = arg
self.db = DB(dbname='structnet_complete',
user='******',
passwd='structnet',
host='localhost')
#self.identity = ''.join(random.choice(string.ascii_lowercase) for _ in range(8))
#print self.identity
print "Conncted!"
self.start_time = time.time()
def get_csv_data():
""" Connect to the PostgreSQL database server """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = DB(**params)
# execute a statement
print('PostgreSQL ALL accepted users who signed up for team matching')
q = conn.query(
"SELECT * FROM users JOIN event_applications ON users.id = event_applications.user_id WHERE custom_fields ->> 'team_forming' = 'Yes, sign me up!' AND status = 'accepted';"
)
data = q.dictresult()
f = StringIO()
writer = csv.writer(f,
delimiter=',',
quotechar='"',
quoting=csv.QUOTE_MINIMAL)
writer.writerow(features)
print(f'Adding {len(data)} entries to csv file')
for row in data:
str_id = str(row['user_id'])
# get the MD5 hash of id
result = hashlib.md5(str_id.encode())
hashed_id = result.hexdigest()
full_duration = (row['custom_fields'].get(
'arrival_time', '') == "I'm staying for the entire event")
user_features = [hashed_id, row['first_name'], row['last_name'], row['email'], row['phone'], full_duration, \
row['age'], row['pronoun'], row['university'], row['education_lvl'], row['major'], \
row['grad_year'], row['custom_fields'].get("travel", None), row['custom_fields'].get("programming_skills", None), \
row['custom_fields'].get("been_to_ttb", None), None, None, row['custom_fields'].get("linkedin_url", None), \
row['custom_fields'].get("github_url", None), row['custom_fields'].get("other_url", None), row['custom_fields'].get("how_did_you_hear", None), \
None, row['custom_fields'].get("programming_experience", None), row['custom_fields'].get("how_many_hackathons", None), None, \
row['custom_fields'].get("other_skills", None), row['custom_fields'].get("particular_topic", None), row['custom_fields'].get("goals", None), \
row['custom_fields'].get("experience_area", None), row['custom_fields'].get("teammate_preference", None), None]
writer.writerow(user_features)
# move the pointer back to beginning of file
f.seek(0)
return f
except (Exception) as error:
print("Error:", error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def __init__(self):
self.connection = DB(dbname=environ.get('POSTGRESQL_ADDON_DB'),
host=environ.get('POSTGRESQL_ADDON_HOST'),
port=int(environ.get('POSTGRESQL_ADDON_PORT')),
user=environ.get('POSTGRESQL_ADDON_USER'),
passwd=environ.get('POSTGRESQL_ADDON_PASSWORD'))
try:
self.connection.query("SELECT * FROM stats")
except ProgrammingError:
print "Create Table"
self.connection.query("""CREATE TABLE stats (timestamp TIMESTAMP PRIMARY KEY,value NUMERIC(5,2))""")
def connect(self):
if not self.pgsql:
raise ValueError('No coordinates to connect to the db.')
try:
self.db = DB(dbname=self.pgsql['user'],
host=self.pgsql['host'],
port=int(self.pgsql['port']),
user=self.pgsql['user'],
passwd=self.pgsql['password'])
except:
raise IOError('Could not connect to the db.')
self.status = "Connected"
def update_configs(all_analyses, dbset):
'''
Syncs configs from blip server with database and returns configs to pull
data from.
:param all_analyses:
List of blip configurations
:param dbset:
Dictionary to connect to PostgreSQL database
'''
db = DB(**dbset)
db.begin()
db.query('''TRUNCATE bluetooth.all_analyses_day_old;
INSERT INTO bluetooth.all_analyses_day_old SELECT * FROM bluetooth.all_analyses;'''
)
db.commit()
analyses_pull_data = {}
for report in all_analyses:
report.outcomes = [outcome.__json__() for outcome in report.outcomes]
report.routePoints = [
route_point.__json__() for route_point in report.routePoints
]
row = dict(device_class_set_name=report.deviceClassSetName,
analysis_id=report.id,
minimum_point_completed=db.encode_json(
report.minimumPointCompleted.__json__()),
outcomes=report.outcomes,
report_id=report.reportId,
report_name=report.reportName,
route_id=report.routeId,
route_name=report.routeName,
route_points=report.routePoints)
#If upsert fails, log error and continue, don't add analysis to analyses to pull
try:
upserted = db.upsert('bluetooth.all_analyses',
row,
pull_data='included.pull_data')
analyses_pull_data[upserted['analysis_id']] = {
'pull_data': upserted['pull_data'],
'report_name': upserted['report_name']
}
except IntegrityError as err:
LOGGER.error(err)
db.close()
analyses_to_pull = {
analysis_id: analysis
for (analysis_id, analysis) in analyses_pull_data.items()
if analysis['pull_data']
}
return analyses_to_pull
def createConnection():
"""
Create PostgreSQL connection with configured db/port/user/password
:return: PostgreSQL connection to const.DB_DATABASE
"""
try:
conn = DB(dbname=const.DB_DATABASE,
host=config.db_ip,
port=config.db_port,
user=config.db_user,
passwd=config.db_password)
return conn
except Exception as e:
logging.exception("Create DB connection error!!")
def healthcheck(self):
self._generic_healthcheck()
spec = self.get_spec()
env = {e['name']: e['value'] for e in spec['containers'][0]['env']}
user = env['POSTGRES_USER']
passwd = env['POSTGRES_PASSWORD']
db = DB(dbname=user,
host=self.host,
port=5432,
user=user,
passwd=passwd)
sql = "create table test_table(id serial primary key, name varchar)"
db.query(sql)
assert_in('public.test_table', db.get_tables())
def get_tables():
db = DB(dbname = vDatabase, host = vHost)
table_list = []
if options.usertables:
table_list = db.get_tables()
else:
table_list = db.get_tables('system')
db.close()
if vSchema:
tables = []
regex = "^" + vSchema + "\."
for table in table_list:
if re.match(regex, table, re.I):
tables.append(table)
else:
tables = table_list
return tables
def main(**kwargs):
CONFIG = configparser.ConfigParser()
CONFIG.read('db.cfg')
dbset = CONFIG['DBSETTINGS']
logger.info('Connecting to Database')
db = DB(dbname=dbset['database'],
host=dbset['host'],
user=dbset['user'],
passwd=dbset['password'])
proxies = {'http': kwargs.get('proxy', None)}
logger.info('Requesting data')
r = requests.get(
'http://app.toronto.ca/cc_sr_v1_app/data/edc_eventcal_APR',
proxies=proxies)
events = r.json()
global CURID, ODID
CURID = db.query('SELECT max(id) FROM city.venues').getresult()[0][0]
ODID = db.query('SELECT max(od_id) FROM city.od_venues').getresult()[0][0]
logger.info('Processing events')
inserted_events, inserted_venues, updated_venues = 0, 0, 0
for i, entry0 in enumerate(events):
try:
inserted_venue, updated_venue = process_event(
i, entry0['calEvent'], db)
inserted_events += 1
inserted_venues += inserted_venue
updated_venues += updated_venue
except KeyError as key_error:
logger.error('Key error with event: %s, key %s, skipping',
entry0['calEvent'].get('eventName',
''), key_error.args[0])
logger.info('%s events processed, %s venues inserted, %s venues updated',
inserted_events, inserted_venues, updated_venues)
logger.info('closing connection to DB')
db.close()
