[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.
