def create_tables(cur: DictCursor, conn: DictConnection): """ Function that creates all tables from `CREATE_TABLE_QUERIES` Parameters ---------- cur conn """ for query in CREATE_TABLE_QUERIES: cur.execute(query) conn.commit()
def load_staging_tables(cur: DictCursor, conn: DictConnection): """ Function that loads all data from the source S3 bucket into the staging Redshift tables by using the `COPY_TABLE_QUERIES` variable Parameters ---------- cur conn """ for query in COPY_TABLE_QUERIES: cur.execute(query) conn.commit()
def insert_tables(cur: DictCursor, conn: DictConnection): """ Function that inserts data from the staging Redshift tables into the Redshift analytical tables by using the `INSERT_TABLE_QUERIES` variable Parameters ---------- cur conn """ for query in INSERT_TABLE_QUERIES: cur.execute(query) conn.commit()
def connect(self, connection_string=None): """Used in code to connect using last self.strcon""" if connection_string == None: s = self.connection_string() else: s = connection_string try: self._con = DictConnection(s) self.init = datetime.now() self._active = True except OperationalError as e: self._active = False print('Unable to connect: {}'.format(e))
def drop_tables(cur: DictCursor, conn: DictConnection): """ Function that drops all tables from `DROP_TABLES` by using the `DROP_TABLE_FORMAT` string format Parameters ---------- cur conn """ for table in DROP_TABLES: cur.execute(DROP_TABLE_FORMAT.format(table=table)) conn.commit()
def connect(self, connection_string=None): if connection_string==None: s=self.connection_string() else: s=connection_string try: self._con=DictConnection(s) self.init=datetime.now() self._active=True return True except OperationalError as e: self._active=False print('Unable to connect: {}'.format(e)) print('Connection string used: {}'.format(s)) return False
def setup(self): if self.initialized: return if not self.connectString: raise ValueError('No connection string set, call ' '"connection(connectString)" first.') from psycopg2.extras import DictConnection self.connection = DictConnection(self.connectString) self.initialized = True
class Connection: def __init__(self): self.user = None self.password = None self.server = None self.port = None self.db = None self._con = None self.init = None self.last_sql = "" def init__create(self, user, password, server, port, db): self.user = user self.password = password self.server = server self.port = port self.db = db return self def cursor(self): return self._con.cursor() def mogrify(self, sql, arr): cur = self._con.cursor() s = cur.mogrify(sql, arr) cur.close() self.last_sql = s return s def setAutocommit(self, b): self._con.autocommit = b ## Used to execute an sql command without returning anything def execute(self, sql, arr=[]): cur = self._con.cursor() s = self.mogrify(sql, arr) cur.execute(s) cur.close() def cursor_one_row(self, sql, arr=[]): cur = self._con.cursor() s = self.mogrify(sql, arr) cur.execute(s) row = cur.fetchone() cur.close() return row def cursor_rows(self, sql, arr=[]): cur = self._con.cursor() s = self.mogrify(sql, arr) cur.execute(s) rows = cur.fetchall() cur.close() return rows def load_script(self, file): cur = self._con.cursor() f = open(file, 'r', encoding='utf-8') procedures = f.read() self.last_sql = procedures cur.execute(procedures) cur.close() f.close() def cursor_one_column(self, sql, arr=[]): """Returns un array with the results of the column""" cur = self._con.cursor() s = self.mogrify(sql, arr) cur.execute(s) for row in cur: arr.append(row[0]) cur.close() return arr def cursor_one_field(self, sql, arr=[]): """Returns only one field""" cur = self._con.cursor() s = self.mogrify(sql, arr) cur.execute(s) row = cur.fetchone()[0] cur.close() return row def commit(self): self._con.commit() def rollback(self): self._con.rollback() def connection_string(self): return "dbname='{}' port='{}' user='******' host='{}' password='******'".format( self.db, self.port, self.user, self.server, self.password) ## Returns an url of the type psql:// def url_string(self): return "psql://{}@{}:{}/{}".format(self.user, self.server, self.port, self.db) def connect(self, connection_string=None): """Used in code to connect using last self.strcon""" if connection_string == None: s = self.connection_string() else: s = connection_string try: self._con = DictConnection(s) self.init = datetime.now() self._active = True except OperationalError as e: self._active = False print('Unable to connect: {}'.format(e)) def disconnect(self): if self.is_active() == True: self._con.close() self._active = False ##Returns if connection is active def is_active(self): return self._active def is_superuser(self): """Checks if the user has superuser role""" res = False cur = self.cursor() cur.execute("SELECT rolsuper FROM pg_roles where rolname=%s;", (self.user, )) if cur.rowcount == 1: if cur.fetchone()[0] == True: res = True cur.close() return res ## Function to get password user PGPASSWORD environment or ask in console for it def get_password(self, gettext_module=None, gettex_locale=None): try: import gettext t = gettext.translation(gettext_module, gettex_locale) _ = t.gettext except: _ = str from os import environ from getpass import getpass try: self.password = environ['PGPASSWORD'] except: print(_("Write the password for {}").format(self.url_string())) self.password = getpass() return self.password
class Connection: def __init__(self): self.user=None self.password=None self.server=None self.port=None self.db=None self._con=None self.init=None self.last_sql="" def init__create(self, user, password, server, port, db): self.user=user self.password=password self.server=server self.port=port self.db=db return self def cursor(self): return self._con.cursor() def mogrify(self, sql, arr): cur=self._con.cursor() s=cur.mogrify(sql, arr) cur.close() self.last_sql=s return s ## Sometimes it's needed to work with sql after converting %s as a string. ## There is a problem with '%' when is used in a field and I use sql_insert returning a string ## So I need to keep both parameters (sql, arr) and mogrify converts them correctly def sql_string(self, sql, arr): try: from moneymoney.casts import b2s except ImportError: raise NotImplementedError("You need https://github.com/turulomio/django_moneymoney/moneymoney/casts.py to use this function.") return b2s(self.mogrify(sql,arr)) def setAutocommit(self, b): self._con.autocommit = b ## Used to execute an sql command without returning anything def execute(self, sql, arr=[]): cur=self._con.cursor() s=self.mogrify(sql,arr) cur.execute(s) cur.close() def cursor_one_row(self, sql, arr=[]): cur=self._con.cursor() s=self.mogrify(sql,arr) cur.execute(s) if cur.rowcount==0: cur.close() return None elif cur.rowcount==1: row=cur.fetchone() cur.close() return row else: cur.close() debug("More than one row is returned in cursor_one_row. Use cursor_rows instead.") return None def cursor_rows(self, sql, arr=[]): cur=self._con.cursor() s=self.mogrify(sql,arr) cur.execute(s) rows=cur.fetchall() cur.close() return rows def load_script(self, file): cur= self._con.cursor() f = open(file,'r', encoding='utf-8') procedures=f.read() self.last_sql=procedures cur.execute(procedures) cur.close() f.close() def cursor_one_column(self, sql, arr=[]): """Returns un array with the results of the column""" cur=self._con.cursor() s=self.mogrify(sql,arr) cur.execute(s) for row in cur: arr.append(row[0]) cur.close() return arr def cursor_one_field(self, sql, arr=[]): """Returns only one field""" cur=self._con.cursor() s=self.mogrify(sql,arr) cur.execute(s) if cur.rowcount==0: return None row=cur.fetchone()[0] cur.close() return row def commit(self): self._con.commit() def rollback(self): self._con.rollback() def connection_string(self): return "dbname='{}' port='{}' user='******' host='{}' password='******'".format(self.db, self.port, self.user, self.server, self.password) ## Returns an url of the type psql:// def url_string(self): return "psql://{}@{}:{}/{}".format(self.user, self.server, self.port, self.db) ## @param connection_string string. If None automatic connection_string is generated from attributes ## @return boolean True if connection was made def connect(self, connection_string=None): if connection_string==None: s=self.connection_string() else: s=connection_string try: self._con=DictConnection(s) self.init=datetime.now() self._active=True return True except OperationalError as e: self._active=False print('Unable to connect: {}'.format(e)) print('Connection string used: {}'.format(s)) return False def disconnect(self): if self.is_active()==True: self._con.close() self._active=False ##Returns if connection is active def is_active(self): return self._active def is_superuser(self): """Checks if the user has superuser role""" res=False cur=self.cursor() cur.execute("SELECT rolsuper FROM pg_roles where rolname=%s;", (self.user, )) if cur.rowcount==1: if cur.fetchone()[0]==True: res=True cur.close() return res ## Function to get password user PGPASSWORD environment or ask in console for it def get_password(self, gettext_module=None, gettex_locale=None): try: import gettext t=gettext.translation(gettext_module, gettex_locale) _=t.gettext except: _=str from os import environ from getpass import getpass try: self.password=environ['PGPASSWORD'] except: print(_("Write the password for {}").format(self.url_string())) self.password=getpass() return self.password def unogenerator_values_in_sheet(self,doc, coord_start, sql, params=[], columns_header=0, color_row_header=0xffdca8, color_column_header=0xc0FFc0, color=0xFFFFFF, styles=None): from unogenerator.commons import Coord as C, guess_object_style cur=self._con.cursor() s=self.mogrify(sql, params) cur.execute(s) rows=cur.fetchall() cur.close() coord_start=C.assertCoord(coord_start) keys=[] for desc in cur.description: keys.append(desc.name) for column, key in enumerate(keys): doc.addCellWithStyle(coord_start.addColumnCopy(column), key, color_row_header, "BoldCenter") coord_data=coord_start.addRowCopy(1) #Data for row, od in enumerate(rows): for column, key in enumerate(keys): if styles is None: style=guess_object_style(od[key]) elif styles.__class__.__name__ != "list": style=styles else: style=styles[column] if column+1<=columns_header: color_=color_column_header else: color_=color doc.addCellWithStyle(coord_data.addRowCopy(row).addColumnCopy(column), od[key], color_, style) ## @params columns_widths must be a list def unogenerator_sheet(self, filename, sql, params=[], sheet_name="Data", columns_widths=None, columns_header=0, color_row_header=0xffdca8, color_column_header=0xc0FFc0, color=0xFFFFFF, styles=None): from unogenerator import ODS_Standard, __version__ doc=ODS_Standard() doc.setMetadata( "Query result", "Query result", "Connection_pg from https://github.com/turulomio/reusingcode/", f"This file have been generated with ConnectionPg and UnoGenerator-{__version__}. You can see UnoGenerator main page in http://github.com/turulomio/unogenerator/", ["unogenerator", "sql", "query"] ) doc.createSheet(sheet_name) if columns_widths is not None: doc.setColumnsWidth(columns_widths) self.unogenerator_values_in_sheet(doc, "A1", sql, params,columns_header, color_row_header, color_column_header, color, styles) doc.removeSheet(0) doc.save(filename) doc.close()
class Connection: def __init__(self): self.user = None self.password = None self.server = None self.port = None self.db = None self._con = None self.init = None self.last_sql = "" def init__create(self, user, password, server, port, db): self.user = user self.password = password self.server = server self.port = port self.db = db return self def cursor(self): return self._con.cursor() def mogrify(self, sql, arr): cur = self._con.cursor() s = cur.mogrify(sql, arr) cur.close() self.last_sql = s return s ## Sometimes it's needed to work with sql after converting %s as a string. ## There is a problem with '%' when is used in a field and I use sql_insert returning a string ## So I need to keep both parameters (sql, arr) and mogrify converts them correctly def sql_string(self, sql, arr): return b2s(self.mogrify(sql, arr)) def setAutocommit(self, b): self._con.autocommit = b ## Used to execute an sql command without returning anything def execute(self, sql, arr=[]): cur = self._con.cursor() s = self.mogrify(sql, arr) cur.execute(s) cur.close() def cursor_one_row(self, sql, arr=[]): cur = self._con.cursor() s = self.mogrify(sql, arr) cur.execute(s) if cur.rowcount == 0: cur.close() return None elif cur.rowcount == 1: row = cur.fetchone() cur.close() return row else: cur.close() debug( "More than one row is returned in cursor_one_row. Use cursor_rows instead." ) return None def cursor_rows(self, sql, arr=[]): cur = self._con.cursor() s = self.mogrify(sql, arr) cur.execute(s) rows = cur.fetchall() cur.close() return rows def load_script(self, file): cur = self._con.cursor() f = open(file, 'r', encoding='utf-8') procedures = f.read() self.last_sql = procedures cur.execute(procedures) cur.close() f.close() def cursor_one_column(self, sql, arr=[]): """Returns un array with the results of the column""" cur = self._con.cursor() s = self.mogrify(sql, arr) cur.execute(s) for row in cur: arr.append(row[0]) cur.close() return arr def cursor_one_field(self, sql, arr=[]): """Returns only one field""" cur = self._con.cursor() s = self.mogrify(sql, arr) cur.execute(s) row = cur.fetchone()[0] cur.close() return row def commit(self): self._con.commit() def rollback(self): self._con.rollback() def connection_string(self): return "dbname='{}' port='{}' user='******' host='{}' password='******'".format( self.db, self.port, self.user, self.server, self.password) ## Returns an url of the type psql:// def url_string(self): return "psql://{}@{}:{}/{}".format(self.user, self.server, self.port, self.db) ## @param connection_string string. If None automatic connection_string is generated from attributes ## @return boolean True if connection was made def connect(self, connection_string=None): if connection_string == None: s = self.connection_string() else: s = connection_string try: self._con = DictConnection(s) self.init = datetime.now() self._active = True return True except OperationalError as e: self._active = False print('Unable to connect: {}'.format(e)) print('Connection string used: {}'.format(s)) return False def disconnect(self): if self.is_active() == True: self._con.close() self._active = False ##Returns if connection is active def is_active(self): return self._active def is_superuser(self): """Checks if the user has superuser role""" res = False cur = self.cursor() cur.execute("SELECT rolsuper FROM pg_roles where rolname=%s;", (self.user, )) if cur.rowcount == 1: if cur.fetchone()[0] == True: res = True cur.close() return res ## Function to get password user PGPASSWORD environment or ask in console for it def get_password(self, gettext_module=None, gettex_locale=None): try: import gettext t = gettext.translation(gettext_module, gettex_locale) _ = t.gettext except: _ = str from os import environ from getpass import getpass try: self.password = environ['PGPASSWORD'] except: print(_("Write the password for {}").format(self.url_string())) self.password = getpass() return self.password
class DatabaseClass: ################ def __init__(self): self.initialized = False self.connectString = None ################## def __del__(self): self.close() ######################################## def connect(self, connectString=None): if connectString is None: from pgcredentials import connectString self.connectString = connectString self.setup() ################ def setup(self): if self.initialized: return if not self.connectString: raise ValueError( 'No connection string set, call ' '"connection(connectString)" first.') from psycopg2.extras import DictConnection self.connection = DictConnection(self.connectString) self.initialized = True ################ def close(self): if not self.initialized: return self.connection.close() self.connection = None self.initialized = False ############################## def query(self, query, *args): self.setup() cursor = self.connection.cursor() cursor.execute(query, args) return(CursorHelper(cursor)) ########################## def queryone(self, *args): '''Like query(), but if you are expecting only one result. Either returns None if there were no results returned, or the row. ''' try: ret = self.query(*args)[0] except IndexError: return(None) return(ret) ################# def commit(self): self.setup() self.connection.commit() ################### def rollback(self): self.setup() self.connection.rollback()
from shapely.geometry.collection import GeometryCollection from shapely.wkb import loads, dumps from shapely.ops import cascaded_union, polygonize_full from shapely.affinity import translate from pyproj import Proj from flask import Flask, abort, make_response app = Flask(__name__) # load configuration with open('config.json', 'r') as fp: config = json.load(fp) # create DB connection db = DictConnection(config['db']) # serve index.html on main route, yeah I know this is ugly @app.route("/") def index(): with open('static/html/index.html', 'r') as fp: return fp.read() # serve street geometry (WKB) @app.route("/streets/<float:x1>/<float:y1>/<float:x2>/<float:y2>") def render_streets(x1, y1, x2, y2): cursor = db.cursor() # types of roads to load and their width in meters
class DatabaseClass: ################ def __init__(self): self.initialized = False self.connectString = None ################## def __del__(self): self.close() ######################################## def connect(self, connectString = None): if connectString == None: from pgcredentials import connectString self.connectString = connectString self.setup() ################ def setup(self): if self.initialized: return if not self.connectString: raise ValueError('No connection string set, call ' '"connection(connectString)" first.') from psycopg2.extras import DictConnection self.connection = DictConnection(self.connectString) self.initialized = True ################ def close(self): if not self.initialized: return self.connection.close() self.connection = None self.initialized = False ############################## def query(self, query, *args): self.setup() cursor = self.connection.cursor() cursor.execute(query, args) return(CursorHelper(cursor)) ########################## def queryone(self, *args): '''Like query(), but if you are expecting only one result. Either returns None if there were no results returned, or the row. ''' try: ret = self.query(*args)[0] except IndexError: return(None) return(ret) ############################################### def insert(self, table, dict = None, **kwargs): '''Insert a row into the specified table, using the keyword arguments or dictionary elements as the fields. If a dictionary is specified with keys matching kwargs, then the dictionary takes precedence. For example: insert('users', name = 'Sean', uid = 10, password = '******') will run the SQL: INSERT INTO users ( name, uid, password ) VALUES ( 'Sean', 10, 'xyzzy') ''' if dict is not None: kwargs.update(dict) values = kwargs.values() cmd = ('INSERT INTO %s ( %s ) VALUES ( %s )' % ( table, ','.join(kwargs.keys()), ','.join(['%s'] * len(values)), )) self.query(cmd, *values) ################# def commit(self): self.setup() self.connection.commit() ################### def rollback(self): self.setup() self.connection.rollback()