Python Connection.execute方法代码示例

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [as 别名]
 def _fetch_account(self, conn: sqlite3.Connection, account_id: int) -> Account:
     acc = conn.execute("SELECT * FROM Account WHERE id=?", (account_id,)).fetchone()
     priv_result = conn.execute("SELECT privilege FROM Privilege WHERE account=?", (account_id,)).fetchall()
     privileges = {pr["privilege"] for pr in priv_result}
     storydata_result = conn.execute("SELECT format, data FROM StoryData WHERE account=?", (account_id,)).fetchone()
     if storydata_result:
         if storydata_result["format"] == "json":
             storydata = json.loads(storydata_result["data"], encoding="utf-8")
         elif storydata_result["format"] == "serpent":
             storydata = serpent.loads(storydata_result["data"])
         else:
             raise ValueError("invalid storydata format in database: " + storydata_result["format"])
         if not isinstance(storydata, dict):
             raise TypeError("storydata should be a dict")
     else:
         storydata = {}
     stats_result = dict(conn.execute("SELECT * FROM CharStat WHERE account=?", (account_id,)).fetchone() or {})
     del stats_result["id"]
     del stats_result["account"]
     stats = base.Stats()
     for key, value in stats_result.items():
         if hasattr(stats, key):
             setattr(stats, key, value)
         else:
             raise AttributeError("stats doesn't have attribute: " + key)
     stats.set_stats_from_race()   # initialize static stats from races table
     return Account(acc["name"], acc["email"], acc["pw_hash"], acc["pw_salt"], privileges,
                    acc["created"], acc["logged_in"], bool(acc["banned"]), stats, storydata)

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [as 别名]
    def __update_pragma(connection: sqlite3.Connection, name: str, value: Any) -> None:
        """
        Updates PRAGMA value

        :param connection:  Database connection
        :param name:        Pragma's name
        :param value:       Pragma's value
        """
        connection.execute("PRAGMA {0} = {1}".format(name, str(value)))

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [as 别名]
 def _store_stats(self, conn: sqlite3.Connection, account_id: int, stats: base.Stats) -> None:
     columns = ["account"]
     values = [account_id]
     stat_vars = dict(vars(stats))
     for not_stored in ["bodytype", "language", "weight", "size"]:
         del stat_vars[not_stored]    # these are not stored, but always initialized from the races table
     for key, value in stat_vars.items():
         columns.append(key)
         values.append(value)
     sql = "INSERT INTO CharStat(" + ",".join(columns) + ") VALUES (" + ",".join('?' * len(columns)) + ")"
     conn.execute(sql, values)

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [as 别名]
def get_friend(ds_connection: sqlite3.Connection, id: str) -> dict:
    """
    Obtain a specific friend record and return a representation of it.

    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.

    Returns
        A JSON ready dictionary representing a specific
        row of the friends table.
    """
    cursor = ds_connection.execute(
        'select id, first_name, last_name, telephone, email, notes '
        'from friends where lower(id) = ?',
        [id.lower()])

    friend_row = cursor.fetchone()

    if friend_row:
        return {
            "id": friend_row[0],
            "first_name": friend_row[1],
            "last_name": friend_row[2],
            "telephone": friend_row[3],
            "email": friend_row[4],
            "notes": friend_row[5]}

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [as 别名]
def get_friends(ds_connection: sqlite3.Connection) -> dict:
    """
    Return a representation of all rows in the friends table.

    Args:
        ds_connection (sqllite3.Connection): An active connection to a
            sqllite datastore containing a friends table.

    Returns
        A JSON ready dictionary representing all rows of the friends table.
    """
    cursor = ds_connection.execute(
        'select id, first_name, last_name, telephone, email, notes '
        'from friends')

    friends_collection = list()
    for friend_row in cursor.fetchall():
        friends_collection.append(
            {"id": friend_row[0],
             "first_name": friend_row[1],
             "last_name": friend_row[2],
             "telephone": friend_row[3],
             "email": friend_row[4],
             "notes": friend_row[5]})

    return friends_collection

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [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 execute [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 execute [as 别名]
    def __read_pragma(connection: sqlite3.Connection, name: str) -> Any:
        """
        Reads PRAGMA value

        :param connection:  Database connection
        :param name:        Pragma's name
        """
        cursor = connection.execute("PRAGMA {0}".format(name))

        value = None
        row = cursor.fetchone()

        if row is not None:
            value = row[0]

        return value

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [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 execute [as 别名]
def calc_longitudinal_qc(infiles):
    qcmap = {}
    qcpsms = []
    psms = parse_psms(infiles['psmtable'], is_instrument_qc=True)
    header = next(psms)
    perrorix = header.index('PrecursorError(ppm)')
    qvalix = header.index('QValue')
    msgfix = header.index('MSGFScore')
    rtix = header.index('Retention time(min)')
    misclix = header.index('missed_cleavage')
    for line in psms:
        # FIXME filtering in galaxy? will be incorrect num of peptides
        if float(line[qvalix]) > 0.01:
            continue
        qcpsms.append(line)
        if int(line[misclix]) < 4:
            mckey = 'miscleav{}'.format(line[misclix])
            try:
                qcmap[mckey] += 1
            except KeyError:
                qcmap[mckey] = 1
    qcmap['perror'] = calc_boxplot([psm[perrorix] for psm in qcpsms])
    qcmap['msgfscore'] = calc_boxplot([psm[msgfix] for psm in qcpsms])
    qcmap['rt'] = calc_boxplot([psm[rtix] for psm in qcpsms])
    con = Connection(infiles['sqltable'])
    qcmap.update({'psms': len(qcpsms), 
                  'scans': con.execute('SELECT COUNT(*) FROM mzml').fetchone()[0]})
    peps = []
    with open(infiles['peptable']) as fp:
        header, lines = table_reader(fp)
        areaix = header.index('MS1 area (highest of all PSMs)')
        protix = header.index('Protein(s)')
        count = 0
        unicount = 0
        for line in lines:
            count += 1
            if ';' not in line[protix]:
                unicount += 1
            try:
                peps.append(line)
            except ValueError:
                pass
    qcmap['peparea'] = calc_boxplot([x[areaix] for x in peps])
    qcmap.update({'peptides': count, 'unique_peptides': unicount})
    with open(infiles['prottable']) as fp:
        qcmap['proteins'] = sum(1 for _ in fp) - 1
    return qcmap

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [as 别名]
def get_friends(ds_connection: sqlite3.Connection) -> dict:
    """docstring"""

    cursor = ds_connection.execute("select id, first_name, last_name, telephone, email, notes " "from friends")

    friends_collection = list()
    for friend_row in cursor.fetchall():
        friends_collection.append(
            {
                "id": friend_row[0],
                "first_name": friend_row[1],
                "last_name": friend_row[2],
                "telephone": friend_row[3],
                "email": friend_row[4],
                "notes": friend_row[5],
            }
        )

    return friends_collection

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [as 别名]
def get_friend(ds_connection: sqlite3.Connection, id: str) -> dict:
    """
    donstring
    """
    cursor = ds_connection.execute(
        "select id, first_name, last_name, telephone, email, notes " "from friends where lower(id) = ?", [id.lower()]
    )

    friend_row = cursor.fetchone()

    if friend_row:
        return {
            "id": friend_row[0],
            "first_name": friend_row[1],
            "last_name": friend_row[2],
            "telephone": friend_row[3],
            "email": friend_row[4],
            "notes": friend_row[5],
        }

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [as 别名]
def create_table(db: sqlite3.Connection):
    c.check_type(db, "db", sqlite3.Connection)

    L.info(s.log_create_table_in_db)
    with db:
        db.execute(
            """
           CREATE TABLE followers
           (
           id       INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
           name     TEXT            NOT NULL,
           in_name  TEXT            NOT NULL
           );
           """
        )

        db.execute(
            """
           CREATE TABLE meta
           (
           id       INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
           name     TEXT            NOT NULL,
           in_name  TEXT            NOT NULL,
           cookies  TEXT            NOT NULL
           );
            """
        )

        db.execute(
            """
           CREATE TABLE log
           (
           id               INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
           time             DATETIME        NOT NULL,
           follower_number  INT             NOT NULL,
           increase         INT             NOT NULL,
           message          TEXT            NOT NULL
           );
            """
        )
    L.info(s.success)

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [as 别名]
 def drop_db_table(i_db_conn: sqlite3.Connection, i_table_name: str) -> None:
     i_db_conn.execute("DROP TABLE IF EXISTS " + i_table_name)

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import execute [as 别名]
def initial_schema_and_setup(i_db_conn: sqlite3.Connection) -> None:
    # Auto-increment is not needed in our case: https://www.sqlite.org/autoinc.html

    i_db_conn.execute(
        "CREATE TABLE " + Schema.PhrasesTable.name + "("
        + Schema.PhrasesTable.Cols.id + " INTEGER PRIMARY KEY, "
        + Schema.PhrasesTable.Cols.vertical_order + " INTEGER NOT NULL, "
        + Schema.PhrasesTable.Cols.title + " TEXT NOT NULL, "
        + Schema.PhrasesTable.Cols.ib_phrase + " TEXT NOT NULL, "
        + Schema.PhrasesTable.Cols.ob_phrase + " TEXT NOT NULL, "
        + Schema.PhrasesTable.Cols.ib_short_phrase + " TEXT NOT NULL DEFAULT '', "
        + Schema.PhrasesTable.Cols.ob_short_phrase + " TEXT NOT NULL DEFAULT '', "
        + Schema.PhrasesTable.Cols.type + " INTEGER NOT NULL"
        + " DEFAULT " + str(mc_global.BreathingPhraseType.in_out.value)
        + ")"
    )

    i_db_conn.execute(
        "CREATE TABLE " + Schema.RestActionsTable.name + "("
        + Schema.RestActionsTable.Cols.id + " INTEGER PRIMARY KEY, "
        + Schema.RestActionsTable.Cols.vertical_order + " INTEGER NOT NULL, "
        + Schema.RestActionsTable.Cols.title + " TEXT NOT NULL"
        + ")"
    )

    i_db_conn.execute(
        "CREATE TABLE " + Schema.SettingsTable.name + "("
        + Schema.SettingsTable.Cols.id + " INTEGER PRIMARY KEY, "
        + Schema.SettingsTable.Cols.rest_reminder_active + " INTEGER NOT NULL"
        + " DEFAULT " + str(SQLITE_TRUE_INT) + ", "
        + Schema.SettingsTable.Cols.rest_reminder_interval + " INTEGER NOT NULL"
        + " DEFAULT " + str(DEFAULT_REST_REMINDER_INTERVAL_MINUTES_INT) + ", "
        + Schema.SettingsTable.Cols.rest_reminder_audio_filename + " TEXT NOT NULL"
        + " DEFAULT '" + mc_global.WIND_CHIMES_FILENAME_STR + "'" + ", "
        + Schema.SettingsTable.Cols.rest_reminder_volume + " INTEGER NOT NULL"
        + " DEFAULT " + str(DEFAULT_VOLUME_INT) + ", "
        + Schema.SettingsTable.Cols.rest_reminder_notification_type + " INTEGER NOT NULL"
        + " DEFAULT " + str(mc_global.NotificationType.Both.value) + ", "
        + Schema.SettingsTable.Cols.breathing_reminder_active + " INTEGER NOT NULL"
        + " DEFAULT " + str(SQLITE_TRUE_INT) + ", "
        + Schema.SettingsTable.Cols.breathing_reminder_interval + " INTEGER NOT NULL"
        + " DEFAULT " + str(DEFAULT_BREATHING_REMINDER_INTERVAL_MINUTES_INT) + ", "
        + Schema.SettingsTable.Cols.breathing_reminder_audio_filename + " TEXT NOT NULL"
        + " DEFAULT '" + mc_global.SMALL_BELL_SHORT_FILENAME_STR + "'" + ", "
        + Schema.SettingsTable.Cols.breathing_reminder_volume + " INTEGER NOT NULL"
        + " DEFAULT " + str(DEFAULT_VOLUME_INT) + ", "
        + Schema.SettingsTable.Cols.breathing_reminder_notification_type + " INTEGER NOT NULL"
        + " DEFAULT " + str(mc_global.NotificationType.Both.value) + ", "
        + Schema.SettingsTable.Cols.breathing_reminder_phrase_setup + " INTEGER NOT NULL"
        + " DEFAULT " + str(mc_global.PhraseSetup.Long.value) + ", "
        + Schema.SettingsTable.Cols.breathing_reminder_nr_before_dialog + " INTEGER NOT NULL"
        + " DEFAULT " + str(DEFAULT_BREATHING_REMINDER_NR_BEFORE_DIALOG_INT) + ", "
        + Schema.SettingsTable.Cols.breathing_reminder_dialog_audio_active + " INTEGER NOT NULL"
        + " DEFAULT " + str(SQLITE_TRUE_INT) + ", "
        + Schema.SettingsTable.Cols.breathing_reminder_dialog_close_on_hover + " INTEGER NOT NULL"
        + " DEFAULT " + str(SQLITE_FALSE_INT) + ", "
        + Schema.SettingsTable.Cols.breathing_reminder_text + " TEXT NOT NULL"
        + " DEFAULT ''" + ", "
        + Schema.SettingsTable.Cols.breathing_dialog_phrase_selection + " INTEGER NOT NULL"
        + " DEFAULT " + str(mc_global.PhraseSelection.random.value) + ", "
        + Schema.SettingsTable.Cols.prep_reminder_audio_filename + " TEXT NOT NULL"
        + " DEFAULT '" + mc_global.SMALL_BELL_LONG_FILENAME_STR + "'" + ", "
        + Schema.SettingsTable.Cols.prep_reminder_audio_volume + " INTEGER NOT NULL"
        + " DEFAULT " + str(DEFAULT_VOLUME_INT) + ", "
        + Schema.SettingsTable.Cols.run_on_startup + " INTEGER NOT NULL"
        + " DEFAULT " + str(SQLITE_FALSE_INT)
        + ")"
    )

    db_connection = Helper.get_db_connection()

    db_cursor = db_connection.cursor()
    db_cursor.execute(
        "INSERT OR IGNORE INTO " + Schema.SettingsTable.name + "("
        + Schema.SettingsTable.Cols.id
        + ") VALUES (?)", (SINGLE_SETTINGS_ID_INT,)
    )
    # -please note "OR IGNORE"
    db_connection.commit()