def new_entry(DATE_K, TIME_K, BAL_VOLUME, VOLUME, OPEN, HIGH, LOW, CLOSE,
currency):
conn = None
try:
params = config.db_config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
query = "INSERT INTO " + currency + " (DATE_K, TIME_K, BAL_VOLUME, VOLUME, OPEN, HIGH, LOW, CLOSE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s);"
data = (DATE_K, TIME_K, BAL_VOLUME, VOLUME, OPEN, HIGH, LOW, CLOSE)
cur.execute(query, data)
#
#cur.execute(str_to_record);
# close communication with the PostgreSQL database server
cur.close()
# commit the changes
conn.commit()
# print ("Records in ", currency, " created successfully")
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def __init__(self):
self.db_config = config.db_config()
self.db_session = {}
self.cache_session = {}
#####################
# MASTER DATA STORE #
#####################
_engine = create_engine('mysql+mysqldb://%s:%s@%s/%s' %
(self.db_config['PUPPET']['Username'],
self.db_config['PUPPET']['Password'],
self.db_config['PUPPET']['Host'],
self.db_config['PUPPET']['Database']),
echo=True)
# Bind the engine to the metadata of the PuppetBase class so that the
# declaratives can be accessed through a DBSession instance
PuppetModel.metadata.bind = _engine
_db_session = scoped_session(sessionmaker(bind=_engine))
self.db_session['puppet'] = _db_session()
######################
# SESSION DATA CACHE #
######################
self.cache_session['sessions'] = redis.StrictRedis(
host=self.db_config['SESSION-CACHE']['Host'],
port=self.db_config['SESSION-CACHE']['Port'],
db=self.db_config['SESSION-CACHE']['Database'],
socket_connect_timeout=3)
def db_connect_tool_database():
vnf_params = db_config('database.ini', 'vnf_onboarding')
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**vnf_params)
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
# conn.cursor will return a cursor object, you can use this cursor to perform queries
cursor = conn.cursor()
table_name = get_config_param('database.ini', 'Details', 'table')
table_exist_query = "SELECT EXISTS(SELECT 1 FROM information_schema.tables WHERE table_catalog= %s AND table_schema='public' AND table_name=%s);"
table_exist_data = (vnf_params['dbname'], table_name)
cursor.execute(table_exist_query, table_exist_data)
records = cursor.fetchall()
pprint.pprint((records[0])[0])
isTable = (records[0])[0]
print(isTable)
if len(records) > 0 and isTable == True:
print "Database table {} exists".format(table_name)
else:
print "Database table {} does NOT exist".format(table_name)
try:
create_table = "CREATE TABLE {} (username varchar(18) UNIQUE not null, password varchar(18) not null , emailid varchar(50) UNIQUE not null)".format(
table_name)
#table_data = (AsIs(table_name))
print("Creating Table %s", table_name)
print("Query %s", create_table)
cursor.execute(create_table)
print("Table Created")
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def get_raw_log_data(day, hour=18):
'''从数据库读取od数据
Parameters:
----------
day : int
9月的日期
hour : int
小时{0-23}
Returns:
---------
rows : list
[ (log_time, car_id, on_service, x, y, v), ...]
'''
sql = '''select log_time, car_id, on_service, ST_X(geom), ST_Y(geom), velocity from gps_log_9_{day} where EXTRACT(HOUR FROM log_time) = {hour} order by car_id, log_time limit 10000;'''.format(day=day, hour=hour)
conn = None
try:
params = db_config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(sql)
rows = cur.fetchall()
cur.close()
return rows
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def entry_check(entry_pos, currency_table):
conn = None
try:
params = config.db_config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("SELECT (DATE_K), TIME_K FROM " + currency_table +
" ORDER BY DATE_K, TIME_K ")
rows = cur.fetchall()
# Check if there is any content in the table
if len(rows) != 0:
if entry_pos == 'first':
last_entry = (rows[0])
else:
last_entry = (rows[-1])
date_str = (str(last_entry[0]) + " " + str(last_entry[1]))
#date_str = last_entry[0] + " " + last_entry[1]
print(date_str)
cur.close()
return date_str
else:
return False
except (Exception, psycopg2.DatabaseError) as error:
# Return false, indicating most likely that the table does not exist
return False
print(error)
finally:
if conn is not None:
conn.close()
def eval_SSE(individual):
"""
Calculate SSE for travel time from each simulation iteration.
:type individual: toolbox object
:param individual: gene from GA run holding binary info, e.g. "0001011100"
:return: a single element tuple
"""
sqlite_file = 'vissim_db.sqlite'
conn = sqlite3.connect(sqlite_file)
file = 'TravelTime_Google.csv'
benchmark = pd.read_csv(file)
cfg = config.db_config()
cc0 = decode(cfg['cc0_max'], cfg['cc0_min'], individual[:7])
cc1 = int(decode(cfg['cc1_max'], cfg['cc1_min'], individual[7:9]))
cc4 = decode(cfg['cc4_max'], cfg['cc4_min'], individual[9:12])
cc5 = decode(cfg['cc5_max'], cfg['cc5_min'], individual[-3:])
input = {'cc0':cc0, 'cc1':cc1, 'cc4':cc4, 'cc5':cc5}
result = mockSim(input)
x = benchmark.loc[benchmark.Route == 10, 'Average of Travel_Time(min)'].iloc[0] * 60
result['Diff'] = result['TravTm'] - x
sse = sum(result['Diff']**2)
SSE = (sse, )
finalResult = pd.DataFrame({'Route':result.iloc[0,0], 'Diff':np.mean(result['Diff']),
'SSE':sse,'cc0':cc0, 'cc1':cc1,'cc4':cc4, 'cc5':cc5,
'Timestamp':datetime.datetime.now()}, index = [0])
finalResult.to_sql('result', conn, if_exists='append', index=False)
return SSE
def db_check():
""" Connect to the PostgreSQL database server """
conn = None
try:
# read connection parameters
params = config.db_config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# execute a statement
print('PostgreSQL database version:')
cur.execute('SELECT version()')
# display the PostgreSQL database server version
db_version = cur.fetchone()
print(db_version)
# close the communication with the PostgreSQL
cur.close()
# Assume that database is OK
return True
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def db_check_credentials(username, password):
""" Connect to the PostgreSQL database server """
conn = None
# read connection parameters
params = db_config('database.ini', 'vnf_onboarding')
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# conn.cursor will return a cursor object, you can use this cursor to perform queries
cursor = conn.cursor()
# execute our Query
#cursor.execute("SELECT username,password FROM account WHERE ( account.username = username and account.password = password)")
cursor.execute("SELECT username,password FROM vnf_onboarding_tool_users")
# retrieve the records from the database
records = cursor.fetchall()
print(records)
for rec in records:
print rec
if rec[0] == username and rec[1] == password:
print("found record")
return True
print("Did not find record")
cursor.close()
return False
def __init__(self):
DB_CONFIG = db_config()
self.mariadb_connection = mariadb.connect(
host=DB_CONFIG['db_host'],
port=DB_CONFIG['db_port'],
user=DB_CONFIG['db_username'],
password=DB_CONFIG['db_password'],
database='hotscrap')
self.cursor = self.mariadb_connection.cursor()
def db_user_signup(username, password, emailid):
""" Connect to the PostgreSQL database server """
conn = None
# read connection parameters
params = db_config()
pprint.pprint(params)
#pprint.pprint(params['users_database'])
#dbname = params['users_database']
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
#conn = psycopg2.connect(params['dbname'],params['user'],params['password'],params['host'])
#Needed for Create DB op
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
# conn.cursor will return a cursor object, you can use this cursor to perform queries
cursor = conn.cursor()
vnf_params = db_config('database.ini', 'vnf_onboarding')
vnf_dbname = vnf_params['dbname']
#cursor.execute("SELECT datname FROM pg_catalog.pg_database WHERE datname = 'vnf_onboarding_tool_db'")
cursor.execute(
"SELECT datname FROM pg_catalog.pg_database WHERE datname = %(dname)s",
{'dname': vnf_dbname})
records = cursor.fetchall()
if len(records) > 0:
print "Database {} exists".format(vnf_dbname)
else:
print "Databasedoes NOT exist"
try:
cdb_query = "CREATE DATABASE %s OWNER %s"
cdb_data = (AsIs(vnf_dbname), params['user'])
cursor.execute(cdb_query, cdb_data)
#cursor.execute("CREATE DATABASE %(dname)s OWNER %(user)s;",{'dname': AsIs(vnf_dbname),'user':params['user']})
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
#finally:
#if con:
# con.close()
pprint.pprint("Database %s Created successfully", vnf_dbname)
def __init__(self):
self.db_config = config.db_config()
_engine = create_engine('mysql+mysqldb://%s:%s@%s/%s' %
(self.db_config['PUPPET']['Username'],
self.db_config['PUPPET']['Password'],
self.db_config['PUPPET']['Host'],
self.db_config['PUPPET']['Database']),
echo=True)
PuppetModel.metadata.create_all(_engine)
def insert_point_contact(point_name, contact_name, contact):
conn = None
try:
conn = psycopg2.connect(db_config())
params = {'var_point_name': point_name, 'var_contact_name': contact_name, 'var_contact': contact}
exec_to(conn, 'insert_point_contact', params)
conn.commit()
except Exception as error:
print('err| {}'.format(error))
finally:
if conn is not None:
conn.close()
def insert_point(name, formfactor, payment, address):
conn = None
try:
conn = psycopg2.connect(db_config())
params = {'var_name': name, 'var_form': formfactor, 'var_payment': payment, 'var_address': address}
exec_to(conn, 'insert_point', params)
conn.commit()
except Exception as error:
print('err| {}'.format(error))
finally:
if conn is not None:
conn.close()
def insert_trader(name):
conn = None
try:
conn = psycopg2.connect(db_config())
params = {'var_trader_name': name}
exec_to(conn, 'insert_trader', params)
conn.commit()
except Exception as error:
print('err| {}'.format(error))
finally:
if conn is not None:
conn.close()
def inner(*args, **kwargs):
conn = None
try:
conn = psycopg2.connect(db_config())
return fn(conn, *args, **kwargs)
conn.commit()
except Exception as error:
print(error)
print("err")
finally:
if conn is not None:
conn.close()
def insert_trade(trader_name, point_name, count, sale, date=datetime.date.today()):
conn = None
try:
conn = psycopg2.connect(db_config())
params = {'var_trader_name': trader_name, 'var_point_name': point_name, 'var_count': count, 'var_sale': sale, 'var_date': date}
exec_to(conn, 'insert_traded', params)
conn.commit()
except Exception as error:
print('err| {}'.format(error))
finally:
if conn is not None:
conn.close()
def db_remove_row(table, key_column, key):
conn = None
try:
params = config.db_config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("DELETE FROM " + table + " WHERE " + key_column + " = %s",
(key))
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def get_rows(com_str):
conn = None
try:
params = config.db_config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
#cur.execute("SELECT DATE_K, TIME_K, BAL_VOLUME, VOLUME, OPEN, HIGH, LOW, CLOSE FROM " + table + " ORDER BY DATE_K, TIME_K ")
cur.execute(com_str)
rows = cur.fetchall()
print("The number of parts: ", cur.rowcount)
for row in rows:
print(row)
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def db_create_row(table, content_list):
data = []
conn = None
try:
params = config.db_config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
query = "INSERT INTO " + table + " ("
for idx, element in enumerate(content_list):
# If this is not the last element in the array, add comma for the next element
if (idx + 1) < len(content_list):
query = query + " " + element[0] + ","
else:
query = query + " " + element[0]
query = query + ") VALUES ("
# Add enough placeholders for each table value
for idx, element in enumerate(content_list):
if (idx + 1) < len(content_list):
query = query + "%s, "
else:
query = query + "%s);"
#
# Save values of the table's elements:
for idx, element in enumerate(content_list):
data.append(element[1])
#query = "INSERT INTO " + table + " (DATE_K, TIME_K, BAL_VOLUME, VOLUME, OPEN, HIGH, LOW, CLOSE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s);"
#data = (DATE_K, TIME_K, BAL_VOLUME, VOLUME, OPEN, HIGH, LOW, CLOSE)
cur.execute(query, data)
# close communication with the PostgreSQL database server
cur.close()
# commit the changes
conn.commit()
# print ("Records in ", currency, " created successfully")
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def db_connet():
conn = None
try:
conn = psycopg2.connect(db_config())
test(conn)
fill_dics(conn)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
print("err")
finally:
if conn is not None:
conn.close()
insert_point('name1', 'sale point', 'real', 'Дильмухамеда 91')
insert_point_contact('name1', 'Kamil', '8 701 449 19 12')
insert_trader('Erba')
insert_trade('Erba', 'name1', '10', '15000', datetime.date(2012, 12, 14))
insert_trade('Erba', 'name1', '10', '15000')
def flush_table(table_to_flush):
conn = None
try:
params = config.db_config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
com_str = "DROP TABLE " + table_to_flush + ";"
cur.execute(com_str)
cur.close()
# commit the changes
conn.commit()
print("table deleted")
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def insert_data(username, password, emailid):
vnf_params = db_config('database.ini', 'vnf_onboarding')
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**vnf_params)
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
# conn.cursor will return a cursor object, you can use this cursor to perform queries
cursor = conn.cursor()
table_name = get_config_param('database.ini', 'Details', 'table')
try:
#insertop = "INSERT INTO {} (username, password, emailid ) VALUES({},{},{})".format(table_name,username,password,emailid)
insertop = "INSERT INTO %s (username, password, emailid ) VALUES(%s,%s,%s)"
insertData = (AsIs(table_name), username, password, emailid)
print("insert query = %s", insertop)
cursor.execute(insertop, insertData)
except psycopg2.DatabaseError, e:
print 'Error %s' % e
#sys.exit(1)
return "False"
def new_table(com_str):
conn = None
try:
params = config.db_config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
# Create table with given name if it doesnt exists yet
cur.execute(com_str)
# close communication with the PostgreSQL database server
cur.close()
# commit the changes
conn.commit()
# print (table_name, " table created/existence verified successfully")
except (Exception, psycopg2.DatabaseError) as error:
# print ("Failed to create table for ", table_name)
print(error)
finally:
if conn is not None:
conn.close()
def db_read_row(table, rows, key_column, key):
conn = None
try:
params = config.db_config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
#cur.execute("SELECT * FROM " + table + " WHERE (" + key_column + " = " + key + ")")
query = "SELECT * FROM " + table + " WHERE " + key_column + " = %s"
cur.execute(query, (key))
#cur.execute("SELECT FROM " + table + " WHERE " + key_column + " = %s", (key))
if cur.fetchone() is not None:
row = cur.fetchone()
return row
else:
return None
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def db_check_credentials(username, password):
""" Connect to the PostgreSQL database server """
conn = None
# read connection parameters
params = db_config('database.ini', 'vnf_onboarding')
db_table = get_config_param('database.ini', 'Details', 'table')
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# conn.cursor will return a cursor object, you can use this cursor to perform queries
cursor = conn.cursor()
# execute our Query
check_user_query = "SELECT username,password FROM {} WHERE ( {}.username = '******' )".format(
db_table, db_table, username)
try:
cursor.execute(check_user_query)
except:
print "db_check_credentials:Cannot execute", check_user_query
# retrieve the records from the database
records = cursor.fetchall()
print(records)
for rec in records:
print rec
db_password = sha256.verify(password, rec[1])
print "Verified password", db_password, rec[1]
if rec[0] == username and db_password == True:
print "db_check_credentials:User {} is Authenticated".format(
username)
return True
print "Did not find user", username
cursor.close()
return False
def pg_db_connection(cursor_factory=None):
try:
from config import db_config
db = db_config()
con = psycopg2.connect(
user=db['user'],
database=db['database'],
host=db['host'],
port=int(db['port']),
password=db['password'] if db['password'] is not None else '')
if cursor_factory is not None:
cur = con.cursor(cursor_factory=cursor_factory)
else:
cur = con.cursor()
except psycopg2.Error as err:
yield None, None, err
else:
try:
yield con, cur, None
finally:
cur.close()
con.commit()
con.close()
def mysql_db_connection(cursor_factory=None):
try:
from config import db_config
db = db_config()
con = pymysql.connect(user=db['user'],
db=db['database'],
host=db['host'],
password=db['password'] if db['password'] is not None else '',
port=int(db['port']),
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
if cursor_factory is not None:
cur = con.cursor(cursor=cursor_factory)
else:
cur = con.cursor()
except pymysql.Error as err:
yield None, None, err
else:
try:
yield con, cur, None
finally:
cur.close()
con.close()
def read_table(currency, period):
conn = None
market_data_arr = np.zeros(shape=(period, 6))
date_arr = np.zeros(shape=(period, 1), dtype='datetime64[m]')
try:
params = config.db_config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(
"SELECT DATE_K, TIME_K, BAL_VOLUME, VOLUME, OPEN, HIGH, LOW, CLOSE from "
+ currency)
rows = cur.fetchall()
# Extract only data within specified time
for idx, row in enumerate(rows[-period:]):
comb_date = datetime.combine((row[0]), (row[1]))
date_arr[idx] = np.datetime64(comb_date) # DATE
market_data_arr[idx, 0] = row[2] # BAL_VOLUME
market_data_arr[idx, 1] = row[3] # VOLUME
market_data_arr[idx, 2] = row[4] # OPEN
market_data_arr[idx, 3] = row[5] # HIGH
market_data_arr[idx, 4] = row[6] # LOW
market_data_arr[idx, 5] = row[7] # CLOSE
#
print("Required records successfully retrieved")
conn.close()
return date_arr, market_data_arr
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
#!/usr/bin/env python #coding:utf8 # Author : tuxpy # Email : [email protected] # Last modified : 2014-12-07 15:21:40 # Filename : data/db.py # Description : import pymongo from config import db_config, cfg_file cfg = db_config() db = pymongo.Connection(cfg.get('mongodb', 'host'), int(cfg.get('mongodb', 'port'))).tuxpy
def etl():
try:
params = db_config()
connection = connect(**params)
cursor = connection.cursor()
# Clear DB before executing scripts.
print("Clearing DB...")
cursor.execute("drop schema public cascade; "
"create schema public;")
print("Cleared DB.\n")
# Execute given and star schema script.
print("Executing SQL scripts...")
cursor.execute(open("stores-and-products.sql", encoding="utf-8").read())
cursor.execute(open("star-schema.sql", encoding="utf-8").read())
connection.commit()
print("Executed SQL scripts.\n")
# Transfer existing data to the new tables.
print("Transferring existing data...")
print("Transferring shops...")
cursor.execute("select s.shopid, s.name, ci.name, r.name, co.name "
"from shop s "
"join city ci on s.cityid = ci.cityid "
"join region r on ci.regionid = r.regionid "
"join country co on r.countryid = co.countryid")
shops = cursor.fetchall()
for s in shops:
cursor.execute("insert into shopid(id, name, city, region, country) "
"values(%s, %s, %s, %s, %s) "
"on conflict (id) do nothing",
(s[0], s[1], s[2], s[3], s[4]))
connection.commit()
print("Transferring articles...")
cursor.execute("select a.articleid, a.name, a.price, pg.name, pf.name, pc.name "
"from article a "
"join productgroup pg on a.productgroupid = pg.productgroupid "
"join productfamily pf on pg.productfamilyid = pf.productfamilyid "
"join productcategory pc on pf.productcategoryid = pc.productcategoryid")
articles = cursor.fetchall()
for a in articles:
cursor.execute("insert into articleid(id, name, price, productgroup, productfamily, productcategory) "
"values(%s, %s, %s, %s, %s, %s) "
"on conflict (id) do nothing",
(a[0], a[1], a[2], a[3], a[4], a[5]))
connection.commit()
print("Transferred existing data.\n")
print("Writing data from CSV file to DB...")
print("Writing dates...")
with open("sales.csv", "r", encoding="latin-1") as f:
# Open a CSV reader, skip headline, initialize dateid, list of dateids and list of dates.
reader = csv.reader(f, delimiter=";")
next(reader)
did = 1
dids = []
dates = []
# Iterate over each line, split the date string into day, month, year,
# check if it's already in the list and append if not. Increase count.
for row in reader:
if row[0].split(".") not in dates:
dids.append(did)
dates.append(row[0].split("."))
did += 1
# Zip dateids and dates together and write to the table.
dateids = list(zip(dids, dates))
for d in dateids:
cursor.execute("insert into dateid(id, day, month, year) "
"values(%s, %s, %s, %s) "
"on conflict (id) do nothing",
(d[0], d[1][0], d[1][1], d[1][2]))
connection.commit()
print("Writing data...")
amount = len(open("sales.csv").readlines())
with open("sales.csv", "r", encoding="latin-1") as f:
reader = csv.reader(f, delimiter=";")
next(reader)
id = 1
count = 0
# Give a progress counter for amount of lines already processed.
for row in reader:
if count % 1000 == 0:
print(str(count) + "/" + str(amount))
# Get ids from tables, check if exists.
cursor.execute("select id from dateid "
"where day = %s "
"and month = %s "
"and year = %s",
(row[0][:2], row[0][3:5], row[0][6:10]))
did = cursor.fetchone()
if did:
did = did[0]
cursor.execute("select id from shopid "
"where name = %s",
(row[1],))
sid = cursor.fetchone()
if sid:
sid = sid[0]
cursor.execute("select id from articleid "
"where name = %s",
(row[2],))
aid = cursor.fetchone()
if aid:
aid = aid[0]
# Check if proper values are present.
try:
sold = int(row[3])
except ValueError:
sold = row[3]
try:
rev = float(row[4].replace(",", "."))
except ValueError:
rev = row[4]
# Check again before writing to DB.
if isinstance(did, int) and isinstance(sid, int) and isinstance(aid, int) \
and isinstance(sold, int) and isinstance(rev, float):
cursor.execute("insert into facttable(id, dateid, shopid, articleid, sold, revenue) "
"values(%s, %s, %s, %s, %s, %s) "
"on conflict (id) do nothing",
(id, did, sid, aid, sold, rev))
connection.commit()
id += 1
count += 1
cursor.close()
connection.close()
return "\nFinished writing data from CSV file."
except (Exception, Error) as error:
return error
finally:
if connection:
cursor.close()
connection.close()
SQL_GET_RANDOM_FILE='''WITH aggr AS (
SELECT MAX(sf.Id) AS ID
FROM public.S3_Files sf
GROUP BY sf.FileName
)
SELECT sf.id, sf.PublicUrl, sf.FileName, sf.Created_At
FROM aggr
JOIN public.S3_Files sf on sf.Id=aggr.Id
ORDER BY RANDOM() LIMIT 1'''
SQL_SET_FILES="INSERT INTO public.S3_Files (PublicUrl, FileName) VALUES (%s,%s)"
##inserts only, kind of log, no merge and unique by name
###
params = db_config()
logging.basicConfig(filename='app.log', filemode='w', format='%(asctime)s -%(name)s - %(levelname)s - %(message)s', datefmt='%d-%b-%y %H:%M:%S', level=logging.ERROR)
# for some reason for ubuntu we should write absolute path wothout first slash: filename='home/ubuntu/flaskapp/app.log'
def test_connect():
""" Connect to the PostgreSQL database server """
logging.error("test log")
conn = None
try:
# read connection parameters
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
cur.execute('SELECT version()')
# display the PostgreSQL database server version
db_version = cur.fetchone()
