CODEWITHSUNDEEP
pythonsqlpython-3.xsqlite
snapcrack
snapcrack

Reputation: 1811

UPDATE TABLE returning empty values when querying with variable

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.

Upvotes: 1

Views: 57

Answers (2)

lmiguelvargasf
lmiguelvargasf

Reputation: 69755

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

Upvotes: 1

snapcrack
snapcrack

Reputation: 1811

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.

Upvotes: 0

Related Questions