Python sqlite3.connect() Examples

def contextMenuEvent(self, event):
        right_click_menu = Qw.QMenu(self)

        act_open = right_click_menu.addAction('Open')
        act_open.triggered.connect(self.parent().parent().right_clk_open)

        act_open_path = right_click_menu.addAction('Open Path')
        act_open_path.triggered.connect(self.parent().parent().right_clk_path)

        right_click_menu.addSeparator()

        act_copy_path = right_click_menu.addAction('Copy Path')
        act_copy_path.triggered.connect(self.parent().parent().right_clk_copy)

        right_click_menu.exec_(event.globalPos())


# THE PRIMARY GUI DEFINING INTERFACE WIDGET, THE WIDGET WITHIN THE MAINWINDOW 

def ip_latency_test(ip, port=443):
    tag = 'IP_Latency_TEST'
    print_with_tag(tag, ['Prepare IP latency test for ip', ip, 'Port', str(port)])
    s_test = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    s_test.settimeout(10)
    s_start = time.time()
    try:
        s_test.connect((ip, port))
        s_test.shutdown(socket.SHUT_RD)
    except Exception as e:
        print_with_tag(tag, ['Error:', e])
        return None
    s_stop = time.time()
    s_runtime = '%.2f' % (1000 * (s_stop - s_start))
    print_with_tag(tag, [ip, 'Latency:', s_runtime])
    return float(s_runtime) 

def get_history(self):
        sql = "SELECT * FROM conversation"
        conn = sqlite3.connect(self.database)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute(sql)
        results = [dict(row) for row in cursor.fetchall()]
        conn.close()

        return results 

def getGenomeWgsCount(self, ncbid, threshold):
        """
            Gets the number of genomes/wgs available in the directory.

            @param threshold: it returns max. threshold genomes/wgs (after this threshold is reached, it returns)
            @param dir: directory that contains genomes/wgs in the form: "ncbid.[0-9]*.f[an][sa]"

            @return: the number of genomes/wgs from different species that are subclades of the input ncbid
        """
        try:
            conn = sqlite3.connect(os.path.normpath(self._databaseFile))
            cursor = conn.cursor()

            speciesIdsList = []
            self._collectSpecies(speciesIdsList, cursor, ncbid, threshold)
            return len(speciesIdsList)
        except Exception:
            print "Failed to create connection to a database:", self._databaseFile
            raise
        finally:
            cursor.close()
            conn.close() 

def sql_query(dbname, query):
    """
    Execute an SQL query over a database.
    :param dbname: filename of persistent store
    :type schema: str
    :param query: SQL query
    :type rel_name: str
    """
    import sqlite3
    try:
        path = nltk.data.find(dbname)
        connection =  sqlite3.connect(str(path))
        cur = connection.cursor()
        return cur.execute(query)
    except (ValueError, sqlite3.OperationalError):
        import warnings
        warnings.warn("Make sure the database file %s is installed and uncompressed." % dbname)
        raise 

def replace_old_db_with_new(self):
        global con
        global DATABASE_PATH

        temp_db_path = TEMP_PATH + '/angry_database.db'

        dir_path = os.path.dirname(DATABASE_PATH)

        if not os.path.exists(temp_db_path):
            return
        if not os.path.exists(dir_path):
            os.makedirs(dir_path)

        if con:
            con.close()
        shutil.move(temp_db_path, DATABASE_PATH)

        con = sqlite3.connect(DATABASE_PATH, check_same_thread=False)
        con.create_function("regexp", 2, regexp) 

def get(self, key):
        self.logger.debug("Fetching key %s", key)
        query = "SELECT value from %s WHERE key=?" % self.table
        try:
            conn = sqlite3.connect(self.dburi)
            c = conn.cursor()
            r = c.execute(query, (key,))
            value = r.fetchall()
        except sqlite3.Error:
            self.logger.exception("Error fetching key %s", key)
            raise CSStoreError('Error occurred while trying to get key')
        self.logger.debug("Fetched key %s got result: %r", key, value)
        if len(value) > 0:
            return value[0][0]
        else:
            return None 

