[sqlalchemy] SQLite and Dates
I have the following code. Simple Calendar and list of holidays. If I try and use a Date and datetime.date I get an error as follows Traceback (most recent call last): File H:\workspace\Test\src\stest.py, line 78, in module lon.holidays = [Holiday(lon, holiday=datetime.date('2011-01-01'),description='New Year'), Holiday(lon, holiday=datetime.date('2011-12-25'),description=xmas)] TypeError: an integer is required If I change it to use a string/String for the date, its fine. I know there is an issue with Date and Sqlite but I can't fix it. The String version is commented out in the holiday_tables code, and it works Any help appreciated = from sqlalchemy import create_engine from sqlalchemy import Table, Column, Date , Integer, String, MetaData, ForeignKey from sqlalchemy.orm import relationship, backref from sqlalchemy.orm import mapper from sqlalchemy.orm import sessionmaker import datetime engine = create_engine('sqlite:///:memory:', echo=True) Session = sessionmaker() Session.configure(bind=engine) session = Session() metadata = MetaData() class Calendar (object): def __init__(self, name, fullname): self.name = name self.fullname = fullname def __repr__(self): return Calendar('%s','%s') % (self.name, self.fullname) class Holiday (object): def __init__(self, holiday, description): self.holiday = holiday self.description = description def __repr__(self): return Holiday('%s', '%s') % (self.holiday, self.description) calendars_table = Table \ ( 'calendars', metadata, Column('id', Integer, primary_key=True), Column('name', String), Column('fullname', String) ) holidays_table = Table \ ( 'holidays', metadata, Column('id', Integer, primary_key=True), #Column('holiday', String, nullable=False), Column('holiday', Date, nullable=False), Column('description', String), Column('calendar_id', Integer, ForeignKey('calendars.id')) ) metadata.create_all(engine) mapper \ ( Calendar, calendars_table, properties= \ { 'holidays': relationship \ ( Holiday, backref='calendar', cascade=all, delete, delete-orphan, lazy='joined' ) } ) mapper(Holiday, holidays_table) session.commit() lon = Calendar('LON', 'London') #lon.holidays = [Holiday(holiday='2011-01-01',description='New Year'), Holiday(holiday='2011-12-25',description=xmas)] session.add(lon) session.commit() lon.holidays = [Holiday(lon, holiday=datetime.date('2011-01-01'),description='New Year'), Holiday(lon, holiday=datetime.date('2011-12-25'),description=xmas)] session.commit() lon = session.query(Calendar).filter_by(name='LON').one() session.delete(lon) session.commit() = -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: spanning relationship on 3 tables fails
I think it was missing a key in foreign_keys: mapper(Sensor, sensors, properties={ 'data': relationship(Data, backref='sensor', foreign_keys=[data.c.id_meas, acquisitions.c.id_cu], primaryjoin=and_(sensors.c.id_meas==data.c.id_meas, data.c.id_acq==acquisitions.c.id, acquisitions.c.id_cu==sensors.c.id_cu), cascade='all, delete-orphan', single_parent=True) }) Now it seems work as expected, thanks. On Feb 28, 1:03 pm, neurino neur...@gmail.com wrote: Sorry if I resume this after two months but I think there's a bug in cascade deletion of the relationship you suggested me: mapper(Sensor, sensors, properties={ 'data': relationship(Data, backref='sensor', foreign_keys=[data.c.id_meas], primaryjoin=and_(sensors.c.id_meas==data.c.id_meas, data.c.id_acq==acquisitions.c.id, acquisitions.c.id_cu==sensors.c.id_cu), cascade='all, delete-orphan', single_parent=True) }) since, on a cascade delete of a Sensor sqlalchemy issues this query: SELECT data.id AS data_id, data.id_acq AS data_id_acq, data.id_meas AS data_id_meas, data.id_elab AS data_id_elab, data.value AS data_value FROM data, acquisitions, sensors WHERE ? = data.id_meas AND data.id_acq = acquisitions.id AND acquisitions.id_cu = sensors.id_cu (1,) DELETE FROM data WHERE data.id = ? that's going to delete all data with id_meas = 1 while it should be SELECT data.id AS data_id, data.id_acq AS data_id_acq, data.id_meas AS data_id_meas, data.id_elab AS data_id_elab, data.value AS data_value FROM data, acquisitions, sensors WHERE ? = data.id_meas AND ? = acquisitions.id_cu AND data.id_acq = acquisitions.id AND acquisitions.id_cu = sensors.id_cu (1, 3) DELETE FROM data WHERE data.id = ? with the `AND ? = acquisitions.id_cu` part added because Sensor has a composite primary key (id_cu, id_meas). I know it's a rare situation so I have no problems in removing cascade and doing deletions on my own but I'd like to be sure it's not a fault of mine but a bug. Thanks for your support. On Dec 30 2010, 5:45 pm, Michael Bayer mike...@zzzcomputing.com wrote: this is again my error messages not telling the whole story, ill see if i can get the term foreign_keys back in there: mapper(Sensor, sensors, properties={ 'data': relationship(Data, backref='sensor', foreign_keys=[data.c.id_meas], primaryjoin=and_(sensors.c.id_meas==data.c.id_meas, data.c.id_acq==acquisitions.c.id, acquisitions.c.id_cu==sensors.c.id_cu), cascade='all, delete-orphan', single_parent=True) }) or mapper(Sensor, sensors, properties={ 'data': relationship(Data, backref='sensor', foreign_keys=[sensors.id_meas], primaryjoin=and_(sensors.c.id_meas==data.c.id_meas, data.c.id_acq==acquisitions.c.id, acquisitions.c.id_cu==sensors.c.id_cu), cascade='all, delete-orphan', single_parent=True) }) depending on if this is one-to-many or many-to-one. A relationship like this is really better off as a viewonly=True since populating it is not going to add rows to the acquisitions table. On Dec 30, 2010, at 10:15 AM,neurinowrote: data = Table('data', metadata, Column('id', Integer, primary_key=True), Column('id_acq', Integer, ForeignKey('acquisitions.id'), nullable=False), Column('id_meas', Integer, nullable=False), Column('value', Float, nullable=True), ) acquisitions = Table('acquisitions', metadata, Column('id', Integer, primary_key=True), Column('id_cu', Integer, ForeignKey('ctrl_units.id'), nullable=False), Column('datetime', DateTime, nullable=False), ) sensors = Table('sensors', metadata, Column('id_cu', Integer, ForeignKey('ctrl_units.id'), primary_key=True, autoincrement=False), Column('id_meas', Integer, primary_key=True, autoincrement=False), Column('name', Unicode(20), nullable=False), Column('desc', Unicode(40), nullable=False), ) ctrl_units = Table('ctrl_units', metadata, Column('id', Integer, primary_key=True, autoincrement=False), Column('desc', Unicode(40), nullable=False) ) and this mapping: ... orm.mapper(Sensor, sensors, properties={ 'data': orm.relationship(Data, backref='sensor', primaryjoin=and_(sensors.c.id_meas==data.c.id_meas, data.c.id_acq==acquisitions.c.id, acquisitions.c.id_cu==sensors.c.id_cu), cascade='all, delete-orphan', single_parent=True) }) ... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group,
Re: [sqlalchemy] Re: Extending sqlalchemy.schema.Column and metaprogramming traps
Hi, I know this is an OLD threat but I was searching the group to see If I was not the first one doing this. I am not sure I understand very well what this threat is all about, but I want to extend the Column class for a different reason. I want to add extra functionality to the Column class which is absolutely NOT SA related. SA functionality should not be effected though. say I want to add a config value and some methods for rendering and validating screens: def MyColumn(Column): def __init(): dosomething to init def ExtraInfo(self): do_something_not_sa_related validation = 'someregex' and use MyColumn in places where I normally use Column(..) What do I need to take into account, I've done some tests and Error hell broke loose, where the errors are hidden deep inside SA so hard to overcome. Martijn On Dec 11, 2008, at 16:20 , Michael Bayer wrote: On Dec 11, 2008, at 3:37 AM, Angri wrote: Here it is: http://www.sqlalchemy.org/trac/ticket/1244 Maybe it is good idea to drop some new lines in faq? Something like this: Q: How should I extend sqlalchemy.schema.Column? A: You surely dont need it. Recommended way to achive your possible needs is to write instance-factory function which decorates creation of sqlalchemy.schema.Column instances. Q: But I'm really need it! A: Ok. To subclass Column, this is the current recipe: from sqlalchemy.sql.util import Annotated, annotated_classes class MyColumn(Column): ... class AnnotatedMyColumn(Annotated, MyColumn): pass annotated_classes[MyColumn] = AnnotatedMyColumn Do not forget to put AnnotatedMyColumn in the module namespace, or your schema will not be pickleable! Correct me please if I am wrong somewhere and excuse me for my English. Well the AnnotatedMyColumn part is less than ideal since its an internal. the way that works could very likely change. Creating an AnnotatedXXX class *can* be automated. the pickle thing just might be a caveat we'd document or arrange for an exception to occur (like putting a throw in a __getstate__ method). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Sqlalchemy+Kinterbasdb installation error
Hi everyone, I'm trying to access a Firebird (version 2.5) database through Kinterbasdb under windows 7 with python 2.6. I'have download the file: kinterbasdb-3.3.0.win32-py2.6.msi which can be found at this link: http://www.firebirdsql.org/index.php?op=develsub=python, but when I try to install it I get the following errors: - Could not create: kinterbasdb-py2.6 - Could not set key value: python 2.6 kinterbas-3.3.0 - Could not set key value:C:\Python26\removekinterbasdb.exe - Could not set key value:C:\Python26\kinterbasdb-wininst.log I'm currently running python 2.6 win32 on a 64 bit CPU. The installed Firebird version is 64bit too. Maybe is it a compatibility issue? I also tried kinterbasdb-3.3.0.win-amd64-py2.6.msi, but it didn't work. Any suggestions? Thanks in advance. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] bad sql generated for filter_list using firebird
generated sql contains invalid reference to table NONE I don't know where the problem is in Camelot or Sqlalchemy.(latest 0.6 trunk) I am using a Firebird database. I think that the sql derives from the following statements col = getattr( admin.entity, field_name ) query = select([col], distinct=True, order_by=col.asc()).select_from(table) ERROR:camelot.view.model_thread.signal_slot_model_thread:exception caught in model thread while executing get_filters_and_actions - TableView.set_filters_and_actions Traceback (most recent call last): File /var/git/camelot/camelot/view/model_thread/ signal_slot_model_thread.py, line 57, in execute result = self._request() File /var/git/camelot/camelot/view/controls/tableview.py, line 559, in get_filters_and_actions return ( admin.get_filters(), admin.get_list_actions() ) File /var/git/camelot/camelot/view/model_thread/__init__.py, line 57, in wrapper return original_function(*args, **kwargs) File /var/git/camelot/camelot/admin/entity_admin.py, line 364, in get_filters return list(filter_generator()) File /var/git/camelot/camelot/admin/entity_admin.py, line 362, in filter_generator yield (filter, filter.get_name_and_options(self)) File /var/git/camelot/camelot/view/filters.py, line 112, in get_name_and_options for value in session.execute(query): File /usr/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py, line 139, in do return getattr(self.registry(), name)(*args, **kwargs) File /usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py, line 724, in execute clause, params or {}) File /usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py, line 1191, in execute params) File /usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py, line 1271, in _execute_clauseelement return self.__execute_context(context) File /usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py, line 1302, in __execute_context context.parameters[0], context=context) File /usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py, line 1401, in _cursor_execute context) File /usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py, line 1394, in _cursor_execute context) File /usr/lib/python2.7/site-packages/sqlalchemy/dialects/firebird/ base.py, line 680, in do_execute cursor.execute(statement, parameters or []) ProgrammingError: (ProgrammingError) (-204, 'isc_dsql_prepare: \n Dynamic SQL Error\n SQL error code = -204\n Table unknown\n NONE\n At line 2, column 19') 'SELECT DISTINCT assets.yr \nFROM assets, None ORDER BY assets.yr ASC' () the table was defined as create domain D_CURR char (3) default 'EUR' not null; create domain D_MONEY decimal(10,2) default 0 not null; recreate table assets( id integer not null, yr smallint not null, cost d_money not null, curr d_curr not null, residual d_money not null, class integer not null, realised d_money default 0, constraint pk_asset primary key(id) ); -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] UnboundExecutionError: text query with elixir
Hello, I'm trying to run the following query with elixir entities: elixir.metadata.bind = 'mysql://root:root@localhost/mutdb' elixir.metadata.bind.echo = True elixir.setup_all(True) t = text(select * from mutations) result = elixir.session.connection().execute(t) and I got: sqlalchemy.exc.UnboundExecutionError: This session is not bound to a single Engine or Connection, and no context was provided to locate a binding. I though elixir does all the initialization with setup_all(), what else is needed to run queries like this? Normal mapped queries like Mutation.query.all() work normally. thanks david -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] stucco_evolution 0.33 released!
stucco_evolution is my entry into the crowded 'schema migration' space. For people that like writing their own ALTER TABLE statements, stucco_evolution only concerns itself with passing a SQLAlchemy connection to numbered Python scripts in an evolve/ package, maintaining a (name, number) tuple for each managed package. stucco_evolution is 200 lines of code with automated tests that provide 100% statement coverage. It works well for me, but the functions in the API do not have very good names. Suggestions welcome. Daniel Holth -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Extending sqlalchemy.schema.Column and metaprogramming traps
Well, it sounds like you're taking the wrong approach to me. I'd subclass your ORM objects. Add some simple hooks so that you can use the built in dictionary mixin. Then override update method to apply the validators. Something like this. class ValidatorAspect: validators = {} @classmethod def add_validator(): @classmethod def del_validator(): from UserDict import DictMixin class MyDataObject(Base,ValidatorAspect,DictMixin): def __getitem__(): def __setitem__(): def __delitem__(): def keys(): def update(): #Loop over inputs #Apply validator if present My $.02 Sean On Mon, Feb 28, 2011 at 10:17 AM, Martijn Moeling mart...@xs4us.nu wrote: Hi, I know this is an OLD threat but I was searching the group to see If I was not the first one doing this. I am not sure I understand very well what this threat is all about, but I want to extend the Column class for a different reason. I want to add extra functionality to the Column class which is absolutely NOT SA related. SA functionality should not be effected though. say I want to add a config value and some methods for rendering and validating screens: def MyColumn(Column): def __init(): dosomething to init def ExtraInfo(self): do_something_not_sa_related validation = 'someregex' and use MyColumn in places where I normally use Column(..) What do I need to take into account, I've done some tests and Error hell broke loose, where the errors are hidden deep inside SA so hard to overcome. Martijn On Dec 11, 2008, at 16:20 , Michael Bayer wrote: On Dec 11, 2008, at 3:37 AM, Angri wrote: Here it is: http://www.sqlalchemy.org/trac/ticket/1244 Maybe it is good idea to drop some new lines in faq? Something like this: Q: How should I extend sqlalchemy.schema.Column? A: You surely dont need it. Recommended way to achive your possible needs is to write instance-factory function which decorates creation of sqlalchemy.schema.Column instances. Q: But I'm really need it! A: Ok. To subclass Column, this is the current recipe: from sqlalchemy.sql.util import Annotated, annotated_classes class MyColumn(Column): ... class AnnotatedMyColumn(Annotated, MyColumn): pass annotated_classes[MyColumn] = AnnotatedMyColumn Do not forget to put AnnotatedMyColumn in the module namespace, or your schema will not be pickleable! Correct me please if I am wrong somewhere and excuse me for my English. Well the AnnotatedMyColumn part is less than ideal since its an internal. the way that works could very likely change. Creating an AnnotatedXXX class *can* be automated. the pickle thing just might be a caveat we'd document or arrange for an exception to occur (like putting a throw in a __getstate__ method). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLite and Dates
On Feb 28, 2011, at 8:49 AM, Nickle wrote: I have the following code. Simple Calendar and list of holidays. If I try and use a Date and datetime.date I get an error as follows Traceback (most recent call last): File H:\workspace\Test\src\stest.py, line 78, in module lon.holidays = [Holiday(lon, holiday=datetime.date('2011-01-01'),description='New Year'), Holiday(lon, holiday=datetime.date('2011-12-25'),description=xmas)] TypeError: an integer is required that's Python datetime. the usage is: datetime.date(2011, 12, 25) If I change it to use a string/String for the date, its fine. I know there is an issue with Date and Sqlite but I can't fix it. The String version is commented out in the holiday_tables code, and it works Any help appreciated = from sqlalchemy import create_engine from sqlalchemy import Table, Column, Date , Integer, String, MetaData, ForeignKey from sqlalchemy.orm import relationship, backref from sqlalchemy.orm import mapper from sqlalchemy.orm import sessionmaker import datetime engine = create_engine('sqlite:///:memory:', echo=True) Session = sessionmaker() Session.configure(bind=engine) session = Session() metadata = MetaData() class Calendar (object): def __init__(self, name, fullname): self.name = name self.fullname = fullname def __repr__(self): return Calendar('%s','%s') % (self.name, self.fullname) class Holiday (object): def __init__(self, holiday, description): self.holiday = holiday self.description = description def __repr__(self): return Holiday('%s', '%s') % (self.holiday, self.description) calendars_table = Table \ ( 'calendars', metadata, Column('id', Integer, primary_key=True), Column('name', String), Column('fullname', String) ) holidays_table = Table \ ( 'holidays', metadata, Column('id', Integer, primary_key=True), #Column('holiday', String, nullable=False), Column('holiday', Date, nullable=False), Column('description', String), Column('calendar_id', Integer, ForeignKey('calendars.id')) ) metadata.create_all(engine) mapper \ ( Calendar, calendars_table, properties= \ { 'holidays': relationship \ ( Holiday, backref='calendar', cascade=all, delete, delete-orphan, lazy='joined' ) } ) mapper(Holiday, holidays_table) session.commit() lon = Calendar('LON', 'London') #lon.holidays = [Holiday(holiday='2011-01-01',description='New Year'), Holiday(holiday='2011-12-25',description=xmas)] session.add(lon) session.commit() lon.holidays = [Holiday(lon, holiday=datetime.date('2011-01-01'),description='New Year'), Holiday(lon, holiday=datetime.date('2011-12-25'),description=xmas)] session.commit() lon = session.query(Calendar).filter_by(name='LON').one() session.delete(lon) session.commit() = -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bad sql generated for filter_list using firebird
On Feb 28, 2011, at 7:42 AM, bigt wrote: generated sql contains invalid reference to table NONE I don't know where the problem is in Camelot or Sqlalchemy.(latest 0.6 trunk) I am using a Firebird database. I think that the sql derives from the following statements col = getattr( admin.entity, field_name ) query = select([col], distinct=True, order_by=col.asc()).select_from(table) ProgrammingError: (ProgrammingError) (-204, 'isc_dsql_prepare: \n Dynamic SQL Error\n SQL error code = -204\n Table unknown\n NONE\n At line 2, column 19') 'SELECT DISTINCT assets.yr \nFROM assets, None ORDER BY assets.yr ASC' () this suggests that the variable named table is None. select_from() is usually not needed in any case as the FROM clause is derived from the columns and the WHERE clause. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] UnboundExecutionError: text query with elixir
On Feb 28, 2011, at 9:05 AM, David Borsodi wrote: Hello, I'm trying to run the following query with elixir entities: elixir.metadata.bind = 'mysql://root:root@localhost/mutdb' elixir.metadata.bind.echo = True elixir.setup_all(True) t = text(select * from mutations) result = elixir.session.connection().execute(t) and I got: sqlalchemy.exc.UnboundExecutionError: This session is not bound to a single Engine or Connection, and no context was provided to locate a binding. I though elixir does all the initialization with setup_all(), what else is needed to run queries like this? Normal mapped queries like Mutation.query.all() work normally. I would have thought Elixir associates the engine with the Session, if not you may ask on their list. You can associate the contextual session (i.e. , a ScopedSession wrapper) with a bind by doing: session.configure(bind=engine) before the contextual session is first used. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Extending sqlalchemy.schema.Column and metaprogramming traps
Column can be subclassed but because they are intensively used in complex expression transformations, your custom class may be used in more scenarios than you first anticipate. There are two scenarios where Column objects are copied, and in one case copied into an altered class, so the copying of Column uses an attribute called _constructor to point to which class should be used when creating this copy. Usually setting that to Column: class MyColumn(Column): _constructor = Column # go nuts is all you need. On Feb 28, 2011, at 10:17 AM, Martijn Moeling wrote: Hi, I know this is an OLD threat but I was searching the group to see If I was not the first one doing this. I am not sure I understand very well what this threat is all about, but I want to extend the Column class for a different reason. I want to add extra functionality to the Column class which is absolutely NOT SA related. SA functionality should not be effected though. say I want to add a config value and some methods for rendering and validating screens: def MyColumn(Column): def __init(): dosomething to init def ExtraInfo(self): do_something_not_sa_related validation = 'someregex' and use MyColumn in places where I normally use Column(..) What do I need to take into account, I've done some tests and Error hell broke loose, where the errors are hidden deep inside SA so hard to overcome. Martijn On Dec 11, 2008, at 16:20 , Michael Bayer wrote: On Dec 11, 2008, at 3:37 AM, Angri wrote: Here it is: http://www.sqlalchemy.org/trac/ticket/1244 Maybe it is good idea to drop some new lines in faq? Something like this: Q: How should I extend sqlalchemy.schema.Column? A: You surely dont need it. Recommended way to achive your possible needs is to write instance-factory function which decorates creation of sqlalchemy.schema.Column instances. Q: But I'm really need it! A: Ok. To subclass Column, this is the current recipe: from sqlalchemy.sql.util import Annotated, annotated_classes class MyColumn(Column): ... class AnnotatedMyColumn(Annotated, MyColumn): pass annotated_classes[MyColumn] = AnnotatedMyColumn Do not forget to put AnnotatedMyColumn in the module namespace, or your schema will not be pickleable! Correct me please if I am wrong somewhere and excuse me for my English. Well the AnnotatedMyColumn part is less than ideal since its an internal. the way that works could very likely change. Creating an AnnotatedXXX class *can* be automated. the pickle thing just might be a caveat we'd document or arrange for an exception to occur (like putting a throw in a __getstate__ method). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Find whether a synonym points to a foreign key or a relationship (Looking for your blessing)
Thank you... Of course using all those descriptors for every attribute is a pretty java-esque Yeah... but you know... It's not easy getting rid of the past... And I'm pretty O.C.D, so I lve getters/setters... I'm opened to new experiences, though :-) Any hint, suggestion... whatever! you may have will be very appreciated... As usual, thank you so much! 2011/2/27 Michael Bayer mike...@zzzcomputing.com: On Feb 27, 2011, at 6:45 PM, Hector Blanco wrote: A few days ago I asked what appears in the body of the message, a few lines below. To summarize: Let's say I have a class User (yeah, to define users in my application) and each user can belong to one UserGroup (another class of my application). The User class would be something like: class User(declarativeBase): Represents a user __tablename__ = users _id = Column(id, Integer, primary_key=True) _firstName = Column(first_name, String(50)) _lastName = Column(last_name, String(50)) _userName = Column(user_name, String(50), unique=True, nullable=False) _password = Column(password, String(64), nullable=False) _userGroupId = Column(user_group_id, Integer, ForeignKey(user_groups.id)) _userGroup = relationship(UserGroup, uselist=False) id = synonym('_id', descriptor=property(getId, setId)) firstName = synonym('_firstName', descriptor=property(getFirstName, setFirstName)) lastName = synonym('_lastName', descriptor=property(getLastName, setLastName)) userName = synonym('_userName', descriptor=property(getUserName, setUserName)) password = synonym('_password', descriptor=property(getPassword, setPassword)) userGroupId = synonym('_userGroupId', descriptor=property(getUserGroupId, setUserGroupId)) userGroup = synonym('_userGroup', descriptor=property(getUserGroup, setUserGroup)) I wanted to find a way to find which synonyms pointed to foreign keys and which ones pointed to relationships. Basically, having a couple of methods like the following: def getRelationships(cls): that when invoked with getRelationships(User.User) would return a list with [userGroup] (withouth the _ in front) and another: def getForeignKeys(cls): that would return [userGroupId] So far I've done this: def getRelationships(cls): retval = list() mapper = sqlalchemy.orm.class_mapper(cls) actualNameToSynonym = dict() relationships = set() for prop in mapper.iterate_properties: if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty): actualNameToSynonym[prop.name] = prop.key # dictionary _userName, userName, userGroup, _userGroup elif isinstance(prop, sqlalchemy.orm.properties.RelationshipProperty): relationships.add(prop.key) #set with _userGroup, and rest of relationships for relationship in relationships: retval.append(actualNameToSynonym[relationship]) return retval def getForeignKeys(cls): retval = list() mapper = sqlalchemy.orm.class_mapper(cls) actualNameToSynonym = dict() columnsWithForeignKeys = set() for prop in mapper.iterate_properties: if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty): actualNameToSynonym[prop.name] = prop.key # dictionary _userName, userName, userGroup, _userGroup elif isinstance(prop, sqlalchemy.orm.properties.ColumnProperty): for column in prop.columns: if len(column.foreign_keys) 0: columnsWithForeignKeys.add(prop.key) for columnWithForeignKeys in columnsWithForeignKeys: retval.append(actualNameToSynonym[columnWithForeignKeys]) return retval Both are very similar: First they create a dictionary mapping the synonym's key with the real name (_userGroup, userGroup) and store the relationships or the columns that have a foreign key in a set (for the method that tries to get relationships, that set would be set(_userGroup) and for the one that tries to get foreign keys, set(_userGroupId)) . In a second for loop they match that underscored name with the name of the synonym to return a list with the names of the synonyms, and not the actual columns (basically, to transform _userGroupId to userGroupId) They seem to work, at least with my not-complicated-at-all classes, but I'd like to know what do you guys think of my approach. Is it good? Can it break something? Is there a better way? No thats a pretty OK way , there's an argument called resolve_synonyms to get_property() in 0.6 but that's gone away in 0.7 anyway. There
Re: [sqlalchemy] Re: Extending sqlalchemy.schema.Column and metaprogramming traps
On Feb 28, 2011, at 18:21 , Michael Bayer wrote: Column can be subclassed but because they are intensively used in complex expression transformations, your custom class may be used in more scenarios than you first anticipate. There are two scenarios where Column objects are copied, and in one case copied into an altered class, so the copying of Column uses an attribute called _constructor to point to which class should be used when creating this copy. Usually setting that to Column: class MyColumn(Column): _constructor = Column # go nuts LOL!! Thanks is all you need. On Feb 28, 2011, at 10:17 AM, Martijn Moeling wrote: Hi, I know this is an OLD threat but I was searching the group to see If I was not the first one doing this. I am not sure I understand very well what this threat is all about, but I want to extend the Column class for a different reason. I want to add extra functionality to the Column class which is absolutely NOT SA related. SA functionality should not be effected though. say I want to add a config value and some methods for rendering and validating screens: def MyColumn(Column): def __init(): dosomething to init def ExtraInfo(self): do_something_not_sa_related validation = 'someregex' and use MyColumn in places where I normally use Column(..) What do I need to take into account, I've done some tests and Error hell broke loose, where the errors are hidden deep inside SA so hard to overcome. Martijn On Dec 11, 2008, at 16:20 , Michael Bayer wrote: On Dec 11, 2008, at 3:37 AM, Angri wrote: Here it is: http://www.sqlalchemy.org/trac/ticket/1244 Maybe it is good idea to drop some new lines in faq? Something like this: Q: How should I extend sqlalchemy.schema.Column? A: You surely dont need it. Recommended way to achive your possible needs is to write instance-factory function which decorates creation of sqlalchemy.schema.Column instances. Q: But I'm really need it! A: Ok. To subclass Column, this is the current recipe: from sqlalchemy.sql.util import Annotated, annotated_classes class MyColumn(Column): ... class AnnotatedMyColumn(Annotated, MyColumn): pass annotated_classes[MyColumn] = AnnotatedMyColumn Do not forget to put AnnotatedMyColumn in the module namespace, or your schema will not be pickleable! Correct me please if I am wrong somewhere and excuse me for my English. Well the AnnotatedMyColumn part is less than ideal since its an internal. the way that works could very likely change. Creating an AnnotatedXXX class *can* be automated. the pickle thing just might be a caveat we'd document or arrange for an exception to occur (like putting a throw in a __getstate__ method). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] UnboundExecutionError: text query with elixir
Yes, I also looked and the elixir session is an SA ScopedSession, I'll try the explicit bind. thanks david On Mon, Feb 28, 2011 at 5:58 PM, Michael Bayer mike...@zzzcomputing.comwrote: ssociates the engine wit -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Sqlalchemy+Kinterbasdb installation error
On 28/02/2011 16:31, Massi wrote: Hi everyone, I'm trying to access a Firebird (version 2.5) database through Kinterbasdb under windows 7 with python 2.6. I'have download the file: kinterbasdb-3.3.0.win32-py2.6.msi which can be found at this link: http://www.firebirdsql.org/index.php?op=develsub=python, but when I try to install it I get the following errors: - Could not create: kinterbasdb-py2.6 - Could not set key value: python 2.6 kinterbas-3.3.0 - Could not set key value:C:\Python26\removekinterbasdb.exe - Could not set key value:C:\Python26\kinterbasdb-wininst.log Sounds like you are not running is as admin. I'm currently running python 2.6 win32 on a 64 bit CPU. The installed Firebird version is 64bit too. Maybe is it a compatibility issue? I also tried kinterbasdb-3.3.0.win-amd64-py2.6.msi, but it didn't work. I am on Win 7 64 bit but all my Python stuff and FB is 32 bit - so don't know if it would work. Anyhow you should better ask kinterbasdb questions on the FB driver list. Werner -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Is Session.execute SQL injection safe?
Can't find anything in the docs as to whether query strings passed into Session.execute are escaped/safe from SQL injection. Any insights? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Is Session.execute SQL injection safe?
On Feb 28, 2011, at 5:35 PM, Ryan wrote: Can't find anything in the docs as to whether query strings passed into Session.execute are escaped/safe from SQL injection. Any insights? Thanks. A literal query string is only safe against injection if you ensure that the string contains no portions of user-entered text inside of it.Bind parameters should always be used for literal values. Docs on the text() construct which Session.execute() uses, and the accepted bind parameter format, are here: http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.text -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] More autocommit and exception-handling questions
Getting some conflicting advice on autocommit and wrapping the request in a try/except block on the Tornado mailing list, was wondering what your thoughts are on the issues brought up in the following message and its replies: http://groups.google.com/group/python-tornado/msg/d06a7e244fc9fe29 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Is Session.execute SQL injection safe?
Great. Thank you. On Mon, Feb 28, 2011 at 9:37 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Feb 28, 2011, at 5:35 PM, Ryan wrote: Can't find anything in the docs as to whether query strings passed into Session.execute are escaped/safe from SQL injection. Any insights? Thanks. A literal query string is only safe against injection if you ensure that the string contains no portions of user-entered text inside of it.Bind parameters should always be used for literal values. Docs on the text() construct which Session.execute() uses, and the accepted bind parameter format, are here: http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.text -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.