Python Connection.commit方法代码示例

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]
    def _purge_old_search_metadata_communities(self):
        """
        Cleans up all SearchCommunity and MetadataCommunity stuff in dispersy database.
        """
        db_path = os.path.join(self.session.get_state_dir(), u"sqlite", u"dispersy.db")
        if not os.path.isfile(db_path):
            return

        communities_to_delete = (u"SearchCommunity", u"MetadataCommunity", u"TunnelCommunity")

        connection = Connection(db_path)
        cursor = connection.cursor()

        for community in communities_to_delete:
            try:
                result = list(cursor.execute(u"SELECT id FROM community WHERE classification == ?;", (community,)))

                for community_id, in result:
                    cursor.execute(u"DELETE FROM community WHERE id == ?;", (community_id,))
                    cursor.execute(u"DELETE FROM meta_message WHERE community == ?;", (community_id,))
                    cursor.execute(u"DELETE FROM sync WHERE community == ?;", (community_id,))
            except StopIteration:
                continue

        cursor.close()
        connection.commit()
        connection.close()

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]
    def _update_dispersy(self):
        """
        Cleans up all SearchCommunity and MetadataCommunity stuff in dispersy database.
        """
        db_path = os.path.join(self.session.get_state_dir(), u"sqlite", u"dispersy.db")
        if not os.path.isfile(db_path):
            return

        communities_to_delete = (u"SearchCommunity", u"MetadataCommunity")

        connection = Connection(db_path)
        cursor = connection.cursor()

        data_updated = False
        for community in communities_to_delete:
            try:
                result = list(cursor.execute(u"SELECT id FROM community WHERE classification == ?", (community,)))

                for community_id, in result:
                    self._logger.info(u"deleting all data for community %s...", community_id)
                    cursor.execute(u"DELETE FROM community WHERE id == ?", (community_id,))
                    cursor.execute(u"DELETE FROM meta_message WHERE community == ?", (community_id,))
                    cursor.execute(u"DELETE FROM sync WHERE community == ?", (community_id,))
                    data_updated = True
            except StopIteration:
                continue

        if data_updated:
            connection.commit()
        cursor.close()
        connection.close()

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]
def parse_and_import_cards(
    input_file: pathlib.Path, sql_connection: sqlite3.Connection
) -> None:
    """
    Parse the JSON cards and input them into the database
    :param input_file: AllSets.json file
    :param sql_connection: Database connection
    """
    LOGGER.info("Loading JSON into memory")
    json_data = json.load(input_file.open("r"))

    LOGGER.info("Building sets")
    for set_code, set_data in json_data.items():
        # Handle set insertion
        LOGGER.info("Inserting set row for {}".format(set_code))
        set_insert_values = handle_set_row_insertion(set_data)
        sql_dict_insert(set_insert_values, "sets", sql_connection)

        for card in set_data.get("cards"):
            LOGGER.debug("Inserting card row for {}".format(card.get("name")))
            card_attr: Dict[str, Any] = handle_card_row_insertion(card, set_code)
            sql_insert_all_card_fields(card_attr, sql_connection)

        for token in set_data.get("tokens"):
            LOGGER.debug("Inserting token row for {}".format(token.get("name")))
            token_attr = handle_token_row_insertion(token, set_code)
            sql_dict_insert(token_attr, "tokens", sql_connection)

    sql_connection.commit()

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]
class Graph(object):
    """
    Initializes a new Graph object.

    :param uri: The URI of the SQLite db.
    :param graphs: Graphs to create.
    """
    def __init__(self, uri, graphs=()):
        self.uri = uri
        self.db = Connection(database=uri)
        self.setup_sql(graphs)

    def setup_sql(self, graphs):
        """
        Sets up the SQL tables for the graph object,
        and creates indexes as well.

        :param graphs: The graphs to create.
        """
        with closing(self.db.cursor()) as cursor:
            for table in graphs:
                cursor.execute(SQL.CREATE_TABLE % (table))
                for index in SQL.INDEXES:
                    cursor.execute(index % (table))
            self.db.commit()

    def close(self):
        """
        Close the SQLite connection.
        """
        self.db.close()

    __del__ = close

    def __contains__(self, edge):
        """
        Checks if an edge exists within the database
        with the given source and destination nodes.

        :param edge: The edge to query.
        """
        with closing(self.db.cursor()) as cursor:
            cursor.execute(*SQL.select_one(edge.src, edge.rel, edge.dst))
            return bool(cursor.fetchall())

    def find(self, edge_query):
        """
        Returns a Query object that acts on the graph.
        """
        return Query(self.db)(edge_query)

    def transaction(self):
        """
        Returns a Transaction object. All modifying
        operations, i.e. ``store``, ``delete`` must
        then be performed on the transaction object.
        """
        return Transaction(self.db)

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]
def insert(db: sqlite3.Connection, t):
    id = int(t['id_str'])
    created_at = int(timestamp_from_id(id))
    cur = db.cursor()
    cur.execute(
        """
        INSERT INTO fav_tweets (`id`, `tweet`, `created_at`)
        VALUES (?, ?, ?)
        ON CONFLICT (`id`) DO NOTHING
        """,
        (id, json.dumps(t), created_at)
    )
    db.commit()
    cur.close()

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]
def delete_friend(ds_connection: sqlite3.Connection, id: str) -> dict:
    """
    Delete a given entry from the friends table in a given SQLite connection.

    Args:
        ds_connection (sqllite3.Connection): An active connection to a
            sqllite datastore containing a friends table.
        id (str): An `id` value which will be used to find a specific
            datastore row to delete.
    """
    cursor = ds_connection.execute("DELETE  " "from friends where lower(id) = ?", [id.lower()])

    if not cursor.rowcount:
        raise ValueError()

    ds_connection.commit()

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]
def add_friend(ds_connection: sqlite3.Connection, entry_data: dict):
    """
    Create a new row in the friends table.

    Args:
        ds_connection (sqllite3.Connection): An active connection to a
            sqllite datastore containing a friends table.
        entry_data (dict): The data needed to created a new entry.
    """
    ds_connection.execute(
        "insert into friends (id, first_name, last_name, telephone, email, notes) "
        "values (?, ?, ?, ?, ?, ?)",
        [entry_data['id'],
         entry_data['firstName'],
         entry_data['lastName'],
         entry_data['telephone'],
         entry_data['email'],
         entry_data['notes']])
    ds_connection.commit()

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]
def fully_update_friend(ds_connection: sqlite3.Connection, entry_data: dict):
    """
    Update all aspects of given row in the friends table.

    Args:
        ds_connection (sqllite3.Connection): An active connection to a
            sqllite datastore containing a friends table.
        entry_data (dict): The data needed to update a given entry.  The
            `id` value of this dictionary is used to identify the entry
            to update.
    """
    ds_connection.execute(
        "UPDATE friends "
        "SET id=?, first_name=?, last_name=?, telephone=?, email=?, notes=? "
        "WHERE lower(id) = ?",
        [entry_data['id'],
         entry_data['firstName'],
         entry_data['lastName'],
         entry_data['telephone'],
         entry_data['email'],
         entry_data['notes'],
         entry_data['id'].lower()])
    ds_connection.commit()

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]
class SimpleBot(Client):
    def on_connected(self):
        self.init_db()

        self.authorized_users = list()
        self.command_prefix = "*"

        for channel in self.config['channels']:
            self.join(channel)

        self.aliases = {
            "telka": ["телка", "телочка"]
        }

    def on_disconnected(self):
        if self.db:
            self.db.close()

    def init_db(self):
        self.db = Connection(self.config['database'])
        self.db.execute("""
            CREATE TABLE IF NOT EXISTS
            message_log (
                id INTEGER PRIMARY KEY ASC,
                channel TEXT,
                nick TEXT,
                ident TEXT,
                host TEXT,
                message TEXT,
                date INTEGER
            )
        """)
        self.db.execute("""
            CREATE TABLE IF NOT EXISTS
            social_telki (
              id INTEGER PRIMARY KEY ASC,
              rating INTEGER,
              displayed_times INNTEGER,
              url TEXT,
              who_added TEXT,
              date_added INTEGER
            )
        """)
        def dict_factory(cursor, row):
            d = {}
            for idx, col in enumerate(cursor.description):
                d[col[0]] = row[idx]
            return d

        self.db.row_factory = dict_factory

    def get_unix_timestamp(self):
        return int(time.mktime(datetime.datetime.now().timetuple()))

    def log_message(self, nick_name, ident, host_name, message, channel):
        self.db.execute("""
        INSERT INTO message_log (channel, nick, ident, host, message, date)
        VALUES (?,?,?,?,?,?)
        """, (channel, nick_name, ident, host_name, message, self.get_unix_timestamp()))
        self.db.commit()

    def on_private(self, nick, ident, host, message):
        print u"pm from:%s: %s" % (nick, message)

    def on_channel(self, nick, ident, host, message, channel):
        print u"on %s from %s: %s" % (channel, nick, message)


    def on_privmsg(self, nick, ident, host, params, trailing):

        channel = None
        message = trailing

        if params == self.config['nick']:
            self.on_private(nick, ident, host, message)
        else:
            channel = params.decode('utf-8')
            self.on_channel(nick, ident, host, message, channel)

        self.log_message(nick.decode('utf-8'), ident.decode('utf-8'), host.decode('utf-8'), message.decode('utf-8'), channel)

        if message.startswith(self.command_prefix):
            self.handle_command(nick, ident, host, message[len(self.command_prefix):], channel)

    def on_nick(self, old_nick, ident, host, params, new_nick):
        if old_nick == self.nick_name:
            self.print_debug("Yay! My name changed to: %s" % new_nick)
            self.nick_name = new_nick

    def is_authorized(self, nick, ident, host):
        for login_data in self.authorized_users:
            if login_data[0] == nick and login_data[1] == ident and login_data[2] == host:
                return True
        return False

    def authorize(self, nick, ident, host, password):
        for bot_oper in self.config['allowed_ops']:
            if bot_oper['nick'] == nick and bot_oper['password'] == password:
                self.authorized_users.append((nick, ident, host))
                return True