def new_database_lite(table):
    temp_db_path = TEMP_PATH + '/angry_database.db'

    if os.path.exists(temp_db_path):
        os.remove(temp_db_path)

    con = sqlite3.connect(temp_db_path, check_same_thread=False)
    cur = con.cursor()

    if fts5_pragma_check():
        cur.execute('''CREATE VIRTUAL TABLE angry_table
                        USING fts5(directory, path)''')
        cur.execute('''PRAGMA user_version = 4;''')
    else:
        cur.execute('''CREATE VIRTUAL TABLE angry_table
                        USING fts4(directory, path)''')
        cur.execute('''PRAGMA user_version = 3;''')

    cur.executemany('''INSERT INTO angry_table VALUES (?, ?)''', table)

    con.commit()
    replace_old_db_with_new() 

def new_database(table):
    temp_db_path = TEMP_PATH + '/angry_database.db'

    if os.path.exists(temp_db_path):
        os.remove(temp_db_path)

    con = sqlite3.connect(temp_db_path, check_same_thread=False)
    cur = con.cursor()

    if fts5_pragma_check():
        cur.execute('''CREATE VIRTUAL TABLE angry_table
                        USING fts5(directory, path, size, date)''')
        cur.execute('''PRAGMA user_version = 4;''')
    else:
        cur.execute('''CREATE VIRTUAL TABLE angry_table
                        USING fts4(directory, path, size, date)''')
        cur.execute('''PRAGMA user_version = 3;''')

    cur.executemany('''INSERT INTO angry_table VALUES (?, ?, ?, ?)''', table)

    con.commit()
    replace_old_db_with_new() 

def set(self, key, value, replace=False):
        self.logger.debug("Setting key %s to value %s (replace=%s)",
                          key, value, replace)
        if key.endswith('/'):
            raise ValueError('Invalid Key name, cannot end in "/"')
        if replace:
            query = "INSERT OR REPLACE into %s VALUES (?, ?)"
        else:
            query = "INSERT into %s VALUES (?, ?)"
        setdata = query % (self.table,)
        try:
            conn = sqlite3.connect(self.dburi)
            with conn:
                c = conn.cursor()
                self._create(c)
                c.execute(setdata, (key, value))
        except sqlite3.IntegrityError as err:
            raise CSStoreExists(str(err))
        except sqlite3.Error:
            self.logger.exception("Error storing key %s", key)
            raise CSStoreError('Error occurred while trying to store key') 

def span(self, key):
        name = key.rstrip('/')
        self.logger.debug("Creating container %s", name)
        query = "INSERT into %s VALUES (?, '')"
        setdata = query % (self.table,)
        try:
            conn = sqlite3.connect(self.dburi)
            with conn:
                c = conn.cursor()
                self._create(c)
                c.execute(setdata, (name,))
        except sqlite3.IntegrityError as err:
            raise CSStoreExists(str(err))
        except sqlite3.Error:
            self.logger.exception("Error creating key %s", name)
            raise CSStoreError('Error occurred while trying to span container') 

def _process_repo_serial(git_repo_dir, sqlite_db_file, commits, extraction_settings):
    """ Processes all commits in a given git repository in a serial manner.

    Args:
        git_repo_dir: path to the git repository that is mined
        sqlite_db_file: path (including database name) where the sqlite database will be created
        commits: list of commits that have to be processed
        extraction_settings: settings for the extraction

    Returns:
        sqlite database will be written at specified location
    """

    git_repo = pydriller.GitRepository(git_repo_dir)

    con = sqlite3.connect(sqlite_db_file)

    for commit in tqdm(commits, desc='Serial'):
        args = {'git_repo_dir': git_repo_dir, 'commit_hash': commit.hash, 'extraction_settings': extraction_settings}
        result = _process_commit(args)

        if not result['edits'].empty:
            result['edits'].to_sql('edits', con, if_exists='append', index=False)
        if not result['commit'].empty:
            result['commit'].to_sql('commits', con, if_exists='append', index=False) 

