UPDATE TABLE returning empty values when querying with variable

Question

I have a table with the columns 'id', 'title', 'name' and am trying to insert values into it based on parsed XML with Beautiful Soup. The function to keep track of where each value goes is defined by:

def getmax(column):
    query = 'select count(?) from table'
    c.execute(query, [column])
    result = c.fetchall()
    for e, i in enumerate(result):
        for y in enumerate(i):
            if not y[1]:
                return 0
            else:
                return y[1] # used because fetchall returns a tuple

which gives me the last row where a value was inserted for each column. The function to insert values looks like:

for e in soup.findAll('title'):
    e = str(e)
    query = "insert into table (id, title) values (?, ?)"
        c.execute(query, (getmax('title') + 1, e))
        db.commit()
for e in soup.findAll('name'):
    e = str(e)
    query = "UPDATE table SET name = (?) WHERE id = (?);"
    c.execute(query, (e, getmax('name') + 1))
    db.commit()

This returns:

id    title    name 

1     title1
2     title2
3     title3
4     title4
5     title5

Thanks very much for any help.

lmiguelvargasf · Accepted Answer · 2017-05-27 02:34:56Z

1

You can use the format() (see this for more info) method to solve this problem. In addition, you can use the a if condition else b in order to simplify your code:

def getmax(column):
    query = 'select count({}) from longform'.format(column)
    c.execute(query)
    result = c.fetchall()
    for e, i in enumerate(result):
        for y in enumerate(i):
            return y[1] if y[1] else 0

answered May 27, 2017 at 2:34

lmiguelvargasf

71.2k55 gold badges231 silver badges239 bronze badges

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

2 Comments

snapcrack Over a year ago

Thanks. Do you have any idea why that (and my solution) solve it while the first method I tried didn't? I looked into it more and for some reason that first method was miscounting the number of values in each row.

lmiguelvargasf Over a year ago

@snapcrack, not really sure, but as far as I know you should avoid using ? on table or column names. Optionally you can use the concatenation operator "+", but I prefer to use the format function in general.

snapcrack · Accepted Answer · 2017-05-27 02:10:40Z

0

It seems it's a problem with the way variables are handled in Python. Strangely, replacing the (?) with %s fixed it. So this:

def getmax(column):
    query = 'select count(%s) from longform' % column
    c.execute(query)
    result = c.fetchall()
    for e, i in enumerate(result):
        for y in enumerate(i):
            if not y[1]:
                return 0
            else:
                return y[1]

works, while the version of getmax posted in the question doesn't. Would be curious to know why.

answered May 27, 2017 at 2:10

snapcrack

1,8313 gold badges23 silver badges45 bronze badges

Collectives™ on Stack Overflow

UPDATE TABLE returning empty values when querying with variable

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related