ValueError: operation parameter must be str

Question

I'm trying to update some database fields using a python function to an SQLite DB. I keep getting the following error:

Below is my code. I would love to know how to update multiple columns in an sqlite db.

def updateEventData():
    ID = input('Enter ID of row you\'d like to update: ')
    eventname = input('\nPlease enter event name: ')
    startdate = input('\nPlease enter event start date: (DD/MM/YYYY): ')
    enddate = input('\nPlease enter event end date: (DD/MM/YYYY): ')
    venue = input('\nPlease enter event venue: ')

    # Sql update
    sql = """
    UPDATE event_details
    SET name, startdate, enddate, venue, (?, ?, ?, ?)
    WHERE ROWID = ?
    """, (eventname, startdate, enddate, venue, ID)
    c.execute(sql)
    conn.commit()

stacktrace please. – Jean-François Fabre Jan 23 '17 at 13:31 — Jean-François Fabre, Jan 23 '17 at 13:31

score 5 · Answer 1 · answered Jan 23 '17 at 13:31

You need to pass sql and parameter separately, not as a tuple containing sql and parameter:

sql = """
UPDATE event_details
SET name, startdate, enddate, venue, (?, ?, ?, ?)
WHERE ROWID = ?
"""
c.execute(sql, (eventname, startdate, enddate, venue, ID))
#         ^^^  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#     separated argument, not as a single argument
conn.commit()

See DB API 2 documentation, Cursor.execute(operation, parameter)

score -1 · Answer 2 · answered Jan 23 '17 at 14:07

If you are using flask, All you have to do to update multiple fields is instantiate the database class and commit the session (sessions are gonna save you loads of time check them out here)

E.g. your DB class name is Events, and the fields are event_name, start_date, end_date and venue with sessions:

def updateEventData():
    ID = input('Enter ID of row you\'d like to update: ')
    eventname = input('\nPlease enter event name: ')
    startdate = input('\nPlease enter event start date: (DD/MM/YYYY): ')
    enddate = input('\nPlease enter event end date: (DD/MM/YYYY): ')
    venue = input('\nPlease enter event venue: ')

    events = Events(event_name=eventname, start_date=startdate, end_date=enddate, venue=venue)
    events.session.commit()

To instantiate sessions, you need sqlalchemy, just import the sessionmaker from sqlalchemy.orm and create_engine then you are good to go. full code:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

class Foo(object):

    engine = create_engine('sqlite:///foo.sqlite', echo = False)

    # create new session object for queries
    Session = sessionmaker()
    Session.configure(bind=engine)
    session = Session()

    def updateEventData():
        ID = input('Enter ID of row you\'d like to update: ')
        eventname = input('\nPlease enter event name: ')
        startdate = input('\nPlease enter event start date: (DD/MM/YYYY): ')
        enddate = input('\nPlease enter event end date: (DD/MM/YYYY): ')
        venue = input('\nPlease enter event venue: ')

        events = Events(event_name=eventname, start_date=startdate, end_date=enddate, venue=venue)
        events.session.add(events)
        events.session.commit()

ValueError: operation parameter must be str

2 Answers2