How to use the apsw.format_sql_value function in apsw
To help you get started, we’ve selected a few apsw examples, based on popular ways it is used in public projects.
Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
plasticityai / magnitude / pymagnitude / third_party / _apsw / tools / shell.py View on Github 
oldtable=self._output_table
try:
self.push_output()
self.output=self.output_insert
# Dump the table
for table in tables:
for sql in self.db.cursor().execute("SELECT sql FROM sqlite_master WHERE name=?1 AND type='table'", (table,)):
comment("Table "+table)
# Special treatment for virtual tables - they
# get called back on drops and creates and
# could thwart us so we have to manipulate
# sqlite_master directly
if sql[0].lower().split()[:3]==["create", "virtual", "table"]:
self.write(self.stdout, "DELETE FROM sqlite_master WHERE name="+apsw.format_sql_value(table)+" AND type='table';\n")
self.write(self.stdout, "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql) VALUES('table',%s,%s,0,%s);\n"
% (apsw.format_sql_value(table), apsw.format_sql_value(table), apsw.format_sql_value(sql[0])))
else:
self.write(self.stdout, "DROP TABLE IF EXISTS "+self._fmt_sql_identifier(table)+";\n")
self.write(self.stdout, sqldef(sql[0]))
self._output_table=self._fmt_sql_identifier(table)
self.process_sql("select * from "+self._fmt_sql_identifier(table), internal=True)
# Now any indices or triggers
first=True
for name,sql in self.db.cursor().execute("SELECT name,sql FROM sqlite_master "
"WHERE sql NOT NULL AND type IN ('index', 'trigger') "
"AND tbl_name=?1 AND name NOT LIKE 'sqlite_%' "
"ORDER BY lower(name)", (table,)):
if first:
comment("Triggers and indices on "+table)
first=False
self.write(self.stdout, sqldef(sql))
blank()oldtable=self._output_table
try:
self.push_output()
self.output=self.output_insert
# Dump the table
for table in tables:
for sql in self.db.cursor().execute("SELECT sql FROM sqlite_master WHERE name=?1 AND type='table'", (table,)):
comment("Table "+table)
# Special treatment for virtual tables - they
# get called back on drops and creates and
# could thwart us so we have to manipulate
# sqlite_master directly
if sql[0].lower().split()[:3]==["create", "virtual", "table"]:
self.write(self.stdout, "DELETE FROM sqlite_master WHERE name="+apsw.format_sql_value(table)+" AND type='table';\n")
self.write(self.stdout, "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql) VALUES('table',%s,%s,0,%s);\n"
% (apsw.format_sql_value(table), apsw.format_sql_value(table), apsw.format_sql_value(sql[0])))
else:
self.write(self.stdout, "DROP TABLE IF EXISTS "+self._fmt_sql_identifier(table)+";\n")
self.write(self.stdout, sqldef(sql[0]))
self._output_table=self._fmt_sql_identifier(table)
self.process_sql("select * from "+self._fmt_sql_identifier(table), internal=True)
# Now any indices or triggers
first=True
for name,sql in self.db.cursor().execute("SELECT name,sql FROM sqlite_master "
"WHERE sql NOT NULL AND type IN ('index', 'trigger') "
"AND tbl_name=?1 AND name NOT LIKE 'sqlite_%' "
"ORDER BY lower(name)", (table,)):
if first:
comment("Triggers and indices on "+table)
first=False
self.write(self.stdout, sqldef(sql))
blank()oldtable=self._output_table
try:
self.push_output()
self.output=self.output_insert
# Dump the table
for table in tables:
for sql in self.db.cursor().execute("SELECT sql FROM sqlite_master WHERE name=?1 AND type='table'", (table,)):
comment("Table "+table)
# Special treatment for virtual tables - they
# get called back on drops and creates and
# could thwart us so we have to manipulate
# sqlite_master directly
if sql[0].lower().split()[:3]==["create", "virtual", "table"]:
self.write(self.stdout, "DELETE FROM sqlite_master WHERE name="+apsw.format_sql_value(table)+" AND type='table';\n")
self.write(self.stdout, "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql) VALUES('table',%s,%s,0,%s);\n"
% (apsw.format_sql_value(table), apsw.format_sql_value(table), apsw.format_sql_value(sql[0])))
else:
self.write(self.stdout, "DROP TABLE IF EXISTS "+self._fmt_sql_identifier(table)+";\n")
self.write(self.stdout, sqldef(sql[0]))
self._output_table=self._fmt_sql_identifier(table)
self.process_sql("select * from "+self._fmt_sql_identifier(table), internal=True)
# Now any indices or triggers
first=True
for name,sql in self.db.cursor().execute("SELECT name,sql FROM sqlite_master "
"WHERE sql NOT NULL AND type IN ('index', 'trigger') "
"AND tbl_name=?1 AND name NOT LIKE 'sqlite_%' "
"ORDER BY lower(name)", (table,)):
if first:
comment("Triggers and indices on "+table)
first=False
self.write(self.stdout, sqldef(sql))
blank()if not first:
blank()
# sqlite sequence
# does it exist
if len(self.db.cursor().execute("select * from sqlite_master where name='sqlite_sequence'").fetchall()):
first=True
for t in tables:
v=self.db.cursor().execute("select seq from main.sqlite_sequence where name=?1", (t,)).fetchall()
if len(v):
assert len(v)==1
if first:
comment("For primary key autoincrements the next id "
"to use is stored in sqlite_sequence")
first=False
self.write(self.stdout, 'DELETE FROM main.sqlite_sequence WHERE name=%s;\n' % (apsw.format_sql_value(t),))
self.write(self.stdout, 'INSERT INTO main.sqlite_sequence VALUES (%s, %s);\n' % (apsw.format_sql_value(t), v[0][0]))
if not first:
blank()
finally:
self.pop_output()
self._output_table=oldtable
# analyze
if analyze_needed:
comment("You had used the analyze command on these tables before. Rerun for this new data.")
for n in analyze_needed:
self.write(self.stdout, "ANALYZE "+self._fmt_sql_identifier(n)+";\n")
blank()
# user version pragma
uv=self.db.cursor().execute("pragma user_version").fetchall()[0][0]first=True
for name,sql in self.db.cursor().execute("SELECT name,sql FROM sqlite_master "
"WHERE sql NOT NULL AND type IN ('index', 'trigger') "
"AND tbl_name=?1 AND name NOT LIKE 'sqlite_%' "
"ORDER BY lower(name)", (table,)):
if first:
comment("Triggers and indices on "+table)
first=False
self.write(self.stdout, sqldef(sql))
blank()
# Views done last. They have to be done in the same order as they are in sqlite_master
# as they could refer to each other
first=True
for name,sql in self.db.cursor().execute("SELECT name,sql FROM sqlite_master "
"WHERE sql NOT NULL AND type='view' "
"AND name IN ( "+",".join([apsw.format_sql_value(i) for i in tables])+
") ORDER BY _ROWID_"):
if first:
comment("Views")
first=False
self.write(self.stdout, "DROP VIEW IF EXISTS %s;\n" % (self._fmt_sql_identifier(name),))
self.write(self.stdout, sqldef(sql))
if not first:
blank()
# sqlite sequence
# does it exist
if len(self.db.cursor().execute("select * from sqlite_master where name='sqlite_sequence'").fetchall()):
first=True
for t in tables:
v=self.db.cursor().execute("select seq from main.sqlite_sequence where name=?1", (t,)).fetchall()
if len(v):if not first:
blank()
# sqlite sequence
# does it exist
if len(self.db.cursor().execute("select * from sqlite_master where name='sqlite_sequence'").fetchall()):
first=True
for t in tables:
v=self.db.cursor().execute("select seq from main.sqlite_sequence where name=?1", (t,)).fetchall()
if len(v):
assert len(v)==1
if first:
comment("For primary key autoincrements the next id "
"to use is stored in sqlite_sequence")
first=False
self.write(self.stdout, 'DELETE FROM main.sqlite_sequence WHERE name=%s;\n' % (apsw.format_sql_value(t),))
self.write(self.stdout, 'INSERT INTO main.sqlite_sequence VALUES (%s, %s);\n' % (apsw.format_sql_value(t), v[0][0]))
if not first:
blank()
finally:
self.pop_output()
self._output_table=oldtable
# analyze
if analyze_needed:
comment("You had used the analyze command on these tables before. Rerun for this new data.")
for n in analyze_needed:
self.write(self.stdout, "ANALYZE "+self._fmt_sql_identifier(n)+";\n")
blank()
# user version pragma
uv=self.db.cursor().execute("pragma user_version").fetchall()[0][0]blank()
# sqlite sequence
# does it exist
if len(self.db.cursor().execute("select * from sqlite_master where name='sqlite_sequence'").fetchall()):
first=True
for t in tables:
v=self.db.cursor().execute("select seq from main.sqlite_sequence where name=?1", (t,)).fetchall()
if len(v):
assert len(v)==1
if first:
comment("For primary key autoincrements the next id "
"to use is stored in sqlite_sequence")
first=False
self.write(self.stdout, 'DELETE FROM main.sqlite_sequence WHERE name=%s;\n' % (apsw.format_sql_value(t),))
self.write(self.stdout, 'INSERT INTO main.sqlite_sequence VALUES (%s, %s);\n' % (apsw.format_sql_value(t), v[0][0]))
if not first:
blank()
finally:
self.pop_output()
self._output_table=oldtable
# analyze
if analyze_needed:
comment("You had used the analyze command on these tables before. Rerun for this new data.")
for n in analyze_needed:
self.write(self.stdout, "ANALYZE "+self._fmt_sql_identifier(n)+";\n")
blank()
# user version pragma
uv=self.db.cursor().execute("pragma user_version").fetchall()[0][0]
if uv:first=True
for name,sql in self.db.cursor().execute("SELECT name,sql FROM sqlite_master "
"WHERE sql NOT NULL AND type IN ('index', 'trigger') "
"AND tbl_name=?1 AND name NOT LIKE 'sqlite_%' "
"ORDER BY lower(name)", (table,)):
if first:
comment("Triggers and indices on "+table)
first=False
self.write(self.stdout, sqldef(sql))
blank()
# Views done last. They have to be done in the same order as they are in sqlite_master
# as they could refer to each other
first=True
for name,sql in self.db.cursor().execute("SELECT name,sql FROM sqlite_master "
"WHERE sql NOT NULL AND type='view' "
"AND name IN ( "+",".join([apsw.format_sql_value(i) for i in tables])+
") ORDER BY _ROWID_"):
if first:
comment("Views")
first=False
self.write(self.stdout, "DROP VIEW IF EXISTS %s;\n" % (self._fmt_sql_identifier(name),))
self.write(self.stdout, sqldef(sql))
if not first:
blank()
# sqlite sequence
# does it exist
if len(self.db.cursor().execute("select * from sqlite_master where name='sqlite_sequence'").fetchall()):
first=True
for t in tables:
v=self.db.cursor().execute("select seq from main.sqlite_sequence where name=?1", (t,)).fetchall()
if len(v):
Popular apsw functions
- apsw.apswversion
- apsw.BusyError
- apsw.CantOpenError
- apsw.complete
- apsw.Connection
- apsw.Connection.__init__
- apsw.Connection.cursor
- apsw.ConstraintError
- apsw.CorruptError
- apsw.Error
- apsw.ExecutionCompleteError
- apsw.format_sql_value
- apsw.Shell
- apsw.SQLError
- apsw.SQLITE_OPEN_CREATE
- apsw.SQLITE_OPEN_READWRITE
- apsw.SQLITE_OPEN_URI
- apsw.sqlitelibversion
- apsw.VFS
- apsw.VFS.__init__