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