I'm trying to populate a database with a single column with a list of strings (links). I scraped the list and I must modify every single link before sending it to the database. This is the code:

for event in events:
    link_url = "https://www.website.com" event "#all"
    c.execute("INSERT INTO table (links) VALUES(?)", link_url)

I can get it working if I modify the variables and send a tuple, like this:

for event in events:
    link_url = "https://www.website.com" event "#all"
    link = (link_url,)
    c.execute("INSERT INTO seriea (links) VALUES(?)", link_url)

but I don't want to use this solution since I want to get a list of strings back out later:

c = connection.execute('select links from table')
list_of_urls = c.fetchall()

But this gives me a list of tuples.

This is the error I have: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 80 supplied.

I think that's because the string characters are counted (actually more but I noticed that the number before "supplied" changes with the link fed)

CodePudding user response：

I don't want to use this solution since I want to get a list of strings back out later:

c = connection.execute('select links from table')
list_of_urls = c.fetchall()

But this gives me a list of tuples.

The list of tuples you're getting when you do a select have nothing to do with the way you insert data. Remember, tables have two dimensions:

When you do a select you get a list that corresponds to the rows here. But each row has multiple fields: id, links, something, and else. Each tuple in the list contains the values for each of the fields in the table.

If you just want the URLs as a list of strings you can use a list comprehension or similar:

c = connection.execute('select links from table')
list_of_rows = c.fetchall()
list_of_strings = [row[0] for row in list_of_rows]
#                      ^ index of first element in
#                  ^^^ the tuple of values for each row

Note that you do have to provide a tuple or other sequence when you insert the data:

For the qmark style, parameters must be a sequence. For the named style, it can be either a sequence or dict instance. The length of the sequence must match the number of placeholders, or a ProgrammingError is raised. If a dict is given, it must contain keys for all named parameters.

You might be thinking of the tuple part of it the wrong way. You don't need to pass in a tuple of URLs, you need to pass in a tuple of parameters. You're not saying "the links column should contain this tuple" but rather "this tuple contains enough values to fill in the placeholders in this query".

I'd rewrite that like so:

for event in events:
    link_url = "https://www.website.com" event "#all"
    c.execute("INSERT INTO seriea (links) VALUES(?)", (link_url,))

This is so you can have multiple parameters, e.g.

c.execute(
    "INSERT INTO seriea (links, some, other) VALUES(?, ?, ?)",
    (link_url, foo, bar),
)

The current statement uses 1, and there are 80 supplied.

I think that's because the string characters are counted

Yes, that's most likely what's happening. c.execute() expects to receive a sequence, and strings are a sequence of characters.