#.........这里部分代码省略.........

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]
class Database(object):

    __metaclass__ = ABCMeta

    def __init__(self, file_path):
        """
        Initialize a new Database instance.

        @param file_path: the path to the database file.
        @type file_path: unicode
        """
        assert isinstance(file_path, unicode)
        logger.debug("loading database [%s]", file_path)
        self._file_path = file_path

        # _CONNECTION, _CURSOR, AND _DATABASE_VERSION are set during open(...)
        self._connection = None
        self._cursor = None
        self._database_version = 0

        # _commit_callbacks contains a list with functions that are called on each database commit
        self._commit_callbacks = []

        # Database.commit() is enabled when _pending_commits == 0.  Database.commit() is disabled
        # when _pending_commits > 0.  A commit is required when _pending_commits > 1.
        self._pending_commits = 0

        if __debug__:
            self._debug_thread_ident = 0

    def open(self, initial_statements=True, prepare_visioning=True):
        assert self._cursor is None, "Database.open() has already been called"
        assert self._connection is None, "Database.open() has already been called"
        if __debug__:
            self._debug_thread_ident = thread.get_ident()
        logger.debug("open database [%s]", self._file_path)
        self._connect()
        if initial_statements:
            self._initial_statements()
        if prepare_visioning:
            self._prepare_version()
        return True

    def close(self, commit=True):
        assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
        assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
        if commit:
            self.commit(exiting=True)
        logger.debug("close database [%s]", self._file_path)
        self._cursor.close()
        self._cursor = None
        self._connection.close()
        self._connection = None
        return True

    def _connect(self):
        self._connection = Connection(self._file_path)
        self._cursor = self._connection.cursor()

    def _initial_statements(self):
        assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
        assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"

        # collect current database configuration
        page_size = int(next(self._cursor.execute(u"PRAGMA page_size"))[0])
        journal_mode = unicode(next(self._cursor.execute(u"PRAGMA journal_mode"))[0]).upper()
        synchronous = unicode(next(self._cursor.execute(u"PRAGMA synchronous"))[0]).upper()

        #
        # PRAGMA page_size = bytes;
        # http://www.sqlite.org/pragma.html#pragma_page_size
        # Note that changing page_size has no effect unless performed on a new database or followed
        # directly by VACUUM.  Since we do not want the cost of VACUUM every time we load a
        # database, existing databases must be upgraded.
        #
        if page_size < 8192:
            logger.debug("PRAGMA page_size = 8192 (previously: %s) [%s]", page_size, self._file_path)

            # it is not possible to change page_size when WAL is enabled
            if journal_mode == u"WAL":
                self._cursor.executescript(u"PRAGMA journal_mode = DELETE")
                journal_mode = u"DELETE"
            self._cursor.execute(u"PRAGMA page_size = 8192")
            self._cursor.execute(u"VACUUM")
            page_size = 8192

        else:
            logger.debug("PRAGMA page_size = %s (no change) [%s]", page_size, self._file_path)

        #
        # PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF
        # http://www.sqlite.org/pragma.html#pragma_page_size
        #
        if not (journal_mode == u"WAL" or self._file_path == u":memory:"):
            logger.debug("PRAGMA journal_mode = WAL (previously: %s) [%s]", journal_mode, self._file_path)
            self._cursor.execute(u"PRAGMA journal_mode = WAL")

        else:
            logger.debug("PRAGMA journal_mode = %s (no change) [%s]", journal_mode, self._file_path)

