def test_lookup(self):
from psycopg2 import errors
self.assertIs(errors.lookup('42P01'), errors.UndefinedTable)
with self.assertRaises(KeyError):
errors.lookup('XXXXX')
def test_lookup(self):
from psycopg2 import errors
self.assertIs(errors.lookup('42P01'), errors.UndefinedTable)
with self.assertRaises(KeyError):
errors.lookup('XXXXX')
def create_tables(conn, cursor):
LOG.info("Creating tables")
with open('queries/CreateTestTable.sql') as create_file:
student_table_create = create_file.read().format(
table_name=TEST_TABLE_NAME)
# print(student_table_create)
with open('queries/CreateLastRowTable.sql') as create_file:
row_table_create = create_file.read().format(table_name=LAST_ROW_TABLE)
# print(subject_table_create)
try:
cursor.execute(student_table_create)
LOG.info(f"Table {TEST_TABLE_NAME} created successfully")
except lookup(
"42P07"
): # psycopg2.errors.DuplicateTable: relation "studenttable" already exists
LOG.info(f"Table {TEST_TABLE_NAME} already exists")
conn.commit()
try:
cursor.execute(row_table_create)
cursor.execute(f"INSERT INTO {LAST_ROW_TABLE} VALUES (0, 0, 0)"
) # set default values
LOG.info(f"Table {LAST_ROW_TABLE} created successfully")
except lookup(
"42P07"
): # psycopg2.errors.DuplicateTable: relation "subjecttable" already exists
LOG.info(f"Table {LAST_ROW_TABLE} already exists")
conn.commit()
def start_watching(watcher_id: int, target_id: int):
res = 'Возникли непредвиденные ошибки в архиве.'
try:
_insert_query('surveillance', watcher_id=watcher_id, target_id=target_id)
res = f'<@{watcher_id}> ведет наблюдение за <@{target_id}>'
except lookup(er_code.CHECK_VIOLATION) as e:
res = 'Следить за самим собой обязанность каждого Коммуниста.'
except lookup(er_code.UNIQUE_VIOLATION) as e:
res = f'<@{watcher_id}> уже ведет наблюдение за <@{target_id}>'
except Exception as e:
print(e)
finally:
return res
def db_try_create_table(table_name: str, column_definitions: list) -> bool:
column_list = db_get_column_list(column_definitions)
query_create = f'CREATE TABLE {table_name} ({column_list});'
success = False
connected = db_connect()
if connected:
cursor = db_get_cursor()
if cursor is not None:
try:
db_execute(query_create, cursor)
success = db_try_commit()
except db_error.lookup('42P07'): # DuplicateTable
db_try_rollback()
success = True
except (Exception, psycopg2.DatabaseError) as error:
error_name = error.__class__.__name__
print(
f'[db_try_create_table] {error_name} while performing the query \'{query_create}\': {error}'
)
db_close_cursor(cursor)
db_disconnect()
else:
print('[db_try_create_table] could not get cursor')
db_disconnect()
else:
print('[db_try_create_table] could not connect to db')
return success
def main():
global db
sys.excepthook = handle_exception
app = QApplication(sys.argv)
login, password, ok = Authorization.ask_login_password()
if not ok:
sys.exit(1)
try:
db = Database(login, password)
except OperationalError as e:
QMessageBox.critical(None, 'Ошибка', 'Не удалось подключиться к базе')
print(e)
print(e.pgcode)
print(e.pgerror)
sys.exit(1)
try:
gui = GUI(db)
app.exec_()
except lookup('42501'):
QMessageBox.critical(None, 'Ошибка',
'Недостаточно прав для осуществления действия')
sys.exit(1)
db.close()
def add_toon(self, toon, family, toon_class, planner_url):
cursor = self.connection.cursor()
try:
cursor.execute(
"INSERT INTO \"toons\"(name, family, class, url, level, xp) "
"VALUES (\'{}\', \'{}\', \'{}\', \'{}\', {}, \'{}\')".format(
toon, family, toon_class, planner_url, 0, 0))
except errors.lookup(FOREIGN_KEY_VIOLATION):
raise
self.connection.commit()
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
try:
op.execute('CREATE EXTENSION pg_trgm')
# thanks to https://stackoverflow.com/a/58743364/1428034 !
except sa.exc.ProgrammingError as e:
if isinstance(e.orig, errors.lookup(DUPLICATE_OBJECT)):
print(">>> pg_trgm already loaded, ignore")
op.execute("Rollback")
op.create_index('note_pg_trgm_index', 'alias', ['note'], unique=False, postgresql_ops={'note': 'gin_trgm_ops'}, postgresql_using='gin')
def table_exists(client, table_name):
cursor = client.cursor()
try:
cursor.execute("SELECT exists(SELECT 1 from {0})".format(table_name))
client.commit()
cursor.close()
return True
except psql_errors.lookup("42P01"):
cursor.close()
client.rollback()
logger.debug('Table {0} does not exists'.format(table_name))
return False
def __run_class_migrate(self, migration: MigrationsDto):
if migration.command == 'all':
try:
self.__db.select("select count(*) from users")
return 1
except errors.lookup('42P01') as e:
pass
self.__ALL = True
for _, model in Models.list_models.items():
self.__db.migration(model.get_code())
else:
self.__db.migration(
Models.list_models.get(migration.command).get_code())
def read(self, query, objects):
query = Persistent.sanitize_query(query)
objects = Persistent.sanitize_objects(objects)
try:
self.cursor.execute(query, objects)
results = self.cursor.fetchall()
print("[" + str(datetime.datetime.today()) + "]" + query % objects)
return results
except lookup("25P02") as e:
self.cursor.execute("ROLLBACK")
self.connection.commit()
print("[" + str(datetime.datetime.today()) + "] ROLLBACK | " +
query % objects)
print(traceback.format_exc())
return []
def _make_database(self):
"""
Makes the database within postgres
"""
# Throwaway connection
conn = psycopg2.connect(user=self.USER,
password=self.PASSWORD,
host=self.HOST,
port=self.PORT,
dbname="postgres")
# Set autocommit isolation
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
try:
cursor.execute(
sql.SQL("CREATE DATABASE {}").format(
sql.Identifier(self.DB_NAME)))
except lookup(DUPLICATE_DATABASE):
# Allow a duplicate error!
pass
finally:
conn.close()
async def launch_input(func, configurator, need_admin,
message: discord.Message, words, **kwargs):
channel = message.channel
print(func)
print(words)
print(kwargs)
has_failed = True
error_msg = ""
try:
kwargs["is_admin"] = configurator.is_admin(message.author,
message.guild.id)
if func in need_admin and not kwargs["is_admin"]:
await Bot.unauthorized(message, **kwargs)
else:
await func(message, words, **kwargs)
print("has failed")
has_failed = False
print(has_failed)
except (IndexError, CommandException) as e:
await channel.send(
"Il semblerait que je ne puisse pas faire suivre votre requête !"
)
except InitializationException as e:
await channel.send("Votre demande ne suit pas le format attendu.")
except lookup("25P02") as e:
await channel.send("Une entrée existe déjà pour cet objet")
except Exception as e:
error_msg = str(e)
msg = "Erreur, l'administrateur et d'autres personnes ont été notifiées !"
await message.channel.send(msg)
if has_failed:
msg = "[" + message.guild.name + "][ERROR]"
error_msg += msg + "\n" + traceback.format_exc() + "\n"
error_msg += "caused by: '" + message.content + "'"
error_msg += "from '" + message.author.name + "'"
await configurator.warn_error(error_msg, message.guild.id)
def test_lookup(self):
self.assertIs(errors.lookup('42P01'), errors.UndefinedTable)
with self.assertRaises(KeyError):
errors.lookup('XXXXX')
from airflow.providers.amazon.aws.hooks.base_aws import AwsBaseHook
from airflow.providers.postgres.hooks.postgres import PostgresHook
from airflow.models import BaseOperator
from airflow.utils.decorators import apply_defaults
from dateutil import parser
from psycopg2 import errors
table_does_not_exist = errors.lookup('42P01')
class StageToRedshiftOperator(BaseOperator):
"""
Operator populates the staging table from the given S3 data buckets. Backfilling is supported so based on the execution time
"""
ui_color = '#358140'
COPY_SQL = """
COPY {}
FROM '{}'
ACCESS_KEY_ID '{}'
SECRET_ACCESS_KEY '{}'
REGION AS '{}'
FORMAT AS JSON '{}'
"""
template_fields = ("execution_date", )
@apply_defaults
def __init__(self,
redshift_conn_id='',
aws_credentials_id='',
def handle_exception(cls, exception, traceback):
if type(exception) == lookup('42501'):
QMessageBox.critical(None, 'Ошибка', 'Не удалось выполнить операцию')
db.conn.rollback()
else:
print(exception)
async def privatechannel(self, ctx, arg1=None, arg2=None, arg3=None):
global categoryname
if constants.channel_enable == "false":
await ctx.send(
f"{ctx.author.mention} this Function is not enabled by the bot hoster"
)
return
mycursor = self.database_connection.cursor()
if arg1 is None:
embed = discord.Embed(title="Create custom Channels")
embed.add_field(name="Functions for everyone:",
value="create, add, delete")
embed.add_field(name="Admin Only: ",
value="on, off, category, remove")
await ctx.send(embed=embed)
elif arg1 == "delete":
code2execute = "SELECT channelid FROM " + f'"{ctx.message.guild.id}"' + " WHERE memberid = " + f"'{ctx.message.author.id}'"
mycursor.execute(code2execute)
result = mycursor.fetchone()
self.database_connection.commit()
channelid = result[0]
channel = self.client.get_channel(channelid)
await channel.delete()
code2execute = "DELETE FROM " + f'"{ctx.message.guild.id}"' + " WHERE memberid = " + f"'{ctx.message.author.id}'"
mycursor.execute(code2execute)
self.database_connection.commit()
await ctx.send(f'"{channel.name}" was deleted!')
elif arg1 == "add":
code2execute = "SELECT channelid FROM " + f'"{ctx.message.guild.id}"' + " WHERE memberid = " + f"'{ctx.message.author.id}'"
mycursor.execute(code2execute)
result = mycursor.fetchone()
self.database_connection.commit()
channelid = result[0]
channel = self.client.get_channel(channelid)
converter = MemberConverter()
member = await converter.convert(ctx, arg2)
await channel.set_permissions(member,
connect=True,
speak=True,
use_voice_activation=True,
view_channel=True,
stream=True)
await ctx.send(f"{member.mention} can now access the Channel :)")
elif arg1 == "deny":
code2execute = "SELECT channelid FROM " + f'"{ctx.message.guild.id}"' + " WHERE memberid = " + f"'{ctx.message.author.id}'"
mycursor.execute(code2execute)
result = mycursor.fetchone()
channelid = result[0]
channel = self.client.get_channel(channelid)
converter = MemberConverter()
member = await converter.convert(ctx, arg2)
await channel.set_permissions(member, connect=False)
await ctx.send(f"{member.mention} can't access The Channel anymore"
)
elif arg1 == "on":
if ctx.message.author.guild_permissions.administrator:
code2execute = f"UPDATE servers SET state = true WHERE id = {ctx.guild.id}"
self.database_connection.commit()
mycursor.execute(code2execute)
self.database_connection.commit()
try:
code2execute = f'create table "{ctx.guild.id}"(memberid bigint not null constraint "{ctx.guild.id}_pkey" primary key,channelid bigint not null, creation_time timestamp not null)'
mycursor.execute(code2execute)
self.database_connection.commit()
except errors.lookup("42P07"):
pass
await ctx.send("Custom User Channels are now **activated**")
else:
raise MissingPermissions
elif arg1 == "off":
if ctx.message.author.guild_permissions.administrator:
code2execute = f"UPDATE servers SET state = false WHERE id = {ctx.guild.id}"
self.database_connection.commit()
mycursor.execute(code2execute)
self.database_connection.commit()
await ctx.send("Custom User Channels are now **disabled**")
else:
raise PermissionError
elif arg1 == "create":
code2execute = f"SELECT state FROM servers where id = {ctx.guild.id}"
mycursor.execute(code2execute)
result = mycursor.fetchone()
self.database_connection.commit()
if result[0] is True:
code2execute = f"SELECT * FROM " + f'"{ctx.guild.id}"' + " WHERE memberid = " + f"'{ctx.message.author.id}'"
mycursor.execute(code2execute)
result = mycursor.fetchone()
if result is not None:
await ctx.send(
f"{ctx.author.mention} you already have a Channel!")
else:
code2execute = f"SELECT name FROM categorynames WHERE serverid = {ctx.message.guild.id}"
try:
mycursor.execute(code2execute)
result = mycursor.fetchone()
hey = result[0]
except:
await ctx.send(
"The category where the Channels should be created is not set"
)
categoryname = result[0]
await ctx.send(
"Channel is being created (This might take some time)",
delete_after=5)
if arg2 is None:
channelnamefinal = ctx.message.author.name + "'s Channel"
else:
channelnamefinal = arg2
if arg3 is None:
maxusersfinal = 5
elif int(arg3) > 99:
await ctx.send(
f"{ctx.message.author.mention} you cant do more than 99 Members!",
delete_after=15)
maxusersfinal = 99
else:
maxusersfinal = arg3
if arg2 is None and arg3 is None:
await ctx.send(
f"{ctx.message.author.mention} btw you can use ur own Channel Name and Member Limit\n"
f"Example: {self.prefix}channel create nice 69")
guild = ctx.guild
category = discord.utils.get(ctx.guild.categories,
name=categoryname)
created_channel = await guild.create_voice_channel(
name=channelnamefinal,
user_limit=maxusersfinal,
category=category,
bitrate=guild.bitrate_limit)
channel = discord.utils.get(ctx.guild.channels,
id=created_channel.id)
await channel.set_permissions(ctx.message.author,
connect=True,
move_members=True,
speak=True,
mute_members=True,
use_voice_activation=True,
view_channel=True,
stream=True)
zeit = datetime.datetime.now()
zeit_srftime = zeit.strftime("%Y-%m-%d %H:%M:%S")
sql = "INSERT INTO " + f'"{ctx.guild.id}"' + " (memberid, channelid, creation_time) VALUES (%s, %s, %s) "
val = [(f"{ctx.message.author.id}",
f"{created_channel.id}", f"{zeit_srftime}")]
mycursor.executemany(sql, val)
self.database_connection.commit()
await ctx.send(
f'Channel "{channelnamefinal}" was successfully created!\n'
f"With {self.prefix}channel add @User you can give people access to your channel\n"
f"With {self.prefix}channel deny @User you can take away people access to your channel\n"
f"With {self.prefix}channel delete you can delete your channel!"
)
else:
await ctx.send(
f"This function is not enabled on this Discord.\n"
f"Please contact {ctx.guild.owner.mention} if you want to have it activated"
)
elif arg1 == "category":
if ctx.message.author.guild_permissions.administrator:
try:
self.database_connection.commit()
code2execute = f"INSERT INTO categorynames (serverid, name) VALUES ({ctx.guild.id}, '{arg2}')"
mycursor.execute(code2execute)
self.database_connection.commit()
except:
self.database_connection.commit()
code2execute = f"UPDATE categorynames SET name = '{arg2}' WHERE serverid = {ctx.guild.id}"
mycursor.execute(code2execute)
self.database_connection.commit()
await ctx.send(
f'The Category where custom user Channels will now be created will be "{arg2}"'
)
else:
raise PermissionError
elif arg1 == "remove":
if ctx.message.author.guild_permissions.administrator:
converter = MemberConverter()
member = await converter.convert(ctx, arg2)
code2execute = "SELECT channelid FROM " + f'"{ctx.message.guild.id}"' + " WHERE memberid = " + f"'{member.id}'"
mycursor.execute(code2execute)
result = mycursor.fetchone()
self.database_connection.commit()
channelid = result[0]
channel = self.client.get_channel(channelid)
await channel.delete()
code2execute = "DELETE FROM " + f'"{ctx.message.guild.id}"' + " WHERE memberid = " + f"'{member.id}'"
mycursor.execute(code2execute)
self.database_connection.commit()
await ctx.send(
f'"{channel.name}" from {member.mention} was removed!')
else:
raise PermissionError
elif arg1 == "setup":
if ctx.message.author.guild_permissions.administrator:
code2execute = f"UPDATE servers SET state = true WHERE id = {ctx.guild.id}"
self.database_connection.commit()
mycursor.execute(code2execute)
self.database_connection.commit()
try:
code2execute = f'create table "{ctx.guild.id}"(memberid bigint not null constraint "{ctx.guild.id}_pkey" primary key,channelid bigint not null, creation_time timestamp not null)'
mycursor.execute(code2execute)
self.database_connection.commit()
except errors.lookup("42P07"):
pass
await ctx.send("What is the Name of the Category?")
try:
categoryname = await self.client.wait_for("message",
timeout=15)
except asyncio.TimeoutError:
await ctx.send(
f"{ctx.author.mention} you needed too long to respond!"
)
return
try:
self.database_connection.commit()
code2execute = f"INSERT INTO categorynames (serverid, name) VALUES ({ctx.guild.id}, '{categoryname.content}')"
mycursor.execute(code2execute)
self.database_connection.commit()
except:
self.database_connection.commit()
code2execute = f"UPDATE categorynames SET name = '{categoryname.content}' WHERE serverid = {ctx.guild.id}"
mycursor.execute(code2execute)
self.database_connection.commit()
await ctx.send(
f'The Category where custom user Channels will be created is "{categoryname.content}"'
)
else:
raise PermissionError
else:
raise CommandNotFound
self.database_connection.commit()
def test_lookup(self):
self.assertIs(errors.lookup('42P01'), errors.UndefinedTable)
with self.assertRaises(KeyError):
errors.lookup('XXXXX')
def test_connection_exceptions_backwards_compatibility(self):
err = errors.lookup('08000')
# connection exceptions are classified as operational errors
self.assert_(issubclass(err, errors.OperationalError))
# previously these errors were classified only as DatabaseError
self.assert_(issubclass(err, errors.DatabaseError))
def test_2(self):
cursor = self.connection.cursor()
# 23503 is the code error for ForeignKeyViolation
self.assertRaises(errors.lookup('23503'),
function_example.broken_function, cursor)
cursor.close()
connection = psycopg2.connect(user='******', password=password, host='localhost', port=5432)
connection.close()
except psycopg2.OperationalError:
print('Server run in dockers container.')
host = 'host.docker.internal'
else:
host = 'localhost'
try:
connection = psycopg2.connect(user='******', password=password, host=host, port=5432)
connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = connection.cursor()
sql_create_database = cursor.execute('CREATE DATABASE users')
cursor.close()
connection.close()
except errors.lookup('42P04'):
print('The database has already been created.')
engine = create_engine(f'postgresql+psycopg2://postgres:{password}@{host}:5432/users')
session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
Base = declarative_base()
Base.query = session.query_property()
from app.models import *
Base.metadata.create_all(bind=engine)
@app.route('/users', methods=['GET'])
import logging
import os
import records
import sqlalchemy
from auth0.v2.management import Auth0
from . import myemail
import traceback # Just to show the full traceback
from psycopg2 import errors
InFailedSqlTransaction = errors.lookup('25P02')
UniqueViolation = errors.lookup('23505')
# importing module
# Create and configure logger
logging.basicConfig(filename='Logfile.log',
filemode='a',
format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
datefmt='%d-%b-%y %H:%M:%S')
# Creating an object
logger = logging.getLogger()
# Auth0 API Client
auth0_domain = os.environ['AUTH0_DOMAIN']
auth0_token = os.environ['AUTH0_JWT_V2_TOKEN']
auth0 = Auth0(auth0_domain, auth0_token)
# Database connection.
from server import create_app
from init_app import add_sl_domains
app = create_app()
app.config["TESTING"] = True
app.config["WTF_CSRF_ENABLED"] = False
app.config["SERVER_NAME"] = "sl.test"
# enable pg_trgm extension
with engine.connect() as conn:
try:
conn.execute("DROP EXTENSION if exists pg_trgm")
conn.execute("CREATE EXTENSION pg_trgm")
except sqlalchemy.exc.InternalError as e:
if isinstance(e.orig, errors.lookup(DEPENDENT_OBJECTS_STILL_EXIST)):
print(">>> pg_trgm can't be dropped, ignore")
conn.execute("Rollback")
Base.metadata.create_all(engine)
add_sl_domains()
@pytest.fixture
def flask_app():
yield app
@pytest.fixture
def flask_client():
