Re: [sqlalchemy] CreateTable display is not right for autoincrement in mysql
Thank you very much. This fixes my problem. So it's not a bug?
On Sat, Oct 5, 2013 at 2:17 PM, Michael Bayer wrote:
>
> On Oct 5, 2013, at 1:58 AM, limodou wrote:
>
> Today I found if I have an id column in a table, and the autoincrement
> attribute is True, when I created the table it's right, but when I print
> the create statment is not right for autoincrement. The testing code is:
>
> from sqlalchemy import *
> from sqlalchemy.schema import CreateTable
>
> engine = create_engine('mysql://root:root@localhost/test', echo=True)
> metadata = MetaData()
> t = Table('mytable', metadata,
> Column('gid', Integer, primary_key=True, autoincrement=True),
> Column('id', Integer, primary_key=True)
> )
> metadata.create_all(engine)
> print CreateTable(t)
>
>
> do the create table like this:
>
> print CreateTable(t).compile(dialect=engine.dialect)
>
>
>
>
> And the result is:
>
> 2013-10-05 12:59:54,523 INFO sqlalchemy.engine.base.Engine SELECT
> DATABASE()
> 2013-10-05 12:59:54,523 INFO sqlalchemy.engine.base.Engine ()
> 2013-10-05 12:59:54,526 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
> LIKE 'character_set%%'
> 2013-10-05 12:59:54,526 INFO sqlalchemy.engine.base.Engine ()
> 2013-10-05 12:59:54,526 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
> LIKE 'sql_mode'
> 2013-10-05 12:59:54,528 INFO sqlalchemy.engine.base.Engine ()
> 2013-10-05 12:59:54,538 INFO sqlalchemy.engine.base.Engine DESCRIBE
> `mytable`
> 2013-10-05 12:59:54,538 INFO sqlalchemy.engine.base.Engine ()
> 2013-10-05 12:59:54,552 INFO sqlalchemy.engine.base.Engine ROLLBACK
> 2013-10-05 12:59:54,575 INFO sqlalchemy.engine.base.Engine
> CREATE TABLE mytable (
> gid INTEGER NOT NULL AUTO_INCREMENT,
> id INTEGER NOT NULL,
> PRIMARY KEY (gid, id)
> )
>
>
> 2013-10-05 12:59:54,575 INFO sqlalchemy.engine.base.Engine ()
> 2013-10-05 12:59:55,332 INFO sqlalchemy.engine.base.Engine COMMIT
>
> CREATE TABLE mytable (
> gid INTEGER NOT NULL,
> id INTEGER NOT NULL,
> PRIMARY KEY (gid, id)
> )
>
> So the above is right, and it's gid INTEGER NOT NULL AUTO_INCREMENT, but
> the next is not right, it losts AUTO_INCREMENT.
>
> I tried to check the code and I found the implementation of
> dialects/sql/mysql/base.py:get_column_specification(1408) is not like
> sql/compiler.py:get_column_specification(2021). I think maybe is a bug.
>
> BTW, my salalchemy version is 8.0.
>
> --
> I like python!
> UliPad <>: http://code.google.com/p/ulipad/
> UliWeb <>: https://github.com/limodou/uliweb
> My Blog: http://my.oschina.net/limodou
>
> --
> 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.
>
>
>
--
I like python!
UliPad <>: http://code.google.com/p/ulipad/
UliWeb <>: https://github.com/limodou/uliweb
My Blog: http://my.oschina.net/limodou
--
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] CreateTable display is not right for autoincrement in mysql
On Oct 5, 2013, at 1:58 AM, limodou wrote:
> Today I found if I have an id column in a table, and the autoincrement
> attribute is True, when I created the table it's right, but when I print the
> create statment is not right for autoincrement. The testing code is:
>
> from sqlalchemy import *
> from sqlalchemy.schema import CreateTable
>
> engine = create_engine('mysql://root:root@localhost/test', echo=True)
> metadata = MetaData()
> t = Table('mytable', metadata,
> Column('gid', Integer, primary_key=True, autoincrement=True),
> Column('id', Integer, primary_key=True)
> )
> metadata.create_all(engine)
> print CreateTable(t)
do the create table like this:
print CreateTable(t).compile(dialect=engine.dialect)
>
> And the result is:
>
> 2013-10-05 12:59:54,523 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
> 2013-10-05 12:59:54,523 INFO sqlalchemy.engine.base.Engine ()
> 2013-10-05 12:59:54,526 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
> LIKE 'character_set%%'
> 2013-10-05 12:59:54,526 INFO sqlalchemy.engine.base.Engine ()
> 2013-10-05 12:59:54,526 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
> LIKE 'sql_mode'
> 2013-10-05 12:59:54,528 INFO sqlalchemy.engine.base.Engine ()
> 2013-10-05 12:59:54,538 INFO sqlalchemy.engine.base.Engine DESCRIBE `mytable`
> 2013-10-05 12:59:54,538 INFO sqlalchemy.engine.base.Engine ()
> 2013-10-05 12:59:54,552 INFO sqlalchemy.engine.base.Engine ROLLBACK
> 2013-10-05 12:59:54,575 INFO sqlalchemy.engine.base.Engine
> CREATE TABLE mytable (
> gid INTEGER NOT NULL AUTO_INCREMENT,
> id INTEGER NOT NULL,
> PRIMARY KEY (gid, id)
> )
>
>
> 2013-10-05 12:59:54,575 INFO sqlalchemy.engine.base.Engine ()
> 2013-10-05 12:59:55,332 INFO sqlalchemy.engine.base.Engine COMMIT
>
> CREATE TABLE mytable (
> gid INTEGER NOT NULL,
> id INTEGER NOT NULL,
> PRIMARY KEY (gid, id)
> )
>
> So the above is right, and it's gid INTEGER NOT NULL AUTO_INCREMENT, but the
> next is not right, it losts AUTO_INCREMENT.
>
> I tried to check the code and I found the implementation of
> dialects/sql/mysql/base.py:get_column_specification(1408) is not like
> sql/compiler.py:get_column_specification(2021). I think maybe is a bug.
>
> BTW, my salalchemy version is 8.0.
>
> --
> I like python!
> UliPad <>: http://code.google.com/p/ulipad/
> UliWeb <>: https://github.com/limodou/uliweb
> My Blog: http://my.oschina.net/limodou
>
> --
> 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] CreateTable display is not right for autoincrement in mysql
Today I found if I have an id column in a table, and the autoincrement
attribute is True, when I created the table it's right, but when I print
the create statment is not right for autoincrement. The testing code is:
from sqlalchemy import *
from sqlalchemy.schema import CreateTable
engine = create_engine('mysql://root:root@localhost/test', echo=True)
metadata = MetaData()
t = Table('mytable', metadata,
Column('gid', Integer, primary_key=True, autoincrement=True),
Column('id', Integer, primary_key=True)
)
metadata.create_all(engine)
print CreateTable(t)
And the result is:
2013-10-05 12:59:54,523 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2013-10-05 12:59:54,523 INFO sqlalchemy.engine.base.Engine ()
2013-10-05 12:59:54,526 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'character_set%%'
2013-10-05 12:59:54,526 INFO sqlalchemy.engine.base.Engine ()
2013-10-05 12:59:54,526 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'sql_mode'
2013-10-05 12:59:54,528 INFO sqlalchemy.engine.base.Engine ()
2013-10-05 12:59:54,538 INFO sqlalchemy.engine.base.Engine DESCRIBE
`mytable`
2013-10-05 12:59:54,538 INFO sqlalchemy.engine.base.Engine ()
2013-10-05 12:59:54,552 INFO sqlalchemy.engine.base.Engine ROLLBACK
2013-10-05 12:59:54,575 INFO sqlalchemy.engine.base.Engine
CREATE TABLE mytable (
gid INTEGER NOT NULL AUTO_INCREMENT,
id INTEGER NOT NULL,
PRIMARY KEY (gid, id)
)
2013-10-05 12:59:54,575 INFO sqlalchemy.engine.base.Engine ()
2013-10-05 12:59:55,332 INFO sqlalchemy.engine.base.Engine COMMIT
CREATE TABLE mytable (
gid INTEGER NOT NULL,
id INTEGER NOT NULL,
PRIMARY KEY (gid, id)
)
So the above is right, and it's gid INTEGER NOT NULL AUTO_INCREMENT, but
the next is not right, it losts AUTO_INCREMENT.
I tried to check the code and I found the implementation of
dialects/sql/mysql/base.py:get_column_specification(1408) is not like
sql/compiler.py:get_column_specification(2021). I think maybe is a bug.
BTW, my salalchemy version is 8.0.
--
I like python!
UliPad <>: http://code.google.com/p/ulipad/
UliWeb <>: https://github.com/limodou/uliweb
My Blog: http://my.oschina.net/limodou
--
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] textcolumn.contains() escaping, the lack of icontains(), and possible bug?
Converting strings to lower case and comparing them is not the same as a true case-insensitive comparison. Python 3.3 adds a str.casefold method for this reason. The docs for that method give a good explanation of the distinction: """Casefolding is similar to lowercasing but more aggressive because it is intended to remove all case distinctions in a string. For example, the German lowercase letter 'ß' is equivalent to "ss". Since it is already lowercase, lower() would do nothing to 'ß'; casefold() converts it to "ss". The casefolding algorithm is described in section 3.13 of the Unicode Standard.""" -- 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] Query for date between a range
Enrico, It should be available on 0.7.10
Simon, you are right. The expression is indeed a must.
class Plan(Base):
@hybrid_property
def calculated_date(self):
return date(self.year, self.month, self.day)
@calculated_date.expression
def calculated_date(self):
return sa.cast(self.year + '-' + self.month + '-' + self.day,
sa.Date)
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Simon King
Sent: Friday, October 04, 2013 7:01 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Query for date between a range
I'm not sure that will work on it's own, will it? When used in a class
context (Plan.calculated_date), you will end up calling the date function
with 3 SQLAlchemy column objects, which won't work.
At a minimum, you'd need this:
class Plan(Base):
@hybrid_property
def calculated_date(self):
return date(self.year, self.month, self.day)
@calculated_date.expression
def calculated_date(cls):
# I suspect that what you put in here depends on the database
return sa.cast(cls.year + '-' + cls.month + '-' + cls.day, sa.Date)
On Fri, Oct 4, 2013 at 2:25 PM, Ofir Herzas wrote:
> I'm sorry, you should use hybrid_property:
>
> from sqlalchemy.ext.hybrid import hybrid_property
>
> class Plan(Base):
> @hybrid_property
> def calculated_date(self):
> return date(self.year, self.month, self.day)
>
>
> Also, in your query, don't use between:
> session.query(Plan).\
> filter(Plan.calculated_date >= from_date).\
> filter(Plan.calculated_date <= to_date))
>
> Cheers,
> Ofir
>
> -Original Message-
> From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
> On Behalf Of Enrico Morelli
> Sent: Friday, October 04, 2013 4:05 PM
> To: sqlalchemy@googlegroups.com
> Subject: Re: [sqlalchemy] Query for date between a range
>
> On Fri, 4 Oct 2013 15:55:07 +0300
> "Ofir Herzas" wrote:
>
>> You can create a custom field in your model and check against it:
>>
>> class Plan(Base):
>> .
>> .
>> .
>> @property
>> def calculated_date(self):
>> return date(self.year, self.month, self.day)
>>
>>
>> Then, in your query, use that field:
>> session.query(Plan).filter(Plan.calculated_date.between(from_date,
>> to_date))
>>
>> Haven't checked it myself, but I guess it should work ...
>
> Thanks, but now I receive the error:
>
> AttributeError: 'property' object has no attribute 'between'
>
>>
>> -Original Message-
>> From: sqlalchemy@googlegroups.com
>> [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
>> Sent: Friday, October 04, 2013 12:07 PM
>> To: sqlalchemy@googlegroups.com
>> Subject: [sqlalchemy] Query for date between a range
>>
>> Dear all,
>>
>> I've a table where the date is separated in single fields, one for
>> year, one for day and one for month. So I need to query for a date
>> range. I search in Internet and I found the following query that
>> seems to be works: SELECT * FROM plan WHERE year * 1 + month *
>> 100 + day BETWEEN +'20130101' AND '20130131';
>>
>> Now I'm trying to translate to sqlalchemy, but I receive the
>> following
>> error:
>>
>> DataError: (DataError) invalid input syntax for integer:
>> "2013-01-01T00:00:00" LINE 3: ...year * 1000 + plan.month * 100 +
>> plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data,
>> plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
>> count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year *
>> %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s
>> AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data,
>> plan.month, plan.instrument_id ORDER BY month, instrument_id'
>> {'data_1': u'%#L%', 'param_1': datetime.datetime(2013, 1, 1, 0, 0),
>> 'month_1': 100, 'year_1': 1000, 'param_2':
>> datetime.datetime(2013, 10, 3, 0, 0)}
>>
>>
>> The latest attempt to write the correct code is the following (the
>> range come from a web form using a javascript plugin):
>>
>> from_date = request.POST.get('from_date', '') to_date =
>> request.POST.get('to_date', '') from_date =
>> datetime.strptime(from_date, '%Y-%m-%d') to_date =
>> datetime.strptime(to_date, '%Y-%m-%d') if from_date.day < 10:
>>day = "0%s" % from_date.day
>> else:
>>day = "%s" % from_date.day
>> if from_date.month < 10:
>>month = "0%s" % from_date.month
>> else:
>>month = "%s" % from_date.month
>> if to_date.day < 10:
>>tday = "0%s" % to_date.day
>> else:
>>tday = "%s" % to_date.day
>> if to_date.month < 10:
>>tmonth = "0%s" % to_date.month
>> else:
>>tmonth = "%s" % to_date.month
>> fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' %
>> (to_date.year, tmonth, tday) print fd, td results =
>> Session.query(Plan.data,
>> Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter(
>> and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
>>
Re: [sqlalchemy] Query for date between a range
That query looks weird.
I'd suggest converting everything to a date in the database and having the
db sort it --
on postgres it would look like this --
create table test_date ( id serial primary key not null , int , mm int
, dd int );
select *
from
test_date
where
( to_date(||'-'||mm||'-'||dd,'-mm-dd') <
to_date('2013-01-01','-mm-dd') )
and
( to_date(||'-'||mm||'-'||dd,'-mm-dd') >
to_date('2012-01-01','-mm-dd') )
;
it would be different on other databases, usually similar though.
When I've had to store dates like you did, I usually store a real date in
addition, so my sql statements aren't so damn messy. storage is relatively
cheap, and having a real datetime in the database makes selects so much
faster than computed options.
create table better_date(
real_date date ,
INT,
mm INT,
dd INT,
)
--
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] Query for date between a range
I'm not sure that will work on it's own, will it? When used in a class
context (Plan.calculated_date), you will end up calling the date
function with 3 SQLAlchemy column objects, which won't work.
At a minimum, you'd need this:
class Plan(Base):
@hybrid_property
def calculated_date(self):
return date(self.year, self.month, self.day)
@calculated_date.expression
def calculated_date(cls):
# I suspect that what you put in here depends on the database
return sa.cast(cls.year + '-' + cls.month + '-' + cls.day, sa.Date)
On Fri, Oct 4, 2013 at 2:25 PM, Ofir Herzas wrote:
> I'm sorry, you should use hybrid_property:
>
> from sqlalchemy.ext.hybrid import hybrid_property
>
> class Plan(Base):
> @hybrid_property
> def calculated_date(self):
> return date(self.year, self.month, self.day)
>
>
> Also, in your query, don't use between:
> session.query(Plan).\
> filter(Plan.calculated_date >= from_date).\
> filter(Plan.calculated_date <= to_date))
>
> Cheers,
> Ofir
>
> -Original Message-
> From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
> Behalf Of Enrico Morelli
> Sent: Friday, October 04, 2013 4:05 PM
> To: sqlalchemy@googlegroups.com
> Subject: Re: [sqlalchemy] Query for date between a range
>
> On Fri, 4 Oct 2013 15:55:07 +0300
> "Ofir Herzas" wrote:
>
>> You can create a custom field in your model and check against it:
>>
>> class Plan(Base):
>> .
>> .
>> .
>> @property
>> def calculated_date(self):
>> return date(self.year, self.month, self.day)
>>
>>
>> Then, in your query, use that field:
>> session.query(Plan).filter(Plan.calculated_date.between(from_date,
>> to_date))
>>
>> Haven't checked it myself, but I guess it should work ...
>
> Thanks, but now I receive the error:
>
> AttributeError: 'property' object has no attribute 'between'
>
>>
>> -Original Message-
>> From: sqlalchemy@googlegroups.com
>> [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
>> Sent: Friday, October 04, 2013 12:07 PM
>> To: sqlalchemy@googlegroups.com
>> Subject: [sqlalchemy] Query for date between a range
>>
>> Dear all,
>>
>> I've a table where the date is separated in single fields, one for
>> year, one for day and one for month. So I need to query for a date
>> range. I search in Internet and I found the following query that seems
>> to be works: SELECT * FROM plan WHERE year * 1 + month * 100 + day
>> BETWEEN +'20130101' AND '20130131';
>>
>> Now I'm trying to translate to sqlalchemy, but I receive the following
>> error:
>>
>> DataError: (DataError) invalid input syntax for integer:
>> "2013-01-01T00:00:00" LINE 3: ...year * 1000 + plan.month * 100 +
>> plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data,
>> plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
>> count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year *
>> %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s
>> AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data,
>> plan.month, plan.instrument_id ORDER BY month, instrument_id'
>> {'data_1': u'%#L%', 'param_1': datetime.datetime(2013, 1, 1, 0, 0),
>> 'month_1': 100, 'year_1': 1000, 'param_2':
>> datetime.datetime(2013, 10, 3, 0, 0)}
>>
>>
>> The latest attempt to write the correct code is the following (the
>> range come from a web form using a javascript plugin):
>>
>> from_date = request.POST.get('from_date', '') to_date =
>> request.POST.get('to_date', '') from_date =
>> datetime.strptime(from_date, '%Y-%m-%d') to_date =
>> datetime.strptime(to_date, '%Y-%m-%d') if from_date.day < 10:
>>day = "0%s" % from_date.day
>> else:
>>day = "%s" % from_date.day
>> if from_date.month < 10:
>>month = "0%s" % from_date.month
>> else:
>>month = "%s" % from_date.month
>> if to_date.day < 10:
>>tday = "0%s" % to_date.day
>> else:
>>tday = "%s" % to_date.day
>> if to_date.month < 10:
>>tmonth = "0%s" % to_date.month
>> else:
>>tmonth = "%s" % to_date.month
>> fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' %
>> (to_date.year, tmonth, tday) print fd, td results =
>> Session.query(Plan.data,
>> Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter(
>> and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
>> to_date), Plan.data.ilike('%%%s%%' % item),)).group_by(
>> Plan.data, Plan.month,
>> Plan.instrument_id ).order_by('month', 'instrument_id').all()
>>
>> Where I'm wrong?
>>
>> Thanks to all
>> --
>> -
>> Enrico Morelli
>> System Administrator | Programmer | Web Developer
>>
>> CERM - Polo Scientifico
>> Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
>> phone: +39 055 457 4269
>> fax: +39 055 457 4253
>> -
>>
>> --
>> You received this message becaus
Re: [sqlalchemy] Session Management Issues While Integrating With Old Code
On Fri, Oct 4, 2013 at 3:47 PM, Russell Holloway wrote: > Hello all, > > I am trying to migrate some custom ORM code to use SQLAlchemy instead for > database interactions. I'm having some issues with proper session > management. The main issue that seems to occur is the operationalerror > 'mysql has gone away' with every now and then one comes up StatementError > "can't reconnect until invalid transaction is rolled back (original cause: > InvalidRequestError)". > > I am aware that others have had issues with the first error on long standing > connections. For example, if someone creates a session when python script > initially loaded and tries to reuse much later, the error occurs. There is > scoped_session which should be tied to the specific request (Flask) and > solve the issue if a session is created and closed within the route. > > The problem comes with trying to wrap the code in such a way that it aligns > with previous code. With SQLAlchemy, you call > > session.query(ClassName).get(some_stuff) > > I would like to use the structure below instead: > > ClassName.get(some_stuff) > > To do so, ClassName inherits from a base class that creates the get() > method, which in turn converts to the SQLAlchemy format (and similar for > filter, update, and similar functions). A simple example would be > > @classmethod > def get(cls, arg): > return session.query(cls).get(arg) > > Does anyone have any recommendations on how to best set that session > variable though to use within the base SQLAlchemy class, so that it is > scoped to a request? Is it possible? I suppose I could modify all the > functions and pass in a session variable as well, so from within Flask route > the user would create a (scoped) session and pass it in to all the class > methods (ClassName.get(session, arg)), but that changes syntax and isn't > quite as clear. I thought I would create a custom Session class with a > global session var that a user could set during route function (and then use > that static variable within get() function), but then as multiple Flask > users interact, the static variable would be overwritten by others > prematurely. > > I know there is Flask-SQLAlchemy, and it provides a *close* syntax such as > User.query.get() through the query attribute. Does anyone know how it does > the session management behind the scenes to keep them sane? I am looking for > pure SQLAlchemy solutions, since the API is used both through Flask (web) > and standard python. > > As long as Flask is using a separate thread for each request, then scoped_session basically does all the hard work for you. You want something like this at module-level: session = scoped_session(sessionmaker()) and just use that global session everywhere. scoped_session will make sure that separate threads will have separate sessions. However, you do need to make sure that the session is cleaned up properly at the end of each request. I don't know Flask so I don't know where you would put this, but you basically need to make sure that "session.close()" is called at the end of every request. Typically you would have something that runs at the end of the request, calls session.commit() if there were no errors, or session.rollback() if there were, followed by session.close(). This removes the real session from the scoped_session's registry, so that when this thread services another request, it gets a fresh session. As for your other problem (mysql has gone away), that is typically solved by passing the pool_recycle=n parameter to create_engine. This tells SQLAlchemy not to use a database connection that is older than n seconds. 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.
Re: [sqlalchemy] Query for date between a range
On Fri, 4 Oct 2013 16:03:18 +0200
Enrico Morelli wrote:
> On Fri, 4 Oct 2013 16:25:00 +0300
> "Ofir Herzas" wrote:
>
> > I'm sorry, you should use hybrid_property:
> >
> > from sqlalchemy.ext.hybrid import hybrid_property
> >
> > class Plan(Base):
> > @hybrid_property
> > def calculated_date(self):
> > return date(self.year, self.month, self.day)
> >
> >
> > Also, in your query, don't use between:
> > session.query(Plan).\
> > filter(Plan.calculated_date >= from_date).\
> > filter(Plan.calculated_date <= to_date))
> >
> > Cheers,
> > Ofir
>
> Argh!! I've to use and old version of sqlalchemy (0.6.8) that hasn't
> the hybrid module. It's a dog that bites its own tail.
Sigh! In which version I can found the hybrid module? My application is
in a production environment and I'm afraid to update sqlalchemy.
Usually I create a virtual environment where the python modules are
frozen to avoid problems.
> >
> > -Original Message-
> > From: sqlalchemy@googlegroups.com
> > [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
> > Sent: Friday, October 04, 2013 4:05 PM
> > To: sqlalchemy@googlegroups.com
> > Subject: Re: [sqlalchemy] Query for date between a range
> >
> > On Fri, 4 Oct 2013 15:55:07 +0300
> > "Ofir Herzas" wrote:
> >
> > > You can create a custom field in your model and check against it:
> > >
> > > class Plan(Base):
> > > .
> > > .
> > > .
> > > @property
> > > def calculated_date(self):
> > > return date(self.year, self.month, self.day)
> > >
> > >
> > > Then, in your query, use that field:
> > > session.query(Plan).filter(Plan.calculated_date.between(from_date,
> > > to_date))
> > >
> > > Haven't checked it myself, but I guess it should work ...
> >
> > Thanks, but now I receive the error:
> >
> > AttributeError: 'property' object has no attribute 'between'
> >
> > >
> > > -Original Message-
> > > From: sqlalchemy@googlegroups.com
> > > [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
> > > Sent: Friday, October 04, 2013 12:07 PM
> > > To: sqlalchemy@googlegroups.com
> > > Subject: [sqlalchemy] Query for date between a range
> > >
> > > Dear all,
> > >
> > > I've a table where the date is separated in single fields, one
> > > for year, one for day and one for month. So I need to query for a
> > > date range. I search in Internet and I found the following query
> > > that seems to be works: SELECT * FROM plan WHERE year * 1 +
> > > month * 100 + day BETWEEN +'20130101' AND '20130131';
> > >
> > > Now I'm trying to translate to sqlalchemy, but I receive the
> > > following error:
> > >
> > > DataError: (DataError) invalid input syntax for integer:
> > > "2013-01-01T00:00:00" LINE 3: ...year * 1000 + plan.month * 100 +
> > > plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data,
> > > plan.month AS plan_month, plan.instrument_id AS
> > > plan_instrument_id, count(plan.instrument_id) AS count_1 \nFROM
> > > plan \nWHERE plan.year
> > > * %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN
> > > %(param_1)s AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP
> > > BY plan.data, plan.month, plan.instrument_id ORDER BY month,
> > > instrument_id' {'data_1': u'%#L%', 'param_1':
> > > datetime.datetime(2013, 1, 1, 0, 0), 'month_1': 100, 'year_1':
> > > 1000, 'param_2': datetime.datetime(2013, 10, 3, 0, 0)}
> > >
> > >
> > > The latest attempt to write the correct code is the following
> > > (the range come from a web form using a javascript plugin):
> > >
> > > from_date = request.POST.get('from_date', '') to_date =
> > > request.POST.get('to_date', '') from_date =
> > > datetime.strptime(from_date, '%Y-%m-%d') to_date =
> > > datetime.strptime(to_date, '%Y-%m-%d') if from_date.day < 10:
> > >day = "0%s" % from_date.day
> > > else:
> > >day = "%s" % from_date.day
> > > if from_date.month < 10:
> > >month = "0%s" % from_date.month
> > > else:
> > >month = "%s" % from_date.month
> > > if to_date.day < 10:
> > >tday = "0%s" % to_date.day
> > > else:
> > >tday = "%s" % to_date.day
> > > if to_date.month < 10:
> > >tmonth = "0%s" % to_date.month
> > > else:
> > >tmonth = "%s" % to_date.month
> > > fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' %
> > > (to_date.year, tmonth, tday) print fd, td results =
> > > Session.query(Plan.data,
> > > Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter(
> > > and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
> > > to_date), Plan.data.ilike('%%%s%%' % item),)).group_by(
> > > Plan.data,
> > > Plan.month, Plan.instrument_id ).order_by('month',
> > > 'instrument_id').all()
> > >
> > > Where I'm wrong?
> > >
> > > Thanks to all
> > > --
> > > -
> > > Enrico Morelli
> > > System Administrator | Programmer | Web Developer
> > >
> > > CERM - Po
[sqlalchemy] Session Management Issues While Integrating With Old Code
Hello all, I am trying to migrate some custom ORM code to use SQLAlchemy instead for database interactions. I'm having some issues with proper session management. The main issue that seems to occur is the operationalerror 'mysql has gone away' with every now and then one comes up StatementError "can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError)". I am aware that others have had issues with the first error on long standing connections. For example, if someone creates a session when python script initially loaded and tries to reuse much later, the error occurs. There is scoped_session which should be tied to the specific request (Flask) and solve the issue if a session is created and closed within the route. The problem comes with trying to wrap the code in such a way that it aligns with previous code. With SQLAlchemy, you call session.query(ClassName).get(some_stuff) I would like to use the structure below instead: ClassName.get(some_stuff) To do so, ClassName inherits from a base class that creates the get() method, which in turn converts to the SQLAlchemy format (and similar for filter, update, and similar functions). A simple example would be @classmethod def get(cls, arg): return session.query(cls).get(arg) Does anyone have any recommendations on how to best set that session variable though to use within the base SQLAlchemy class, so that it is scoped to a request? Is it possible? I suppose I could modify all the functions and pass in a session variable as well, so from within Flask route the user would create a (scoped) session and pass it in to all the class methods (ClassName.get(session, arg)), but that changes syntax and isn't quite as clear. I thought I would create a custom Session class with a global session var that a user could set during route function (and then use that static variable within get() function), but then as multiple Flask users interact, the static variable would be overwritten by others prematurely. I know there is Flask-SQLAlchemy, and it provides a *close* syntax such as User.query.get() through the query attribute. Does anyone know how it does the session management behind the scenes to keep them sane? I am looking for pure SQLAlchemy solutions, since the API is used both through Flask (web) and standard python. -- 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] Query for date between a range
On Fri, 4 Oct 2013 16:25:00 +0300
"Ofir Herzas" wrote:
> I'm sorry, you should use hybrid_property:
>
> from sqlalchemy.ext.hybrid import hybrid_property
>
> class Plan(Base):
> @hybrid_property
> def calculated_date(self):
> return date(self.year, self.month, self.day)
>
>
> Also, in your query, don't use between:
> session.query(Plan).\
> filter(Plan.calculated_date >= from_date).\
> filter(Plan.calculated_date <= to_date))
>
> Cheers,
> Ofir
Argh!! I've to use and old version of sqlalchemy (0.6.8) that hasn't
the hybrid module. It's a dog that bites its own tail.
>
> -Original Message-
> From: sqlalchemy@googlegroups.com
> [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
> Sent: Friday, October 04, 2013 4:05 PM
> To: sqlalchemy@googlegroups.com
> Subject: Re: [sqlalchemy] Query for date between a range
>
> On Fri, 4 Oct 2013 15:55:07 +0300
> "Ofir Herzas" wrote:
>
> > You can create a custom field in your model and check against it:
> >
> > class Plan(Base):
> > .
> > .
> > .
> > @property
> > def calculated_date(self):
> > return date(self.year, self.month, self.day)
> >
> >
> > Then, in your query, use that field:
> > session.query(Plan).filter(Plan.calculated_date.between(from_date,
> > to_date))
> >
> > Haven't checked it myself, but I guess it should work ...
>
> Thanks, but now I receive the error:
>
> AttributeError: 'property' object has no attribute 'between'
>
> >
> > -Original Message-
> > From: sqlalchemy@googlegroups.com
> > [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
> > Sent: Friday, October 04, 2013 12:07 PM
> > To: sqlalchemy@googlegroups.com
> > Subject: [sqlalchemy] Query for date between a range
> >
> > Dear all,
> >
> > I've a table where the date is separated in single fields, one for
> > year, one for day and one for month. So I need to query for a date
> > range. I search in Internet and I found the following query that
> > seems to be works: SELECT * FROM plan WHERE year * 1 + month *
> > 100 + day BETWEEN +'20130101' AND '20130131';
> >
> > Now I'm trying to translate to sqlalchemy, but I receive the
> > following error:
> >
> > DataError: (DataError) invalid input syntax for integer:
> > "2013-01-01T00:00:00" LINE 3: ...year * 1000 + plan.month * 100 +
> > plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data,
> > plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
> > count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year
> > * %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN
> > %(param_1)s AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY
> > plan.data, plan.month, plan.instrument_id ORDER BY month,
> > instrument_id' {'data_1': u'%#L%', 'param_1':
> > datetime.datetime(2013, 1, 1, 0, 0), 'month_1': 100, 'year_1':
> > 1000, 'param_2': datetime.datetime(2013, 10, 3, 0, 0)}
> >
> >
> > The latest attempt to write the correct code is the following (the
> > range come from a web form using a javascript plugin):
> >
> > from_date = request.POST.get('from_date', '') to_date =
> > request.POST.get('to_date', '') from_date =
> > datetime.strptime(from_date, '%Y-%m-%d') to_date =
> > datetime.strptime(to_date, '%Y-%m-%d') if from_date.day < 10:
> >day = "0%s" % from_date.day
> > else:
> >day = "%s" % from_date.day
> > if from_date.month < 10:
> >month = "0%s" % from_date.month
> > else:
> >month = "%s" % from_date.month
> > if to_date.day < 10:
> >tday = "0%s" % to_date.day
> > else:
> >tday = "%s" % to_date.day
> > if to_date.month < 10:
> >tmonth = "0%s" % to_date.month
> > else:
> >tmonth = "%s" % to_date.month
> > fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' %
> > (to_date.year, tmonth, tday) print fd, td results =
> > Session.query(Plan.data,
> > Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter(
> > and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
> > to_date), Plan.data.ilike('%%%s%%' % item),)).group_by(
> > Plan.data, Plan.month,
> > Plan.instrument_id ).order_by('month', 'instrument_id').all()
> >
> > Where I'm wrong?
> >
> > Thanks to all
> > --
> > -
> > Enrico Morelli
> > System Administrator | Programmer | Web Developer
> >
> > CERM - Polo Scientifico
> > Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
> > phone: +39 055 457 4269
> > fax: +39 055 457 4253
> > -
> >
> > --
> > 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://gr
Re: [sqlalchemy] AttributeError: 'ColumnProperty' object has no attribute 'strategy'
this is definitely not related to any issue from 2007. See what happens if you call sqlalchemy.orm.configure_mappers() right before you emit that Query in this particular test.the error indicates this particular class was not present when the mappers post-configured themselves. On Oct 4, 2013, at 4:10 AM, kim wrote: > T_T > > Hi, I am using SQLAlchemy 0.8.2 with PostgreSQL and having exactly same > problem. > > I have a lot of table classes inherited declarative base and only some of > classes raise errors. (https://gist.github.com/yoloseem/d1c9b0f8d3cef6c196e4) > > Actually, even for same buggy classes, errors are appeared in some special > environment (for me, calling exactly same function works well in web > view(Flask) or shell context but always erroneous in Celery task). > > To figure out what causes this, I've turn logging level for 'sqlalchemy.orm' > to INFO, > and I found some differences between tables working well and error causing > tables. (https://gist.github.com/yoloseem/b20cb4a283212ceb0439) > Working things printed out about "_post_configure_properties()" and buggy > things printed out about "Identified primary key columns". > > please somebody help me? !! > > > 2007년 11월 24일 토요일 오전 4시 6분 3초 UTC+9, Michael Bayer 님의 말: > > On Nov 23, 2007, at 12:53 PM, lur ibargutxi wrote: > > my problem is that in my local configuration I have SQLAlchemy0.4.0 > > and collective.lead and my aplication work very well but when i try > > to do the same thing in the server I have this problem: > > > > ArgumentError: Error determining primary and/or secondary join for > > relationship ' Areas.idareatype2 (Areatypes)'. If the underlying > > error cannot be corrected, you should specify the 'primaryjoin' (and > > 'secondaryjoin', if there is an association table present) keyword > > arguments to the relation() function (or for backrefs, by specifying > > the backref using the backref() function with keyword arguments) to > > explicitly specify the join conditions. Nested error is "Can't find > > any foreign key relationships between 'areas' and 'areatypes'" > > > > okusing table reflection for your tables (i.e. autoload=True)? > MySQL with MyISAM (the default) tables ? foreign keys won't reflect > in that case, you have to either specify those columns in your Table > defs manually or use InnoDB tables in your MySQL database. > > > > -- > 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
RE: [sqlalchemy] Query for date between a range
I'm sorry, you should use hybrid_property:
from sqlalchemy.ext.hybrid import hybrid_property
class Plan(Base):
@hybrid_property
def calculated_date(self):
return date(self.year, self.month, self.day)
Also, in your query, don't use between:
session.query(Plan).\
filter(Plan.calculated_date >= from_date).\
filter(Plan.calculated_date <= to_date))
Cheers,
Ofir
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Enrico Morelli
Sent: Friday, October 04, 2013 4:05 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Query for date between a range
On Fri, 4 Oct 2013 15:55:07 +0300
"Ofir Herzas" wrote:
> You can create a custom field in your model and check against it:
>
> class Plan(Base):
> .
> .
> .
> @property
> def calculated_date(self):
> return date(self.year, self.month, self.day)
>
>
> Then, in your query, use that field:
> session.query(Plan).filter(Plan.calculated_date.between(from_date,
> to_date))
>
> Haven't checked it myself, but I guess it should work ...
Thanks, but now I receive the error:
AttributeError: 'property' object has no attribute 'between'
>
> -Original Message-
> From: sqlalchemy@googlegroups.com
> [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
> Sent: Friday, October 04, 2013 12:07 PM
> To: sqlalchemy@googlegroups.com
> Subject: [sqlalchemy] Query for date between a range
>
> Dear all,
>
> I've a table where the date is separated in single fields, one for
> year, one for day and one for month. So I need to query for a date
> range. I search in Internet and I found the following query that seems
> to be works: SELECT * FROM plan WHERE year * 1 + month * 100 + day
> BETWEEN +'20130101' AND '20130131';
>
> Now I'm trying to translate to sqlalchemy, but I receive the following
> error:
>
> DataError: (DataError) invalid input syntax for integer:
> "2013-01-01T00:00:00" LINE 3: ...year * 1000 + plan.month * 100 +
> plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data,
> plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
> count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year *
> %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s
> AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data,
> plan.month, plan.instrument_id ORDER BY month, instrument_id'
> {'data_1': u'%#L%', 'param_1': datetime.datetime(2013, 1, 1, 0, 0),
> 'month_1': 100, 'year_1': 1000, 'param_2':
> datetime.datetime(2013, 10, 3, 0, 0)}
>
>
> The latest attempt to write the correct code is the following (the
> range come from a web form using a javascript plugin):
>
> from_date = request.POST.get('from_date', '') to_date =
> request.POST.get('to_date', '') from_date =
> datetime.strptime(from_date, '%Y-%m-%d') to_date =
> datetime.strptime(to_date, '%Y-%m-%d') if from_date.day < 10:
>day = "0%s" % from_date.day
> else:
>day = "%s" % from_date.day
> if from_date.month < 10:
>month = "0%s" % from_date.month
> else:
>month = "%s" % from_date.month
> if to_date.day < 10:
>tday = "0%s" % to_date.day
> else:
>tday = "%s" % to_date.day
> if to_date.month < 10:
>tmonth = "0%s" % to_date.month
> else:
>tmonth = "%s" % to_date.month
> fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' %
> (to_date.year, tmonth, tday) print fd, td results =
> Session.query(Plan.data,
> Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter(
> and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
> to_date), Plan.data.ilike('%%%s%%' % item),)).group_by(
> Plan.data, Plan.month,
> Plan.instrument_id ).order_by('month', 'instrument_id').all()
>
> Where I'm wrong?
>
> Thanks to all
> --
> -
> Enrico Morelli
> System Administrator | Programmer | Web Developer
>
> CERM - Polo Scientifico
> Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
> phone: +39 055 457 4269
> fax: +39 055 457 4253
> -
>
> --
> 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.
>
--
-
Enrico Morelli
System Administrator | Programmer | Web Developer
CERM - Polo Scientifico
Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
phone: +39 055 457 4269
fax: +39 055 457 4253
-
Re: [sqlalchemy] Query for date between a range
On Fri, 4 Oct 2013 15:55:07 +0300
"Ofir Herzas" wrote:
> You can create a custom field in your model and check against it:
>
> class Plan(Base):
> .
> .
> .
> @property
> def calculated_date(self):
> return date(self.year, self.month, self.day)
>
>
> Then, in your query, use that field:
> session.query(Plan).filter(Plan.calculated_date.between(from_date,
> to_date))
>
> Haven't checked it myself, but I guess it should work ...
Thanks, but now I receive the error:
AttributeError: 'property' object has no attribute 'between'
>
> -Original Message-
> From: sqlalchemy@googlegroups.com
> [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
> Sent: Friday, October 04, 2013 12:07 PM
> To: sqlalchemy@googlegroups.com
> Subject: [sqlalchemy] Query for date between a range
>
> Dear all,
>
> I've a table where the date is separated in single fields, one for
> year, one for day and one for month. So I need to query for a date
> range. I search in Internet and I found the following query that
> seems to be works: SELECT *
> FROM plan
> WHERE year * 1 + month * 100 + day BETWEEN +'20130101' AND
> '20130131';
>
> Now I'm trying to translate to sqlalchemy, but I receive the following
> error:
>
> DataError: (DataError) invalid input syntax for integer:
> "2013-01-01T00:00:00" LINE 3: ...year * 1000 + plan.month * 100 +
> plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data,
> plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
> count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year *
> %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s
> AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data,
> plan.month, plan.instrument_id ORDER BY month,
> instrument_id' {'data_1': u'%#L%', 'param_1': datetime.datetime(2013,
> 1, 1, 0, 0), 'month_1': 100, 'year_1': 1000, 'param_2':
> datetime.datetime(2013, 10, 3, 0, 0)}
>
>
> The latest attempt to write the correct code is the following (the
> range come from a web form using a javascript plugin):
>
> from_date = request.POST.get('from_date', '') to_date =
> request.POST.get('to_date', '') from_date =
> datetime.strptime(from_date, '%Y-%m-%d') to_date =
> datetime.strptime(to_date, '%Y-%m-%d') if from_date.day < 10:
>day = "0%s" % from_date.day
> else:
>day = "%s" % from_date.day
> if from_date.month < 10:
>month = "0%s" % from_date.month
> else:
>month = "%s" % from_date.month
> if to_date.day < 10:
>tday = "0%s" % to_date.day
> else:
>tday = "%s" % to_date.day
> if to_date.month < 10:
>tmonth = "0%s" % to_date.month
> else:
>tmonth = "%s" % to_date.month
> fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' %
> (to_date.year, tmonth, tday) print fd, td results =
> Session.query(Plan.data,
> Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter(
> and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
> to_date), Plan.data.ilike('%%%s%%' % item),)).group_by(
> Plan.data, Plan.month,
> Plan.instrument_id ).order_by('month', 'instrument_id').all()
>
> Where I'm wrong?
>
> Thanks to all
> --
> -
> Enrico Morelli
> System Administrator | Programmer | Web Developer
>
> CERM - Polo Scientifico
> Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
> phone: +39 055 457 4269
> fax: +39 055 457 4253
> -
>
> --
> 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.
>
--
-
Enrico Morelli
System Administrator | Programmer | Web Developer
CERM - Polo Scientifico
Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
phone: +39 055 457 4269
fax: +39 055 457 4253
-
--
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] Query for date between a range
You can create a custom field in your model and check against it:
class Plan(Base):
.
.
.
@property
def calculated_date(self):
return date(self.year, self.month, self.day)
Then, in your query, use that field:
session.query(Plan).filter(Plan.calculated_date.between(from_date, to_date))
Haven't checked it myself, but I guess it should work ...
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Enrico Morelli
Sent: Friday, October 04, 2013 12:07 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Query for date between a range
Dear all,
I've a table where the date is separated in single fields, one for year, one
for day and one for month. So I need to query for a date range. I search in
Internet and I found the following query that seems to be works:
SELECT *
FROM plan
WHERE year * 1 + month * 100 + day BETWEEN +'20130101' AND '20130131';
Now I'm trying to translate to sqlalchemy, but I receive the following
error:
DataError: (DataError) invalid input syntax for integer:
"2013-01-01T00:00:00" LINE 3: ...year * 1000 + plan.month * 100 + plan.day
BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data, plan.month AS
plan_month, plan.instrument_id AS plan_instrument_id,
count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year *
%(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s AND
%(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data, plan.month,
plan.instrument_id ORDER BY month, instrument_id' {'data_1': u'%#L%',
'param_1': datetime.datetime(2013, 1, 1, 0, 0), 'month_1': 100, 'year_1':
1000, 'param_2':
datetime.datetime(2013, 10, 3, 0, 0)}
The latest attempt to write the correct code is the following (the range
come from a web form using a javascript plugin):
from_date = request.POST.get('from_date', '') to_date =
request.POST.get('to_date', '') from_date = datetime.strptime(from_date,
'%Y-%m-%d') to_date = datetime.strptime(to_date, '%Y-%m-%d') if
from_date.day < 10:
day = "0%s" % from_date.day
else:
day = "%s" % from_date.day
if from_date.month < 10:
month = "0%s" % from_date.month
else:
month = "%s" % from_date.month
if to_date.day < 10:
tday = "0%s" % to_date.day
else:
tday = "%s" % to_date.day
if to_date.month < 10:
tmonth = "0%s" % to_date.month
else:
tmonth = "%s" % to_date.month
fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' % (to_date.year,
tmonth, tday) print fd, td results = Session.query(Plan.data,
Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter(
and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
to_date), Plan.data.ilike('%%%s%%' % item),)).group_by(
Plan.data, Plan.month,
Plan.instrument_id ).order_by('month', 'instrument_id').all()
Where I'm wrong?
Thanks to all
--
-
Enrico Morelli
System Administrator | Programmer | Web Developer
CERM - Polo Scientifico
Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
phone: +39 055 457 4269
fax: +39 055 457 4253
-
--
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.
[sqlalchemy] Query for date between a range
Dear all,
I've a table where the date is separated in single fields, one for
year, one for day and one for month. So I need to query for a date
range. I search in Internet and I found the following query that seems
to be works:
SELECT *
FROM plan
WHERE year * 1 + month * 100 + day BETWEEN +'20130101' AND
'20130131';
Now I'm trying to translate to sqlalchemy, but I receive the following
error:
DataError: (DataError) invalid input syntax for integer:
"2013-01-01T00:00:00" LINE 3: ...year * 1000 + plan.month * 100 +
plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data,
plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year *
%(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s
AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data,
plan.month, plan.instrument_id ORDER BY month,
instrument_id' {'data_1': u'%#L%', 'param_1': datetime.datetime(2013,
1, 1, 0, 0), 'month_1': 100, 'year_1': 1000, 'param_2':
datetime.datetime(2013, 10, 3, 0, 0)}
The latest attempt to write the correct code is the following (the
range come from a web form using a javascript plugin):
from_date = request.POST.get('from_date', '')
to_date = request.POST.get('to_date', '')
from_date = datetime.strptime(from_date, '%Y-%m-%d')
to_date = datetime.strptime(to_date, '%Y-%m-%d')
if from_date.day < 10:
day = "0%s" % from_date.day
else:
day = "%s" % from_date.day
if from_date.month < 10:
month = "0%s" % from_date.month
else:
month = "%s" % from_date.month
if to_date.day < 10:
tday = "0%s" % to_date.day
else:
tday = "%s" % to_date.day
if to_date.month < 10:
tmonth = "0%s" % to_date.month
else:
tmonth = "%s" % to_date.month
fd = '%s%s%s' % (from_date.year, month, day)
td = '%s%s%s' % (to_date.year, tmonth, tday)
print fd, td
results = Session.query(Plan.data,
Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter(
and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
to_date), Plan.data.ilike('%%%s%%' % item),)).group_by(
Plan.data, Plan.month,
Plan.instrument_id ).order_by('month', 'instrument_id').all()
Where I'm wrong?
Thanks to all
--
-
Enrico Morelli
System Administrator | Programmer | Web Developer
CERM - Polo Scientifico
Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
phone: +39 055 457 4269
fax: +39 055 457 4253
-
--
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] AttributeError: 'ColumnProperty' object has no attribute 'strategy'
T_T Hi, I am using SQLAlchemy 0.8.2 with PostgreSQL and having exactly same problem. I have a lot of table classes inherited declarative base and only some of classes raise errors. (https://gist.github.com/yoloseem/d1c9b0f8d3cef6c196e4 ) Actually, even for same buggy classes, errors are appeared in some special environment (for me, calling exactly same function works well in web view(Flask) or shell context but always erroneous in Celery task). To figure out what causes this, I've turn logging level for 'sqlalchemy.orm' to INFO, and I found some differences between tables working well and error causing tables. (https://gist.github.com/yoloseem/b20cb4a283212ceb0439) Working things printed out about "_post_configure_properties()" and buggy things printed out about "Identified primary key columns". please somebody help me? !! 2007년 11월 24일 토요일 오전 4시 6분 3초 UTC+9, Michael Bayer 님의 말: > > > On Nov 23, 2007, at 12:53 PM, lur ibargutxi wrote: > > > my problem is that in my local configuration I have SQLAlchemy0.4.0 > > and collective.lead and my aplication work very well but when i try > > to do the same thing in the server I have this problem: > > > > ArgumentError: Error determining primary and/or secondary join for > > relationship ' Areas.idareatype2 (Areatypes)'. If the underlying > > error cannot be corrected, you should specify the 'primaryjoin' (and > > 'secondaryjoin', if there is an association table present) keyword > > arguments to the relation() function (or for backrefs, by specifying > > the backref using the backref() function with keyword arguments) to > > explicitly specify the join conditions. Nested error is "Can't find > > any foreign key relationships between 'areas' and 'areatypes'" > > > > okusing table reflection for your tables (i.e. autoload=True)? > MySQL with MyISAM (the default) tables ? foreign keys won't reflect > in that case, you have to either specify those columns in your Table > defs manually or use InnoDB tables in your MySQL database. > > -- 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.