#.........这里部分代码省略.........

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]

#.........这里部分代码省略.........
        if db_version == 12:
            # Added 'hunted' table
            c.execute("""CREATE TABLE hunted_list (
                name TEXT,
                is_guild BOOLEAN DEFAULT 0,
                server_id INTEGER
            );""")
            db_version += 1
        if db_version == 13:
            # Renamed table hunted_list to watched_list and related server properties
            c.execute("ALTER TABLE hunted_list RENAME TO watched_list")
            c.execute("UPDATE server_properties SET name = 'watched_channel' WHERE name LIKE 'hunted_channel'")
            c.execute("UPDATE server_properties SET name = 'watched_message' WHERE name LIKE 'hunted_message'")
            db_version += 1
        if db_version == 14:
            c.execute("""CREATE TABLE ignored_channels (
                server_id INTEGER,
                channel_id INTEGER
            );""")
            db_version += 1
        if db_version == 15:
            c.execute("""CREATE TABLE highscores (
                rank INTEGER,
                category TEXT,
                world TEXT,
                name TEXT,
                vocation TEXT,
                value INTEGER
            );""")
            c.execute("""CREATE TABLE highscores_times (
                world TEXT,
                last_scan INTEGER
            );""")
            db_version += 1
        if db_version == 16:
            c.execute("ALTER table highscores_times ADD category TEXT")
            db_version += 1
        if db_version == 17:
            # Cleaning up unused columns and renaming columns
            c.execute("""CREATE TABLE chars_temp(
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                name TEXT,
                level INTEGER DEFAULT -1,
                vocation TEXT,
                world TEXT,
                guild TEXT
            );""")
            c.execute("INSERT INTO chars_temp SELECT id, user_id, name, last_level, vocation, world, guild FROM chars")
            c.execute("DROP TABLE chars")
            c.execute("ALTER table chars_temp RENAME TO chars")
            c.execute("DROP TABLE IF EXISTS user_servers")
            c.execute("""CREATE TABLE users_temp(
                id INTEGER NOT NULL,
                name TEXT,
                PRIMARY KEY(id)
            );""")
            c.execute("INSERT INTO users_temp SELECT id, name FROM users")
            c.execute("DROP TABLE users")
            c.execute("ALTER table users_temp RENAME TO users")
            db_version += 1
        if db_version == 18:
            # Adding event participants
            c.execute("ALTER TABLE events ADD joinable INTEGER DEFAULT 1")
            c.execute("ALTER TABLE events ADD slots INTEGER DEFAULT 0")
            c.execute("""CREATE TABLE event_participants(
                event_id INTEGER NOT NULL,
                char_id INTEGER NOT NULL
            );""")
            db_version += 1
        if db_version == 19:
            # Adding reason and author to watched-list
            c.execute("ALTER TABLE watched_list ADD reason TEXT")
            c.execute("ALTER TABLE watched_list ADD author INTEGER")
            c.execute("ALTER TABLE watched_list ADD added INTEGER")
            db_version += 1
        if db_version == 20:
            # Joinable ranks
            c.execute("""CREATE TABLE joinable_roles(
                server_id INTEGER NOT NULL,
                role_id INTEGER NOT NULL
            );""")
            db_version += 1
        if db_version == 21:
            # Autoroles
            c.execute("""CREATE TABLE auto_roles(
                server_id INTEGER NOT NULL,
                role_id INTEGER NOT NULL,
                guild TEXT NOT NULL
            );""")
            db_version += 1
        log.info("\tUpdated database to version {0}".format(db_version))
        c.execute("UPDATE db_info SET value = ? WHERE key LIKE 'version'", (db_version,))
        return True
    except Exception as e:
        log.error(f"\tError reading sqlite database: {e}")
        return False
    finally:
        c.close()
        conn.commit()

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import commit [as 别名]

