Python sqlite3.Error方法代碼示例

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def _DetectApplicationUsageTable(self, conn):
    """Detect whether the application usage table exists.

    Args:
      conn: sqlite3.Connection object
    Returns:
      True if the table exists, False if not.
    Raises:
      sqlite3.Error: if error occurs
    """
    try:
      conn.execute(APPLICATION_USAGE_TABLE_DETECT)
      exists = True
    except sqlite3.OperationalError, e:
      if e.args[0].startswith('no such table'):
        exists = False
      else:
        raise 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def VerifyDatabase(self, fix=False):
    """Verify database integrity."""
    conn = self._Connect()
    try:
      q = conn.execute(APPLICATION_USAGE_TABLE_SELECT)
      unused_rows = q.fetchall()
      ok = True
    except sqlite3.Error:
      ok = False

    if not ok:
      if fix:
        print 'Recreating database.'
        print 'Recovered %d rows.' % self._RecreateDatabase()
      else:
        print 'Database is malformed.  Run with --fix to attempt repair.'
    else:
      print 'Database is OK.' 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def create_table_permissions(db_cursor) -> bool:
        table_query = """
            CREATE TABLE IF NOT EXISTS permissions (
                user_id INTEGER UNIQUE PRIMARY KEY,
                level INTEGER NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(user_id),
                FOREIGN KEY (level) REFERENCES permission_levels(level_id)
            );
        """
        try:
            db_cursor.execute(table_query)
            return True
        except Error as err:
            dprint(err)
            log(WARNING, f'There was an error creating the permissions table in the user_privileges database: {err}')
            return False 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def create_table_commands(db_cursor) -> bool:
        table_query = """
            CREATE TABLE IF NOT EXISTS commands (
                command_id INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT,
                plugin_id INTEGER NOT NULL,
                name TEXT UNIQUE NOT NULL,
                level INTEGER NOT NULL,
                FOREIGN KEY (plugin_id) REFERENCES plugins(plugin_id),
                FOREIGN KEY (level) REFERENCES permission_levels(level_id)
            );
        """
        try:
            db_cursor.execute(table_query)
            return True
        except Error as err:
            dprint(err)
            return False 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def insert_new_plugin(db_conn, plugin_name, ignore_file_save=False) -> bool:
        table_query = f"""
            INSERT INTO plugins(name)
            VALUES (
            ?
            );
        """
        try:
            db_conn.cursor().execute(table_query, (plugin_name, ))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Inserted new plugin into the database: {plugin_name}",
                       origin=L_DATABASE)
                log(INFO, f"Inserted new plugin into the database: {plugin_name}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            if 'UNIQUE' not in str(err):
                dprint(err)
            return False 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def insert_new_command(db_conn, plugin_name, command_name, permission_level, ignore_file_save=False) -> bool:
        table_query = f"""
            INSERT INTO commands(plugin_id, name, level)
            VALUES (
            (SELECT plugins.plugin_id FROM plugins WHERE plugins.name = ? LIMIT 1),
            ?,
            ?
            );
        """
        try:
            db_conn.cursor().execute(table_query, (plugin_name, command_name, permission_level))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Inserted new command into the database: {plugin_name}-{command_name}-{permission_level}", origin=L_DATABASE)
                log(INFO, f"Inserted new command into the database: {plugin_name}-{command_name}-{permission_level}", origin=L_DATABASE)
                return True
            return False
        except Error as err:
            if 'UNIQUE' not in str(err):
                dprint(err)
            return False 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def insert_new_plugins_help(db_conn, plugin_name, help_text, ignore_file_save=False) -> bool:
        table_query = f"""
            INSERT INTO plugins_help(plugin_id, help_text)
            VALUES (
            (SELECT plugins.plugin_id FROM plugins WHERE plugins.name = ? LIMIT 1),
            ?
            );
        """
        try:
            db_conn.cursor().execute(table_query, (plugin_name, help_text))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Inserted new plugin help data into the database: {plugin_name}",
                       origin=L_DATABASE)
                log(INFO, f"Inserted new plugin help data into the database: {plugin_name}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            if 'UNIQUE' not in str(err):
                dprint(err)
            return False 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def insert_new_alias(db_conn, alias_name, commands, ignore_file_save=False) -> bool:
        table_query = f"""
                INSERT INTO aliases(name, alias)
                VALUES (
                ?,
                ?
                );
            """
        try:
            db_conn.cursor().execute(table_query, (alias_name, commands))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Inserted new alias into the database: {alias_name}",
                       origin=L_DATABASE)
                log(INFO, f"Inserted new alias into the database: {alias_name}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            if 'UNIQUE' not in str(err):
                dprint(err)
            return False 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def insert_new_permission_level(db_conn, level_id: int, level_type: str, ignore_file_save=False) -> bool:
        table_query = f"""
            INSERT INTO permission_levels(level_id, level_type)
            VALUES (
            ?, 
            ?
            );
        """
        try:
            db_conn.cursor().execute(table_query, (level_id, level_type))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Inserted new permission level into the database: {level_id}-{level_type}",
                       origin=L_DATABASE)
                log(INFO, f"Inserted new permission level into the database: {level_id}-{level_type}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            dprint(err)
            return False 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def insert_new_permission(db_conn, username: str, permission_level: int, ignore_file_save=False) -> bool:
        table_query = f"""
            INSERT INTO permissions(user_id, level)
            VALUES (
            (SELECT users.user_id FROM users WHERE users.name = ? LIMIT 1), 
            (SELECT permission_levels.level_id FROM permission_levels WHERE permission_levels.level_id = ? LIMIT 1)
            );
        """
        try:
            db_conn.cursor().execute(table_query, (username, permission_level))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Inserted new permission into the database: {username}-{permission_level}",
                       origin=L_DATABASE)
                log(INFO, f"Inserted new permission into the database: {username}-{permission_level}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            dprint(err)
            return False 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def delete_all_aliases(db_conn, ignore_file_save=False):
        # Output Format: [(name, alias), (name, alias), ...]
        delete_aliases_query = f"""
            DELETE FROM aliases;
        """
        try:
            db_conn.cursor().execute(delete_aliases_query)
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount > 0:
                dprint(f"Deleted all alias in the database",
                       origin=L_DATABASE)
                log(INFO, f"Deleted all alias in the database",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            dprint(err)
            return False 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def get_all_user_data(db_cursor):
        # Output Format: [(user, level), (user, level), ...]
        get_plugin_query = f"""SELECT users.user_id, users.name FROM users"""
        try:
            db_cursor.execute(get_plugin_query)
            result_list = []
            result_row = db_cursor.fetchall()
            if result_row is None:
                return None
            for i, item in enumerate(result_row):
                user_data = GetDB.get_user_data(db_cursor=db_cursor, user_id=list(result_row)[i][0])
                result_list.append((user_data['name'], user_data['level']))
            return result_list
        except Error as err:
            dprint(err)
            return None 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def get_all_aliases(db_cursor):
        # Output Format: [(name, alias), (name, alias), ...]
        get_plugin_query = f"""SELECT aliases.alias_id, aliases.name FROM aliases"""
        try:
            db_cursor.execute(get_plugin_query)
            result_list = []
            result_row = db_cursor.fetchall()
            if result_row is None:
                return None
            for i, item in enumerate(result_row):
                alias_data = GetDB.get_alias(db_cursor=db_cursor, alias_id=list(result_row)[i][0])
                result_list.append((alias_data['name'], alias_data['alias']))
            return result_list
        except Error as err:
            dprint(err)
            return None 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def get_all_plugin_help(db_cursor):
        # Output Format: List[str]
        get_plugin_help_query = f"""
                            SELECT plugins.name, plugins_help.help_text
                            FROM plugins, plugins_help
                            WHERE plugins.plugin_id = plugins_help.plugin_id;
                        """
        try:
            db_cursor.execute(get_plugin_help_query)
            result_dict = {}
            result_row = db_cursor.fetchall()
            if result_row is None:
                return None
            for i, item in enumerate(result_row):
                result_dict[list(result_row)[i][0]] = GetDB.get_plugin_data(db_cursor=db_cursor,
                                                                            plugin_name=list(result_row)[i][0])
            return result_dict
        except Error as err:
            dprint(err)
            return None 

# 需要導入模塊: import sqlite3 [as 別名]
# 或者: from sqlite3 import Error [as 別名]
def get_all_plugin_data(db_cursor):
        # Output Format: { plugin_name: [(command_name, level), (command_name, level)], plugin_name: ...}
        get_plugin_query = f"""
                    SELECT plugins.name, commands.name, commands.level
                    FROM plugins, commands, permission_levels
                    WHERE plugins.plugin_id = commands.plugin_id
                    AND commands.level = permission_levels.level_id
                """
        try:
            db_cursor.execute(get_plugin_query)
            result_dict = {}
            result_row = db_cursor.fetchall()
            if result_row is None:
                return None
            for i, item in enumerate(result_row):
                result_dict[list(result_row)[i][0]] = GetDB.get_plugin_data(db_cursor=db_cursor,
                                                                            plugin_name=list(result_row)[i][0])
            return result_dict
        except Error as err:
            dprint(err)
            return None