def sqlite_get_user_lang(user):
    BASE_DIR = os.path.dirname(os.path.abspath(__file__))
    db_path = os.path.join(BASE_DIR, "eig_bot.db")
    conn = sqlite3.connect(db_path)
    sql = "SELECT lang FROM users WHERE user='"+user+"';";
    c = conn.cursor()
    c.execute(sql)
    data=c.fetchone()
    if not data:
        sql = "INSERT INTO users(user, lang) VALUES ('"+user+"', 'en');"
        c.execute(sql)
        conn.commit()
        conn.close()
        return 'en'
    else :
        lang = data[0]
        conn.close()
        return lang 

def connection(self, commit_on_success=False):
        with self._lock:
            if self._bulk_commit:
                if self._pending_connection is None:
                    self._pending_connection = sqlite.connect(self.filename)
                con = self._pending_connection
            else:
                con = sqlite.connect(self.filename)
            try:
                if self.fast_save:
                    con.execute("PRAGMA synchronous = 0;")
                yield con
                if commit_on_success and self.can_commit:
                    con.commit()
            finally:
                if not self._bulk_commit:
                    con.close() 

def connection(self, commit_on_success=False):
        with self._lock:
            if self._bulk_commit:
                if self._pending_connection is None:
                    self._pending_connection = sqlite.connect(self.filename)
                con = self._pending_connection
            else:
                con = sqlite.connect(self.filename)
            try:
                if self.fast_save:
                    con.execute("PRAGMA synchronous = 0;")
                yield con
                if commit_on_success and self.can_commit:
                    con.commit()
            finally:
                if not self._bulk_commit:
                    con.close() 

def __init__(self,ip,name):
        with lock:
            # Create a database in RAM
            self.db = sqlite3.connect('/home/vagrant/iSDX/xrs/ribs/'+ip+'.db',check_same_thread=False)
            self.db.row_factory = sqlite3.Row
            self.name = name

            qs = ', '.join(['?']*len(labels))
            self.insertStmt = 'insert into %s values (%s)' % (self.name, qs)

            stmt = (
                    'create table if not exists '+self.name+
                    ' ('+ ', '.join([l+' '+t for l,t in zip(labels, types)])+')'
                    )

            cursor = self.db.cursor()
            cursor.execute(stmt)
            self.db.commit() 

def check_schema_version(self):
        query = "SELECT  Version FROM  Globals WHERE Id IS 1"
        self.cur.execute(query)
        version = float(self.cur.fetchone()[0])
        if version > self.VERSION:
            raise ValueError("Database version is newer than gphotos-sync")
        elif version < self.VERSION:
            log.warning(
                "Database schema out of date. Flushing index ...\n"
                "A backup of the previous DB has been created"
            )
            self.con.commit()
            self.con.close()
            self.backup_sql_file()
            self.con = lite.connect(str(self.db_file))
            self.con.row_factory = lite.Row
            self.cur = self.con.cursor()
            self.cur2 = self.con.cursor()
            self.clean_db() 

def __init__(self, filename):
        """
        Setup the connection and initialize the database.
        """
        self.log = logging.getLogger('DATABASE')
        self.valid = validate.Validate()
        self.filename = filename
        self.con = sqlite3.connect(self.filename)
        self.con.row_factory = sqlite3.Row
        self.cur = self.con.cursor() 

def run_test_build_journal(self, rule_id, test, journal_file,
                               tablename, http_ua=None):
        """
        Build journal entries from a test within a specified rule_id
        Pass in the rule_id, test object, and path to journal_file
        DB MUST already be instantiated from util.instantiate_database()
        """
        conn = sqlite3.connect(journal_file)
        conn.text_factory = str
        cur = conn.cursor()
        for i, stage in enumerate(test.stages):
            response = None
            status = None
            try:
                print('Running test %s from rule file %s' %
                      (test.test_title, rule_id))
                if not http_ua:
                    http_ua = http.HttpUA()
                start = datetime.datetime.utcnow()
                http_ua.send_request(stage.input)
                response = http_ua.response_object.response
                status = http_ua.response_object.status
            except errors.TestError as e:
                print('%s got error. %s' % (test.test_title, str(e)))
                response = str(e)
                status = -1
            finally:
                end = datetime.datetime.utcnow()
                ins_q = util.get_insert_statement(tablename)
                cur.execute(ins_q, (rule_id, test.test_title, start,
                                    end, response, status, i))
                conn.commit() 

