[sqlalchemy] many queries select if in cycle has insert into table
my program code:
engine = create_engine(connect_str, echo=True)Session =
sessionmaker(bind=engine)for bar in default_session.query(BarLog)[:3]:
conf = ManagerConfig(indicator_config='', timeframe=bar.timeframe,
paper_no=1)
default_session.add(conf)
default_session.commit()
log:
2013-08-29 22:52:58,640 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-08-29 22:52:58,642 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id AS
bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min,
bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume AS
bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS
bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS
bar_log_paper_no
FROM bar_log
LIMIT %(param_1)s
2013-08-29 22:52:58,642 INFO sqlalchemy.engine.base.Engine {'param_1': 3}
2013-08-29 22:52:58,646 INFO sqlalchemy.engine.base.Engine INSERT INTO
manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s,
%(timeframe)s, %(indicator_config)s) RETURNING manager_config.id
2013-08-29 22:52:58,646 INFO sqlalchemy.engine.base.Engine {'indicator_config':
‘', ’paper_no': 1, ‘timeframe’: 1}
2013-08-29 22:52:58,647 INFO sqlalchemy.engine.base.Engine COMMIT
2013-08-29 22:52:58,662 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-08-29 22:52:58,664 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id AS
bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min,
bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume AS
bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS
bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS
bar_log_paper_no
FROM bar_log
WHERE bar_log.id = %(param_1)s
2013-08-29 22:52:58,664 INFO sqlalchemy.engine.base.Engine {'param_1': 2}
2013-08-29 22:52:58,667 INFO sqlalchemy.engine.base.Engine INSERT INTO
manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s,
%(timeframe)s, %(indicator_config)s) RETURNING manager_config.id
2013-08-29 22:52:58,668 INFO sqlalchemy.engine.base.Engine {'indicator_config':
‘', ’paper_no': 1, ‘timeframe’: 1}
2013-08-29 22:52:58,670 INFO sqlalchemy.engine.base.Engine COMMIT
2013-08-29 22:52:58,679 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-08-29 22:52:58,681 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id AS
bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min,
bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume AS
bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS
bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS
bar_log_paper_no
FROM bar_log
WHERE bar_log.id = %(param_1)s
2013-08-29 22:52:58,681 INFO sqlalchemy.engine.base.Engine {'param_1': 3}
2013-08-29 22:52:58,685 INFO sqlalchemy.engine.base.Engine INSERT INTO
manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s,
%(timeframe)s, %(indicator_config)s) RETURNING manager_config.id
2013-08-29 22:52:58,685 INFO sqlalchemy.engine.base.Engine {'indicator_config':
‘', ’paper_no': 1, ‘timeframe’: 1}
2013-08-29 22:52:58,688 INFO sqlalchemy.engine.base.Engine COMMIT
how to use one select query and many insert queries?
--
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] Calculate birthdays
Nice to start some discussion and sorry about the unclarity. Gulli, you are spot on with your code. Thanks! Next challange is to get all the birthdays of the next 7 days. This code does *not *do the trick; members_next = Member.query.filter( \ (extract(MONTH, Member.dateofbirth).between(today.month, nextdays.month) \ (extract(DAY, Member.dateofbirth).between(today.day, nextdays.day) \ ).all() Because a birthday at 31-08 will not be between ( 30, 6). Any idea's? -- 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] many queries select if in cycle has insert into table
On Fri, Aug 30, 2013 at 9:10 AM, Дмитрий Косолапов
kosolapo...@gmail.com wrote:
my program code:
engine = create_engine(connect_str, echo=True)
Session = sessionmaker(bind=engine)
for bar in default_session.query(BarLog)[:3]:
conf = ManagerConfig(indicator_config='', timeframe=bar.timeframe,
paper_no=1)
default_session.add(conf)
default_session.commit()
log:
2013-08-29 22:52:58,640 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-08-29 22:52:58,642 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id
AS bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min,
bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume
AS bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS
bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS
bar_log_paper_no
FROM bar_log
LIMIT %(param_1)s
2013-08-29 22:52:58,642 INFO sqlalchemy.engine.base.Engine {'param_1': 3}
2013-08-29 22:52:58,646 INFO sqlalchemy.engine.base.Engine INSERT INTO
manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s,
%(timeframe)s, %(indicator_config)s) RETURNING manager_config.id
2013-08-29 22:52:58,646 INFO sqlalchemy.engine.base.Engine
{'indicator_config': '', 'paper_no': 1, 'timeframe': 1}
2013-08-29 22:52:58,647 INFO sqlalchemy.engine.base.Engine COMMIT
2013-08-29 22:52:58,662 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-08-29 22:52:58,664 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id
AS bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min,
bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume
AS bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS
bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS
bar_log_paper_no
FROM bar_log
WHERE bar_log.id = %(param_1)s
2013-08-29 22:52:58,664 INFO sqlalchemy.engine.base.Engine {'param_1': 2}
2013-08-29 22:52:58,667 INFO sqlalchemy.engine.base.Engine INSERT INTO
manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s,
%(timeframe)s, %(indicator_config)s) RETURNING manager_config.id
2013-08-29 22:52:58,668 INFO sqlalchemy.engine.base.Engine
{'indicator_config': '', 'paper_no': 1, 'timeframe': 1}
2013-08-29 22:52:58,670 INFO sqlalchemy.engine.base.Engine COMMIT
2013-08-29 22:52:58,679 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-08-29 22:52:58,681 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id
AS bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min,
bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume
AS bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS
bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS
bar_log_paper_no
FROM bar_log
WHERE bar_log.id = %(param_1)s
2013-08-29 22:52:58,681 INFO sqlalchemy.engine.base.Engine {'param_1': 3}
2013-08-29 22:52:58,685 INFO sqlalchemy.engine.base.Engine INSERT INTO
manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s,
%(timeframe)s, %(indicator_config)s) RETURNING manager_config.id
2013-08-29 22:52:58,685 INFO sqlalchemy.engine.base.Engine
{'indicator_config': '', 'paper_no': 1, 'timeframe': 1}
2013-08-29 22:52:58,688 INFO sqlalchemy.engine.base.Engine COMMIT
how to use one select query and many insert queries?
The reason you are getting a new SELECT each time is that, by default,
session.commit() expires all the objects in the session. This is
usually what you want, as you generally want to get the latest state
from the database after each transaction has finished.
If you don't want that behaviour, you can set expire_on_commit to
False when constructing your session:
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#sqlalchemy.orm.session.Session.commit
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] many queries select if in cycle has insert into table
The reason for the extra selects is that calling commit() marks objects in
the session as expired, so they need to be refreshed. From the ORM
tutorialhttp://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html
:
SQLAlchemy by default refreshes data from a previous transaction the first
time it’s accessed within a new transaction, so that the most recent state
is available. The level of reloading is configurable as is described in *Using
the Session* http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html.
From the section on
committinghttp://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#committing
:
Another behavior of commit() is that by default it expires the state of all
instances present after the commit is complete. This is so that when the
instances are next accessed, either through attribute access or by them
being present in a Query result set, they receive the most recent state.
To disable this behavior, configure sessionmaker with
expire_on_commit=False.
The other option, is to just commit after the loop, not inside it. That is
OK if the loop is not too long-running and there aren't tricky locking
ramifications — and indeed it may be preferable if you want to make sure
the ManagerConfigs you create are transactionally consistent with the
BarLogs they are based on; for that you might also consider loading the
BarLogs .with_lockmode('read')
Gulli
On Fri, Aug 30, 2013 at 8:10 AM, Дмитрий Косолапов kosolapo...@gmail.comwrote:
my program code:
engine = create_engine(connect_str, echo=True)Session =
sessionmaker(bind=engine)for bar in default_session.query(BarLog)[:3]:
conf = ManagerConfig(indicator_config='', timeframe=bar.timeframe,
paper_no=1)
default_session.add(conf)
default_session.commit()
log:
2013-08-29 22:52:58,640 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-08-29 22:52:58,642 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id
AS bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min,
bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume
AS bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS
bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS
bar_log_paper_no
FROM bar_log
LIMIT %(param_1)s
2013-08-29 22:52:58,642 INFO sqlalchemy.engine.base.Engine {'param_1': 3}
2013-08-29 22:52:58,646 INFO sqlalchemy.engine.base.Engine INSERT INTO
manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s,
%(timeframe)s, %(indicator_config)s) RETURNING manager_config.id
2013-08-29 22:52:58,646 INFO sqlalchemy.engine.base.Engine
{'indicator_config': ‘', ’paper_no': 1, ‘timeframe’: 1}
2013-08-29 22:52:58,647 INFO sqlalchemy.engine.base.Engine COMMIT
2013-08-29 22:52:58,662 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-08-29 22:52:58,664 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id
AS bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min,
bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume
AS bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS
bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS
bar_log_paper_no
FROM bar_log
WHERE bar_log.id = %(param_1)s
2013-08-29 22:52:58,664 INFO sqlalchemy.engine.base.Engine {'param_1': 2}
2013-08-29 22:52:58,667 INFO sqlalchemy.engine.base.Engine INSERT INTO
manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s,
%(timeframe)s, %(indicator_config)s) RETURNING manager_config.id
2013-08-29 22:52:58,668 INFO sqlalchemy.engine.base.Engine
{'indicator_config': ‘', ’paper_no': 1, ‘timeframe’: 1}
2013-08-29 22:52:58,670 INFO sqlalchemy.engine.base.Engine COMMIT
2013-08-29 22:52:58,679 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-08-29 22:52:58,681 INFO sqlalchemy.engine.base.Engine SELECT bar_log.id
AS bar_log_id, bar_log.max AS bar_log_max, bar_log.min AS bar_log_min,
bar_log.open AS bar_log_open, bar_log.close AS bar_log_close, bar_log.volume
AS bar_log_volume, bar_log.time_start AS bar_log_time_start, bar_log.date AS
bar_log_date, bar_log.timeframe AS bar_log_timeframe, bar_log.paper_no AS
bar_log_paper_no
FROM bar_log
WHERE bar_log.id = %(param_1)s
2013-08-29 22:52:58,681 INFO sqlalchemy.engine.base.Engine {'param_1': 3}
2013-08-29 22:52:58,685 INFO sqlalchemy.engine.base.Engine INSERT INTO
manager_config (paper_no, timeframe, indicator_config) VALUES (%(paper_no)s,
%(timeframe)s, %(indicator_config)s) RETURNING manager_config.id
2013-08-29 22:52:58,685 INFO sqlalchemy.engine.base.Engine
{'indicator_config': ‘', ’paper_no': 1, ‘timeframe’: 1}
2013-08-29 22:52:58,688 INFO sqlalchemy.engine.base.Engine COMMIT
how to use one select query and many insert queries?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails
[sqlalchemy] How-to filter by the time part of a datetime field?
Hi, I've a model with a DateTime column and I need to select rows where the time part of this column is between two hours (something like: select all rows where the date is between 6:00 and 11:00). I need the date information, rows must be deleted after a couple of days. I don't know how-to extract the time part of my DateTime field in .filter(). Should I add a Time column ? Or is it possible to use only the DateTime column? Thanks. -- Laurent Meunier laur...@deltalima.net -- 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] How-to filter by the time part of a datetime field?
Hi, I've a model with a DateTime column and I need to select rows where the time part of this column is between two hours (something like: select all rows where the date is between 6:00 and 11:00). I need the date information, rows must be deleted after a couple of days. I don't know how-to extract the time part of my DateTime field in .filter(). Should I add a Time column ? Or is it possible to use only the DateTime column? Thanks. -- Laurent Meunier laur...@deltalima.net -- 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. Hi Laurent I'm sure there is a better way, but you could always filter using a date/time and supply the date part as well (i.e. today) so that you are comparing datetime to datetime. (Something like: select all rows where the datetime is between 2013-08-30 06:00:00 and 2013-08-30 11:00:00) :-) Cheers Warwick -- 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] How-to filter by the time part of a datetime field?
Hi Warwick, On 30/08/2013 14:38, Warwick Prince wrote: I'm sure there is a better way, but you could always filter using a date/time and supply the date part as well (i.e. today) so that you are comparing datetime to datetime. (Something like: select all rows where the datetime is between 2013-08-30 06:00:00 and 2013-08-30 11:00:00) :-) Thanks for your suggestion, this could do the trick. However my rows are split over a lot of days and if I follow your advice I'll end with a lot of between 2013-08-30 06:00:00 and 2013-08-30 11:00:00 (one for each day). This will certainly work as expected, but I'm looking for a more efficient way of doing this. Thanks. -- Laurent Meunier laur...@deltalima.net -- 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] How-to filter by the time part of a datetime field?
Hi Warwick, On 30/08/2013 14:38, Warwick Prince wrote: I'm sure there is a better way, but you could always filter using a date/time and supply the date part as well (i.e. today) so that you are comparing datetime to datetime. (Something like: select all rows where the datetime is between 2013-08-30 06:00:00 and 2013-08-30 11:00:00) :-) Thanks for your suggestion, this could do the trick. However my rows are split over a lot of days and if I follow your advice I'll end with a lot of between 2013-08-30 06:00:00 and 2013-08-30 11:00:00 (one for each day). This will certainly work as expected, but I'm looking for a more efficient way of doing this. Thanks. -- Laurent Meunier laur...@deltalima.net Ahh - I see. Oh well, I'm sure someone with infinitely better SQL skills with chime in shortly. :-) Warwick -- 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] Getting the identity key of a mapped instance w/o SQL query
Hi *,
I am trying to cache SQLAlchemy queries in memory for a rich client
application. To invalidate the cache for changes seen in the database, I
am trying to drop in-memory instances that have been changed or deleted.
This requires comparing the identity of the deleted objects with
in-memory objects. I tried using identity_key for this and failed,
because it tries to reload from the database and I expire the instances
when I am told they had some changes.
The attached IPython notebook shows the behaviour. Short summary:
Reloads expired state (potential ObjectDeletedError)
identity_key(instance=instance)
mapper.identity_key_from_instance(instance)
mapper.primary_key_from_instance(instance)
Uses old primary key (no reload, no ObjectDeletedError)
object_state(user).identity_key
object_state(user).identity
object_state(user).key
The main reason why I care is that identity_key may generate database
queries which kill any performance improvement of my query cache.
I think this should be documented in SQLAlchemy, I did not expect those
functions to ever raise an exception.
Please consider extending the documentation via my attached patch (also
adds a unit test for the ObjectDeletedError).
Greetings, Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-312002-10
Fax: +49-(0)351-312002-29
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
DYNAmore Gesellschaft für FEM Ingenieurdienstleistungen mbH
Registration court: Stuttgart, HRB 733694
Managing director: Prof. Dr. Karl Schweizerhof, Dipl.-Math. Ulrich Franz
--
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.
{
metadata: {
name:
},
nbformat: 3,
nbformat_minor: 0,
worksheets: [
{
cells: [
{
cell_type: code,
collapsed: false,
input: [
import sqlalchemy\n,
sqlalchemy.__version__
],
language: python,
metadata: {
slideshow: {
slide_type: slide
}
},
outputs: [
{
output_type: pyout,
prompt_number: 1,
text: [
'0.9.0'
]
}
],
prompt_number: 1
},
{
cell_type: code,
collapsed: false,
input: [
from sqlalchemy import Column, Integer, String, create_engine\n,
from sqlalchemy.orm import sessionmaker\n,
from sqlalchemy.orm.util import identity_key, object_state,
object_mapper, class_mapper\n,
from sqlalchemy.ext.declarative import declarative_base\n,
\n,
Base = declarative_base()\n,
\n,
class User(Base):\n,
__tablename__ = 'users'\n,
\n,
id = Column(Integer, primary_key=True)\n,
name = Column(String(50))\n,
\n,
engine = create_engine(\sqlite:///\)\n,
Base.metadata.create_all(engine)\n,
\n,
Session = sessionmaker(engine)\n,
session = Session()\n,
\n,
user = User(name=\Joe\)\n,
session.add(user)\n,
session.commit()
],
language: python,
metadata: {
slideshow: {
slide_type: slide
}
},
outputs: [],
prompt_number: 2
},
{
cell_type: code,
collapsed: false,
input: [
identity_key(instance=user)
],
language: python,
metadata: {
slideshow: {
slide_type: subslide
}
},
outputs: [
{
output_type: pyout,
prompt_number: 3,
text: [
(__main__.User, (1,))
]
}
],
prompt_number: 3
},
{
cell_type: code,
collapsed: false,
input: [
object_state(user).identity_key
],
language: python,
metadata: {
slideshow: {
slide_type: subslide
}
},
outputs: [
{
output_type: pyout,
prompt_number: 4,
text: [
(__main__.User, (1,))
]
}
],
prompt_number: 4
},
{
cell_type: code,
collapsed: false,
input: [
object_state(user).identity
],
language: python,
metadata: {
slideshow: {
slide_type: subslide
}
},
outputs: [
{
output_type: pyout,
prompt_number: 5,
text: [
(1,)
]
}
],
prompt_number: 5
},
{
cell_type: code,
collapsed: false,
input: [
object_state(user).key
],
language: python,
metadata: {
slideshow: {
slide_type: fragment
}
},
outputs: [
{
output_type: pyout,
prompt_number: 6,
text: [
(__main__.User, (1,))
RE: [sqlalchemy] How-to filter by the time part of a datetime field?
One way to do this is to use a function within your database to convert a
timestamp down to a basic time type, and then do comparison on the converted
value. Here is an example using sqlite as the back end. Sqlite has a time
function that can convert a datetime down to a time for you, so we get at
that using sqlalchemy's func:
from sqlalchemy import create_engine, Column, Integer, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///test.sqlite')
Session = sessionmaker(bind=engine)
Base = declarative_base()
class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
timestamp = Column(DateTime, nullable=False)
Base.metadata.create_all(bind=engine)
# This is just sticking random timestamps into the database...
import datetime
import random
session = Session()
session.query(Test).delete()
for i in range(100):
d = random.randint(1, 30)
h = random.randint(0, 23)
m = random.randint(0, 59)
test = Test()
test.timestamp = datetime.datetime(2013, 8, d, h, m)
session.add(test)
session.commit()
# Heres the important part. Pull in func
from sqlalchemy import func
# Say we want any timestamp in the db regardless of date where the time
# is between 12:00 and 12:30
t1 = datetime.time(12, 00)
t2 = datetime.time(12, 30)
query = session.query(Test).filter(func.time(Test.timestamp).between(t1,
t2))
for row in query.all():
print(row.timestamp)
Regards,
Jeff Peck
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Warwick Prince
Sent: Friday, August 30, 2013 8:01 AM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] How-to filter by the time part of a datetime
field?
Hi Warwick,
On 30/08/2013 14:38, Warwick Prince wrote:
I'm sure there is a better way, but you could always filter using a
date/time and supply the date part as well (i.e. today) so that you are
comparing datetime to datetime. (Something like: select all rows where the
datetime is between 2013-08-30 06:00:00 and 2013-08-30 11:00:00) :-)
Thanks for your suggestion, this could do the trick.
However my rows are split over a lot of days and if I follow your advice
I'll end with a lot of between 2013-08-30 06:00:00 and 2013-08-30 11:00:00
(one for each day). This will certainly work as expected, but I'm looking
for a more efficient way of doing this.
Thanks.
--
Laurent Meunier laur...@deltalima.net
Ahh - I see. Oh well, I'm sure someone with infinitely better SQL skills
with chime in shortly. :-)
Warwick
--
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.
--
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] How-to filter by the time part of a datetime field?
Wonderfull! Exactly what I was looking for. Thank you Jeff.
I was searching in the sqlalchemy documentation for a time function,
but haven't found one. In fact, you can use any function supported by
the database directly with `func`. So I can use `func.time` and this
will use the TIME function of sqlite ... awesome! I've learnt something
new today :)
On 30/08/2013 15:53, Jeff Peck wrote:
One way to do this is to use a function within your database to convert a
timestamp down to a basic time type, and then do comparison on the converted
value. Here is an example using sqlite as the back end. Sqlite has a time
function that can convert a datetime down to a time for you, so we get at
that using sqlalchemy's func:
from sqlalchemy import create_engine, Column, Integer, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///test.sqlite')
Session = sessionmaker(bind=engine)
Base = declarative_base()
class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
timestamp = Column(DateTime, nullable=False)
Base.metadata.create_all(bind=engine)
# This is just sticking random timestamps into the database...
import datetime
import random
session = Session()
session.query(Test).delete()
for i in range(100):
d = random.randint(1, 30)
h = random.randint(0, 23)
m = random.randint(0, 59)
test = Test()
test.timestamp = datetime.datetime(2013, 8, d, h, m)
session.add(test)
session.commit()
# Heres the important part. Pull in func
from sqlalchemy import func
# Say we want any timestamp in the db regardless of date where the time
# is between 12:00 and 12:30
t1 = datetime.time(12, 00)
t2 = datetime.time(12, 30)
query = session.query(Test).filter(func.time(Test.timestamp).between(t1,
t2))
for row in query.all():
print(row.timestamp)
Regards,
Jeff Peck
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Warwick Prince
Sent: Friday, August 30, 2013 8:01 AM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] How-to filter by the time part of a datetime
field?
Hi Warwick,
On 30/08/2013 14:38, Warwick Prince wrote:
I'm sure there is a better way, but you could always filter using a
date/time and supply the date part as well (i.e. today) so that you are
comparing datetime to datetime. (Something like: select all rows where the
datetime is between 2013-08-30 06:00:00 and 2013-08-30 11:00:00) :-)
Thanks for your suggestion, this could do the trick.
However my rows are split over a lot of days and if I follow your advice
I'll end with a lot of between 2013-08-30 06:00:00 and 2013-08-30 11:00:00
(one for each day). This will certainly work as expected, but I'm looking
for a more efficient way of doing this.
Thanks.
--
Laurent Meunier laur...@deltalima.net
Ahh - I see. Oh well, I'm sure someone with infinitely better SQL skills
with chime in shortly. :-)
Warwick
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy hot-copy capability
Hi Pierre! SQLAlchemy doesn't do that, because it depends on the underlying connection layer but I already had this question and made a solution. You simply have to use another SQLite library, ASPW, - https://code.google.com/p/apsw/ There are some material over the web (Stack Exchange) that shows how to do it. If you still can't do it, let me now that I can share some code with you. Best regards, Richard. On 08/30/2013 11:46 AM, pr64 wrote: Hi, I'm currently running several python applications (each app using sqlalchemy) accessing (read/write) a single SQLite database stored on disk. For performance reasons, I would like to store this db file in RAM memory (ie, in my /dev/shm) The applications would then access a shared in-memory db through SQLAlchemy and a backup application would periodically make a hot copy of the in-memory db to disk. Then, on a [power off, power on] sequence, this backup app would copy the backed up db file from disk to RAM before launching the other apps. Before starting, I would like to know if you think it is feaseable. My questions are: 1- Has SQLALchemy an API to do a hot copy? (based on http://sqlite.org/backup.html for this particular db type) 2- If so, is this an actual hot copy, ie: the other apps will still run without waiting for the backup app to finish he backup? 3- Is there a particular configuration in SQLAlchemy that enables sharing an in-momory db from different apps (python processes)? Thanks a lot for your feedback, Pierre -- 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] SQLAlchemy hot-copy capability
Hi, I'm currently running several python applications (each app using sqlalchemy) accessing (read/write) a single SQLite database stored on disk. For performance reasons, I would like to store this db file in RAM memory (ie, in my /dev/shm) The applications would then access a shared in-memory db through SQLAlchemy and a backup application would periodically make a hot copy of the in-memory db to disk. Then, on a [power off, power on] sequence, this backup app would copy the backed up db file from disk to RAM before launching the other apps. Before starting, I would like to know if you think it is feaseable. My questions are: 1- Has SQLALchemy an API to do a hot copy? (based on http://sqlite.org/backup.html for this particular db type) 2- If so, is this an actual hot copy, ie: the other apps will still run without waiting for the backup app to finish he backup? 3- Is there a particular configuration in SQLAlchemy that enables sharing an in-momory db from different apps (python processes)? Thanks a lot for your feedback, Pierre -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
RE: [sqlalchemy] SQLAlchemy hot-copy capability
Hi, I'm currently running several python applications (each app using sqlalchemy) accessing (read/write) a single SQLite database stored on disk. For performance reasons, I would like to store this db file in RAM memory (ie, in my /dev/shm) The applications would then access a shared in-memory db through SQLAlchemy and a backup application would periodically make a hot copy of the in-memory db to disk. Then, on a [power off, power on] sequence, this backup app would copy the backed up db file from disk to RAM before launching the other apps. Before starting, I would like to know if you think it is feaseable. My questions are: 1- Has SQLALchemy an API to do a hot copy? (based on http://sqlite.org/backup.html http://sqlite.org/backup.html for this particular db type) 2- If so, is this an actual hot copy, ie: the other apps will still run without waiting for the backup app to finish he backup? 3- Is there a particular configuration in SQLAlchemy that enables sharing an in-momory db from different apps (python processes)? Thanks a lot for your feedback, Pierre -- Pierre, While I do think this is feasible, I would discourage going down this path unless you have a really good reason. It sounds like you need a real database engine like postgres here. It basically does everything you describe out of the box, is easy to set up, and will likely be more reliable than anything you could come up with on your own. It will also perform better when dealing with many simultaneous transactions. If you still run into performance problems you could look into introducing a caching layer such as memcached, but I wouldn't cross that bridge until I had thoroughly tweaked my db settings and identified real bottlenecks in my application(s). Jeff Peck -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] sqlite string concats and datetime arithmetics
I recently had exactly the same problem on SQLAlchemy 0.8 where doing
something like somestring + Item.some_column would not result in a ||
but in a + operator which sqlite could not handle. To note here in my
case: The some_column was the id, thus an integer. I'm not sure how
SQLAlchemy handles the operators exactly, but could this be a bug? Or is it
intended behaviour?
On Thursday, August 29, 2013 8:58:55 PM UTC+2, Gunnlaugur Briem wrote:
I would have expected the SQLite dialect to know how to compile concat to
|| if that's the operator. But failing that, something more explicit like
this ought to do the trick:
from sqlalchemy.sql import literal_column
literal_column('+ ').op('||')(seconds.c.n).op('||')(literal_column('
seconds'))
Gulli
On Thu, Aug 29, 2013 at 12:33 PM, Greg Yang sorcer...@gmail.comjavascript:
wrote:
I'm trying to get a series of datetimes using func.datetime. The format
of input is func.datetime(basetime, '+ NNN seconds'), which works nicely if
the shift applied is constant. However I need to add 10, 20, 30 seconds,
etc to this base time. So I want something like func.datetime(basetime,
concat('+', seconds.c.n, ' seconds')), but concat doesn't work for sqlite,
which concatenates using the '||' operator. Is there working method to
concat in sqlite?
Failing that, is there another way to get at what I want with datetime
arithmetics?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] How-to filter by the time part of a datetime field?
In addition to using `func.*` methods, there's also the `extract` method that works (in most databases) specifically on datetime objects. sqlalchemy.sql.expression.extract(*field*, *expr*)http://docs.sqlalchemy.org/en/rel_0_8/core/sqlelement.html?highlight=extract#sqlalchemy.sql.expression.extract in any event, the general approach is the same -- the query should tell the database to turn a datetime into just a time, and compare that value to your filter. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy hot-copy capability
Well, what Jeff wrote is also true. I do hot-copy of databases because we have a set of products that have full automated builds and, to increase performance, I made the build generate the SQLite database on memory and then dump it to the filesystem. Cheers, Richard. Hi, I'm currently running several python applications (each app using sqlalchemy) accessing (read/write) a single SQLite database stored on disk. For performance reasons, I would like to store this db file in RAM memory (ie, in my /dev/shm) The applications would then access a shared in-memory db through SQLAlchemy and a backup application would periodically make a hot copy of the in-memory db to disk. Then, on a [power off, power on] sequence, this backup app would copy the backed up db file from disk to RAM before launching the other apps. Before starting, I would like to know if you think it is feaseable. My questions are: 1- Has SQLALchemy an API to do a hot copy? (based on http://sqlite.org/backup.htmlfor this particular db type) 2- If so, is this an actual hot copy, ie: the other apps will still run without waiting for the backup app to finish he backup? 3- Is there a particular configuration in SQLAlchemy that enables sharing an in-momory db from different apps (python processes)? Thanks a lot for your feedback, Pierre -- Pierre, While I do think this is feasible, I would discourage going down this path unless you have a really good reason. It sounds like you need a real database engine like postgres here. It basically does everything you describe out of the box, is easy to set up, and will likely be more reliable than anything you could come up with on your own. It will also perform better when dealing with many simultaneous transactions. If you still run into performance problems you could look into introducing a caching layer such as memcached, but I wouldn't cross that bridge until I had thoroughly tweaked my db settings and identified real bottlenecks in my application(s). Jeff Peck -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Calculate birthdays
that's less of a SqlAlchemy question and more of a general database question. there are a handful of approaches on StackOverflow, and the easier approach can differ across databases. try searching for birthdate/birthday range and postgresql or mysql . I'd suggest that you find one there, test the raw sql against your dataset, and then people here can help convert the raw sql to your sqlalchemy model. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] sqlite string concats and datetime arithmetics
Oh, that's what it is. The override of the addition + operator by the
concatenation operator || only happens if the right-hand side type is also
a “concatenable” or NULL, not just the left side:
https://github.com/zzzeek/sqlalchemy/blob/rel_0_8_2/lib/sqlalchemy/types.py#L1017-L1023
and integer types are not marked as concatenable. Minimal test of this:
from sqlalchemy.types import Text, Integer
from sqlalchemy.sql import literal_column
print literal_column('foo', type_=Text) + literal_column('bar',
type_=Text)
print literal_column('foo', type_=Text) + literal_column(3,
type_=Integer)
This prints:
'foo' || 'bar'
'foo' + 3
Not sure if it would be unambiguously correct to apply the concatenation
override whenever the left side is a concatenable; seems superficially like
it might be so, but there may well be cases where that's a problem. If so,
this is probably not a bug.
In any case, you can sidestep the whole issue of how + is interpreted, by
being explicit about what you meant, using .concat():
print literal_column('foo', type_=Text).concat(literal_column(3,
type_=Integer))
which prints what you wanted:
'foo' || 3
Gulli
On Fri, Aug 30, 2013 at 3:34 PM, Florian Rüchel
florian.ruec...@gmail.comwrote:
I recently had exactly the same problem on SQLAlchemy 0.8 where doing
something like somestring + Item.some_column would not result in a ||
but in a + operator which sqlite could not handle. To note here in my
case: The some_column was the id, thus an integer. I'm not sure how
SQLAlchemy handles the operators exactly, but could this be a bug? Or is it
intended behaviour?
On Thursday, August 29, 2013 8:58:55 PM UTC+2, Gunnlaugur Briem wrote:
I would have expected the SQLite dialect to know how to compile concatto
|| if that's the operator. But failing that, something more explicit
like this ought to do the trick:
from sqlalchemy.sql import literal_column
literal_column('+ ').op('||')(seconds.c.n).op('**||')(literal_column('
seconds'))
Gulli
On Thu, Aug 29, 2013 at 12:33 PM, Greg Yang sorcer...@gmail.com wrote:
I'm trying to get a series of datetimes using func.datetime. The format
of input is func.datetime(basetime, '+ NNN seconds'), which works nicely if
the shift applied is constant. However I need to add 10, 20, 30 seconds,
etc to this base time. So I want something like func.datetime(basetime,
concat('+', seconds.c.n, ' seconds')), but concat doesn't work for sqlite,
which concatenates using the '||' operator. Is there working method to
concat in sqlite?
Failing that, is there another way to get at what I want with datetime
arithmetics?
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+...@**googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at
http://groups.google.com/**group/sqlalchemyhttp://groups.google.com/group/sqlalchemy
.
For more options, visit
https://groups.google.com/**groups/opt_outhttps://groups.google.com/groups/opt_out
.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] sqlite string concats and datetime arithmetics
This might be a bug then. String || Integer ; Integer || String - PostgreSQL and sqlite both allow for a sting integer to be concat together into a string. Order does not matter. Integer || Integer - PostgreSQL will error if 2 ints are concat together. - sqlite seems to cast both into a string, and returns a string ( i.e. Select 1 || 2 == 12 == str(12) ) -- 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] checking script validity
none that I know of beside actually running it on that target database and see if it raises any errors. On Aug 30, 2013, at 1:30 AM, monosij.for...@gmail.com wrote: Hello - Not very familiar with sqlalchemy yet ... Is there a way to check if a script I have generated (not using sqlalchemy) is valid for a particular DBMS (Postgres, MySQL, etc.) It would be an ANSI SQL script and there is module ansisql but not able to find where I can check a script for validity against a particular DBMS (Postgres). I would need to validate a create table script as well index creation script and foreign key constraint script. Thank you. -- 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] sqlite string concats and datetime arithmetics
(note: please keep answering the emails! this is great, I just happen to have a little bit of net access here..) here's how you can turn any expression into any other type for Python-side operator or data coercion purposes (that is, like a CAST but doesn't render CAST on the database): from sqlalchemy import type_coerce type_coerce(any_expression, String) + type_coerce(any_other_expression, String) you'll get any expression || any other expression no matter what the two sides are. (if you don't, then *that's* the bug) On Aug 30, 2013, at 1:34 PM, Jonathan Vanasco jonat...@findmeon.com wrote: This might be a bug then. String || Integer ; Integer || String - PostgreSQL and sqlite both allow for a sting integer to be concat together into a string. Order does not matter. Integer || Integer - PostgreSQL will error if 2 ints are concat together. - sqlite seems to cast both into a string, and returns a string ( i.e. Select 1 || 2 == 12 == str(12) ) -- 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] sqlite string concats and datetime arithmetics
Okay so basically what you are saying is that this is intended behaviour? I've been trying to dig through the source for quite some time now but finding the point where it is decided is harder than I thought. Could you explain why String + Int gives the operator add and not concat_op? On 31.08.2013 00:28, Michael Bayer wrote: (note: please keep answering the emails! this is great, I just happen to have a little bit of net access here..) here's how you can turn any expression into any other type for Python-side operator or data coercion purposes (that is, like a CAST but doesn't render CAST on the database): from sqlalchemy import type_coerce type_coerce(any_expression, String) + type_coerce(any_other_expression, String) you'll get any expression || any other expression no matter what the two sides are. (if you don't, then *that's* the bug) On Aug 30, 2013, at 1:34 PM, Jonathan Vanasco jonat...@findmeon.com mailto:jonat...@findmeon.com wrote: This might be a bug then. String || Integer ; Integer || String - PostgreSQL and sqlite both allow for a sting integer to be concat together into a string. Order does not matter. Integer || Integer - PostgreSQL will error if 2 ints are concat together. - sqlite seems to cast both into a string, and returns a string ( i.e. Select 1 || 2 == 12 == str(12) ) -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. smime.p7s Description: S/MIME Cryptographic Signature
Re: [sqlalchemy] Getting the identity key of a mapped instance w/o SQL query
On Aug 30, 2013, at 9:25 AM, Torsten Landschoff torsten.landsch...@dynamore.de wrote: Hi *, I am trying to cache SQLAlchemy queries in memory for a rich client application. To invalidate the cache for changes seen in the database, I am trying to drop in-memory instances that have been changed or deleted. This requires comparing the identity of the deleted objects with in-memory objects. I tried using identity_key for this and failed, because it tries to reload from the database and I expire the instances when I am told they had some changes. The attached IPython notebook shows the behaviour. Short summary: Reloads expired state (potential ObjectDeletedError) identity_key(instance=instance) mapper.identity_key_from_instance(instance) mapper.primary_key_from_instance(instance) Uses old primary key (no reload, no ObjectDeletedError) object_state(user).identity_key object_state(user).identity object_state(user).key The main reason why I care is that identity_key may generate database queries which kill any performance improvement of my query cache. I think this should be documented in SQLAlchemy, I did not expect those functions to ever raise an exception. well those are old functions and they should document that what you usually want is just inspect(obj).key, if you have an object already. I added http://www.sqlalchemy.org/trac/ticket/2816 for that. just to verify, state.key does what you want, right? signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] sqlite string concats and datetime arithmetics
On Aug 30, 2013, at 6:32 PM, Florian Rüchel florian.ruec...@inexplicity.de
wrote:
Okay so basically what you are saying is that this is intended behaviour?
I've been trying to dig through the source for quite some time now but
finding the point where it is decided is harder than I thought. Could you
explain why String + Int gives the operator add and not concat_op?
because concatenable says this:
def _adapt_expression(self, op, other_comparator):
if op is operators.add and isinstance(other_comparator,
(Concatenable.Comparator, NullType.Comparator)):
return operators.concat_op, self.expr.type
else:
return op, self.expr.type
that is, the other side has to be concatenable also, or nulltype.
if on 0.8 at least you can always use the concat() operator directly:
from sqlalchemy.sql import table, column, operators
from sqlalchemy import Integer, String
print column('x', String) + column('y', Integer)
print operators.concat_op(column('x', String), column('y', Integer))
print column('x', String).concat(column('y', Integer))
http://docs.sqlalchemy.org/en/rel_0_8/core/metadata.html?highlight=concat#sqlalchemy.schema.Column.concat
On 31.08.2013 00:28, Michael Bayer wrote:
(note: please keep answering the emails! this is great, I just happen to
have a little bit of net access here..)
here's how you can turn any expression into any other type for Python-side
operator or data coercion purposes (that is, like a CAST but doesn't render
CAST on the database):
from sqlalchemy import type_coerce
type_coerce(any_expression, String) + type_coerce(any_other_expression,
String)
you'll get any expression || any other expression no matter what the two
sides are.
(if you don't, then *that's* the bug)
On Aug 30, 2013, at 1:34 PM, Jonathan Vanasco jonat...@findmeon.com wrote:
This might be a bug then.
String || Integer ; Integer || String
- PostgreSQL and sqlite both allow for a sting integer to be concat
together into a string. Order does not matter.
Integer || Integer
- PostgreSQL will error if 2 ints are concat together.
- sqlite seems to cast both into a string, and returns a string ( i.e.
Select 1 || 2 == 12 == str(12) )
--
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] Mimik joinedload for core tables/queries
Could anyone provide some suggestions on where to look in the SQLA code (or even an example, or some general thoughts) for how to perform a `joinedload` as the ORM does it for Core tables (or actually an already executed query which has unresolved references which I'd like to load in a subsequent query)? For performance reasons, I prefer using Core for loading data and do the post-processing manually. -- 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.