#.........这里部分代码省略.........

    # Build legalities table
    cursor.execute(
        "CREATE TABLE `legalities` (" "uuid TEXT," "format TEXT," "status TEXT" ")"
    )

    # Build ruling table
    cursor.execute("CREATE TABLE `rulings` (" "uuid TEXT," "date TEXT," "text TEXT" ")")

    # Build cards table
    cursor.execute(
        "CREATE TABLE `cards` ("
        "artist TEXT,"
        "borderColor TEXT,"
        "colorIdentity TEXT,"
        "colorIndicator TEXT,"
        "colors TEXT,"
        "convertedManaCost FLOAT,"
        "duelDeck TEXT,"
        "faceConvertedManaCost FLOAT,"
        "flavorText TEXT,"
        "frameEffect TEXT,"
        "frameVersion TEXT,"
        "hand TEXT,"
        "hasFoil INTEGER NOT NULL DEFAULT 0,"
        "hasNonFoil INTEGER NOT NULL DEFAULT 0,"
        "isAlternative INTEGER NOT NULL DEFAULT 0,"
        "isOnlineOnly INTEGER NOT NULL DEFAULT 0,"
        "isOversized INTEGER NOT NULL DEFAULT 0,"
        "isReserved INTEGER NOT NULL DEFAULT 0,"
        "isStarter INTEGER NOT NULL DEFAULT 0,"
        "isTimeshifted INTEGER NOT NULL DEFAULT 0,"
        "layout TEXT,"
        "life TEXT,"
        "loyalty TEXT,"
        "manaCost TEXT,"
        "mcmName TEXT DEFAULT NULL,"
        "mcmId INTEGER DEFAULT 0,"
        "mcmMetaId INTEGER DEFAULT 0,"
        "multiverseId INTEGER,"
        "name TEXT,"
        "names TEXT,"
        "number TEXT,"
        "originalText TEXT,"
        "originalType TEXT,"
        "printings TEXT,"
        "power TEXT,"
        "purchaseUrls TEXT,"
        "rarity TEXT,"
        "scryfallId TEXT,"
        "scryfallOracleId TEXT,"
        "scryfallIllustrationId TEXT,"
        "setCode TEXT,"
        "side TEXT,"
        "subtypes TEXT,"
        "supertypes TEXT,"
        "tcgplayerProductId INTEGER,"
        "tcgplayerPurchaseUrl TEXT,"
        "text TEXT,"
        "toughness TEXT,"
        "type TEXT,"
        "types TEXT,"
        "uuid TEXT(36) PRIMARY KEY,"
        "uuidV421 TEXT,"
        "variations TEXT,"
        "watermark TEXT"
        ")"
    )

    # Build tokens table
    cursor.execute(
        "CREATE TABLE `tokens` ("
        "artist TEXT,"
        "borderColor TEXT,"
        "colorIdentity TEXT,"
        "colorIndicator TEXT,"
        "colors TEXT,"
        "isOnlineOnly INTEGER NOT NULL DEFAULT 0,"
        "layout TEXT,"
        "loyalty TEXT,"
        "name TEXT,"
        "number TEXT,"
        "power TEXT,"
        "reverseRelated TEXT,"
        "scryfallId TEXT,"
        "scryfallOracleId TEXT,"
        "scryfallIllustrationId TEXT,"
        "setCode TEXT,"
        "side TEXT,"
        "text TEXT,"
        "toughness TEXT,"
        "type TEXT,"
        "uuid TEXT,"
        "uuidV421 TEXT,"
        "watermark TEXT"
        ")"
    )

    # Execute the commands
    sql_connection.commit()