invalid input syntax for integer (string)

Question

I am trying to run a small app on apache2 server, the original database I used was Sqlite and now I am switching to Postgresql. The project uses Flask and Sqlalchemy and I get 500 Internal Error and error log says invalid input syntax for integer (items in name Column in Categories).

I have narrowed down the root of the problem down to the following lines but currently unable to solve the problem. addItem(DBSampleData.SampleItem)

def addItem(item):
    for i in item:
        items = Item(name=i[0], price=i[1], description=i[2],
                     category_ID=i[3], user_ID=i[4])
        session.add(items)
        session.commit

One of the SampleItem is:

    ('Maelstrom','2800',
     'A hammer forged for the gods themselves, '
     'Maelstrom allows its user to harness the '
     'power of lightning.',
     'Attack Speed',"guberland")

and the Categories DB setup part is:

class User(Base):
    __tablename__ = 'user'
    ID = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)

class Categories(Base):
    __tablename__ = 'categories'
    ID = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)


class Item(Base):
    __tablename__ = 'item'
    ID = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
    description = Column(String(250))
    price = Column(String(8))
    category_ID = Column(Integer, ForeignKey('categories.ID'))
    category = relationship(Categories)
    user_ID = Column(Integer, ForeignKey('user.ID'))
    user = relationship(User)

The part of error log from apache2 is:

[Tue Oct 31 16:17:18.814754 2017] [wsgi:error] [pid 1631:tid 140448423360256] [client 75.155.173.141:51827] psycopg2.DataError: invalid input syntax for integer: “Attack Damage”

where the "Attack Damage" is the Categories.name Thanks in advance!

you need to pass ID to Categoriessince you don't have ID set to auto-increment. Also, you forgot the parenthesis on session.commit() — kstullich
@kstullich I tried to change the code to ID = Column(Integer, primary_key=True,autoincrement =True) at the moment but still unable to solve the issue — guberland
Please do not post essential information in private forum posts. Please resubmit the traceback as an edit to this question. — univerio
@kstullich Regarding autoincrement: "The default value is the string "auto" which indicates that a single-column primary key that is of an INTEGER type with no stated client-side or python-side defaults should receive auto increment semantics automatically", which applies here. — Ilja Everilä

knitti knitti · Accepted Answer · 2017-11-03T08:11:37

I wouldn't have believed it, then I created a MCVE with sqlite, and it didn't raise an error. But it created an invalid database, as you can see here

Obviously "Attack Speed" is not a category_ID and "guberland" is not an user_ID. You need either provide the ID to an existing object, or, much nicer for CRUD applications, a database object for the relationship:

db_item = Item(
    name=i[0], price=i[1], description=i[2], 
    category=Categories(name=i[3]), user=User(name=i[4]))

which works well and also creates the appropriate entries in the user and categories table.

Note: you have to call the commit() method of session.

invalid input syntax for integer (string)

1 Answers