def copy_from(df: pd.DataFrame,
table: str,
connection: psycopg2.extensions.connection,
chunk_size: int = 10000):
cursor = connection.cursor()
df = df.copy()
escaped = {
'\\': '\\\\',
'\n': r'\n',
'\r': r'\r',
'\t': r'\t',
}
for col in df.columns:
if df.dtypes[col] == 'object':
for v, e in escaped.items():
df[col] = df[col].str.replace(v, e)
try:
for i in tqdm(range(0, df.shape[0], chunk_size)):
f = StringIO()
chunk = df.iloc[i:(i + chunk_size)]
chunk.to_csv(f,
index=False,
header=False,
sep='\t',
na_rep='\\N',
quoting=None)
f.seek(0)
cursor.copy_from(f, table, columns=[f'"{i}"' for i in df.columns])
connection.commit()
except psycopg2.Error as e:
print(e)
connection.rollback()
cursor.close()
def copy_from(df: pd.DataFrame,
table: str,
connection: psycopg2.extensions.connection,
chunk_size: int = 10000):
cursor = connection.cursor()
df = df.copy()
escaped = {'\\': '\\\\', '\n': r'\n', '\r': r'\r', '\t': r'\t'}
for col in df.columns:
if df.dtypes[col] == 'object':
for v, e in escaped.items():
df[col] = df[col].str.replace(v, e)
try:
for i in range(0, df.shape[0], chunk_size):
f = io.StringIO()
chunk = df.iloc[i:(i + chunk_size)]
# The default separator is a tab, and NULLs are indicated by the two character-string '\N'
chunk.to_csv(f,
index=False,
header=False,
sep='\t',
na_rep='\\N',
quoting=None)
f.seek(0)
cursor.copy_from(f, table, columns=list(df.columns))
connection.commit()
except psycopg2.Error:
connection.rollback()
cursor.close()
def get_data_from_db(query: str, conn: psycopg2.extensions.connection,
cur: psycopg2.extensions.cursor, df: pd.DataFrame,
col_names: List[str]) -> pd.DataFrame:
try:
cur.execute(query)
while True:
# Fetch the next 100 rows
query_results = cur.fetchmany(100)
# If an empty list is returned, then we've reached the end of the results
if query_results == list():
break
# Create a list of dictionaries where each dictionary represents a single row
results_mapped = [{
col_names[i]: row[i]
for i in range(len(col_names))
} for row in query_results]
# Append the fetched rows to the DataFrame
df = df.append(results_mapped, ignore_index=True)
return df
except Exception as error:
print(f"{type(error).__name__}: {error}")
print("Query:", cur.query)
conn.rollback()
def recognise_employee_sql(connection: psycopg2.extensions.connection,
encodings):
"""Распознавание сотрудника sql-запросом"""
cursor = connection.cursor()
try:
query = '''
SELECT first_name, last_name FROM employees
WHERE sqrt( power( CUBE( array[{}]) <-> vec_low, 2) +
power( CUBE( array[{}]) <-> vec_high, 2)) <= {}
'''.format(
','.join(str(s) for s in encodings[0:64]),
','.join(str(s) for s in encodings[64:128]),
THRESHOLD,
) + \
'''
ORDER BY sqrt( power( CUBE( array[{}]) <-> vec_low, 2) +
power( CUBE( array[{}]) <-> vec_high, 2)) ASC LIMIT 1'''.format(
','.join(str(s) for s in encodings[0:64]),
','.join(str(s) for s in encodings[64:128]),
)
cursor.execute(query)
print(cursor.fetchall())
except (Exception, Error) as error:
print('Ошибка при запросе к БД:', error)
connection.rollback()
return False
def add_employee_to_db(connection: psycopg2.extensions.connection,
employee: Person):
try:
cursor = connection.cursor()
v_low = ','.join(str(s) for s in employee.face_encoding[0:64])
v_high = ','.join(str(s) for s in employee.face_encoding[64:128])
name, surname = employee.name.split()
query = f'''
INSERT INTO employees(
first_name, last_name, image_path, access, vec_low,vec_high
) VALUES (
'{name}',
'{surname}',
'{employee.image_path}',
'{employee.access}',
CUBE(array[{v_low}]),
CUBE(array[{v_high}])
) ON CONFLICT DO NOTHING;
'''
cursor.execute(query)
cursor.close()
return True
except (Exception, Error) as error:
print('Ошибка при добавлении пользователя в базу:', error)
connection.rollback()
return False
def create_table(sql_query: str, conn: psycopg2.extensions.connection,
cur: psycopg2.extensions.cursor) -> None:
try:
# Execute the table creation query
cur.execute(sql_query)
except Exception as e:
print(f"{type(e).__name__}: {e}")
print(f"Query: {cur.query}")
conn.rollback()
cur.close()
else:
# To take effect, changes need be committed to the database
conn.commit()
def run_query(
sql_query: str,
conn: psycopg2.extensions.connection,
cur: psycopg2.extensions.cursor,
) -> None:
try:
cur.execute(sql_query)
except Exception as e:
print(f"{type(e).__name__}: {e}")
print(f"Query: {cur.query}")
conn.rollback()
cur.close()
else:
conn.commit()
def get_curr_rev_id(conn: psycopg2.extensions.connection) -> Union[str, None]:
curs = conn.cursor()
try:
curs.execute('SELECT ver FROM migro_ver')
return curs.fetchone()[0]
except psycopg2.ProgrammingError:
conn.rollback()
curs.execute(
'CREATE TABLE migro_ver (ver VARCHAR(12) PRIMARY KEY)')
conn.commit()
return None
except TypeError:
return None
finally:
curs.close()
def execute_query(
connection: psycopg2.extensions.connection,
query: str,
query_parameters: tuple = None
) -> Tuple[Optional[List[dict]], Optional[Dict[str, Any]]]:
"""
Functie om de database connecties uit connect_databases.py te gebruiken om informatie op te vragen of wijzigingen
te doen in de database.
Benodigde input is de database connectie van de bonvengenoemde functie, die in het voorbeeld in een dictionary staat
en een query op de in de functie ingevoerde database. Optioneel zijn andere parameters
die psycopg2 accepteerd, zie:
http://initd.org/psycopg/docs/usage.html
http://initd.org/psycopg/docs/cursor.html
"""
if connection.closed:
print(
f"Reconnecting closed connection to {connection.get_dsn_parameters()['dbname']}"
)
try:
# Following http://initd.org/psycopg/docs/faq.html#best-practices
# And https://stackoverflow.com/questions/21158033/query-from-postgresql-using-python-as-dictionary#21158697
with connection.cursor(cursor_factory=DictCursor) as cursor:
print(f'Executing query {query} with {query_parameters}')
cursor.execute(query, query_parameters)
try:
results = [dict(row) for row in cursor]
# no records have returned
except psycopg2.ProgrammingError:
results = []
connection.commit()
except Exception as e:
print(
f"Error: {e}, \ndatabase: {connection.get_dsn_parameters()['dbname']}, \nquery: {query}, \nparameters: "
f"{query_parameters}")
connection.rollback()
error = {
'response_json':
json.dumps({'error': {
'response_code': '400',
'reason': str(e)
}}),
'status_code':
400
}
return None, error
return results, None
def insert_data(query: str, conn: psycopg2.extensions.connection,
cur: psycopg2.extensions.cursor, df: pd.DataFrame,
page_size: int) -> None:
data_tuples = [tuple(row.to_numpy()) for index, row in df.iterrows()]
try:
psql_extras.execute_values(cur,
query,
data_tuples,
page_size=page_size)
print("Query:", cur.query)
except Exception as error:
print(f"{type(error).__name__}: {error}")
print("Query:", cur.query)
conn.rollback()
cur.close()
else:
conn.commit()
def main_ingest(df: pd.DataFrame,
table: str,
connection: psycopg2.extensions.connection,
chunk_size: int = 10000):
"""needs a table first"""
print(connection)
cursor = connection.cursor()
df = df.copy()
escaped = {
'\\': '\\\\',
'\n': r'\n',
'\r': r'\r',
'\t': r'\t',
}
for col in df.columns:
if df.dtypes[col] == 'object':
for v, e in escaped.items():
df[col] = df[col].apply(lambda x: x.replace(v, '') if (
x is not None) and (isinstance(x, str)) else x)
try:
for i in tqdm(range(0, df.shape[0], chunk_size)):
f = StringIO()
chunk = df.iloc[i:(i + chunk_size)]
chunk.to_csv(f,
index=False,
header=False,
sep='\t',
na_rep='\\N',
quoting=None)
f.seek(0)
cursor.copy_from(f,
f'"{table}"',
columns=[f'"{i}"' for i in df.columns])
connection.commit()
except psycopg2.Error as e:
print(e)
connection.rollback()
cursor.close()
def update_employees_in_bd(connection: psycopg2.extensions.connection,
data: list[Person]) -> bool:
try:
cursor = connection.cursor()
sql_get_employees_id = 'select id from employees'
cursor.execute(sql_get_employees_id)
ids_in_db = tuple(row[0] for row in cursor.fetchall())
cursor.close()
for person in data:
if person.id not in ids_in_db:
add_employee_to_db(connection, person)
except (Exception, Error) as error:
print('Ошибка при обновлении базы:', error)
connection.rollback()
return False
finally:
print('База обовлена.')
return True