CODEWITHSUNDEEP
pythonsqlalchemy
Marcin Koprek
Marcin Koprek

Reputation: 29

Error with update in SQLAlchemy

I have a problem with a code (python 3.6, SQLAlchemy 1.1.6), this one works fine:

def delete_item():
    input_id = int(input('Select ID number of item to delete: '))

    delete = create_table().delete().where(create_table().c.id == input_id)

    # Commit delete
    connection.execute(delete)

but with this one I have an error but i realy don't know why:

def update_item():
    input_id = int(input('Select ID number of item to change: '))

    # Get data from rows with selected ID number
    select_data = select(['*']).where(
        create_table().c.id == input_id)
    for row in connection.execute(select_data):
        input_name = input('New name for name: {}: '.format(row[1]))
        input_description = input(
            'New description for description: {}: '.format(row[6]))
        input_exclusion = input(
            'New exclusions for exclusion: {}: '.format(row[7]))
        # OperationalError
        update_data = update(create_table()).where(create_table().c.id == input_id).values(
            name='{}'.format(input_name),
            description='{}'.format(input_description),
            exclusion='{}'.format(input_exclusion))

        # Commit change
        connection.execute(update_data)

Traceback message:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "FROM": 
syntax error [SQL: 'UPDATE clothes_data SET name=?, description=?, 
exclusion=? FROM clothes_data WHERE clothes_data.id = ?'] [parameters:    
('new name', 'new desc', 'new excl', 92)]

create_table function:

def create_table():
    metadata = MetaData()

    # set name of table, names of columns, kind of data in columns
    clothes_data = Table('clothes_data', metadata,
                         #columns
      )

    # commit changes in data base
    metadata.create_all(engine)

    return clothes_data

Upvotes: 0

Views: 1646

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

The root of the problem is the way you create your table(s). Since your create_table() creates new metadata and a new Table instance each time you call it, SQLAlchemy considers them different entities. In general you should create your table definitions once per program.

So in

update_data = update(create_table()).\
    where(create_table().c.id == input_id).values(
        name='{}'.format(input_name),
        description='{}'.format(input_description),
        exclusion='{}'.format(input_exclusion))

the table in the WHERE clause is not the table to UPDATE, and so you've triggered a multiple table update, which SQLite does not support. The fix will depend on how you've set up your program's structure, but you could for example create a module called model, where you store your Tables and declarative classes. A quick and dirty fix would be

def update_item():
    input_id = int(input('Select ID number of item to change: '))
    select_data = select(['*']).where(create_table().c.id == input_id)
    for row in connection.execute(select_data):
        input_name = input('New name for name: {}: '.format(row[1]))
        input_description = input(
            'New description for description: {}: '.format(row[6]))
        input_exclusion = input(
            'New exclusions for exclusion: {}: '.format(row[7]))
        # CREATE ONCE
        table = create_table()
        update_data = update(table).where(table.c.id == input_id).values(
            name='{}'.format(input_name),
            description='{}'.format(input_description),
            exclusion='{}'.format(input_exclusion))    
        # Commits changes, IF autocommit is in use
        connection.execute(update_data)

but please move your tables and model classes to a module.

Upvotes: 1

Related Questions