Insert list into single sqlite database column

Question

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?

@AliGajani thanks, I am having a hard time finding an example related to sqlite, would I then use a blob column type? — Bob
– Bob, Commented Nov 12, 2014 at 5:35
If you want to store a pickled object, you'll need to use a blob, since it is binary data. — Ali Gajani
– Ali Gajani, Commented Nov 12, 2014 at 5: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 doesn't use pickle at all and I'm not sure it can be easily retrieved and worked with (pickle seems better). — Bob
– Bob, Commented Nov 12, 2014 at 5:40

Ali Gajani · Accepted Answer · 2014-11-12 05:49:27Z

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.

edited Nov 12, 2014 at 5:49

answered Nov 12, 2014 at 5:42

Ali Gajani

15.1k12 gold badges64 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ali Gajani Over a year ago

That's fine. Doesn't matter. I am here to help Bob. That matters :)

Bob Over a year ago

and I appreciate it very much

Collectives™ on Stack Overflow

Insert list into single sqlite database column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related