def to_sql_statement_create_or_replace(self):
"""Not implemented, postgres policies do not support replace."""
yield sql_text(
f"DROP POLICY IF EXISTS {self.signature} on {self.on_entity};")
yield sql_text(
f"CREATE POLICY {self.signature} on {self.on_entity} {self.definition};"
)
def to_sql_statement_create_or_replace(self) -> Generator[TextClause, None, None]:
"""Generates a SQL "create or replace view" statement"""
# Remove possible semicolon from definition because we're adding a "WITH DATA" clause
definition = self.definition.rstrip().rstrip(";")
yield sql_text(
f"""DROP MATERIALIZED VIEW IF EXISTS {self.literal_schema}."{self.signature}"; """
)
yield sql_text(
f"""CREATE MATERIALIZED VIEW {self.literal_schema}."{self.signature}" AS {definition} WITH {"NO" if not self.with_data else ""} DATA"""
)
def from_database(cls, connection, schema="%") -> List[PGFunction]:
"""Get a list of all functions defined in the db"""
sql = sql_text(f"""
select
n.nspname as function_schema,
p.proname as function_name,
pg_get_function_arguments(p.oid) as function_arguments,
case
when l.lanname = 'internal' then p.prosrc
else pg_get_functiondef(p.oid)
end as create_statement,
t.typname as return_type,
l.lanname as function_language
from
pg_proc p
left join pg_namespace n on p.pronamespace = n.oid
left join pg_language l on p.prolang = l.oid
left join pg_type t on t.oid = p.prorettype
where
n.nspname not in ('pg_catalog', 'information_schema')
and n.nspname like '{schema}';
""")
rows = connection.execute(sql).fetchall()
db_functions = [PGFunction.from_sql(x[3]) for x in rows]
for func in db_functions:
assert func is not None
return db_functions
def reflect_all_constraint_comments(engine, schema: str) -> CommentMap:
"""Collect a mapping of constraint comments"""
sql = sql_text(
"""
select
c.relnamespace::regnamespace::text schemaname,
c.relname tablename,
t.conname constraintname,
d.description comment_body
from pg_class c
join pg_constraint t
on c.oid = t.conrelid
join pg_description d
on t.oid = d.objoid
and t.tableoid = d.classoid
where
c.relnamespace::regnamespace::text = :schema
"""
)
results = engine.execute(sql, schema=schema).fetchall()
comment_map: CommentMap = {}
for schema_name, table_name, constraint_name, comment in results:
comment_map[schema_name] = comment_map.get(schema_name, {})
comment_map[schema_name][table_name] = comment_map[schema_name].get(table_name, {})
comment_map[schema_name][table_name][constraint_name] = comment
return comment_map
async def notify_processor(self, q, engine):
async def _write(pld):
f = open("output.log", "a")
f.write(f"{datetime.datetime.now().isoformat()} {pld}\n")
f.close()
def json_serial(obj):
"""JSON serializer for objects not serializable by default json code"""
if isinstance(obj, (datetime.datetime, datetime.date)):
return obj.isoformat()
raise TypeError(f"Type {type(obj)} not serializable")
while 1:
item = await q.get()
await _write(json.dumps(item, default=json_serial))
query = sql_text(
f"UPDATE {TABLE_NAME} SET logged_at=:curr_date WHERE id=:the_id"
)
query = query.bindparams(the_id=item.get("id"),
curr_date=datetime.datetime.now())
async with engine.acquire() as conn:
await conn.execute(query)
q.task_done()
def _iterate(self) -> Iterator[bytes]:
"""
Execute a query against a postgres DB
using SQLAlchemy
See http://docs.sqlalchemy.org/en/latest/_modules/examples/performance/large_resultsets.html
for SQLAlchemy docs on querying large data sets
"""
# Execute the query, creating a DB cursor object
self._db_cursor = self._db \
.execution_options(stream_results=True) \
.execute(sql_text(self._query))
while True:
# Fetch 1000 records at a time from the DB
records = self._db_cursor.fetchmany(1000)
# If there are no more results, we can stop iterating
if not records:
yield b""
break
# Format results as a CSV
csv = to_csv(records)
yield csv.encode('utf8')
def to_sql_statement_create(self) -> TextClause:
"""Generates a SQL "create view" statement"""
with_grant_option = " WITH GRANT OPTION" if self.with_grant_option else ""
maybe_columns_clause = f'( {", ".join(self.columns)} )' if self.columns else ""
return sql_text(
f"GRANT {self.grant} {maybe_columns_clause} ON {self.literal_schema}.{coerce_to_quoted(self.table)} TO {coerce_to_quoted(self.role)} {with_grant_option}"
)
def processed_layer_shapes_by_year_id(
year_id: int, *extra_cols: str) -> geopandas.GeoDataFrame:
"""
Loads a GIS Processed Layer into a GeoDataFrame
:param year_id:
:param extra_cols:
:return: geo dataframe of the pl pps, with shapexml converted to shapely geometry
"""
col_names = [name for (name, dtype) in pps_cols()]
extra_cols = extra_cols or []
cols = set(list(extra_cols) + col_names)
cols.add('ShapeXml')
cols_sql = ','.join(cols)
sql_ = sql_text(f"""
SELECT {cols_sql}
FROM GIS..PremierProcessedShapes pps with (NOLOCK)
join gis..ProcessedLayer pl with (NOLOCK) on pl.UID = pps.ProcessedLayerUID
join PlatformManager..YearIDMapping ym on pl.HierarchyItemUID = ym.YearUID
where ym.ID = :YearId
""")
df = pandas.read_sql(sql_, con=gis_db, params={'YearId': year_id})
shape_xml_polygons = [parse_shape_xml(xml) for xml in df['ShapeXml']]
df.drop('ShapeXml', axis=1, inplace=True)
crs_ = {'init': 'epsg:4326'}
geo_df = geopandas.GeoDataFrame(df, crs=crs_, geometry=shape_xml_polygons)
return geo_df
def to_sql_statement_drop(self) -> str:
"""Generates a SQL "drop function" statement for PGFunction"""
template = "{function_name}({parameters})"
result = parse(template, self.signature, case_sensitive=False)
try:
function_name = result["function_name"]
parameters_str = result["parameters"].strip()
except TypeError:
# Did not match, NoneType is not scriptable
result = parse("{function_name}()",
self.signature,
case_sensitive=False)
function_name = result["function_name"]
parameters_str = ""
# NOTE: Will fail if a text field has a default and that deafult contains a comma...
parameters: List[str] = parameters_str.split(",")
parameters = [
x[:len(x.lower().split("default")[0])] for x in parameters
]
parameters = [x.strip() for x in parameters]
drop_params = ", ".join(parameters)
return sql_text(
f'DROP FUNCTION {self.literal_schema}."{function_name}"({drop_params})'
)
def from_database(cls, sess, schema):
"""Get a list of all triggers defined in the db"""
sql = sql_text(
"""
select
pc.relnamespace::regnamespace::text as table_schema,
tgname trigger_name,
pg_get_triggerdef(pgt.oid) definition
from
pg_trigger pgt
inner join pg_class pc
on pgt.tgrelid = pc.oid
where
not tgisinternal
and pc.relnamespace::regnamespace::text like :schema
"""
)
rows = sess.execute(sql, {"schema": schema}).fetchall()
db_triggers = [cls.from_sql(x[2]) for x in rows]
for trig in db_triggers:
assert trig is not None
return db_triggers
def to_sql_statement_create(self):
""" Generates a SQL "create function" statement for PGFunction """
# We need to parse and replace the schema qualifier on the table for simulate_entity to
# operate
_def = self.definition
_template = "{event}{:s}ON{:s}{on_entity}{:s}{action}"
match = parse(_template, _def)
if not match:
raise SQLParseFailure(
f'Failed to parse SQL into PGTrigger.definition """{_def}"""')
event = match["event"]
action = match["action"]
# Ensure entity is qualified with schema
on_entity = match["on_entity"]
if "." in on_entity:
_, _, on_entity = on_entity.partition(".")
on_entity = f"{self.schema}.{on_entity}"
# Re-render the definition ensuring the table is qualified with
def_rendered = _template.replace("{:s}",
" ").format(event=event,
on_entity=on_entity,
action=action)
return sql_text(
f"CREATE{' CONSTRAINT ' if self.is_constraint else ' '}TRIGGER {self.signature} {def_rendered}"
)
def generate_coords(self, latitude, longitude, step_size, distance):
sql = """
SELECT
id as "gym_id",
latitude as "gym_latitude",
longitude as "gym_longitude",
(
6371 * acos (
cos ( radians({0}) )
* cos( radians( latitude ) )
* cos( radians( longitude ) - radians({1}) )
+ sin ( radians({2}) )
* sin( radians( latitude ) )
) * 1000
) AS "gym_distance"
FROM gym
HAVING gym_distance < {3}
ORDER BY gym_distance
""".format(latitude, longitude, latitude, distance)
coords = []
for gym in self.session.execute(sql_text(sql)):
lat = gym[1] + random.uniform(-step_size, step_size)
lng = gym[2] + random.uniform(-step_size, step_size)
coords.append({'lat': lat, 'lng': lng, 'id': gym[0]})
return coords
def generate_coords(self, latitude, longitude, step_size, distance):
sql = """
SELECT
id as "pokestop_id",
latitude as "pokestop_latitude",
longitude as "pokestop_longitude",
(
6371 * acos (
cos ( radians({0}) )
* cos( radians( latitude ) )
* cos( radians( longitude ) - radians({1}) )
+ sin ( radians({2}) )
* sin( radians( latitude ) )
) * 1000
) AS "pokestop_distance"
FROM pokestop
HAVING pokestop_distance < {3}
ORDER BY pokestop_distance
""".format(latitude, longitude, latitude, distance)
coords = []
for pokestop in self.session.execute(sql_text(sql)):
lat = pokestop[1] + random_lat_long_delta()
lng = pokestop[2] + random_lat_long_delta()
coords.append({'lat': lat, 'lng': lng, 'id': pokestop[0]})
return coords
def generate_coords(self, latitude, longitude, step_size, distance):
sql = """
SELECT
id as "pokestop_id",
latitude as "pokestop_latitude",
longitude as "pokestop_longitude",
(
6371 * acos (
cos ( radians({0}) )
* cos( radians( latitude ) )
* cos( radians( longitude ) - radians({1}) )
+ sin ( radians({2}) )
* sin( radians( latitude ) )
) * 1000
) AS "pokestop_distance"
FROM pokestop
HAVING pokestop_distance < {3}
ORDER BY pokestop_distance
""".format(latitude, longitude, latitude, distance)
coords = []
for pokestop in self.session.execute(sql_text(sql)):
lat = pokestop[1] + random_lat_long_delta()
lng = pokestop[2] + random_lat_long_delta()
coords.append({'lat': lat, 'lng': lng, 'id': pokestop[0]})
return coords
def run(self) -> None:
with self.connection('report') as conn:
query = sql_text(
'select * from {object}'.format(object=self.report_name))
result = conn.execute(query)
cols = result.keys()
rows = result.fetchall()
self._csvout().export(cols, rows)
def get_df(db_url, query):
engine = create_engine(db_url)
con = engine.connect()
try:
return pd.read_sql_query(sql_text(query), con=engine)
except exc.SQLAlchemyError as e:
print(e)
finally:
con.close()
def get_pps_crop(year_id: int) -> str:
sql = """
SELECT top 1 pps.Crop
FROM GIS..PremierProcessedShapes as pps with (NOLOCK)
join gis..ProcessedLayer as pl with (NOLOCK ) on pl.UID = pps.ProcessedLayerUID
join PlatformManager..YearIDMapping ym on ym.YearUID = pl.HierarchyItemUID
where ym.id = :year_id;"""
return gis_db.execute(sql_text(sql), year_id=year_id).scalar()
def to_sql_statement_create(self) -> TextClause:
"""Generates a SQL "create view" statement"""
# Remove possible semicolon from definition because we're adding a "WITH DATA" clause
definition = self.definition.rstrip().rstrip(";")
return sql_text(
f'CREATE MATERIALIZED VIEW {self.literal_schema}."{self.signature}" AS {definition} WITH {"NO" if not self.with_data else ""} DATA;'
)
def from_database(cls, connection, schema):
"""Get a list of all policies defined in the db"""
sql = sql_text(
f"""
select
schemaname,
tablename,
policyname,
permissive,
roles,
cmd,
qual,
with_check
from
pg_policies
where
schemaname = '{schema}'
"""
)
rows = connection.execute(sql).fetchall()
def get_definition(permissive, roles, cmd, qual, with_check):
definition = ""
if permissive is not None:
definition += f"as {permissive} "
if cmd is not None:
definition += f"for {cmd} "
if roles is not None:
definition += f"to {', '.join(roles)} "
if qual is not None:
if qual[0] != "(":
qual = f"({qual})"
definition += f"using {qual} "
if with_check is not None:
if with_check[0] != "(":
with_check = f"({with_check})"
definition += f"with check {with_check} "
return definition
db_policies = []
for schema, table, policy_name, permissive, roles, cmd, qual, with_check in rows:
definition = get_definition(permissive, roles, cmd, qual, with_check)
schema = coerce_to_quoted(schema)
table = coerce_to_quoted(table)
policy_name = coerce_to_quoted(policy_name)
policy = PGPolicy.from_sql(
f"create policy {policy_name} on {schema}.{table} {definition}"
)
db_policies.append(policy)
for policy in db_policies:
assert policy is not None
return db_policies
def exist_df(db_url, table):
engine = create_engine(db_url)
con = engine.connect()
try:
sql1 = "select * from to_regclass('{table}')".format(table=table)
table_exist = con.execute(sql_text(sql1)).fetchall()
return table_exist[0][0]
except exc.SQLAlchemyError as e:
print(e)
finally:
con.close()
class Book(Base):
__tablename__ = "book"
id = Column(Integer, primary_key=True, comment="@exclude create, update")
title = Column(Text, nullable=False)
author_id = Column(Integer, ForeignKey("author.id"), nullable=False)
created_at = Column(
DateTime,
nullable=False,
default=sql_text("now()"),
comment="@exclude create, update",
)
def to_sql_statement_create_or_replace(self) -> TextClause:
return sql_text(f"""
do $$
begin
{self.to_sql_statement_drop()};
exception when others then
{self.to_sql_statement_create()};
{self.to_sql_statement_drop()};
end;
$$ language 'plpgsql'
""")
class Author(Base):
__tablename__ = "author"
id = Column(Integer, primary_key=True, comment="@exclude create, update")
name = Column(Text, nullable=False)
created_at = Column(
DateTime,
nullable=False,
server_default=sql_text("now()"),
comment="@exclude create, update",
)
books = relationship("Book", uselist=True)
def apply_filters(self, q):
for col, val in self.data.get('filters', {}).items():
q = q.where(self.get_column(col) == val)
for col, val in self.data.get('filters_not', {}).items():
q = q.where(self.get_column(col) != val)
# not sure this is a great idea:
if self.data.get('where'):
q = q.where(sql_text(self.data.get('where')))
for join in self.data.get('joins', []):
left = self.get_column(join.get('left'))
right = self.get_column(join.get('right'))
q = q.where(left == right)
return q
def reflect_views(engine, schema, declarative_base) -> List[TableProtocol]:
"""Reflect SQLAlchemy ORM Tables from the database"""
sql = sql_text(
"""
select
relname view_name, description view_comment
from
pg_views v
left join pg_description c
on true
left join pg_class on c.objoid = pg_class.oid
left join pg_namespace on pg_class.relnamespace = pg_namespace.oid
where
v.viewname = relname
and nspname= :schema
"""
)
rows = engine.execute(sql, schema=schema).fetchall()
views: List[TableProtocol] = []
for view_name, view_comment in rows:
primary_key_constraint = reflect_virtual_primary_key_constraint(view_comment)
foreign_key_constraints = reflect_virtual_foreign_key_constraints(view_comment)
# Reflect view as base table
view_tab = Table(
view_name,
declarative_base.metadata,
schema=schema,
autoload=True,
autoload_with=engine,
*[primary_key_constraint],
*foreign_key_constraints,
)
class_name = rename_table(declarative_base, view_name, view_tab)
# ORM View Table
view_orm = type(
class_name,
(
declarative_base,
ViewMixin,
),
{"__table__": view_tab},
)
views.append(view_orm) # type: ignore
return views
class Note(db.Model):
__tablename__ = "notes"
id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sql_text("uuid_generate_v4()"), nullable=False)
title = db.Column(db.Text, nullable = False)
text = db.Column(db.Text, nullable = False)
date = db.Column(db.Text, nullable = False)
userId = db.Column(UUID(as_uuid=True), db.ForeignKey("users.id"), nullable=False)
createdAt = db.Column(db.DateTime, nullable=False)
updatedAt = db.Column(db.DateTime, nullable=False)
def __repr__(self):
return f"noteid:{self.id}\ntitele:{self.title}\ntext:{self.text}\ndate:{self.date}\ncreatedAt:{self.createdAt}\nupdatedAt:{self.updatedAt}\nuserId:{self.userId}"
def weather_by_year_id(year_id: int) -> pandas.Series:
sql = """
SELECT TOP 1 w.*
FROM gis..PCS_Weather w
join PlatformManager..YearIDMapping ym on ym.YearUID = w.YearUID
WHERE ym.ID = :year_id"""
exclude_cols = ['FieldID', 'YearUID']
df = pandas.read_sql(sql_text(sql),
con=gis_db,
params={'year_id': year_id})
df.drop(exclude_cols, axis=1, inplace=True)
return df.iloc[0]
def complete(self) -> bool:
'''Each script's last query tells whether it is complete.
It should be a scalar query that returns non-zero for done
and either zero or an error for not done.
'''
last_query = self.last_query()
params = params_used(self.complete_params(), last_query)
with self.connection(event=self.task_family + ' complete query: ' + self.script.name) as conn:
try:
result = conn.scalar(sql_text(last_query), params)
return bool(result)
except DatabaseError as exc:
conn.log.warning('%(event)s: %(exc)s',
dict(event='complete query error', exc=exc))
return False
def post(self):
"""
Add a book to the db
Expect a JSON payload with the following format
{
"title": "The title of the book",
"author": "The author",
}
"""
data = request.get_json()
query = "INSERT INTO `book` (`title`, `author`) VALUES (:title, :author)"
try:
self.db.connection.execute(sql_text(query), data)
return True
except:
return False
def get_titles_from_tconst_list(tconst_list: list) -> list:
"""
:param tconst_list: list of tconst ids, normally generated via get_recommendation_from_tconst
:return: list of tuples, where first field is the tconst and second field is the title from the title_basics table
"""
dbhandler = DbHandler()
dbhandler.connect()
all_titles = [
row for row in dbhandler.conn.execute(
sql_text(f"SELECT tconst, primaryTitle FROM "
f"title_basics NATURAL JOIN title_soup"))
]
all_titles_df = pd.DataFrame(data=all_titles,
columns=['tconst', 'primaryTitle'])
return [(i, all_titles_df.loc[all_titles_df['tconst'] == i]
['primaryTitle'].values[0]) for i in tconst_list]
def post(self):
"""
Add a quote to the db
Expect a JSON payload with the following format
{
"quote": "The quote",
"quote_by": "The person who said the quote",
"added_by": The person who is posting the quote"
}
"""
data = request.get_json()
query = "INSERT INTO `quote` (`quote`, `quote_by`, `added_by`) VALUES (:quote, :quote_by, :added_by)"
try:
self.db.connection.execute(sql_text(query), data)
return True
except:
return False
def from_database(cls, sess, schema):
"""Get a list of all extensions defined in the db"""
sql = sql_text(f"""
select
np.nspname schema_name,
ext.extname extension_name
from
pg_extension ext
join pg_namespace np
on ext.extnamespace = np.oid
where
np.nspname not in ('pg_catalog')
and np.nspname like :schema;
""")
rows = sess.execute(sql, {"schema": schema}).fetchall()
db_exts = [cls(x[0], x[1]) for x in rows]
return db_exts
def _database_execute(self, sql):
return self.session_mysql.execute(sql_text(sql))
