我们从Python开源项目中，提取了以下50个代码示例，用于说明如何使用sqlite3.Connection()。
def __init__(self, conn_or_path, calendar, daily_bar_reader, overwrite=False): if isinstance(conn_or_path, sqlite3.Connection): self.conn = conn_or_path elif isinstance(conn_or_path, str): if overwrite and exists(conn_or_path): try: remove(conn_or_path) except OSError as e: if e.errno != ENOENT: raise self.conn = sqlite3.connect(conn_or_path) else: raise TypeError("Unknown connection type %s" % type(conn_or_path)) self._daily_bar_reader = daily_bar_reader self._calendar = calendar
def __init__(self, database, tabledesc=None, timestamp=False): # type: (str, Optional[Dict[str, str]], bool) -> None self.database = database if tabledesc is None: tabledesc = self.prefixdesc() self.tabledesc = tabledesc timestampsql = self._sqtimestamp if timestamp else '' sqcreate = self._sqcreate % timestampsql self.kv_create = sqcreate.format(**tabledesc) self.kv_get = self._sqget.format(**tabledesc) self.kv_mget = self._sqmget.format(**tabledesc) self.kv_put = self._sqput.format(**tabledesc) self.kv_delete = self._sqdelete.format(**tabledesc) self._connection = None # type: Optional[sqlite3.Connection] self.sqlite_limit_variable_number = 999 self.support_mget = True
def gettemphum(self): instance = com_dht11.DHT11(self.port) nextacq = time.time() while self.counter: if time.time() >= nextacq: nextacq += self.delay self.lock.acquire() connection = sqlite3.Connection(self.database) cursor = connection.cursor() instance.read(self.name, connection, cursor) self.lock.release() self.counter -= 1
def gettemphum(self): instance = com_dht22.DHT22(self.port, self.ledport) nextacq = time.time() while self.counter: if time.time() >= nextacq: nextacq += self.delay self.lock.acquire() connection = sqlite3.Connection(self.database) cursor = connection.cursor() instance.read(self.name, connection, cursor) self.lock.release() self.counter -= 1
def gettemphumpres(self): instance = com_bme280.BME280(self.name) nextacq = time.time() while self.counter: if time.time() >= nextacq: nextacq += self.delay self.lock.acquire() connection = sqlite3.Connection(self.database) cursor = connection.cursor() instance.read(connection, cursor, True) self.lock.release() self.counter -= 1
def getpicture(self): instance = com_camera.Camera('PICTURE', self.cameranumber) nextacq = time.time() while self.counter: if time.time() >= nextacq: nextacq += self.delay self.lock.acquire() connection = sqlite3.Connection(self.database) cursor = connection.cursor() instance.getpicture(connection, cursor) self.lock.release() self.counter -= 1
def gettemphum(self): instance = com_ds18b20.DS18B20(self.name) nextacq = time.time() while self.counter: if time.time() >= nextacq: nextacq += self.delay self.lock.acquire() connection = sqlite3.Connection(self.database) cursor = connection.cursor() instance.read(self.name, self.sensor, connection, cursor) self.lock.release() self.counter -= 1
def job(self, counter, delay): while counter: logger.debug(self.name) self.lock.acquire() conf = com_config.Config() config = conf.getconfig() connection = sqlite3.Connection(config['SQLITE']['database']) cursor = connection.cursor() dalcamera = dal_camera.DAL_Camera(connection, cursor) index = dalcamera.get_last_picture_id() dalcamera.set_last_picture_id(index + 1) dalpicture = dal_picture.DAL_Picture(connection, cursor) dalpicture.setpicture(self.name) time.sleep(delay) counter -= 1 self.lock.release()
def __init__(self, db_conn): self.conn = db_conn if 'sqlite3' in modules and isinstance(db_conn, sqlite3.Connection): self.kind = 'sqlite3' self.insert = SqliteInsert self.update = SqliteUpdate self.column = SqliteColumn else: self.kind = 'postgresql' self.insert = Insert self.update = Update self.column = Column self.select = Select self.delete = Delete self.curs = self.get_cursor() self.refresh_tables() self.conn.rollback() super(DictDB, self).__init__()
def CheckConnectionConstructorCallCheck(self): """ Verifies that connection methods check whether base class __init__ was called. """ class Connection(sqlite.Connection): def __init__(self, name): pass con = Connection(":memory:") try: cur = con.cursor() self.fail("should have raised ProgrammingError") except sqlite.ProgrammingError: pass except: self.fail("should have raised ProgrammingError")
def is_blacklisted(self, contact: Contact or int, database_override: sqlite3.Connection = None) -> bool: """ Checks if a contact is blacklisted :param contact: The contact to check :param database_override: Provides a different database connection. Required for use in other threads. :return: True if the contact is blacklisted else False """ if contact is None: return False user_id = contact if isinstance(contact, int) else contact.database_id db = self.db if database_override is None else database_override result = db.execute( "SELECT * FROM authenticator WHERE user_id=? AND is_blacklisted=1", (user_id,)).fetchall() return len(result) == 1
def get_language_preference(self, contact: Contact, default: str = "en", db: sqlite3.Connection=None) -> str: """ Retrieves a language from the user's preferences in the database :param contact: The user to check the language preference for :param default: A default language value used in case no entry was found :param db: Optionally defines which database connection to use (necessary for access from other thread) :return: The language preferred by the user """ db = db if db is not None else self.db result = db.execute( "SELECT lang_pref FROM language_preferences WHERE user_id=?", (contact.database_id,) ).fetchall() return default if len(result) == 0 else result[0][0]
def __init__(self, database: sqlite3.Connection): """ Initializes the address book. Makes sure that the address book's database table exists and has the correct schema :param database: The database connection to use """ self.db = database self.db.execute( "CREATE TABLE IF NOT EXISTS address_book (" " id INTEGER CONSTRAINT constraint_name PRIMARY KEY," " display_name VARCHAR(255) NOT NULL," " address VARCHAR(255) NOT NULL" ")" ) self.db.commit() self.logger.info("Address Book initialized")
def get_contact_for_id(self, user_id: int, database_override: sqlite3.Connection = None) \ -> Contact: """ Generates a Contact object for a user ID in the address book table :param user_id: The user's ID :param database_override: Can be specified to use a different database connection, useful for calling this method from a different thread :return: The user as a Contact object """ db = self.db if database_override is None else database_override result = db.execute("SELECT * FROM address_book WHERE id=?", (user_id,)).fetchall() if len(result) != 1: # noinspection PyTypeChecker return None else: data = result[0] return Contact(int(data[0]), str(data[1]), str(data[2]))
def initialize_database(database: sqlite3.Connection): """ Initializes the Database Table for the reminder service :param database: The database connection to use :return: None """ # noinspection SqlNoDataSourceInspection,SqlDialectInspection database.execute("CREATE TABLE IF NOT EXISTS reminder (" " id INTEGER CONSTRAINT constraint_name PRIMARY KEY," " sender_id INTEGER NOT NULL," " msg_text VARCHAR(255) NOT NULL," " due_time VARCHAR(255) NOT NULL," " sent BOOLEAN NOT NULL" ")") database.commit()
def store_reminder(database: sqlite3.Connection, message: str, due_time: datetime, sender_id: int): """ Stores a reminder in the database :param database: The database Connection to use :param message: The message text to store :param due_time: The time at which the message should be sent :param sender_id: The initiator's id in the address book table :return: None """ database.execute( "INSERT INTO reminder (id, sender_id, msg_text, due_time, sent) " "VALUES (?, ?, ?, ?, ?)", (get_next_id(database), sender_id, message, convert_datetime_to_string(due_time), False) ) database.commit() logger.info("Reminder stored") # noinspection SqlNoDataSourceInspection,SqlResolve
def get_unsent_reminders(database: sqlite3.Connection) \ -> List[Dict[str, str or int or datetime]]: """ Retrieves all unsent reminders from the database :param database: The database to use :return: A list of dictionaries that contain the reminder information """ results = database.execute( "SELECT reminder.id, reminder.msg_text, reminder.due_time, " "address_book.address, address_book.id, address_book.display_name " "FROM reminder " "JOIN address_book ON reminder.sender_id = address_book.id " "WHERE reminder.sent = 0") formatted_results = [] for result in results: formatted_results.append({ "id": result[0], "message": result[1], "due_time": convert_string_to_datetime(result[2]), "receiver": Contact(result[4], result[5], result[3]) }) return formatted_results
def initialize_database(db: sqlite3.Connection): """ Initializes the Service's database tables :return: None """ logger.info("Initializing Anime Reminder Database") db.execute("CREATE TABLE IF NOT EXISTS anime_reminder_subscriptions (" " id INTEGER CONSTRAINT constraint_name PRIMARY KEY," " user_id INTEGER NOT NULL," " show_name VARCHAR(255) NOT NULL" ")") db.execute("CREATE TABLE IF NOT EXISTS anime_reminder_threads (" " id INTEGER CONSTRAINT constraint_name PRIMARY KEY," " show_name VARCHAR(255) NOT NULL," " episode INTEGER NOT NULL," " thread VARCHAR(255) NOT NULL" ")") db.commit() # noinspection SqlNoDataSourceInspection,SqlDialectInspection,SqlResolve
def subscription_exists(user: int, show_name: str, db: sqlite3.Connection) \ -> bool: """ Checks if a subscription already exists :param user: The user ID for this subscription :param show_name: The show name of the subscription :param db: The database to use :return: True if the subscription already exists, otherwise False """ subscriptions = filter( lambda x: x["show_name"].lower() == show_name.lower(), get_subscriptions(db, user) ) return len(list(subscriptions)) >= 1 # noinspection SqlNoDataSourceInspection,SqlDialectInspection,SqlResolve
def initialize_db(fname): """ Args: fname (str): location of the database. Returns: db (sqlite3.Connection): a SQLite3 database with an embeddings table. """ if path.dirname(fname) and not path.isdir(path.dirname(fname)): makedirs(path.dirname(fname)) db = sqlite3.connect(fname) c = db.cursor() c.execute('create table if not exists embeddings(word text primary key, emb blob)') db.commit() return db
def __init__(self, conn_or_path, equity_daily_bar_reader, calendar, overwrite=False): if isinstance(conn_or_path, sqlite3.Connection): self.conn = conn_or_path elif isinstance(conn_or_path, string_types): if overwrite: try: remove(conn_or_path) except OSError as e: if e.errno != ENOENT: raise self.conn = sqlite3.connect(conn_or_path) self.uri = conn_or_path else: raise TypeError("Unknown connection type %s" % type(conn_or_path)) self._equity_daily_bar_reader = equity_daily_bar_reader self._calendar = calendar
def CheckConnectionConstructorCallCheck(self): """ Verifies that connection methods check wether base class __init__ was called. """ class Connection(sqlite.Connection): def __init__(self, name): pass con = Connection(":memory:") try: cur = con.cursor() self.fail("should have raised ProgrammingError") except sqlite.ProgrammingError: pass except: self.fail("should have raised ProgrammingError")
def _upsert_ad_performance(ad_insights: [adsinsights.AdsInsights], con: sqlite3.Connection): """Creates the ad performance table if it does not exists and upserts the ad insights data afterwards Args: ad_insights: A list of Insights objects con: A sqlite database connection """ con.execute(""" CREATE TABLE IF NOT EXISTS ad_performance ( date DATE NOT NULL, ad_id BIGINT NOT NULL, device TEXT NOT NULL, performance TEXT NOT NULL, PRIMARY KEY (ad_id, device) );""") con.executemany("INSERT OR REPLACE INTO ad_performance VALUES (?,?,?,?)", _to_insight_row_tuples(ad_insights))
def thread_setup(N, cnx, client, target=file_putter , cache = None , db_queue = None ): """ :param N: int -- Number of worker threads... :param cnx: sqlite3.Connection -- database connection object :param client: DrasticClient -- the CDMI client object ... it appears to be thread safe,so no point in replicating it :param target: -- function :param cache: _dirmgmt -- Cache of found filenames... :return: [ queue , [threads] ] """ q = Queue(4096) threads = [] for k in range(N): t = Thread(target=target, args=(q, client, cnx , cache , db_queue )) t.setDaemon(True) #t.start() threads.append(t) return [q, threads]
def test_19_connect_db(): """Verify that we can get a connection based on the definition of the get_db_connection config function.""" pydbvolve.load_config(TEST_CONFIG_FILE) config = pydbvolve.new_config() config.update({'migration_action': 'info', 'version': pydbvolve.LATEST_VERSION, 'migration_user': pydbvolve.get_migration_user(config), 'sequential': False, 'config_file_path': TEST_CONFIG_FILE, 'verbose': False}) pydbvolve.run_config(config) cred = pydbvolve.get_db_credentials(config) conn = pydbvolve.get_db_connection(config, cred) assert(conn is not None) assert(isinstance(conn, sqlite3.Connection)) conn.close() # End test_19_connect_db
def test_20_initialize(capsys): """Verify top-level call to component functions.""" with capsys.disabled(): config = pydbvolve.initialize(TEST_CONFIG_FILE, 'info', 'r1.1.10', True, False) assert(config is not None) assert(isinstance(config, dict)) assert(len(config) > 0) assert(config['base_dir'] == os.path.dirname(TEST_CONFIG_FILE)) assert(config['migration_action'] == 'info') assert(config['version'] == 'r1.1.10') assert(config['sequential'] == True) assert(config['verbose'] == False) assert(config['migration_table_name'] == '__migrations__') assert(isinstance(config['conn'], sqlite3.Connection)) assert('credentials' not in config) config['conn'].close() # End test_20_initialize
def delete_records(keep=20): """Clean up files on server and mark the record as deleted""" sql = "SELECT * from records where is_deleted<>1 ORDER BY id desc LIMIT -1 offset {}".format(keep) assert isinstance(g.db, sqlite3.Connection) c = g.db.cursor() c.execute(sql) rows = c.fetchall() for row in rows: name = row[1] xmind = join(app.config['UPLOAD_FOLDER'], name) xml = join(app.config['UPLOAD_FOLDER'], name[:-5] + 'xml') for f in [xmind, xml]: if exists(f): os.remove(f) sql = 'UPDATE records SET is_deleted=1 WHERE id = ?' c.execute(sql, (row[0],)) g.db.commit()
def _create_new_db(self): # assumes self.database is a sqlite3.Connection create_version_table = "CREATE TABLE version (num INTEGER PRIMARY KEY);" create_info_table = """CREATE TABLE traj_info( hash VARCHAR(64) PRIMARY KEY, length INTEGER, ndim INTEGER, offsets NPARRAY, abs_path VARCHAR(4096) null, version INTEGER, lru_db INTEGER ); """ self._database.execute(create_version_table) self._database.execute(create_info_table) self._database.commit()
def create_filtered_copy(self): # this with statement # is used to ensure that no corrupted/uncompleted files get created in case of problems with util.create_file(self.copy_db_path) as tempfile: logging.info("copying database") shutil.copy(self.this_db_path, tempfile) self.copy_db_conn = sqlite3.connect(tempfile) assert isinstance(self.copy_db_conn, sqlite3.Connection) filtered = False filtered = self._delete_rows_by_start_and_end_date() or filtered if self.copy_db_conn.execute('SELECT count(*) FROM days').fetchone() == (0,): raise ValueError('No data left after filtering') filtered = self._filter_by_calendar() or filtered filtered = self._filter_by_agency() or filtered filtered = self._filter_spatially() or filtered self.copy_db_conn.commit() if filtered: update_secondary_data_copies(db_conn=self.copy_db_conn) if self.update_metadata: self._update_metadata() return
def remove_dangling_shapes(db_conn): """ Remove dangling entries from the shapes directory. Parameters ---------- db_conn: sqlite3.Connection connection to the GTFS object """ db_conn.execute(DELETE_SHAPES_NOT_REFERENCED_IN_TRIPS_SQL) SELECT_MIN_MAX_SHAPE_BREAKS_BY_TRIP_I_SQL = \ "SELECT trips.trip_I, shape_id, min(shape_break) as min_shape_break, max(shape_break) as max_shape_break FROM trips, stop_times WHERE trips.trip_I=stop_times.trip_I GROUP BY trips.trip_I" trip_min_max_shape_seqs= pandas.read_sql(SELECT_MIN_MAX_SHAPE_BREAKS_BY_TRIP_I_SQL, db_conn) rows = [] for row in trip_min_max_shape_seqs.itertuples(): shape_id, min_shape_break, max_shape_break = row.shape_id, row.min_shape_break, row.max_shape_break if min_shape_break is None or max_shape_break is None: min_shape_break = float('-inf') max_shape_break = float('-inf') rows.append( (shape_id, min_shape_break, max_shape_break) ) DELETE_SQL_BASE = "DELETE FROM shapes WHERE shape_id=? AND (seq<? OR seq>?)" db_conn.executemany(DELETE_SQL_BASE, rows) remove_dangling_shapes_references(db_conn)
def get_plugin_ids(self, names): """Gets IDs of plugins, creating rows if they don't exist. This function maintains a cache of the plugins table in local memory, to avoid performing queries when possible. When writing to the table, this function is optimistic and can cause the outer transaction to abort, in which case it will need to be retried. Args: db_conn: A PEP 249 Connection object. names: An iterable of strings of plugin names. Returns: Map of plugin names to their permanent arbitrary IDs. :type names: list[str] :rtype: dict[str, int] """ with self._plugin_ids_by_name_lock: if all(name in self._plugin_ids_by_name for name in names): return self._get_plugin_ids(names) self._plugin_ids_by_name.update( self.run_transaction(functools.partial(_sync_plugins, names))) return self._get_plugin_ids(names)
def _sync_plugins(names, connection): """Fetches Plugins table and assigns IDs for new names if necessary. :type names: list[str] :type connection: Connection :rtype: dict[str, int] """ the_whole_table = {} # type: dict[str, int] names = set(names) max_id = 0 for id_, name in connection.execute('SELECT plugin_id, name FROM Plugins'): if id_ > max_id: max_id = id_ the_whole_table[name] = id_ names.discard(name) new_rows = [] for name in names: max_id += 1 the_whole_table[name] = max_id new_rows.append((max_id, name)) if new_rows: connection.executemany( 'INSERT INTO Plugins (plugin_id, name) VALUES (?, ?)', new_rows) return the_whole_table
def close(self): """ Commit and close the connection. .. seealso:: :py:meth:`sqlite3.Connection.close` """ try: self.check_connection() except (SystemError, NullDatabaseConnectionError): return logger.debug( "close connection to a SQLite database: path='{}'".format( self.database_path)) self.commit() self.connection.close() self.__initialize_connection()
def conn(self): # type: () -> sqlite3.Connection if self._connection is not None: return self._connection connection = sqlite3.Connection(self.database) with connection: connection.execute(self.kv_create) self._connection = connection return connection
def gettemphumpres(self): instance = com_mpu9250.MPU9250(self.name) instance.ready() nextacq = time.time() # rpy = RollPitchYaw.RollPitchYaw() while self.counter: if time.time() >= nextacq: nextacq += self.delay self.lock.acquire() connection = sqlite3.Connection(self.database) cursor = connection.cursor() acc = instance.readaccel() gyro = instance.readgyro() magn = instance.readlmagnet() temp = 0 # instance.readtemp() # calc roll, pitch, yaw # roll = rpy.calcRoll(acc) # pitch = rpy.calcPitch(acc) # yaw = rpy.calcYaw(acc, roll, pitch) # print(math.degrees(roll), math.degrees(pitch), math.degrees(yaw)) dal = dal_mpu9250.DAL_MPU950(connection, cursor) dal.set_mpu9250(self.name, gyro[0], gyro[1], gyro[2], acc[0], acc[1], acc[2], magn[0], magn[1], magn[2], temp) self.lock.release() self.counter -= 1
def __init__(self): conf = com_config.Config() self.config = conf.getconfig() self.connection = sqlite3.Connection(self.config['SQLITE']['database']) self.cursor = self.connection.cursor()
def getgooglemapsimages(self, directory, filename, zoomlevel = 15, width = 320, height = 385, levelprecision = 2, traceroute = False, weight = 5, nbpoint = 10, color = '0xff0000', imageformat = 'png', maptype = 'roadmap'): self.logger.info('Get Google Images - levelprecision: ' + str(levelprecision) + ' Zoom Level: ' + str(zoomlevel)) # Documentation :https://developers.google.com/maps/documentation/static-maps/intro connection = sqlite3.Connection(self.config['SQLITE']['database']) cursor = connection.cursor() google_apikey = 'AIzaSyCdP2hiLc0SNX6eB1w_lb7-JQdF6YO3cr4' counter = 0 mapurl = 'https://maps.googleapis.com/maps/api/staticmap?center=' dal = dal_gps.DAL_GPS(connection, cursor) rows = dal.getCoordinate(levelprecision) for row in rows: counter += 1 file = directory + '/' + filename + str(counter) + '.' + imageformat f = open(file, 'wb') url = mapurl + str(row[2]) + ',' + str(row[3]) + '&zoom=' + str(zoomlevel) + '&size=' + str(width) + 'x' + str( height) + '&visual_refresh=true&maptype=' + maptype + '&format=' + imageformat if traceroute: path = '&path=color:' + color + '|weight:' + str(weight) index = counter - nbpoint if index < 0: index = 0 for i in range(index, counter): path += '|' + str(rows[i][2]) + ',' + str(rows[i][3]) url += path url += '&key=' + google_apikey f.write(requests.get(url).content) f.close() self.logger.debug('Generate file: ' + file)
def exporttogpx(self, filename, trackname = '', simulation = False): self.logger.info('Export GPX') connection = sqlite3.Connection(self.config['SQLITE']['database']) cursor = connection.cursor() # Load GPS data from database dal = dal_gps.DAL_GPS(connection, cursor) rows = dal.getCoordinate(2) gpx = gpxpy.gpx.GPX() # Create first track in our GPX: gpx_track = gpxpy.gpx.GPXTrack() gpx_track.name = trackname gpx.tracks.append(gpx_track) # Create first segment in our GPX track: gpx_segment = gpxpy.gpx.GPXTrackSegment() gpx_track.segments.append(gpx_segment) # Create points: cptsimulation = 1 # Init cpt for altitude simulation cpt = 0 # Counter for point name for row in rows: date = datetime.datetime.strptime(row[1], "%Y-%m-%d %H:%M:%S") if simulation: gpx_segment.points.append(gpxpy.gpx.GPXTrackPoint(row[2], row[3], cptsimulation, date, None, None, row[5], row[6], row[7], row[8], 'Point N' + str(cpt))) cptsimulation += 1 else: gpx_segment.points.append(gpxpy.gpx.GPXTrackPoint(row[2], row[3], row[4], date, None, None, row[5], row[6], row[7], row[8], 'Point N' + str(cpt))) self.logger.debug('Calculation: ' + str(date)) cpt += 1 # You can add routes and waypoints, too... self.logger.info('Generate file: ' + filename) stream = gpx.to_xml() gpx_file = open(filename, 'w') gpx_file.write(stream) gpx_file.close()
def constructs_correctly(self): self.assertIsInstance(self.logdb.conn, sqlite3.Connection) self.assertIsInstance(self.logdb.cursor, sqlite3.Cursor)
def get_connection(self) -> Connection: """ Opens the database and returns a Cursor :return: a Cursor """ return connect(self.db_name)
def rollback(self): global did_rollback did_rollback = True sqlite.Connection.rollback(self)
