4

I have a list of data that needs to be inserted into a single database column. I am getting this error when trying to do it:

sqlite3.InterfaceError: Error binding parameter 4 - probably unsupported type.

That parameter is a list as follows:

['\r\n', ' \n', 'Please let me know if you still need Curve Shift.\n', '\n', 'Thanks,\n', 'Heather\n', ' -----Original Message-----\n', 'From: \tAllen, Phillip K. \n', 'Sent:\tFriday, December 07, 2001 5:14 AM\n', 'To:\tDunton, Heather\n', 'Subject:\tRE: West Position\n', '\n', 'Heather,\n', '\n', 'Did you attach the file to this email?\n', '\n', ' -----Original Message-----\n', ...

(list continues)...

'\n', 'Let me know if you have any questions.\n', '\n', '\n', 'Heather']

I extract part of a text file like so:

def extract_values(f):
    lines = f.readlines()

    for line_number, line in enumerate(lines):
        if line.startswith("X-FileName:"):
            data = lines[line_number:]  # read from specified line until end of file
            break

I'd prefer the data be inserted raw into the table so that it can be read out and enumerated line by line just like a text file. How do I do this? Should I use the blob type? How should I extract the data differently so it is inserted 'as is' without all the tab and newline codes?

Bob
  • 57
  • 1
  • 7
  • pickle i.e. serialization [here](https://docs.python.org/2/library/pickle.html) – Ali Gajani Nov 12 '14 at 04:46
  • @AliGajani thanks, I am having a hard time finding an example related to sqlite, would I then use a blob column type? – Bob Nov 12 '14 at 05:35
  • If you want to store a pickled object, you'll need to use a blob, since it is binary data. – Ali Gajani Nov 12 '14 at 05:36
  • @AliGajani can you link to an example? I can find lots of examples of writing picked binary data to files, but not to database fields. [What I have found for sqlite](http://stackoverflow.com/a/539636/4240919) doesn't use pickle at all and I'm not sure it can be easily retrieved and worked with (pickle seems better). – Bob Nov 12 '14 at 05:40

1 Answers1

4

You can use pickle. It is a Python module used to serialize an object and store it somewhere. If you are unaware of serialization, please read it up.

In short, you convert a data object to binary, marshall it over a network, or store it in a database as a blob. You can then use it later on.

In your query, you can probably have your object pickled, store it in a variable such as pdata, and then put it in your desired blob column field like:

cursor.execute("YOUR SQL QUERY", sqlite3.Binary(pdata))

Also, I just saw that your list contains textual data, which can be converted to JSON and that way, you could easily do a JSON dump of your specified list into the sqllite3 column.

Ali Gajani
  • 14,762
  • 12
  • 59
  • 100