def create_currency_table():
""" Creating new table """
command = '''CREATE TABLE PLN_EXCHANGE_RATES (
date DATE NOT NULL,
rate NUMERIC,
interpolated BOOLEAN
)'''
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS PLN_EXCHANGE_RATES")
cur.execute(command)
cur.close()
conn.commit()
except (Exception, psycopg2.DatabaseError) as err:
print(err)
finally:
if conn is not None:
conn.close()
def connect():
""" 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 = 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()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def execute_command(command):
""" Executes command in the DVDRENTAL database """
transactions = []
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(command)
records = cur.fetchall()
for row in range(0, len(records)):
transactions.append(records[row])
cur.close()
except (Exception, psycopg2.DatabaseError) as err:
print(err)
finally:
if conn is not None:
conn.close()
return transactions
def connect():
""" function for creating db connection object """
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
return (conn)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def set_connection(): # Returns db connection
try:
global connection
con_params = config()
connection = psycopg2.connect(**con_params)
print("Successfully connected to database")
except (Exception, psycopg2.DatabaseError) as err:
print("Database connection failed:\n" + set_connection.__name__, err)
exit(1)
def set_connection(section):
"""
Gets connection to PostgreSQL database instance
:param section: section of database configuration file to use
:return: db connection
"""
try:
params = config(filename='database.ini', section=section)
global conn
conn = psycopg2.connect(**params)
print('Connection to database created')
except (Exception, psycopg2.DatabaseError) as err:
print(set_connection.__name__, err)
exit(1)
def insert_rates(from_date, to_date, currency='USD'):
""" Inserting rates and dates to the newly created table """
usd_rates = stock_rates.get_rates_avg_time(currency, from_date, to_date)
dates_and_rates = []
if (from_date > to_date):
raise Exception("WRONG DATES!")
cur_date = from_date
prev_date = cur_date
while (cur_date <= to_date):
app_date = cur_date.strftime('%Y-%m-%d')
if usd_rates.get(app_date):
interpolated = False
dates_and_rates.append(
(cur_date, usd_rates.get(app_date), interpolated))
else:
interpolated = True
usd_rates[app_date] = usd_rates.get(prev_date)
dates_and_rates.append(
(cur_date, usd_rates.get(prev_date), interpolated))
prev_date = app_date
cur_date += timedelta(days=1)
command = "INSERT INTO PLN_EXCHANGE_RATES(date, rate, interpolated) VALUES(%s, %s, %s)"
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.executemany(command, dates_and_rates)
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as err:
print(err)
finally:
if conn is not None:
conn.close()
def connect(item, high_priority):
""" Connect to the PostgreSQL database server """
conn = None
try:
params = config()
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
cur = conn.cursor()
id = item["id"]
first_name = item["first_name"]
last_name = item["last_name"]
email = item["email"]
gender = item["gender"]
cc = item["cc"]
country = item["country"]
birthdate = item["birthdate"]
# if birthdate == '':
# birthdate = '01-01-2018'
# if salary == '':
# salary = 0
# else:
# salary = salary.replace("$",'')
# salary = float(salary)
# if cc == '':
# cc = 0
sql_script_leads = 'INSERT INTO leads(id, first_name, last_name, email, gender, cc, country, birthdate) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)'
sql_script_high_priority = 'INSERT INTO high_priority(id, first_name, last_name, email, gender, cc, country, birthdate) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)'
record_tuple = (id, first_name, last_name, email, gender, cc, country, birthdate)
if high_priority:
cur.execute(sql_script_high_priority, record_tuple)
else:
cur.execute(sql_script_leads, record_tuple)
conn.commit()
print("Record inserted successfully into Leads table")
db_version = cur.fetchone()
print(db_version)
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def connect():
"""Connect to PSQL db and execute command."""
conn = None
g_id = 1
explanation = "script test"
time_spent = "3 minutes"
date = "2021-10-14"
sql = f"INSERT INTO goal_log VALUES ({g_id},'{explanation}','{time_spent}','{date}')"
try:
# Read connection parameters
print("Reading in connection parameters")
params = config()
# Connect to db
print("Connecting to db")
conn = psycopg2.connect(**params)
print("Connected")
# Create cursor
print("Opening cursor")
cur = conn.cursor()
print("Sending message")
cur.execute(sql)
print("Commiting message")
conn.commit()
print("Closing cursor")
cur.close()
except (Exception, psycopg2.DatabaseError) as err:
print(err)
finally:
if conn is not None:
print("Closing db connection")
conn.close()
print("Connection closed")
import time
#from dsds_rest_service import *
import logging
import os
import psycopg2
from db_config import config
# Log setting and generate a logfile on the current place
logpath = os.getcwd() + "/dsds_server_logs"
logging.basicConfig(level=logging.INFO,
format='[%(levelname)s] %(asctime)s - %(message)s',
filename=logpath)
logger = logging.getLogger('dsds_server_logs')
#configure database connections
params = config()
db = psycopg2.connect(**params)
dbcursor = db.cursor()
dbcursor.execute('SELECT version()')
db_version = dbcursor.fetchone()
logging.info("DB version", db_version)
# function definition (json,dpid,action,
def crud(d, dpid, action, i, *FileName):
query_exec_nr = 0
random_num = random.randrange(10000, 100000, 3)
if action == "reg_single" or action == "reg_multiple":
def connect():
DEV = True
if 'ENVIRONMENT' in os.environ:
env = os.environ['ENVIRONMENT']
print('working on %s' % env)
DEV = 'DEV' in env
conn = None
try:
# get connection parameters
params = config()
conn = psycopg2.connect(**params)
conn.autocommit = True
cur = conn.cursor()
sql_script = ''
if len(sys.argv) > 1 and os.path.exists(sys.argv[1]):
sql_script = sys.argv[1]
print('sql script file: ', sql_script)
fd = open(sql_script, 'r')
sql_commands_full = fd.read()
fd.close()
else:
print('no sql script file defined')
sql_commands_full = 'select to_char(current_timestamp, \'YYYY-MM-DD HH12:MI:SS\') as now;\n\nselect version()'
sql_commands = sql_commands_full.split('\n\n')
print('about to run %s sql commands:\n' % str(len(sql_commands)))
print('============================')
if sql_script != '' and sql_script.lower().find('db_sp_') != -1:
# call stored procedure
print('store procedure: %s \n' % sql_commands_full)
cur.callproc(sql_commands_full)
else:
SQL_CMD_RESULT = ('select', 'show')
for sql_command in sql_commands:
sql_command_to_run = ''
lines = sql_command.split('\n')
for line in lines:
if not line.startswith('--'):
sql_command_to_run = sql_command_to_run + line + '\n'
if len(sql_command_to_run) > 5:
start = time.perf_counter()
print('%s \n' % sql_command)
cur.execute(sql_command_to_run)
if cur.rowcount > 0:
if sql_command_to_run.lower().startswith(SQL_CMD_RESULT) and sql_command_to_run.lower().find('into') == -1:
rows = cur.fetchall()
print(cursor_pprint(cur, rows, 1), '\n')
else:
conn.commit()
print('rows affected: ', cur.rowcount)
else:
print('#### NO RESULTS ###')
print('elapsed time: {0:.4f} minutes'.format((time.perf_counter() - start) / 60))
print('============================')
print('')
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(f'error: {error}')
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
# Import schema.
# NOTE: this imports a number of important variables into the module
# namespace, such as 'session' and all the python classes that correspond
# to the table definitions
from schema import *
import Bunch
class g2dbError(Exception):
pass
try:
import db_config
db_config.config(metadata)
except ImportError, e:
raise g2dbError("Please set your db configuration module")
setup_all()
# LOCKS
# Module-level locks because database is global to all modules
locks = Bunch.Bunch(frame=threading.RLock(), obsnote=threading.RLock())
# ORM ABSTRACTIONS
#END
