Python sqlite3.PARSE_COLNAMES Examples

def migrate_db_v1_to_v2(db_path):
    old_db_path = db_path + datetime.now().strftime("%s") + ".bkp_db_v1"
    os.rename(db_path, old_db_path)

    old_db = sql.connect(old_db_path,
                         detect_types=sql.PARSE_DECLTYPES |
                         sql.PARSE_COLNAMES)
    new_db = DB(db_path)
    old_db.cursor().execute("PRAGMA foreign_keys = ON")

    epoch_list = []
    for epoch, schema in old_db.execute('SELECT epoch, schema '
                                        'FROM snapshots_schemas'):
        new_db.add_snapshot(epoch, schema)
        epoch_list.append(epoch)

    for epoch in epoch_list:
        file_list = list(old_db.execute("SELECT keyspace, tablename, file "
                                        "FROM snapshots_files WHERE epoch = ?",
                                        (epoch,)))
        new_db.add_snapshot_files(epoch, file_list) 

def CheckPragmaSchemaVersion(self):
        # This still crashed pysqlite <= 2.2.1
        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        try:
            cur = self.con.cursor()
            cur.execute("pragma schema_version")
        finally:
            cur.close()
            con.close() 

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 __init__(self, dbfile=None, scan_changes=True, silent=False):
        if not dbfile:
            dblocation = appdirs.user_data_dir("PythonJukebox", "Razorvine")
            os.makedirs(dblocation, mode=0o700, exist_ok=True)
            dbfile = os.path.join(dblocation, "tracks.sqlite")
        dbfile = os.path.abspath(dbfile)
        self.dbfile = dbfile
        self.dbconn = sqlite3.connect(dbfile, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
        self.dbconn.row_factory = sqlite3.Row   # make sure we can get results by column name
        self.dbconn.execute("PRAGMA foreign_keys=ON")
        try:
            self.dbconn.execute("SELECT COUNT(*) FROM tracks").fetchone()
            if not silent:
                print("Connected to database.")
                print("Database file:", dbfile)
            if scan_changes:
                self.scan_changes()
        except sqlite3.OperationalError:
            # the table does not yet exist, create the schema
            if not silent:
                print("Creating new database.")
                print("Database file:", dbfile)
            self.dbconn.execute("""CREATE TABLE tracks
                (
                    id integer PRIMARY KEY,
                    title nvarchar(260),
                    artist nvarchar(260),
                    album nvarchar(260),
                    year int,
                    genre nvarchar(100),
                    duration real NOT NULL,
                    modified timestamp NOT NULL,
                    location nvarchar(500) NOT NULL,
                    hash char(40) NOT NULL UNIQUE
                );""") 

def connect(self):
    self.conn = sqlite3.connect(self._filename, detect_types=sqlite3.PARSE_COLNAMES)
    self.conn.row_factory = sqlite3.Row 

def CheckPragmaSchemaVersion(self):
        # This still crashed pysqlite <= 2.2.1
        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        try:
            cur = self.con.cursor()
            cur.execute("pragma schema_version")
        finally:
            cur.close()
            con.close() 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        self.cur = self.con.cursor()
        self.cur.execute("create table test(x foo)")

        sqlite.converters["FOO"] = lambda x: "[%s]" % x
        sqlite.converters["BAR"] = lambda x: "<%s>" % x
        sqlite.converters["EXC"] = lambda x: 5 // 0
        sqlite.converters["B1B1"] = lambda x: "MARKER" 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        sqlite.register_converter("bin", BinaryConverterTests.convert) 

def CheckPragmaSchemaVersion(self):
        # This still crashed pysqlite <= 2.2.1
        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        try:
            cur = self.con.cursor()
            cur.execute("pragma schema_version")
        finally:
            cur.close()
            con.close() 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        self.cur = self.con.cursor()
        self.cur.execute("create table test(x foo)")

        sqlite.converters["FOO"] = lambda x: "[%s]" % x.decode("ascii")
        sqlite.converters["BAR"] = lambda x: "<%s>" % x.decode("ascii")
        sqlite.converters["EXC"] = lambda x: 5/0
        sqlite.converters["B1B1"] = lambda x: "MARKER" 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        sqlite.register_converter("bin", BinaryConverterTests.convert) 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        sqlite.register_converter("bin", BinaryConverterTests.convert) 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        self.cur = self.con.cursor()
        self.cur.execute("create table test(x foo)")

        sqlite.converters["FOO"] = lambda x: "[%s]" % x.decode("ascii")
        sqlite.converters["BAR"] = lambda x: "<%s>" % x.decode("ascii")
        sqlite.converters["EXC"] = lambda x: 5/0
        sqlite.converters["B1B1"] = lambda x: "MARKER" 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        sqlite.register_converter("bin", BinaryConverterTests.convert) 

def connect(self):
        return sqlite3.connect(self.database, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES) 

def start(self):
        conn = sqlite3.connect(self._db, detect_types=sqlite3.PARSE_COLNAMES)
        c = conn.cursor()
        c.execute("SELECT MIN(created_at) as 'ts [timestamp]' from tweets")
        earliest = c.fetchone()
        if earliest[0] is None:
            earliest = datetime.now()
        else:
            earliest = earliest[0]
        c.close()
        return earliest 

def end(self):
        conn = sqlite3.connect(self._db, detect_types=sqlite3.PARSE_COLNAMES)
        c = conn.cursor()
        c.execute("SELECT MAX(created_at) as 'ts [timestamp]' from tweets")
        latest = c.fetchone()
        if latest[0] is None:
            latest = datetime.now()
        else:
            latest = latest[0]
        c.close()
        return latest 

def tweet_dates(self):
        conn = sqlite3.connect(self._db, detect_types=sqlite3.PARSE_COLNAMES)
        df = pd.read_sql_query(
            'SELECT created_at FROM tweets', conn, parse_dates=['created_at'],
            index_col=['created_at']
            )
        return df 

def CheckPragmaSchemaVersion(self):
        # This still crashed pysqlite <= 2.2.1
        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        try:
            cur = self.con.cursor()
            cur.execute("pragma schema_version")
        finally:
            cur.close()
            con.close() 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        self.cur = self.con.cursor()
        self.cur.execute("create table test(x foo)")

        sqlite.converters["FOO"] = lambda x: "[%s]" % x.decode("ascii")
        sqlite.converters["BAR"] = lambda x: "<%s>" % x.decode("ascii")
        sqlite.converters["EXC"] = lambda x: 5/0
        sqlite.converters["B1B1"] = lambda x: "MARKER" 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        sqlite.register_converter("bin", BinaryConverterTests.convert) 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        self.cur = self.con.cursor()
        self.cur.execute("create table test(x foo)")

        sqlite.converters["FOO"] = lambda x: "[%s]" % x
        sqlite.converters["BAR"] = lambda x: "<%s>" % x
        sqlite.converters["EXC"] = lambda x: 5 // 0
        sqlite.converters["B1B1"] = lambda x: "MARKER" 

def CheckPragmaSchemaVersion(self):
        # This still crashed pysqlite <= 2.2.1
        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        try:
            cur = self.con.cursor()
            cur.execute("pragma schema_version")
        finally:
            cur.close()
            con.close() 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        self.cur = self.con.cursor()
        self.cur.execute("create table test(x foo)")

        sqlite.converters["FOO"] = lambda x: "[%s]" % x
        sqlite.converters["BAR"] = lambda x: "<%s>" % x
        sqlite.converters["EXC"] = lambda x: 5 // 0
        sqlite.converters["B1B1"] = lambda x: "MARKER" 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        sqlite.register_converter("bin", BinaryConverterTests.convert) 

def CheckPragmaSchemaVersion(self):
        # This still crashed pysqlite <= 2.2.1
        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        try:
            cur = self.con.cursor()
            cur.execute("pragma schema_version")
        finally:
            cur.close()
            con.close() 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        self.cur = self.con.cursor()
        self.cur.execute("create table test(x foo)")

        sqlite.converters["FOO"] = lambda x: "[%s]" % x
        sqlite.converters["BAR"] = lambda x: "<%s>" % x
        sqlite.converters["EXC"] = lambda x: 5 // 0
        sqlite.converters["B1B1"] = lambda x: "MARKER" 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        sqlite.register_converter("bin", BinaryConverterTests.convert) 

def CheckPragmaSchemaVersion(self):
        # This still crashed pysqlite <= 2.2.1
        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        try:
            cur = self.con.cursor()
            cur.execute("pragma schema_version")
        finally:
            cur.close()
            con.close() 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        self.cur = self.con.cursor()
        self.cur.execute("create table test(x foo)")

        sqlite.converters["FOO"] = lambda x: "[%s]" % x
        sqlite.converters["BAR"] = lambda x: "<%s>" % x
        sqlite.converters["EXC"] = lambda x: 5 // 0
        sqlite.converters["B1B1"] = lambda x: "MARKER"