def materialized_path(self):
sql = """
WITH RECURSIVE materialized_path_cte(parent_id, GEN_PATH) AS (
SELECT
T.parent_id,
T.name :: TEXT AS GEN_PATH
FROM %s AS T
WHERE T.id = %s
UNION ALL
SELECT
T.parent_id,
(T.name || '/' || R.GEN_PATH) AS GEN_PATH
FROM materialized_path_cte AS R
JOIN %s AS T ON T.id = R.parent_id
WHERE R.parent_id IS NOT NULL
)
SELECT gen_path
FROM materialized_path_cte AS N
WHERE parent_id IS NULL
LIMIT 1;
"""
with connection.cursor() as cursor:
cursor.execute(sql, [
AsIs(self._meta.db_table), self.pk,
AsIs(self._meta.db_table)
])
row = cursor.fetchone()
if not row:
return '/'
path = row[0]
if not self.is_file:
path = path + '/'
return path
def is_checked_out(self):
sql = """
WITH RECURSIVE is_checked_out_cte(id, parent_id, checkout_id) AS (
SELECT
T.id,
T.parent_id,
T.checkout_id
FROM %s AS T
WHERE T.id = %s
UNION ALL
SELECT
T.id,
T.parent_id,
T.checkout_id
FROM is_checked_out_cte AS R
JOIN %s AS T ON T.parent_id = R.id
)
SELECT N.checkout_id
FROM is_checked_out_cte as N
WHERE N.checkout_id IS NOT NULL
LIMIT 1;
"""
with connection.cursor() as cursor:
cursor.execute(sql, [
AsIs(self._meta.db_table), self.pk,
AsIs(self._meta.db_table)
])
row = cursor.fetchone()
if row and row[0]:
return True
return False
def select(self, ticker):
conn = self.get_connection()
cur = conn.cursor()
sql = (
"""
SELECT * FROM %(table)s
"""
)
cur.execute(sql, {'table': AsIs(ticker)})
records = cur.fetchall()
conn.close()
return {'code': AsIs(ticker), 'records': records}
def execute_insert_query(table_type, insert_dict):
statement = 'insert into ' + SCHEMA + '.' + config_json[
table_type] + ' (%s) values %s ON CONFLICT DO NOTHING;'
columns = insert_dict.keys()
values = [insert_dict[column] for column in columns]
cur.execute(statement, (AsIs(','.join(columns)), tuple(values)))
# print(cur.mogrify(statement, (AsIs(','.join(columns)), tuple(values))))
conn.commit()
def insert_dict(self, insertions):
columns, values = zip(*insertions.items())
query = SQL('INSERT INTO {}.{} (%s) VALUES %s').format(
Identifier(self.schema), Identifier(self.table_name))
self.db.cursor.execute(query, [AsIs(','.join(columns)), values])
self.db.connection.commit()
def change_dict(self, item_id, changes):
columns, values = zip(*changes.items())
query = SQL('UPDATE {}.{} SET (%s) = %s WHERE id=%s').format(
Identifier(self.schema), Identifier(self.table_name))
self.db.cursor.execute(query,
[AsIs(','.join(columns)), values, item_id])
self.db.connection.commit()
def selectitem(id, user):
if request.method == 'GET':
cur = conn.cursor()
cur.execute('select * from items where warehouse_id = (%s);', (id, ))
itemss = cur.fetchall()
cur.close()
return render_template('selectitem1.html', itemlist=itemss)
else:
cur = conn.cursor()
items = request.form.getlist('select')
quantity = request.form.getlist('quantity')
a = []
for i in quantity:
if i != '':
a.append(i)
print(a)
print(items)
# print(quantity)
table = "cart" + str(user)
cur.execute('drop table if exists %(table)s cascade ',
{"table": AsIs(table)})
# cur.execute('drop table if exists %s')
# conn.commit()
cur.execute(
'create table %(table)s (item_number serial primary key, item_id integer not null references items(item_id) on delete cascade , price integer not null, quantity integer not null, total integer generated always as (quantity*price) stored); ',
{"table": AsIs(table)})
conn.commit()
for i in range(0, len(items)):
cur.execute('select price from items where item_id = (%s)',
(items[i], ))
price1 = cur.fetchone()[0]
# cur.execute('insert into %(table)s (item_id,price, quantity) values (%s, %s,%s)', {"table":AsIs(table),items[i], price1,quantity[i]})
# try:
cur.execute(
'insert into %s (item_id, price, quantity) values (%%s, %%s, %%s)'
% table, (items[i], price1, a[i]))
# conn.commit()
print('try block working')
# except:
# cur.close()
# return redirect('/selectitem/'+str(id)+'/'+str(user))
conn.commit()
cur.close()
return redirect('/cart/' + str(id) + '/' + str(user))
def insert(self, stock: Stock):
conn = self.get_connection()
cur = conn.cursor()
sql = (
"""
INSERT INTO %(table)s (TIME, PRICE, VOL, CAP) VALUES (%(datetime)s, %(price)s, %(volume)s, %(cap)s);
"""
)
cur.execute(sql, {'table': AsIs(stock.ticker), 'datetime': stock.datetime, 'price': stock.price, 'volume': stock.volume, 'cap': stock.cap})
conn.commit()
conn.close()
def test_database_is_encrypted(self):
eaf = ExternalAccountFactory(**self.encrypted_field_dict)
ea = ExternalAccount.objects.get(id=eaf.id)
ea.reload()
sql = """
SELECT %s FROM osf_externalaccount WHERE id = %s;
"""
with connection.cursor() as cursor:
cursor.execute(sql, [AsIs(', '.join(self.encrypted_field_dict.keys())), ea.id])
row = cursor.fetchone()
for blicky in row:
assert jwe.decrypt(bytes(blicky[len(EncryptedTextField.prefix):]), SENSITIVE_DATA_KEY) == self.magic_string
def install_extension(conn, extension: str):
"""Install Postgres extension."""
query = 'CREATE EXTENSION IF NOT EXISTS "%s";'
with conn.cursor() as cursor:
cursor.execute(query, (AsIs(extension), ))
installed = check_extension(conn, extension)
if not installed:
raise psycopg2.ProgrammingError(
'Postgres extension failed installation.', extension)
def cart(user, ware_id):
if request.method == 'GET':
cur = conn.cursor()
table = 'cart' + str(user)
# cur.execute('select * from %(table)s', {"table":AsIs(table)})
cur.execute('drop view if exists cart_dis')
# conn.commit()
cur.execute(
'create view cart_dis as select item_name, company, items.price, quantity, total from items, %(table)s where %(table)s.item_id = items.item_id;',
{"table": AsIs(table)})
conn.commit()
cur.execute('select * from cart_dis')
global itemss
itemss = cur.fetchall()
print(itemss)
cur.close()
return render_template('cart1.html', list=itemss, order=False)
else:
if request.form['submit'] == 'Place Order':
cur = conn.cursor()
table = 'cart' + str(user)
cur.execute('select * from %(table)s;', {"table": AsIs(table)})
items = cur.fetchall()
for i in items:
cur.execute(
'delete from orders where item_id = (%s) and shop_id = (%s) and ware_id = (%s)',
(i[1], user, ware_id))
# conn.commit()
cur.execute(
'insert into orders(item_num, item_id, price, quantity, shop_id, ware_id) values(%s, %s, %s, %s, %s, %s)',
(i[0], i[1], i[2], i[3], user, ware_id))
# conn.commit()
conn.commit()
cur.close()
return redirect('/slip')
# return render_template('slip.html')
# return render_template('cart1.html', list=itemss, order=True)
else:
return redirect('/selectitem/' + str(ware_id) + '/' + str(user))
def insert_dict(self, db_fields_enum, dict):
try:
table = db_fields_enum.__name__.lower()
none_empty_columns = list(filter(lambda e: dict[e.value] != '', db_fields_enum))
columns = list(map(lambda e: e.value, none_empty_columns))
values = [dict[col] for col in columns]
insert_statement = 'insert into {} (%s) values %s'.format(table)
self.cur.execute(insert_statement, (AsIs(','.join(columns)), tuple(values)))
self.conn.commit()
except Exception as e:
print("cant insert dict")
print(e)
def execute_upsert_query(dict, table, conflict_column_name):
statement = 'insert into ' + SCHEMA + '.' + table + ' (%s) values %s ON CONFLICT' + f'({conflict_column_name}) DO UPDATE SET '
columns = dict.keys()
values = [dict[column] for column in columns]
for column in columns:
if dict[column] is None:
pass
elif column.lower() == conflict_column_name:
pass
elif isinstance(dict[column], datetime.date) or isinstance(
dict[column], str):
statement = statement + f"{column}='{dict[column]}',"
else:
statement = statement + f"{column}={dict[column]},"
statement = statement[:-1] + ";"
cur.execute(statement, (AsIs(','.join(columns)), tuple(values)))
conn.commit()
def __createTableIfNotExists(self, arg):
conn = self.get_connection()
cur = conn.cursor()
cur.execute("select exists(select * from information_schema.tables where table_name=%s)", [arg])
if not cur.fetchone()[0]:
sql = (
"""
CREATE TABLE %(table)s (
TIME BIGINT NOT NULL,
PRICE NUMERIC NOT NULL,
VOL BIGINT NOT NULL,
CAP NUMERIC NOT NULL,
UNIQUE(TIME)
);
"""
)
cur.execute(sql, {'table': AsIs(arg)})
conn.commit()
conn.close()
async def task_process(self):
semaphore = Semaphore(2)
running_procs = []
while self.loop.is_running():
if semaphore > 0:
task = stack.blpop()
if task:
process = Popen(
["python3", "task_scripts/" + task["name"]],
stdout=PIPE,
stderr=PIPE)
tasks_table.update(identifier=task["id"],
data={
"status": "run",
"start_time": "now()",
"pid": process.pid
})
running_procs.append(process)
semaphore.decr() # выполняем задачу из стека
for proc in running_procs:
retcode = proc.poll()
if retcode is not None: # Процесс завершился.
print("Process finished, retcode: ", retcode, "args:",
proc.args)
running_procs.remove(proc)
tasks_table.update(identifier=proc.pid,
data={
"status": "completed",
"exec_time":
AsIs("now() - start_time")
},
search_by="pid")
semaphore.incr()
break
else: # Спим и проверяем ещё раз.
await asyncio.sleep(.1)
await asyncio.sleep(.1)
def adapt_numpy_float(numpy_float):
return AsIs(numpy_float)
def adapter(text_geom):
return AsIs("ST_GeomFromText('%s',%s)" %
(text_geom.string_rep, text_geom.epsg))
def adaptPoint(self, point):
return AsIs("ST_GeomFromText('%s', %s)" % (point.wkt, self.crs))
pdict['reddit_title'] = post.title
pdict['reddit_link'] = "reddit.com" + post.permalink
pdict['title'] = post.title.split("(", 1)[0].split("[", 1)[0]
extras = [num for num in post.title.split() if len(num) < 6]
parentheses = find_in("(", ")", post.title)
brackets = find_in("[", "]", post.title)
both = parentheses + brackets + extras
year, res = get_year_res(both)
pdict['year'] = year
pdict['resolution'] = res
pdict['link'] = post.url
website = post.domain.split(".")[1]
keys = pdict.keys()
mogrified = cur.mogrify(
insertcmd,
(AsIs(','.join(keys)), tuple([pdict[key] for key in keys])))
print(mogrified)
try:
cur.execute(mogrified)
except Exception as e:
raise e
passed = False
# while not passed:
# try:
# cur.execute(mogrified)
# passed = True
# except psycopg2.errors.UniqueViolation as e:
# db_id += 1
# except Exception as e:
# raise e
print(post.title, year, res)
def adapt_action_enum(action):
return AsIs(repr(action.value))
try:
return json.dumps(obj)
except TypeError:
if isinstance(obj, list):
return json.dumps([str(x) for x in obj])
elif isinstance(obj, dict):
return json.dumps({str(k): str(v) for k, v in obj.items()})
# This allows us to directly commit dict and list objects as JSONB with psycopg2
register_adapter(dict, StringConverterJSON)
register_adapter(list, StringConverterJSON)
register_adapter(set, StringConverterJSON)
# We also want our KeyStruct to be adapted as a normal text string so we can insert it directly as text
register_adapter(KeyStruct, lambda x: AsIs("'{}'".format(str(x))))
register_adapter(MR, lambda x: AsIs("'{}'".format(str(x))))
class PostgresConnectionManager(object):
"""
A class that stores and manages a psycopg2 connection pool for a postgres database.
Right now, the class just wraps the SimpleConnectionPool, but in the future could do something more complicated.
"""
def __init__(self, conn_name, minconn=1, maxconn=5, **kwargs):
"""
Instantiates a Simple Connection Pool object with minconn and maxconn
:param conn_name: What to name this connection
:param minconn: The minimum number of connections created
def adaptLineString(self, line, hasQuotation=True):
# lineStringCoordinates = [self.adaptPoint(Point(xy), hasQuotation=False) for xy in line.coords] # xy is a tuple (x, y)
return AsIs("ST_GeomFromText('%s', %s)" %
(line.wkt, self.crs)) # ",".join(lineStringCoordinates))
def adaptPolygon(self, polygon):
return AsIs(
"ST_GeomFromText('%s', %s)" %
(polygon.wkt, self.crs)) # ",".join(lineStringCoordinates))
def object_id_adapter(object_id):
return AsIs(to_sql_value(object_id))
def populateTableRoutesGeometry(self):
print("Creating table routes geometry...")
sql_insert = """INSERT INTO {}(route_id, trip_id, route_short_name, stops, stop_names, route_geom)
VALUES (%s, %s, %s, %s, %s, ST_GeomFromText('LINESTRING(%s)', 4326));
"""
sql_insert = SQL(sql_insert).format(Identifier("routes_geometry_"+str(self.region)))
sql_routes = """
SELECT route_id, route_short_name FROM {};
"""
sql_routes = SQL(sql_routes).format(Identifier("routes_"+str(self.region)))
sql_geometry = """
SELECT ST_Intersects(ST_GeomFromText('LINESTRING(%s)', 4326),
(SELECT polygon from {0} where level = (SELECT min(level) FROM {0})));
"""
sql_geometry = SQL(sql_geometry).format(Identifier("neighborhoods_"+str(self.region)))
try:
cursor = self.conn.getCursor()
cursor.execute(sql_routes)
routes = {}
row = cursor.fetchone()
while row is not None:
(route_id, route_short_name) = row
routes[route_id] = route_short_name
row = cursor.fetchone()
sql_stops = """select st.stop_id, trip_id, s.stop_lat, s.stop_lon, s.stop_parent, s.stop_name
from {} st, {} s
where trip_id in
(select trip_id from {} where route_id = %s)
and st.stop_id = s.stop_id
ORDER BY trip_id, stop_sequence;
"""
sql_stops = SQL(sql_stops).format(Identifier("stop_times_"+str(self.region)),
Identifier("stops_"+str(self.region)),
Identifier("trips_"+str(self.region)))
for route in routes:
cursor.execute(sql_stops, (route, ))
row = cursor.fetchone()
trips_set = []
trips_id = []
route_stops = []
stop_names = []
trip_stops = {}
trip_names = {}
trip_geometry = {}
previous_trip = -1
geometry = ""
while row is not None:
(stop_id, trip_id, lat, lon, parent, name) = row
if not parent:
#stop does not have a parent
parent = stop_id
if trip_id != previous_trip:
if previous_trip != -1:
route_set = set(route_stops)
res = self.checkNewTrip(trips_set, route_set)
if res >= -1:
trips_set.append(set(route_set))
trips_id.append(previous_trip)
trip_stops[previous_trip] = route_stops
trip_names[previous_trip] = stop_names
if res >= 0:
del trips_set[res]
del trips_id[res]
geometry = geometry[:-1]
trip_geometry[previous_trip] = geometry
geometry = ""
geometry += str(lon) + " " + str(lat) + ","
route_stops = [parent]
stop_names = [name]
else:
route_stops.append(parent)
stop_names.append(name)
geometry += str(lon) + " " + str(lat) + ","
previous_trip = trip_id
row = cursor.fetchone()
route_set = set(route_stops)
res = self.checkNewTrip(trips_set, route_set)
if res >= -1:
trips_set.append(set(route_set))
trips_id.append(previous_trip)
trip_stops[previous_trip] = route_stops
trip_names[previous_trip] = stop_names
if res >= 0:
del trips_set[res]
del trips_id[res]
geometry = geometry[:-1]
trip_geometry[previous_trip] = geometry
for trip_id in trips_id:
geometry_trip = AsIs(trip_geometry[trip_id])
stops = trip_stops[trip_id]
cursor.execute(sql_geometry, (geometry_trip, ))
(intersects, ) = cursor.fetchone()
if intersects:
names = trip_names[trip_id]
cursor.execute(sql_insert, (route, trip_id, routes[route], stops, names, geometry_trip))
self.conn.commit()
cursor.close()
except IOError as e:
print("I/O error({0}): {1}".format(e.errno, e.strerror))
except (Exception, psycopg2.DatabaseError) as error:
print(error)
except:
print("Unexpected error:", sys.exc_info()[0])
