[sqlalchemy] Inexplicable NoResultFound error
Hi, I have a script that's basically been running unmodified for years. In it, it performs an SQLAlchemy query that does a simple join between two tables: platePointing = session.query(PlatePointing).join(Plate).filter(Plate.plate_id==plateid).one() Suddenly, I'm getting a NoResultFound error on this. I've double checked the values in the database; the row - a single matching row - is in fact there. I turned on echo to output the raw SQL queries. I copied and pasted the query that SQLAchemy generated into the database, and sure enough it finds the row. The same script/query finds the appropriate row for some values but no others. Since the raw SQL query generated by the line above finds the row but it fails in the script, I'm at a loss on how to proceed to debug this. Any suggestions would be appreciated! Cheers, Demitri -- 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/d/optout.
[sqlalchemy] What about bit fields / bit masks?
Hello, this is maybe more about the database design. I want to store several boolean states of an object. The object may also have more than one state and the available possible states may increase in future. So I want to avoid adding BOOL columns for every new state and I though, hey, what about bit fields - one column, that can store many boolean information/flags. I understand that MySQL provides a datatype BIT, but which may lead to problems, depending on the version and table type. What about sqlalchemy? Is there a reasonable way to use bit masks? Would you recommend anyway to avoid such a design, since it is not that what SQL originally was designed for? I also want the program to be able to run on mysql or postgres or whatever. So maybe choosing bit masks is a bad idea anyway, since it might lead to problems with different database backends? Thanks a lot for your thoughts Cornelius -- 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/d/optout. signature.asc Description: This is a digitally signed message part
Re: [sqlalchemy] What about bit fields / bit masks?
IMHO, it is better to use ints for masks because they can be indexed by the database. AFAIK, bits can't be indexed and any bit operator in a query (let's say, WHERE mybit MYMASK) would probably result in a full table scan. well, it's just my two cents from an info I got a long time ago. I might be wrong now, so ... best regards, Richard. On 08/27/2015 06:54 AM, Cornelius Kölbel wrote: Hello, this is maybe more about the database design. I want to store several boolean states of an object. The object may also have more than one state and the available possible states may increase in future. So I want to avoid adding BOOL columns for every new state and I though, hey, what about bit fields - one column, that can store many boolean information/flags. I understand that MySQL provides a datatype BIT, but which may lead to problems, depending on the version and table type. What about sqlalchemy? Is there a reasonable way to use bit masks? Would you recommend anyway to avoid such a design, since it is not that what SQL originally was designed for? I also want the program to be able to run on mysql or postgres or whatever. So maybe choosing bit masks is a bad idea anyway, since it might lead to problems with different database backends? Thanks a lot for your thoughts Cornelius -- 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/d/optout. attachment: richard.vcf
Re: Not populating values when using offline mode
On 8/29/15 7:13 AM, Martin Marrese wrote: On Fri, Aug 28, 2015 at 8:16 PM, ma...@tokbox.com mailto:ma...@tokbox.com wrote: Hi, I am trying to generate sql scripts in offline mode. But when executing the delete in the following manner: op.execute( foo.delete().where(foo.c.key == 'bar') ) generates sql as DELETE FROM foo WHERE foo.`key` = %s; How can i get the actual value 'bar' instead of %s? Is there a better way to do this ? If I use raw sql statements I can get around the problem although I am trying my best to not execute raw sql statements in there. That is ok, %s is later replaced with 'bar'. For instance, this is the log I get when querying a table named role filtering by id. The python code is : role = session.query(model.Role).get(id_) for --sql mode you can add the literal_binds flag to your environment which will attempt to render those inline: http://alembic.readthedocs.org/en/latest/api/runtime.html?highlight=literal_binds#alembic.runtime.environment.EnvironmentContext.configure.params.literal_binds otherwise you can specify literal values using the inline_literal() construct: http://alembic.readthedocs.org/en/latest/ops.html?highlight=inline_literal#alembic.operations.Operations.inline_literal And the log entries are : 2015-08-29 08:01:53,461 INFO sqlalchemy.engine.base.Engine SELECT role.id http://role.id AS role_id, role.name http://role.name AS role_name, role.is_admin AS role_is_admin FROM role WHERE role.id http://role.id = %s 2015-08-29 08:01:53,462 INFO sqlalchemy.engine.base.Engine (2L,) The first line shows the query to be executed with %s as place holder for the parameters. The second line shows the parameters that will replace each %s. Martín -- Martín -- You received this message because you are subscribed to the Google Groups sqlalchemy-alembic group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy-alembic group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: how to set assocation proxy pattern with automap
Thanks for the reply.
Le vendredi 28 août 2015 18:52:37 UTC+3, Michael Bayer a écrit :
On 8/28/15 3:51 AM, yoch@gmail.com javascript: wrote:
Another question is why sqlalchemy produce two queries to get hardwares
collections from a dispositif :
some_disp.hardwares
2015-08-28 10:36:41,722 INFO sqlalchemy.engine.base.Engine SELECT disp_hdw
.iddispositif AS disp_hdw_iddispositif, disp_hdw.idhardware AS
disp_hdw_idhardware, disp_hdw.instance AS disp_hdw_instance
FROM disp_hdw
WHERE %s = disp_hdw.iddispositif
2015-08-28 10:36:41,725 INFO sqlalchemy.engine.base.Engine (2721L,)
2015-08-28 10:36:41,768 INFO sqlalchemy.engine.base.Engine SELECT hardware
.hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox
AS hardware_idbox
FROM hardware
WHERE hardware.id = %s
2015-08-28 10:36:41,772 INFO sqlalchemy.engine.base.Engine (268L,)
There's no context given here, but that would indicate that the
some_disp instance has been expired. When you .commit() a session, all
objects are expired by default. This is so that if, when you next operate
on the objects, some other transaction has deleted that row, the session
can let you know that you're working on stale data. See the session docs
for options on how to control this behavior.
Here the context, all in the same session :
from dbaccess import *# import engine, Base and tables objects
from sqlalchemy.orm.session import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
l = session.query(Dispositif).all()
some_disp = l[-2]
engine.echo = True
some_disp.hardwares
2015-08-29 20:34:30,972 INFO sqlalchemy.engine.base.Engine SELECT
disp_hdw.iddispositif
AS disp_hdw_iddispositif, disp_hdw.idhardware AS disp_hdw_idhardware,
disp_hdw.instance AS disp_hdw_instance
FROM disp_hdw
WHERE %s = disp_hdw.iddispositif
2015-08-29 20:34:30,975 INFO sqlalchemy.engine.base.Engine (2721L,)
2015-08-29 20:34:31,018 INFO sqlalchemy.engine.base.Engine SELECT
hardware.hdw_type
AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox AS
hardware_idbox
FROM hardware
WHERE hardware.id = %s
2015-08-29 20:34:31,022 INFO sqlalchemy.engine.base.Engine (268L,)
In fact, *hardwares* can be retrieved in one query.
instead of somewhat like :
SELECT hardware.hdw_type AS hardware_hdw_type, hardware.id AS hardware_id,
hardware.idbox AS hardware_idbox
FROM hardware
JOIN disp_hdw ON hardware.id = disp_hdw.idhardware
WHERE disp_hdw.iddispositif = %s
Le vendredi 28 août 2015 10:20:55 UTC+3, yoch@gmail.com a écrit :
Hello,
I want to use assocation proxy pattern
http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html
with automap. I tried this code :
Base = automap_base()
class Dispositif(Base):
__tablename__ = 'dispositifs'
hardwares = association_proxy('disp_hardwares', 'hardware')
class Hardware(Base):
__tablename__ = 'hardware'
Base.prepare(engine, reflect=True)
but it does not work.
Any help appreciated.
Best regards
--
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.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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/d/optout.
Re: [sqlalchemy] Inexplicable NoResultFound error
Hi Michael, On Friday, August 28, 2015 at 11:54:17 AM UTC-4, Michael Bayer wrote: this log shows clearly that the second query is not returning any rows at the DBAPI driver level - you can see that unlike the first query, there is no Row logged. The query you want to run on your database would be using plate_id 8636 and you want to make sure the JOIN is there also: That's the query I retrieved from the echo before. When entered into psql, it returns the (single) row I'm looking for. Hence the perplexity. I take it then this is a psycopg2 problem then? Strange about the duplicate post; I originally sent it via email. When it didn't appear on the list (or the web site), I resent via the web interface. I seem to have this problem with all my Google Groups posts... Thanks, Demitri -- 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/d/optout.
