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()