Re: [sqlalchemy] How To Insert Relationship-Object Data Into DB?
On Feb 4, 2014, at 8:20 PM, Jude Lucien wrote:
>
>
> I am using SQLAlchemy 0.9 with Python 2.7.6 and Flask.
>
> I receive form data, place it into object and add the objects to the session.
> When I try to do a db.commit() I get a rollback which seems to due to the
> relationship object in the INSERT statement. The SQL error is as follows:
>
> INFO sqlalchemy.engine.base.Engine INSERT INTO site (name, address1,
> address2, postcode, city, fk_country_id, fk_school_id) VALUES (%(name)s,
> %(address1)s, %(address2)s, %(postcode)s, %(city)s, %(fk_country_id)s,
> %(fk_school_id)s) RETURNING site.id
>
> INFO sqlalchemy.engine.base.Engine {'city': u'New York', 'fk_school_id':
> ,
> 'name': u'Site1', 'address1': u'2 York Way', 'address2': u'', 'postcode':
> u'12345', 'fk_country_id': u'2'}
>
> INFO sqlalchemy.engine.base.Engine ROLLBACK (ProgrammingError) can't adapt
> type 'InstrumentedAttribute'
>
> The relationship code in the model is as follows:
>
> class Site(db.Model):
> id = db.Column(db.Integer, primary_key = True)
> school = db.relationship('School', backref = 'site', uselist = False)
>
> What is it about the relationship object that causes the rollback? I am new
> to SQLAlchemy and so have followed what it says in the documentation
> regarding relationships.
>
> Both of the data types in the models (Site, School) are ints.
>
>
there’s nothing wrong with the configuration there, the error has to do with
the wrong kind of object being assigned to a database row, in this case the
class-bound attribute.
So basically this:
class Site(Base):
# …
fk_school_id = Column(Integer, ForeignKey(…))
class School(Base):
id = Column(Integer, primary_key=True)
site = Site()
site.fk_school_id = School.id # <— incorrect, School is a class, not an
instance of School
session.add(site)
session.commit()
a class-bound attribute like “School.id” is an InstrumentedAttribute. your
intent here is to assign an integer value to fk_school_id.
But also, when using relationship() you will have an easier time if you work
with objects rather than foreign key attributes:
some_school = School()
site.school = some_school
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] How To Insert Relationship-Object Data Into DB?
I am using SQLAlchemy 0.9 with Python 2.7.6 and Flask.
I receive form data, place it into object and add the objects to the
session. When I try to do a db.commit() I get a rollback which seems to due
to the relationship object in the INSERT statement. The SQL error is as
follows:
INFO sqlalchemy.engine.base.Engine INSERT INTO site (name, address1,
address2, postcode, city, fk_country_id, fk_school_id) VALUES (%(name)s,
%(address1)s, %(address2)s, %(postcode)s, %(city)s, %(fk_country_id)s,
%(fk_school_id)s) RETURNING site.id
INFO sqlalchemy.engine.base.Engine {'city': u'New York', 'fk_school_id':
,
'name': u'Site1', 'address1': u'2 York Way', 'address2': u'', 'postcode':
u'12345', 'fk_country_id': u'2'}
INFO sqlalchemy.engine.base.Engine ROLLBACK (ProgrammingError) can't adapt
type 'InstrumentedAttribute'
The relationship code in the model is as follows:
class Site(db.Model):
id = db.Column(db.Integer, primary_key = True)
school = db.relationship('School', backref = 'site', uselist = False)
What is it about the relationship object that causes the rollback? I am new
to SQLAlchemy and so have followed what it says in the documentation
regarding relationships.
Both of the data types in the models (Site, School) are ints.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] sqlalchemy 0.9.2 + pymysql 0.6.1 + python 3.3 - traceback ?
вторник, 4 февраля 2014 г., 9:36:20 UTC+2 пользователь Alexander Belchenko написал: > > понедельник, 3 февраля 2014 г., 19:13:04 UTC+2 пользователь Michael Bayer > написал: >> >> >> On Feb 3, 2014, at 10:51 AM, Alexander Belchenko >> wrote: >> >> this is a Py3k-specific issue, ticket >> http://www.sqlalchemy.org/trac/ticket/2933 has been created and the >> issue is fixed in 8b08b1a35b85c24349226c34e6. >> >> > Thank you for quick answer and bugfix. I'm going to get latest sources and > try again. > It works for me now. Many thanks! -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] AttributeError: 'CompositeProperty' object has no attribute 'props'
Cool, thanks!
On Mon, Feb 3, 2014 at 7:14 PM, Michael Bayer wrote:
> just call configure_mappers() for now, and the need for that step has been
> removed in b069127b2d3f7b3f2c27f91cf,
> http://www.sqlalchemy.org/trac/ticket/2935.
>
>
> On Feb 3, 2014, at 4:12 PM, Matthew Phipps
> wrote:
>
> Hi SQLAlchemy,
>
> On SQLAlchemy 0.9.2, if I construct a query selecting a composite property
> before constructing any other queries, I see this error:
>
> Traceback (most recent call last):
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/nose/case.py",
> line 197, in runTest
> self.test(*self.arg)
> File "/media/psf/vagrant/test_configure_mappers.py", line 47, in
> test_composite_prop_query
> user_login_query = Session.query(User.login)
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py",
> line 149, in do
> return getattr(self.registry(), name)(*args, **kwargs)
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
> line 1151, in query
> return self._query_cls(entities, self, **kwargs)
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
> line 106, in __init__
> self._set_entities(entities)
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
> line 114, in _set_entities
> entity_wrapper(self, ent)
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
> line 3338, in __init__
> column = column._query_clause_element()
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py",
> line 150, in _query_clause_element
> return self.comparator._query_clause_element()
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py",
> line 407, in _query_clause_element
> return CompositeProperty.CompositeBundle(self.prop,
> self.__clause_element__())
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py",
> line 404, in __clause_element__
> return expression.ClauseList(group=False, *self._comparable_elements)
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py",
> line 689, in __get__
> obj.__dict__[self.__name__] = result = self.fget(obj)
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py",
> line 419, in _comparable_elements
> return self.prop._comparable_elements
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py",
> line 689, in __get__
> obj.__dict__[self.__name__] = result = self.fget(obj)
> File
> "/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py",
> line 236, in _comparable_elements
> for prop in self.props
> AttributeError: 'CompositeProperty' object has no attribute 'props'
>
> I've written a nose test module that exposes this behavior. Note that no
> SQL is ever actually issued (AFAIK?).
>
> import logging
>
> from sqlalchemy import create_engine, Column, Integer, String
> from sqlalchemy.orm import composite, sessionmaker, configure_mappers,
> scoped_session
> from sqlalchemy.ext.declarative import declarative_base
>
> engine = create_engine('sqlite:///:memory:')
> session_factory = sessionmaker(bind=engine)
> Session = scoped_session(session_factory)
> Base = declarative_base()
> logging.basicConfig()
> logging.getLogger('sqlalchemy.orm').setLevel(logging.INFO)
>
>
> class Login(object):
>
> def __init__(self, name, password):
> self.name = name
> self.password = password
>
> def __composite_values__(self):
> return self.name, self.password
>
>
> class User(Base):
> __tablename__ = 'users'
>
> id = Column(Integer, primary_key=True)
> name = Column(String)
> fullname = Column(String)
> password = Column(String)
>
> login = composite(Login, name, password)
>
> def __repr__(self):
>return "" % (
> self.name, self.fullname, self.password)
>
>
> class TestConfigureMappers(object):
>
> def tearDown(self):
> Session.remove()
>
> # This fails
> def test_composite_prop_query(self):
> user_login_query = Session.query(User.login)
>
> # This works
> def test_composite_prop_query_configuring_first(self):
>
> # Either of these two lines will suffice
> user_query = Session.query(User)
> #configure_mappers()
>
> user_login_query = Session.query(User.login)
> user_login = user_login_query
>
> Is this expected behavior? I figure that configure_mappers() must be
> exposed publicly for a
Re: [sqlalchemy] SQLite: OperationalError when decoding 0x92 in TEXT column
I've not done much with reflection, but perhaps you could use the column_reflect event: http://docs.sqlalchemy.org/en/rel_0_8/core/events.html#sqlalchemy.events.DDLEvents.column_reflect Simon On Tue, Feb 4, 2014 at 11:28 AM, Erich Blume wrote: > Thanks Simon, > > Do you know how I might use that with reflection? There's several hundred of > these columns, I'd hate to have to override each one individually - that > sort of defeats the purpose of reflection. > > One thought I just had was perhaps I could subclass the Text type and then > override the ischema_names for SQLite for TEXT type. That'd do the trick, I > suspect! > > > On Tue, Feb 4, 2014 at 3:26 AM, Simon King wrote: >> >> On Tue, Feb 4, 2014 at 10:15 AM, Erich Blume >> wrote: >> > I am working on a binding to a SQLite database that I do not control the >> > creation of, with the aid of reflection. I'm running in to what I >> > believe >> > are very basic UTF-8 decoding errors. For instance, a TEXT cell has the >> > byte >> > '0x92' in it and is causing an OperationalError. Presumably, this is >> > because >> > 0x92 (by itself) is not a valid encoding for any Unicode code point. I >> > would >> > prefer that the decoding from UTF-8 to be forced, perhaps by dropping >> > the >> > bad byte. How can I do this? >> > >> > The database has a table with a column called 'description', which is of >> > type TEXT. The "PRAGMA encoding" is left at 'UTF-8', thank goodness. One >> > of >> > the rows, however, contains within its otherwise ascii byte contents the >> > singleton byte '0x92'. Based on the context of the sentence, it seems >> > that >> > this was intended to be encoded as a single quotation mark, some >> > googling >> > suggests 'RIGHT SINGLE QUOTATION MARK' in unicode, which is '0xE2 0x80 >> > 0x99'. I gather that MSSQL (which was the original source of the data in >> > this database) uses Microsofts' infernal web encodings sometimes and >> > that is >> > probably the source of this byte. >> > >> > The issue is this: I really need to read this data! It would be *ideal* >> > to >> > have the aid of something like python's 'replace' decoding handler but >> > failing that just eliding the byte would do fine in a pinch. >> > >> > When fetching this row in Python 3.3 with SQLAlchemy 0.9.1 my session >> > looks >> > vaguely like this (with the text and stack trace truncated out for >> > brevity). >> > >> > File >> > >> > "/usr/local/Cellar/python3/3.3.3/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/sqlalchemy/engine/result.py", >> > line 760, in >> > return [process_row(metadata, row, processors, keymap) >> > sqlalchemy.exc.OperationalError: (OperationalError) Could not >> > decode >> > to UTF-8 column 'description' with text <...> >> > >> > Is there some way to accomplish this? >> > >> >> The String-related column types have a "unicode_error" parameter which >> sounds like it might be what you want: >> >> >> http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#sqlalchemy.types.String.params.unicode_error >> >> Note the various warnings around it though... >> >> Hope that helps, >> >> Simon >> >> -- >> 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/T--Ftk5EVZg/unsubscribe. >> To unsubscribe from this group and all its topics, send an email to >> sqlalchemy+unsubscr...@googlegroups.com. >> >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/groups/opt_out. > > > -- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SQLite: OperationalError when decoding 0x92 in TEXT column
Thanks Simon, Do you know how I might use that with reflection? There's several hundred of these columns, I'd hate to have to override each one individually - that sort of defeats the purpose of reflection. One thought I just had was perhaps I could subclass the Text type and then override the ischema_names for SQLite for TEXT type. That'd do the trick, I suspect! On Tue, Feb 4, 2014 at 3:26 AM, Simon King wrote: > On Tue, Feb 4, 2014 at 10:15 AM, Erich Blume > wrote: > > I am working on a binding to a SQLite database that I do not control the > > creation of, with the aid of reflection. I'm running in to what I believe > > are very basic UTF-8 decoding errors. For instance, a TEXT cell has the > byte > > '0x92' in it and is causing an OperationalError. Presumably, this is > because > > 0x92 (by itself) is not a valid encoding for any Unicode code point. I > would > > prefer that the decoding from UTF-8 to be forced, perhaps by dropping the > > bad byte. How can I do this? > > > > The database has a table with a column called 'description', which is of > > type TEXT. The "PRAGMA encoding" is left at 'UTF-8', thank goodness. One > of > > the rows, however, contains within its otherwise ascii byte contents the > > singleton byte '0x92'. Based on the context of the sentence, it seems > that > > this was intended to be encoded as a single quotation mark, some googling > > suggests 'RIGHT SINGLE QUOTATION MARK' in unicode, which is '0xE2 0x80 > > 0x99'. I gather that MSSQL (which was the original source of the data in > > this database) uses Microsofts' infernal web encodings sometimes and > that is > > probably the source of this byte. > > > > The issue is this: I really need to read this data! It would be *ideal* > to > > have the aid of something like python's 'replace' decoding handler but > > failing that just eliding the byte would do fine in a pinch. > > > > When fetching this row in Python 3.3 with SQLAlchemy 0.9.1 my session > looks > > vaguely like this (with the text and stack trace truncated out for > brevity). > > > > File > > > "/usr/local/Cellar/python3/3.3.3/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/sqlalchemy/engine/result.py", > > line 760, in > > return [process_row(metadata, row, processors, keymap) > > sqlalchemy.exc.OperationalError: (OperationalError) Could not > decode > > to UTF-8 column 'description' with text <...> > > > > Is there some way to accomplish this? > > > > The String-related column types have a "unicode_error" parameter which > sounds like it might be what you want: > > > http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#sqlalchemy.types.String.params.unicode_error > > Note the various warnings around it though... > > Hope that helps, > > Simon > > -- > 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/T--Ftk5EVZg/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SQLite: OperationalError when decoding 0x92 in TEXT column
On Tue, Feb 4, 2014 at 10:15 AM, Erich Blume wrote: > I am working on a binding to a SQLite database that I do not control the > creation of, with the aid of reflection. I'm running in to what I believe > are very basic UTF-8 decoding errors. For instance, a TEXT cell has the byte > '0x92' in it and is causing an OperationalError. Presumably, this is because > 0x92 (by itself) is not a valid encoding for any Unicode code point. I would > prefer that the decoding from UTF-8 to be forced, perhaps by dropping the > bad byte. How can I do this? > > The database has a table with a column called 'description', which is of > type TEXT. The "PRAGMA encoding" is left at 'UTF-8', thank goodness. One of > the rows, however, contains within its otherwise ascii byte contents the > singleton byte '0x92'. Based on the context of the sentence, it seems that > this was intended to be encoded as a single quotation mark, some googling > suggests 'RIGHT SINGLE QUOTATION MARK' in unicode, which is '0xE2 0x80 > 0x99'. I gather that MSSQL (which was the original source of the data in > this database) uses Microsofts' infernal web encodings sometimes and that is > probably the source of this byte. > > The issue is this: I really need to read this data! It would be *ideal* to > have the aid of something like python's 'replace' decoding handler but > failing that just eliding the byte would do fine in a pinch. > > When fetching this row in Python 3.3 with SQLAlchemy 0.9.1 my session looks > vaguely like this (with the text and stack trace truncated out for brevity). > > File > "/usr/local/Cellar/python3/3.3.3/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/sqlalchemy/engine/result.py", > line 760, in > return [process_row(metadata, row, processors, keymap) > sqlalchemy.exc.OperationalError: (OperationalError) Could not decode > to UTF-8 column 'description' with text <...> > > Is there some way to accomplish this? > The String-related column types have a "unicode_error" parameter which sounds like it might be what you want: http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#sqlalchemy.types.String.params.unicode_error Note the various warnings around it though... Hope that helps, Simon -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] SQLite: OperationalError when decoding 0x92 in TEXT column
I am working on a binding to a SQLite database that I do not control the creation of, with the aid of reflection. I'm running in to what I believe are very basic UTF-8 decoding errors. For instance, a TEXT cell has the byte '0x92' in it and is causing an OperationalError. Presumably, this is because 0x92 (by itself) is not a valid encoding for any Unicode code point. I would prefer that the decoding from UTF-8 to be forced, perhaps by dropping the bad byte. How can I do this? The database has a table with a column called 'description', which is of type TEXT. The "PRAGMA encoding" is left at 'UTF-8', thank goodness. One of the rows, however, contains within its otherwise ascii byte contents the singleton byte '0x92'. Based on the context of the sentence, it seems that this was intended to be encoded as a single quotation mark, some googling suggests 'RIGHT SINGLE QUOTATION MARK' in unicode, which is '0xE2 0x80 0x99'. I gather that MSSQL (which was the original source of the data in this database) uses Microsofts' infernal web encodings sometimes and that is probably the source of this byte. The issue is this: I really need to read this data! It would be *ideal* to have the aid of something like python's 'replace' decoding handler but failing that just eliding the byte would do fine in a pinch. When fetching this row in Python 3.3 with SQLAlchemy 0.9.1 my session looks vaguely like this (with the text and stack trace truncated out for brevity). File "/usr/local/Cellar/python3/3.3.3/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/sqlalchemy/engine/result.py", line 760, in return [process_row(metadata, row, processors, keymap) sqlalchemy.exc.OperationalError: (OperationalError) Could not decode to UTF-8 column 'description' with text <...> Is there some way to accomplish this? Thanks! -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
