Unable to make referential integrity work

[Shola Smith]

I am unable to make reference between two tables work using flask-sqlalchemy. Please find attached the file.

I get an error "expected string or buffer". Please, could someone tell me what I am doing wrong? And the best way to go about it. Thanks

[Mike Bayer]

On 4/10/15 4:10 PM, Shola Smith wrote:

I am unable to make reference between two tables work using flask-sqlalchemy. Please find attached the file.

I get an error "expected string or buffer". Please, could someone tell me what I am doing wrong? And the best way to go about it. Thanks

the script works fine to me, though I'm running it without the flask dependency.   Can you post the full stack trace for the error message?    Also what OS is this, as I see you are using case-sensitive identifier names for columns and MySQL has a problem with that based on platform.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Shola Smith]

Sir,

The script is able to create the tables in the database however, at the point of entering the data, it comes crashing with the following log

:

2015-04-10 22:01:53,263 INFO sqlalchemy.engine.base.Engine ()

2015-04-10 22:01:54,308 INFO sqlalchemy.engine.base.Engine COMMIT

2015-04-10 22:01:54,342 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2015-04-10 22:01:54,345 INFO sqlalchemy.engine.base.Engine INSERT INTO `User` (`FirstName`, `LastName`, `Email`, `PwdHash`) VALUES (%s, %s, %s, %s)

2015-04-10 22:01:54,345 INFO sqlalchemy.engine.base.Engine ('Coffee', 'Shop', 'cof...@shop.com', '12677563I7^54')

2015-04-10 22:01:54,351 INFO sqlalchemy.engine.base.Engine INSERT INTO `Payment` (`uidPayer`, `uidReceiver`, amount) VALUES (%s, %s, %s)

2015-04-10 22:01:54,352 INFO sqlalchemy.engine.base.Engine (<__main__.User object at 0x02FF39F0>, <__main__.User object at 0x02FFE210>, 25)

2015-04-10 22:01:54,352 INFO sqlalchemy.engine.base.Engine ROLLBACK

e = expected string or buffer

:

I'm on Windows 7. I've also changed the uppercase names to lower case but it still persists. A comprehensive error log is below:

Traceback (most recent call last):

  File "C:/Users/shola.smith/PycharmProjects/oSchool/entities/trials.py", line 54, in <module>

    db.session.commit()

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\scoping.py", line 150, in do

    return getattr(self.registry(), name)(*args, **kwargs)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\session.py", line 776, in commit

    self.transaction.commit()

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\session.py", line 377, in commit

    self._prepare_impl()

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\session.py", line 357, in _prepare_impl

    self.session.flush()

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\session.py", line 1919, in flush

    self._flush(objects)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\session.py", line 2037, in _flush

    transaction.rollback(_capture_exception=True)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__

    compat.reraise(exc_type, exc_value, exc_tb)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\session.py", line 2001, in _flush

    flush_context.execute()

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 372, in execute

    rec.execute(self)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 526, in execute

    uow

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\persistence.py", line 65, in save_obj

    mapper, table, insert)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\orm\persistence.py", line 602, in _emit_insert_statements

    execute(statement, params)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\engine\base.py", line 729, in execute

    return meth(self, multiparams, params)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\sql\elements.py", line 322, in _execute_on_connection

    return connection._execute_clauseelement(self, multiparams, params)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\engine\base.py", line 826, in _execute_clauseelement

    compiled_sql, distilled_params

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\engine\base.py", line 958, in _execute_context

    context)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\engine\base.py", line 1162, in _handle_dbapi_exception

    util.reraise(*exc_info)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\engine\base.py", line 951, in _execute_context

    context)

  File "C:\code-environs\iflask\lib\site-packages\sqlalchemy\engine\default.py", line 436, in do_execute

    cursor.execute(statement, parameters)

  File "C:\code-environs\iflask\lib\site-packages\pymysql-0.6.6-py2.7.egg\pymysql\cursors.py", line 132, in execute

    query = query % self._escape_args(args, conn)

  File "C:\code-environs\iflask\lib\site-packages\pymysql-0.6.6-py2.7.egg\pymysql\cursors.py", line 98, in _escape_args

    return tuple(conn.escape(arg) for arg in args)

  File "C:\code-environs\iflask\lib\site-packages\pymysql-0.6.6-py2.7.egg\pymysql\cursors.py", line 98, in <genexpr>

    return tuple(conn.escape(arg) for arg in args)

  File "C:\code-environs\iflask\lib\site-packages\pymysql-0.6.6-py2.7.egg\pymysql\connections.py", line 729, in escape

    return escape_item(obj, self.charset, mapping=mapping)

  File "C:\code-environs\iflask\lib\site-packages\pymysql-0.6.6-py2.7.egg\pymysql\converters.py", line 33, in escape_item

    val = encoder(val, mapping)

  File "C:\code-environs\iflask\lib\site-packages\pymysql-0.6.6-py2.7.egg\pymysql\converters.py", line 74, in escape_unicode

    return escape_str(value, mapping)

  File "C:\code-environs\iflask\lib\site-packages\pymysql-0.6.6-py2.7.egg\pymysql\converters.py", line 71, in escape_str

    return "'%s'" % escape_string(value, mapping)

  File "C:\code-environs\iflask\lib\site-packages\pymysql-0.6.6-py2.7.egg\pymysql\converters.py", line 68, in escape_string

    lambda match: ESCAPE_MAP.get(match.group(0)), value),))

