Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.

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:

enter image description here

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)
share|improve this question
    
Can you please post some python code, what are you exactly trying to do, and which line causes the error? –  drodri Apr 2 '14 at 20:59
    
I have posted my Python code. Thanks –  Michael Murphy Apr 2 '14 at 21:40
    
Line 46? I only get 41 lines, I am missing something? –  drodri Apr 2 '14 at 21:55
    
Sorry I was looking at error output from an older version. Error is on line 41. –  Michael Murphy Apr 2 '14 at 21:58
    
I dont get the error, I guess you have the DB already populated with values, dont you? –  drodri Apr 2 '14 at 22:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.