Python sqlite3.Connection类代码示例

def prepareSqlite(out,featureClass,fileType,includeGeometry):
    [shp,shpType]=getShp(featureClass)
    if shpType == "point":
        gType = 1
    elif shpType == "multipoint":
        gType = 4
    elif shpType == "polyline":
        gType = 5
    elif shpType == "polygon":
        gType = 6
    fields=listFields(featureClass)
    fieldNames = []
    fieldNames.append("OGC_FID INTEGER PRIMARY KEY")
    if includeGeometry:
        fieldNames.append("GEOMETRY blob")
    for field in fields:
        if (fields[field] != u'OID') and field.lower() !=shp.lower():
            fieldNames.append(parseFieldType(field,fields[field]))

    conn=Connection(out)
    c=conn.cursor()
    name = splitext(split(out)[1])[0]
    c.execute("""CREATE TABLE geometry_columns (     f_table_name VARCHAR,      f_geometry_column VARCHAR,      geometry_type INTEGER,      coord_dimension INTEGER,      srid INTEGER,     geometry_format VARCHAR )""")
    c.execute("""insert into geometry_columns( f_table_name, f_geometry_column, geometry_type, coord_dimension, srid, geometry_format) values(?,?,?,?,?,?)""",(name,"GEOMETRY",gType,2,4326,"WKB"))
    c.execute("""CREATE TABLE spatial_ref_sys        (     srid INTEGER UNIQUE,     auth_name TEXT,     auth_srid TEXT,     srtext TEXT)""")
    c.execute("insert into spatial_ref_sys(srid ,auth_name ,auth_srid ,srtext) values(?,?,?,?)",(4326, u'EPSG', 4326, u'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]'))
    c.execute("create table {0}({1})".format(name,", ".join(fieldNames)))
    return [name,c,conn]

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

 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)

def load_max_id(db: sqlite3.Connection):
    cur = db.cursor()
    cur.execute('select min(id) from fav_tweets')
    r = cur.fetchone()
    db.rollback()
    cur.close()
    if r:
        return r[0]
    pass

 def __init__(self, dbname='test.db', autocommit=1, encoding='utf-8'):
     isolation_level = ''
     if autocommit:
         isolation_level = None
     LocalConnection.__init__(self, dbname, isolation_level=isolation_level)
     #self.row_factory = ResultSet
     #self.row_factory = attribute_factory
     #self.row_factory = Row
     self.row_factory = ResultSetRow

    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)))

 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)

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

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

    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 __init__(self, ladder: str, dbh: sqlite3.Connection) -> None:
     self.ladder = ladder
     self.dbh = dbh
     self.cursor = dbh.cursor()
     self.players: Dict[str, Player] = {}
     self.tsh: trueskill.TrueSkill = None
     self.last_ranking = 0

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]}

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

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

def import_sales_into_db(sales, connection: sqlite3.Connection):
    cursor = connection.cursor()

    for sale in sales:
        cursor.execute(
            'insert into sales (item_key, country, city_name, sale_timestamp, price) values (?, ?, ?, ?, ?);',
            [sale.item_key, sale.country, sale.city_name, sale.timestamp.isoformat(), str(sale.price)]
        )