TypeError: expected string or buffer

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/laklD523fhY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Mike Bayer]

On 4/10/15 5:05 PM, Shola Smith wrote:

Sir,

The script is able to create the tables in the database however, at the point of entering the data, it comes crashing with the following log

:

2015-04-10 22:01:53,263 INFO sqlalchemy.engine.base.Engine ()

2015-04-10 22:01:54,308 INFO sqlalchemy.engine.base.Engine COMMIT

2015-04-10 22:01:54,342 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2015-04-10 22:01:54,345 INFO sqlalchemy.engine.base.Engine INSERT INTO `User` (`FirstName`, `LastName`, `Email`, `PwdHash`) VALUES (%s, %s, %s, %s)

2015-04-10 22:01:54,345 INFO sqlalchemy.engine.base.Engine ('Coffee', 'Shop', 'cof...@shop.com', '12677563I7^54')

2015-04-10 22:01:54,351 INFO sqlalchemy.engine.base.Engine INSERT INTO `Payment` (`uidPayer`, `uidReceiver`, amount) VALUES (%s, %s, %s)

2015-04-10 22:01:54,352 INFO sqlalchemy.engine.base.Engine (<__main__.User object at 0x02FF39F0>, <__main__.User object at 0x02FFE210>, 25)

2015-04-10 22:01:54,352 INFO sqlalchemy.engine.base.Engine ROLLBACK

e = expected string or buffer

oh.   Sorry, yes I am getting that, the way you're catching the exception in the script threw me off (thought it succeeded because there was no stack trace).

You need to set those User objects against the relationship you've set up, "payer" and "receiver", not the FK column name uidPayer, uidReceiver:

class Payment(Base):
    __tablename__ = "Payment"
    id = Column(Integer, primary_key=True)
    uidPayer = Column(Integer, ForeignKey("User.ID"))
    uidReceiver = Column(Integer, ForeignKey("User.ID"))
    amount = Column(Float)

    def __init__(self, payer, receiver, amount):
        self.payer = payer
        self.receiver = receiver
        self.amount = amount


then you see this:

2015-04-10 18:07:49,750 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-04-10 18:07:49,751 INFO sqlalchemy.engine.base.Engine INSERT INTO `User` (`FirstName`, `LastName`, `Email`, `PwdHash`) VALUES (%s, %s, %s, %s)
2015-04-10 18:07:49,751 INFO sqlalchemy.engine.base.Engine ('Coffee', 'Shop', 'cof...@shop.com', '12677563I7^54')
2015-04-10 18:07:49,752 INFO sqlalchemy.engine.base.Engine INSERT INTO `User` (`FirstName`, `LastName`, `Email`, `PwdHash`) VALUES (%s, %s, %s, %s)
2015-04-10 18:07:49,752 INFO sqlalchemy.engine.base.Engine ('Coder', 'Shop', 'co...@shop.com', '1254677563I7^')
2015-04-10 18:07:49,753 INFO sqlalchemy.engine.base.Engine INSERT INTO `Payment` (`uidPayer`, `uidReceiver`, amount) VALUES (%s, %s, %s)
2015-04-10 18:07:49,753 INFO sqlalchemy.engine.base.Engine (1, 2, 25)
2015-04-10 18:07:49,754 INFO sqlalchemy.engine.base.Engine COMMIT


In the logged parameters you should make sure you see numbers and strings, not things like <User object at 0xXYZ> that can't be passed to the database adapter directly.

[Shola Smith]

Yes, it did work! Thanks for referring me to this group and keeping to your word of helping out.