sqlite3.OperationalError: unrecognized token: "X'8e7q0300000000'".
I get the above error when inserting data into a SQLite database.
In a Python script I am writing, I am attempting to select data from one database and insert it into another database I am creating. The table I am inserting into is an exact duplicate in which the data is coming from.
One of the rows in the database is a BLOB, when I select the BLOB from the original database it displays in strings with values such as:
b'\x00\x00\x00\x00\x00\x00\x00\x00'
b'\xe8\xe1\x01\x01\x00\x00\x00\x00'
b'\x8e7q\x03\x00\x00\x00\x00'
b'\t\xc3 \x02\x00\x00\x00\x00'
b'\xe8\xe1\x01\x01\x00\x00\x00\x00'
And I want to insert it into the new database with the acceptable format X'e8e1010100000000'
I have tried a string replace, but the data isn't consistent enough and I don't understand why there are elements non in the hexadecimal range. Q for example. It seems that some of the data is extracting in ASCI.
Here is an image to show what I mean:
It there a way that I can insert the data I have extracted into my new database? Sorry for the strange format of my question.
Following is the python code I have, the error is on line 41:
import sys
from os.path import basename
import sqlite3
if len(sys.argv) != 2:
print()
print("Usage:", basename(sys.argv[0]), "<Input Database>")
print()
sys.exit(1)
new_database = "LiveDataOnly-{0}".format(sys.argv[1])
# sqlite_master table query
sqlite_master_query = "SELECT tbl_name, sql FROM sqlite_master WHERE type = 'table' AND tbl_name != 'sqlite_sequence';"
# Get the create table statements from the original database
conn = sqlite3.connect(sys.argv[1])
cur = conn.cursor()
cur3 = conn.cursor()
cur.execute(sqlite_master_query)
# Create a new empty database
conn2 = sqlite3.connect(new_database)
cur2 = conn2.cursor()
cur4 = conn2.cursor()
for row in cur:
table_name, create_table_statement = row
# Create each table
cur2.execute(create_table_statement)
# Select all of the data from each table in the original database
cur3.execute("SELECT * FROM {0};".format(table_name))
# Insert all of the data into the new database
for selected_row in cur3:
statement = "INSERT INTO {0} VALUES {1};".format(table_name, selected_row).replace('None', 'Null').replace("b'\\", "X'\\").replace("\\x","")
print(statement, "\n")
cur4.execute(statement)
