Python Connection.cursor方法代码示例

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [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 cursor [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 cursor [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 cursor [as 别名]
 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

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [as 别名]
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]

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [as 别名]
def import_catalog_into_db(catalog: Dict[str, CatalogEntry], connection: sqlite3.Connection):
    cursor = connection.cursor()

    for catalog_entry in catalog.values():
        cursor.execute(
            'insert into catalog (item_key, category_name) values (?, ?);',
            [catalog_entry.item_key, catalog_entry.category_name]
        )

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [as 别名]
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)]
        )

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [as 别名]
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

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [as 别名]
def refresh_disable_proxies(conn: Connection):
    """
    刷新失效的代理，失效一天以上就可删除
    :param conn: 数据连接
    :return:
    """
    delete_sql = ''' 
        DELETE FROM disable_proxies
        WHERE CAST(strftime('%s', datetime('now', 'localtime')) - strftime('%s', insert_date) as INTEGER) > ?
    '''
    cur = conn.cursor()
    cur.execute(delete_sql, (day_of_seconds,))

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [as 别名]
def insert_disable_proxy(conn: Connection, proxy: ()):
    """
    写入失效的代理
    :param conn: 数据连接
    :param proxy: (代理)
    :return:
    """
    insert_sql = '''
        INSERT OR IGNORE INTO disable_proxies (proxy, insert_date)
        VALUES (?, datetime('now', 'localtime'))
    '''
    cur = conn.cursor()
    cur.execute(insert_sql, proxy)

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [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 cursor [as 别名]
def sql_dict_insert(
    data: Dict[str, Any], table: str, sql_connection: sqlite3.Connection
) -> None:
    """
    Insert a dictionary into a sqlite table
    :param data: Dict to insert
    :param table: Table to insert to
    :param sql_connection: SQL connection
    """
    cursor = sql_connection.cursor()
    columns = ", ".join(data.keys())
    placeholders = ":" + ", :".join(data.keys())
    query = "INSERT INTO " + table + " (%s) VALUES (%s)" % (columns, placeholders)
    cursor.execute(query, data)

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [as 别名]
def update_proxy_error(conn: Connection, proxy_list: []) -> []:
    """
    批量更新代理信息表中代理错误次数
    :param conn: 数据连接
    :proxy_list: 代理列表[(代理１), (代理１), ... , (代理n)]
    :return:
    """
    update_sql = '''
        UPDATE OR IGNORE proxy_list
        SET error_count = error_count + 1
        WHERE proxy = ?
    '''

    cur = conn.cursor()
    cur.executemany(update_sql, proxy_list)

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [as 别名]
def update_tac_status(conn: Connection, tac_data: ()):
    """
    更新tac的状态
    :param conn: 数据连接
    :param tac_data: (状态，tac）
        状态：（2:不存在，3:查询错）
    :return:
    """
    update_sql = '''
        UPDATE OR IGNORE tac_info
        SET status = ? ,
        query_count = query_count + 1 ,
        update_date = datetime('now', 'localtime')
        WHERE tac_id = ?
    '''
    cur = conn.cursor()
    cur.execute(update_sql, tac_data)

# 需要导入模块: from sqlite3 import Connection [as 别名]
# 或者: from sqlite3.Connection import cursor [as 别名]
def reload_activate_proxies(conn: Connection, data_list: []):
    """
    载入活跃代理，载入前先清空数据
    :param conn: 数据连接
    :param data_list: 代理列表，格式为：[(proxy1, response_time1, test_time1, test_xp1),...,(proxyn, response_timen, test_timen, test_xpn)]
    :return:
    """
    delete_sql = ''' 
        DELETE FROM activate_proxies
    '''
    insert_sql = ''' 
        INSERT INTO activate_proxies (proxy, response_time, test_time, test_xp, insert_date)
        VALUES (?, ?, ?, ?, datetime('now', 'localtime'))
    '''

    cur = conn.cursor()
    cur.execute(delete_sql)
    cur.executemany(insert_sql, data_list)