def __init__(self, db):

        self.db = db
        self.conn = sqlite3.connect(self.db, check_same_thread=False)
        self.cursor = self.conn.cursor() 

def __init__(self, dbname):
        self.name = dbname
        self.conn = sqlite3.connect(self.name + '.db', check_same_thread=False) 

def __db(self):
        if not Report.__db_connection:
            Report.__db_connection = sqlite3.connect('reports.sqlite3')
            Report.__db_connection.execute('''
                    CREATE TABLE IF NOT EXISTS `reports` (
                        `sha256` TEXT NOT NULL,
                        `yara_rule`   BLOB NOT NULL,
                        PRIMARY KEY(sha256)
                    ) WITHOUT ROWID;''')
            # This makes SQLite to run faster, but it could result in database corruption
            Report.__db_connection.execute('PRAGMA synchronous = OFF')
        return Report.__db_connection 

def get_memory_db():
    global_settings.mumble_db_string.seek(0)
    temp_conn = sqlite3.connect(":memory:")
    temp_conn.executescript(global_settings.mumble_db_string.read())
    temp_conn.commit()
    temp_conn.row_factory = sqlite3.Row
    return temp_conn 

def __init__(self):
        self.__create_database_file()
        self.conn = sqlite3.connect(self.db_file)
        if not self.__is_table_exists():
            Logger.debug("[SQL]: Table 'accounts' does not exist")
            self.__create_table()
            Logger.debug("[SQL]: Table 'accounts' created successfully") 

def count():
	with sqlite3.connect(path + '/web.db') as conn:
		cursor = conn.cursor()
		result = cursor.execute('SELECT COUNT(id) FROM `fofa`')
		for row in result:
			return row[0] 

def check(_id):
	with sqlite3.connect(path +'/web.db') as conn:
		cursor = conn.cursor()
		result = cursor.execute('SELECT name, keys FROM `fofa` WHERE id=\'{}\''.format(_id))
		for row in result:
			return row[0], row[1] 

def __init__(self,dbpath,debug=False):
        self.conn = sqlite3.connect(dbpath)
        self.checkstr = 'SELECT count(*) FROM tokens WHERE token=?'
        self.insertstr = "INSERT INTO tokens (user,token,ip) VALUES ( ?, ?, ? )"
        self.createstr = 'CREATE TABLE tokens ( user TEXT, token TEXT, ip TEXT, date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);'
        self.indexstr = 'CREATE INDEX idx ON tokens(token);'
        self.debug = debug 

def sql_connect(self):
        if self.uri.scheme == "mysql":
            import mysql.connector
            conn = mysql.connector.connect(
                user=self.uri.username,
                password=self.uri.password,
                host=self.uri.hostname,
                database=self.uri.path[1:])
        elif self.uri.scheme == "sqlite":
            if self.uri.netloc:
                raise ValueError("Malformed database URI %s" % self.uri)
            import sqlite3
            conn = sqlite3.connect(self.uri.path,
                detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
        else:
            raise NotImplementedError("Unsupported database scheme %s, currently only mysql://user:pass@host/database or sqlite:///path/to/database.sqlite is supported" % o.scheme)

        if self.SQL_CREATE_TABLES and self.SQL_CREATE_TABLES not in SCRIPTS:
            cur = conn.cursor()
            buf, path = self.sql_load(self.SQL_CREATE_TABLES)
            click.echo("Executing: %s" % path)
            if self.uri.scheme == "sqlite":
                cur.executescript(buf)
            else:
                cur.execute(buf, multi=True)
            conn.commit()
            cur.close()
        return conn 

def save_memory_db_to_file():
    temp_conn = get_memory_db()
    with sqlite3.connect(f'{get_main_dir()}/cfg/jjmumblebot.db') as new_db:
        temp_conn.backup(new_db)
    # temp_conn.close() 

def connect(self):
        self.dbconn = sqlite3.connect(self.filename)