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