Re: [sqlalchemy] checking in

[Simon King]

You can see the archives at https://groups.google.com/g/sqlalchemy to
get an idea of the traffic.

Simon

On Mon, Jun 14, 2021 at 10:25 PM Rich Shepard  wrote:
>
> I've not worked with SQLAlchemy for several years but now want to use it in
> a couple of applications. I've not seen messages on this maillist for a very
> long time so I tried subscribing and learned that I'm still subscribed.
>
> Am I the only one on this list now?
>
> If not I wonder why messages aren't arriving in my INBOX.
>
> Rich
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full 
> description.
> ---
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/alpine.LNX.2.20.2106141423470.11603%40salmo.appl-ecosys.com.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexc2WRWhb3LZEymfeHypSYv%3DeOQOmC5%2BtNdm%2B1x3Xw%2BBFA%40mail.gmail.com.

Re: [sqlalchemy] checking in

[Mike Bayer]

discussion has mostly moved to github discussions:

https://github.com/sqlalchemy/sqlalchemy/discussions


at the same time there are a few emails per day here still, i just answered at 
least two or three since yesterday.


On Mon, Jun 14, 2021, at 5:25 PM, Rich Shepard wrote:
> I've not worked with SQLAlchemy for several years but now want to use it in
> a couple of applications. I've not seen messages on this maillist for a very
> long time so I tried subscribing and learned that I'm still subscribed.
> 
> Am I the only one on this list now?
> 
> If not I wonder why messages aren't arriving in my INBOX.
> 
> Rich
> 
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/alpine.LNX.2.20.2106141423470.11603%40salmo.appl-ecosys.com.
> 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/ec72cac3-1cfd-4189-881f-4c6a08fb3ee9%40www.fastmail.com.

[sqlalchemy] checking in

[Rich Shepard]

I've not worked with SQLAlchemy for several years but now want to use it in
a couple of applications. I've not seen messages on this maillist for a very
long time so I tried subscribing and learned that I'm still subscribed.

Am I the only one on this list now?

If not I wonder why messages aren't arriving in my INBOX.

Rich

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/alpine.LNX.2.20.2106141423470.11603%40salmo.appl-ecosys.com.

Re: [sqlalchemy] Checking approaches around parallel data import for records

[Mike Bayer]

On Wed, Apr 24, 2019 at 12:19 PM Markus Elfring  wrote:
>
> > Why not report these problems to the cochinelle tool
> > which you are trying to integrate ?
>
> I suggest to take another look at corresponding information sources.
> https://systeme.lip6.fr/pipermail/cocci/2019-April/thread.html
> https://github.com/coccinelle/coccinelle/issues

wow, so I got off light then!   good luck


>
> Regards,
> Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking approaches around parallel data import for records

[Markus Elfring]

> Why not report these problems to the cochinelle tool
> which you are trying to integrate ?

I suggest to take another look at corresponding information sources.
https://systeme.lip6.fr/pipermail/cocci/2019-April/thread.html
https://github.com/coccinelle/coccinelle/issues

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking approaches around parallel data import for records

[Mike Bayer]

On Wed, Apr 24, 2019, 1:50 AM Markus Elfring  wrote:

> >> Other software architectures can support parallelisation better,
> can't they?
> >
> > Can you clarify what you hope to achieve when you continue to make
> statements
> > of this form?  I don't find them to be very constructive.
>
> I dared to point out that I stumbled on another software limitation.
> Now I am also looking again for possible adjustments and extensions.
>


Why not report these problems to the cochinelle tool which you are trying
to integrate ?



>
> > There are many software architectures available for your use
>
> I check a few of them once more.
>
>
> > and you should choose the ones which work best for you.
>
> I hope to achieve more helpful evolution for affected software areas.
>
> Regards,
> Markus
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking approaches around parallel data import for records

[Markus Elfring]

>> Other software architectures can support parallelisation better, can't 
>> they?
>
> Can you clarify what you hope to achieve when you continue to make statements
> of this form?  I don't find them to be very constructive.

I dared to point out that I stumbled on another software limitation.
Now I am also looking again for possible adjustments and extensions.


> There are many software architectures available for your use

I check a few of them once more.


> and you should choose the ones which work best for you.

I hope to achieve more helpful evolution for affected software areas.

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking approaches around parallel data import for records

[Mike Bayer]

On Tue, Apr 23, 2019, 4:07 AM Markus Elfring  wrote:

> > Where would the desired data get lost on the transmission to the
> database table
> > during usage of background process?
> >
> > When using a background process you must first call engine.dispose()
> > when first starting the process, then you start a new transaction
> > and work from there.  You cannot transfer the work of an ongoing
> transaction
> > to another process , a new transaction must be used.
>
> I admit that I did not take such a data processing requirement into account
> so far for my recent developments of SmPL scripts.
> It seems that I can not add customised process preparation code for the
> selected
> execution environment which is supported by the current Coccinelle
> software.
>
> See also:
> Complete support for fork-join work flows
> https://github.com/coccinelle/coccinelle/issues/50
>
> Other software architectures can support parallelisation better, can't
> they?
>


Hi

Can you clarify what you hope to achieve when you continue to make
statements of this form?  I don't find them to be very constructive.
 There are many software architectures available for your use and you
should choose the ones which work best for you.









>
>
>
> Regards,
> Markus
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking approaches around parallel data import for records

[Markus Elfring]

> Where would the desired data get lost on the transmission to the database 
> table
> during usage of background process?
>
> When using a background process you must first call engine.dispose()
> when first starting the process, then you start a new transaction
> and work from there.  You cannot transfer the work of an ongoing transaction
> to another process , a new transaction must be used.

I admit that I did not take such a data processing requirement into account
so far for my recent developments of SmPL scripts.
It seems that I can not add customised process preparation code for the selected
execution environment which is supported by the current Coccinelle software.

See also:
Complete support for fork-join work flows
https://github.com/coccinelle/coccinelle/issues/50

Other software architectures can support parallelisation better, can't they?

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking approaches around parallel data import for records

[Markus Elfring]

> class T2(Base):
> __table__ = Table("t2", Base.metadata, autoload_with=session.connection())
> __mapper_args__ = {
> "primary_key": [__table__.c.source_file]  # a primary key must
> be determined
> }

A variant of this software design approach can work also for my data processing
needs to some degree.

Command example:
elfring@Sonne:~/Projekte/Linux/next-patched> time spatch --timeout 9 -D 
database_URL=postgresql+psycopg2:///serial_DVB_duplicates --dir 
drivers/media/dvb-frontends --sp-file 
~/Projekte/Coccinelle/janitor/list_duplicate_statement_pairs_from_if_branches8.cocci
 > 
~/Projekte/Bau/Linux/scripts/Coccinelle/duplicates1/next/20190418/pair-PG-serial-DVB-results.txt
 2> 
~/Projekte/Bau/Linux/scripts/Coccinelle/duplicates1/next/20190418/pair-PG-serial-DVB-errors.txt

real1m54,399s
user1m52,667s
sys 0m0,382s


Such a data analysis produces an usable result if it is performed
by a single process.
The applied software combination “Coccinelle 1.0.7-00186-g99e081e9 (OCaml 
4.07.1)”
supports also parallel data processing by using background processes.
I observed that no data were imported then into a specified database table.

See also:
Checking import of code search results into a table by parallel SmPL data 
processing
https://systeme.lip6.fr/pipermail/cocci/2019-April/005774.html
https://lore.kernel.org/cocci/bed744fe-5c5c-cf28-f8b2-40a487709...@web.de/

The main developer for the semantic patch language does not like
to debug anything that involves external tools (including my application
of your class library) at the moment.
https://systeme.lip6.fr/pipermail/cocci/2019-April/005778.html
https://lore.kernel.org/cocci/alpine.DEB.2.21.1904220953540.3142@hadrien/

So I am looking for additional solution ideas from other information sources.
Where would the desired data get lost on the transmission to the database table
during usage of background process?

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking approaches around database introspection

[Markus Elfring]

> class T2(Base):
> __table__ = Table("t2", Base.metadata, autoload_with=session.connection())
> __mapper_args__ = {
> "primary_key": [__table__.c.source_file]  # a primary key must
> be determined
> }

I find this software design approach also interesting.

I have tried the following code variant out.

…
   my_inspector = Inspector.from_engine(engine)
   results = Table('t2', MetaData())
   my_inspector.reflecttable(results, None)
   entries = session.query(func.count("*")).select_from(results).scalar()

   if entries > 0:
  delimiter = "|"
  sys.stdout.write(delimiter.join( ("statement1",
"statement2",
'"function name"',
'"source file"',
"incidence") ))
  sys.stdout.write("\r\n")
  for statement1, statement2, name, source_file, incidence \
  in session.query(results.statement1,
   results.statement2,
   results.name,
   results.source_file,
   results.C).order_by(results.source_file,
   results.name,
   results.statement1,
   results.statement2):
 sys.stdout.write(delimiter.join( (statement1,
   statement2,
   name,
   source_file,
   str(incidence)) ))
 sys.stdout.write("\r\n")
   else:
…


Unfortunately, I stumble on another error message after a text line
is displayed in the expected way.

AttributeError: 'Table' object has no attribute 'statement1'


Which adjustments would be needed here?

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the generation of query parameters for record counting

[Markus Elfring]

>> Why is the expression “func.count(Address.id) > literal_column("2")”
>> not referenced there?
>
> there's no reason to refer to this, literal values should be passed as
> bound parameters unless there is some reason they shouldn't.

Can this information be confusing?


> again there is no reason for the asterisk to render in the text unless
> you are using the text in some other way besides executing it.

Do we stumble on different expectations for the usage of bind parameters?
https://docs.sqlalchemy.org/en/13/core/tutorial.html#operators

Is there a need for an adjusted SQL coding style?


> use literal_column('*') if you prefer.

I would prefer to get my query to work also without this function call.
But it seems that your programming interface contains a requirement
for such a data conversion.

Did the Python decorator “compiles” take care of this detail?

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the generation of query parameters for record counting

[Mike Bayer]

On Thu, Apr 18, 2019 at 4:30 PM Markus Elfring  wrote:
>
> > it will generate the same SQL and provide "2" for the bound value
> > instead of "1".
>
> Why is the expression “func.count(Address.id) > literal_column("2")”
> not referenced there?

there's no reason to refer to this, literal values should be passed as
bound parameters unless there is some reason they shouldn't.

>
>
> > I don't know what SQL you are attempting to render.
>
> I would like to get an asterisk instead of the parameters “count_1” and 
> “count_2”.

again there is no reason for the asterisk to render in the text unless
you are using the text in some other way besides executing it.   use
literal_column('*') if you prefer.

if you are trying to print your SQL to a file or use it for display
purposes, there are separate instructions for this at
https://docs.sqlalchemy.org/en/13/faq/sqlexpressions.html#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined





>
> Regards,
> Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the generation of query parameters for record counting

[Markus Elfring]

> it will generate the same SQL and provide "2" for the bound value
> instead of "1".

Why is the expression “func.count(Address.id) > literal_column("2")”
not referenced there?


> I don't know what SQL you are attempting to render.

I would like to get an asterisk instead of the parameters “count_1” and 
“count_2”.

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the generation of query parameters for record counting

[Mike Bayer]

On Thu, Apr 18, 2019 at 4:00 PM Markus Elfring  wrote:
>
> > there's no technical reason the "1" needs to render inline
>
> How does this information fit to the code “having(func.count(Address.id) > 2)”
> from an example?
> https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.having

it will generate the same SQL and provide "2" for the bound value
instead of "1".

>
>
> > and SQLAlchemy defaults literal values to being bound parameters.
>
> Did I overlook a detail from the software documentation?

see here:

https://docs.sqlalchemy.org/en/13/core/tutorial.html#operators


>
> > If you'd like it to say "1", then use having(func.count("*") >
> > literal_column("1")).
>
> I have got the generated codes “count(:count_1) AS "C"” and “HAVING 
> count(:count_2) > 1”
> then for my query approach.
> Which details should I adjust further?

I don't know what SQL you are attempting to render.


>
> Regards,
> Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the generation of query parameters for record counting

[Markus Elfring]

> there's no technical reason the "1" needs to render inline

How does this information fit to the code “having(func.count(Address.id) > 2)”
from an example?
https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.having


> and SQLAlchemy defaults literal values to being bound parameters.

Did I overlook a detail from the software documentation?


> If you'd like it to say "1", then use having(func.count("*") >
> literal_column("1")).

I have got the generated codes “count(:count_1) AS "C"” and “HAVING 
count(:count_2) > 1”
then for my query approach.
Which details should I adjust further?

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the generation of query parameters for record counting

[Markus Elfring]

> there's no technical reason the "1" needs to render inline

How does this information fit to the code “having(func.count(Address.id) > 2)”
from an example?
https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.having


> and SQLAlchemy defaults literal values to being bound parameters.

Did I overlook a detail from the software documentation?


> If you'd like it to say "1", then use having(func.count("*") >
> literal_column("1")).

I have got the generated codes “count(:count_1) AS "C"” and “HAVING 
count(:count_2) > 1”
then for my query approach.
Which details should I adjust further?

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the generation of query parameters for record counting

[Mike Bayer]

On Thu, Apr 18, 2019 at 11:50 AM Markus Elfring  wrote:
>

> * I am experimenting with data analysis in an execution environment
>   where I can not use Python decorators so far.
>
> * I would like to perform queries together with the engine 
> “sqlite:///:memory:”
>   (at the moment).
>
>
> > q = (
> > session.query(
> > Action.statement1,
> > Action.statement2,
> > Action.name,
> > Action.source_file,
> > func.count("*").label("C"),
> > )
> > .group_by(
> > Action.statement1, Action.statement2, Action.name, 
> > Action.source_file
> > )
>
> I wonder about the extra parentheses for this expression.
>
>
> > .having(func.count("*") > 1)
> > )
> >
> > session.execute(CreateTableAs("t2", q))
>
> I wonder also about the aspect that this suggestion should probably work
> while I stumbled on the code “HAVING count(:count_2) > :count_3”
> in a generated SQL command.
> I would expect the filter “HAVING count(*) > 1” there instead.

there's no technical reason the "1" needs to render inline and
SQLAlchemy defaults literal values to being bound parameters.  If
you'd like it to say "1", then use having(func.count("*") >
literal_column("1")).




>
> I would appreciate further advices for this questionable situation.
>
> Regards,
> Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the generation of query parameters for record counting

[Markus Elfring]

> Here is a complete proof of concept using your query,

Thanks for this constructive response.


> I hope this helps!

The provided implementation details look very promising.


> @compiles(CreateTableAs, "postgresql")

There are additional constraints to consider.

* I am experimenting with data analysis in an execution environment
  where I can not use Python decorators so far.

* I would like to perform queries together with the engine “sqlite:///:memory:”
  (at the moment).


> q = (
> session.query(
> Action.statement1,
> Action.statement2,
> Action.name,
> Action.source_file,
> func.count("*").label("C"),
> )
> .group_by(
> Action.statement1, Action.statement2, Action.name, Action.source_file
> )

I wonder about the extra parentheses for this expression.


> .having(func.count("*") > 1)
> )
>
> session.execute(CreateTableAs("t2", q))

I wonder also about the aspect that this suggestion should probably work
while I stumbled on the code “HAVING count(:count_2) > :count_3”
in a generated SQL command.
I would expect the filter “HAVING count(*) > 1” there instead.

I would appreciate further advices for this questionable situation.

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the generation of query parameters for record counting

[Mike Bayer]

Here is a complete proof of concept using your query, I hope this helps!

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr


from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ClauseElement, Executable


class CreateTableAs(Executable, ClauseElement):
def __init__(self, name, query):
self.name = name
if hasattr(query, "statement"):
query = query.statement
self.query = query


@compiles(CreateTableAs, "postgresql")
def _create_table_as(element, compiler, **kw):
return "CREATE TABLE %s AS %s" % (
element.name,
compiler.process(element.query),
)


Base = declarative_base()


class Action(Base):
__tablename__ = "action"
id = Column(Integer, primary_key=True)
some_other_data = Column(String)
statement1 = Column(String)
statement2 = Column(String)
name = Column(String)
source_file = Column(String)


e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = session = Session(e)
s.add_all(
[
Action(
some_other_data="some other data %d" % i,
statement1="s1 %d" % (i % 4),
statement2="s2 %d" % (i % 2),
name="name %d" % (i % 4),
source_file="some file %d" % (i % 4),
)
for i in range(10)
]
)
session.flush()

q = (
session.query(
Action.statement1,
Action.statement2,
Action.name,
Action.source_file,
func.count("*").label("C"),
)
.group_by(
Action.statement1, Action.statement2, Action.name, Action.source_file
)
.having(func.count("*") > 1)
)

session.execute(CreateTableAs("t2", q))


class T2(Base):
__table__ = Table("t2", Base.metadata, autoload_with=session.connection())
__mapper_args__ = {
"primary_key": [__table__.c.source_file]  # a primary key must
be determined
}

print(
session.query(T2).all()
)




On Thu, Apr 18, 2019 at 10:27 AM Markus Elfring  wrote:
>
> > can you perhaps illustrate a code example of what you would like to do ?
>
> I have tried the following approach out for the software “SQLAlchemy 1.3.2”
> together with the engine “sqlite:///:memory:”.
>
> …
>q = session.query(action.statement1, action.statement2, action.name, 
> action.source_file,
>  func.count("*").label("C")
> ).group_by(action.statement1,
>action.statement2,
>action.name,
>action.source_file) \
>  .having(func.count("*") > 1)
>ct = 'create table t2 as ' + str(q.statement)
>sys.stderr.write("Command: " + ct + "\n")
>session.execute(ct)
> …
>
>
> Now I wonder about parameter specifications in the generated SQL command
> (and a traceback with the corresponding error code 
> “http://sqlalche.me/e/cd3x”).
>
> Command: create table t2 as SELECT …, statements.source_file, count(:count_1) 
> AS "C"
> FROM … GROUP BY …
> HAVING count(:count_2) > :count_3
>
>
> Which details should be adjusted a bit more here?
>
> Regards,
> Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the generation of query parameters for record counting

[Markus Elfring]

> can you perhaps illustrate a code example of what you would like to do ?

I have tried the following approach out for the software “SQLAlchemy 1.3.2”
together with the engine “sqlite:///:memory:”.

…
   q = session.query(action.statement1, action.statement2, action.name, 
action.source_file,
 func.count("*").label("C")
).group_by(action.statement1,
   action.statement2,
   action.name,
   action.source_file) \
 .having(func.count("*") > 1)
   ct = 'create table t2 as ' + str(q.statement)
   sys.stderr.write("Command: " + ct + "\n")
   session.execute(ct)
…


Now I wonder about parameter specifications in the generated SQL command
(and a traceback with the corresponding error code “http://sqlalche.me/e/cd3x”).

Command: create table t2 as SELECT …, statements.source_file, count(:count_1) 
AS "C"
FROM … GROUP BY …
HAVING count(:count_2) > :count_3


Which details should be adjusted a bit more here?

Regards,
Markus

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking for a unique constraint violation before inserting new records, is it recommended?

[Jonathan Vanasco]

On Sunday, August 5, 2018 at 5:15:39 PM UTC-4, Mike Bayer wrote:

> Not on my end ! The openstack code I referred towards is regex based but 
> works very well.



I just limit what can trigger the exception and call a flush.  it's not the 
most performant code if you are changing many fields, but it's fine if 
you're just updating a username (which is what I use this for too!)


e.g. something like this


try:
  foo.username = 'foo'
  session.flush()
except sqlalchemy.exc.IntegrityError as e:
  raise UsernameTaken()

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking for a unique constraint violation before inserting new records, is it recommended?

[Mike Bayer]

Not on my end !  The openstack code I referred towards is regex based but
works very well.

On Sun, Aug 5, 2018, 4:08 PM Kevin Isaac  wrote:

> Any updates to this Michael? I've been researching about this but couldn't
> find a better solution than parsing the error message with regex. Would
> love to hear if there's a better way of doing this.
>
> On Saturday, December 20, 2014 at 12:52:49 AM UTC+5:30, Michael Bayer
> wrote:
>>
>>
>>
>> alchemy1  wrote:
>>
>> I'm using ORM and I've defined a unique constraint on a column, and I
>> want to prevent inserting another row with the same value for that column.
>> For example if you don't want 2 users with the same name.
>>
>> One way to prevent it would be to take the provided name and query the
>> user table to see if anyone has that name, and if not then insert the
>> record. The problem with this is that in between checking for the name and
>> inserting the name, someone else could have inserted a record with that
>> name. So is there any advantage to doing this? Is there any better way to
>> do it?
>>
>> If not, I suppose you'd want to handle the IntegrityError that SQLAlchemy
>> throws when it detects a unique constraint violation. But since
>> IntegrityError handles several types of types of violations, would checking
>> the string of the error message be the only way to ensure you're handling
>> the correct error condition, so that you can put a specific message like "A
>> user with that name already exists"?
>>
>> sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates 
>> unique constraint "name_of_constraint"
>>
>> If so, how can I use metadata to get the name of the particular
>> constraint I have in mind, so that I can search for it in the error message
>> string? I've tried looking through the MetaData API and have got this far
>> but I am stuck. I can't seem to find the constraint name set on the name
>> column here.
>>
>> Base.metadata.tables.user.c.name
>> User.name.info
>>
>>
>> yes, the non-standardization of DBAPI errors is something SQLAlchemy as
>> yet has not tried to solve, though in very recent releases it has begun to
>> provide hooks so that these solutions can be rolled more liberally.   The
>> way I’ve solved this in Openstack looks like this:
>>
>>
>> https://github.com/openstack/oslo.db/blob/0265aa4e01270b8fc7cab1266b8602e1921c9ddb/oslo/db/sqlalchemy/exc_filters.py#L93
>>
>> that regular expression is built upon a larger system that ultimately
>> makes use of the SQLAlchemy handle_error() event, which is new in recent
>> 0.9 series.You can see that we pull the columns out of the exception
>> message itself.
>>
>>
>>
>>
>>
>> --
>> 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/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking for a unique constraint violation before inserting new records, is it recommended?

[Kevin Isaac]

Any updates to this Michael? I've been researching about this but couldn't 
find a better solution than parsing the error message with regex. Would 
love to hear if there's a better way of doing this.

On Saturday, December 20, 2014 at 12:52:49 AM UTC+5:30, Michael Bayer wrote:
>
>
>
> alchemy1 > wrote:
>
> I'm using ORM and I've defined a unique constraint on a column, and I want 
> to prevent inserting another row with the same value for that column. For 
> example if you don't want 2 users with the same name.
>
> One way to prevent it would be to take the provided name and query the 
> user table to see if anyone has that name, and if not then insert the 
> record. The problem with this is that in between checking for the name and 
> inserting the name, someone else could have inserted a record with that 
> name. So is there any advantage to doing this? Is there any better way to 
> do it?
>
> If not, I suppose you'd want to handle the IntegrityError that SQLAlchemy 
> throws when it detects a unique constraint violation. But since 
> IntegrityError handles several types of types of violations, would checking 
> the string of the error message be the only way to ensure you're handling 
> the correct error condition, so that you can put a specific message like "A 
> user with that name already exists"?
>
> sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates 
> unique constraint "name_of_constraint"
>
> If so, how can I use metadata to get the name of the particular constraint 
> I have in mind, so that I can search for it in the error message string? 
> I've tried looking through the MetaData API and have got this far but I am 
> stuck. I can't seem to find the constraint name set on the name column here.
>
> Base.metadata.tables.user.c.name
> User.name.info
>
>
> yes, the non-standardization of DBAPI errors is something SQLAlchemy as 
> yet has not tried to solve, though in very recent releases it has begun to 
> provide hooks so that these solutions can be rolled more liberally.   The 
> way I’ve solved this in Openstack looks like this:  
>
>
> https://github.com/openstack/oslo.db/blob/0265aa4e01270b8fc7cab1266b8602e1921c9ddb/oslo/db/sqlalchemy/exc_filters.py#L93
>
> that regular expression is built upon a larger system that ultimately 
> makes use of the SQLAlchemy handle_error() event, which is new in recent 
> 0.9 series.You can see that we pull the columns out of the exception 
> message itself.
>
>
>
>
>
> -- 
> 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/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking for an object in the database before adding a potential duplicate

[Mike Bayer]

On 08/25/2016 06:55 PM, Kovas Palunas wrote:

Hi everyone,


I have an database setup where i have two tables/objects that are
associated via two many to many relationships (with association tables).
 I would like to find a way to quickly query for the existence of one of
the objects, which is partially defined by its associations with the
other object.  This is primarily so I can check to see if a given object
exists before inserting a potential duplicate into my database.


As an example (AX and BX are objects, [B1, B2, ...] is a list of
multiple B instances, --X--> denotes a relationship, and X is some
number denoting a specific object or relationship):


A1 --1--> [B1, B2, B3]

A1 --2--> [B4, B2, B5]

A2 --3--> [B6, B1, B7]




Currently I've thought of two (sub-par) solutions:

1 - Use associations to compute a hash, which is stored in the database
and used to distinguish the object

2 - Read the whole database into python objects and use those object's
lists of the other object to compare


I've implemented the first solution and it works, but feels clunky.  Is
there a better way to do this?


This is a little vague in both the model description and the use case 
description.  Like is this a service that needs to do this per-request 
and it could refer to any data anywhere, or is it a batch process?  how 
big is the DB, etc.   The model here is also vague as to what comprises 
"the object" since it is not just a single model row.


Given what we have, I'd say combine #1 and #2.   Read in the current 
database, compute the hashes, and the just store the hashes in memory, 
not the whole object.If you have zillions of hashes, use a bloom 
filter to just make a decent guess if an object already exists and 
ensure that database constraints will catch the occasional error when 
you try to insert an already-existing record.  (savepoints can be used 
to cleanly rollback part of a transaction when catching for existing rows).








Thanks!


 - Kovas

--
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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Checking for an object in the database before adding a potential duplicate

[Kovas Palunas]

 

Hi everyone,


I have an database setup where i have two tables/objects that are 
associated via two many to many relationships (with association tables).  I 
would 
like to find a way to quickly query for the existence of one of the 
objects, which is partially defined by its associations with the other 
object.  This is primarily so I can check to see if a given object exists 
before inserting a potential duplicate into my database.  


As an example (AX and BX are objects, [B1, B2, ...] is a list of multiple B 
instances, --X--> denotes a relationship, and X is some number denoting a 
specific object or relationship):


A1 --1--> [B1, B2, B3]

A1 --2--> [B4, B2, B5]

A2 --3--> [B6, B1, B7]




Currently I've thought of two (sub-par) solutions: 

1 - Use associations to compute a hash, which is stored in the database and 
used to distinguish the object

2 - Read the whole database into python objects and use those object's 
lists of the other object to compare


I've implemented the first solution and it works, but feels clunky.  Is 
there a better way to do this?  


Thanks!


 - Kovas

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] checking unloaded attributes

[Christopher Lee]

Yeah, the AttributeError on get would have been nice.

I used to call:

if "myattr" in model.__dict__:
delattr(model, "myattr")

Which still kinda works, because if a value is explicitly set, then it
appears in the dict and can be deleted.  It would be better to say:

if hasattr(model, "myattr"):
delattr(model, "myattr")

But that doesn't work, since __get__ on InstrumentedAttribute will return
None instead of raising, which means that delattr can attempt to delete a
key that does not actually exist.

I think the right thing for me to do is:

try:
delattr(model, "myattr")
except KeyError:
pass

Which is a bit more Pythonic anyway.  (It just means updating an annoying
number of occurrences.)


On Wed, Jan 6, 2016 at 3:27 PM, Christopher Lee  wrote:

> I'm happy for the change.  Having getattr (and even hasattr!) change the
> state of the object was occasionally infuriating to debug, because my
> variable watches would trigger state changes, and cause data to be
> overwritten with NULLs.
>
> I'll have to dig into my code a little deeper and figure out a workaround.
>
> Thanks!
>
>
> On Wed, Jan 6, 2016 at 1:50 PM, Mike Bayer 
> wrote:
>
>>
>>
>> On 01/06/2016 04:35 PM, Christopher Lee wrote:
>> >
>> > My code tests to see if an column on a model is loaded, in part because
>> > I merge partial models to perform a sparse update on the database.  I do
>> > that by checking the __dict__ of the model, though sqlalchemy.inspect
>> > also works.
>> >
>> > Using SQLAlchemy 0.8.0:
>> >
>> >
>> > import sqlalchemy as sa
>> > import sqlalchemy.ext.declarative
>> > Base = sa.ext.declarative.declarative_base()
>> >
>> > class Foo(Base):
>> > __tablename__ = 'foos'
>> > x = sa.Column(sa.String(36), primary_key=True)
>> >
>> > foo = Foo()
>> >
>> > # Test unloaded attribute
>>  'x' in foo.__dict__
>> > False
>>  sa.inspect(foo).unloaded
>> > set(['x'])
>> >
>> > # Access attribute
>>  foo.x
>> >
>> > # Test loaded attribute
>>  'x' in foo.__dict__
>> > True
>>  sa.inspect(foo).unloaded
>> > set([])
>> >
>> > However, in sqlalchemy 1.0.10, that is no longer the case.  The
>> > attribute is still marked as unloaded, and does not appear in the
>> > __dict__.  Is that intentional, or is it a regression?
>>
>> that is intentional, details on this change are here:
>>
>>
>> http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#changes-to-attribute-events-and-other-operations-regarding-attributes-that-have-no-pre-existing-value
>>
>> the case directly below it illustrates why this consistency is important:
>>
>>
>> http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#priority-of-attribute-changes-on-relationship-bound-attributes-vs-fk-bound-may-appear-to-change
>>
>> If I were writing SQLAlchemy today, I'd probably have done away with
>> this behavior entirely and it would be emitting AttributeError just like
>> regular Python.
>>
>>
>>
>>  Setting the
>> > attribute marks it as loaded, as expected.
>> I haven't narrowed down the
>> > issue to a specific version, because I wasn't sure if it is intentional.
>> >  (i.e., maybe attributes are only marked as loaded if they are a
>> > relationship)?
>> >
>> > --
>> > 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 https://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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] checking unloaded attributes

[Christopher Lee]

I'm happy for the change.  Having getattr (and even hasattr!) change the
state of the object was occasionally infuriating to debug, because my
variable watches would trigger state changes, and cause data to be
overwritten with NULLs.

I'll have to dig into my code a little deeper and figure out a workaround.

Thanks!


On Wed, Jan 6, 2016 at 1:50 PM, Mike Bayer  wrote:

>
>
> On 01/06/2016 04:35 PM, Christopher Lee wrote:
> >
> > My code tests to see if an column on a model is loaded, in part because
> > I merge partial models to perform a sparse update on the database.  I do
> > that by checking the __dict__ of the model, though sqlalchemy.inspect
> > also works.
> >
> > Using SQLAlchemy 0.8.0:
> >
> >
> > import sqlalchemy as sa
> > import sqlalchemy.ext.declarative
> > Base = sa.ext.declarative.declarative_base()
> >
> > class Foo(Base):
> > __tablename__ = 'foos'
> > x = sa.Column(sa.String(36), primary_key=True)
> >
> > foo = Foo()
> >
> > # Test unloaded attribute
>  'x' in foo.__dict__
> > False
>  sa.inspect(foo).unloaded
> > set(['x'])
> >
> > # Access attribute
>  foo.x
> >
> > # Test loaded attribute
>  'x' in foo.__dict__
> > True
>  sa.inspect(foo).unloaded
> > set([])
> >
> > However, in sqlalchemy 1.0.10, that is no longer the case.  The
> > attribute is still marked as unloaded, and does not appear in the
> > __dict__.  Is that intentional, or is it a regression?
>
> that is intentional, details on this change are here:
>
>
> http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#changes-to-attribute-events-and-other-operations-regarding-attributes-that-have-no-pre-existing-value
>
> the case directly below it illustrates why this consistency is important:
>
>
> http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#priority-of-attribute-changes-on-relationship-bound-attributes-vs-fk-bound-may-appear-to-change
>
> If I were writing SQLAlchemy today, I'd probably have done away with
> this behavior entirely and it would be emitting AttributeError just like
> regular Python.
>
>
>
>  Setting the
> > attribute marks it as loaded, as expected.
> I haven't narrowed down the
> > issue to a specific version, because I wasn't sure if it is intentional.
> >  (i.e., maybe attributes are only marked as loaded if they are a
> > relationship)?
> >
> > --
> > 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 https://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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] checking unloaded attributes

[Mike Bayer]

On 01/06/2016 04:35 PM, Christopher Lee wrote:
> 
> My code tests to see if an column on a model is loaded, in part because
> I merge partial models to perform a sparse update on the database.  I do
> that by checking the __dict__ of the model, though sqlalchemy.inspect
> also works.
> 
> Using SQLAlchemy 0.8.0:
> 
> 
> import sqlalchemy as sa
> import sqlalchemy.ext.declarative
> Base = sa.ext.declarative.declarative_base()
> 
> class Foo(Base):
> __tablename__ = 'foos'
> x = sa.Column(sa.String(36), primary_key=True)
> 
> foo = Foo()
> 
> # Test unloaded attribute
 'x' in foo.__dict__
> False
 sa.inspect(foo).unloaded
> set(['x'])
> 
> # Access attribute
 foo.x
> 
> # Test loaded attribute
 'x' in foo.__dict__
> True
 sa.inspect(foo).unloaded
> set([])
> 
> However, in sqlalchemy 1.0.10, that is no longer the case.  The
> attribute is still marked as unloaded, and does not appear in the
> __dict__.  Is that intentional, or is it a regression? 

that is intentional, details on this change are here:

http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#changes-to-attribute-events-and-other-operations-regarding-attributes-that-have-no-pre-existing-value

the case directly below it illustrates why this consistency is important:

http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#priority-of-attribute-changes-on-relationship-bound-attributes-vs-fk-bound-may-appear-to-change

If I were writing SQLAlchemy today, I'd probably have done away with
this behavior entirely and it would be emitting AttributeError just like
regular Python.



 Setting the
> attribute marks it as loaded, as expected.  
I haven't narrowed down the
> issue to a specific version, because I wasn't sure if it is intentional.
>  (i.e., maybe attributes are only marked as loaded if they are a
> relationship)?
> 
> -- 
> 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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Checking the handling of unique keys/indexes

[Michael Bayer]

SF Markus Elfring elfr...@users.sourceforge.net wrote:

 Now I stumble on an error message like the following.
 ...
cursor.executemany(statement, parameters)
 sqlalchemy.exc.IntegrityError: (IntegrityError) UNIQUE constraint failed: 
 positions.function, 
 ...
 
 
 The message might be appropriate in principle for my concrete use case.
 But I observe that the constraint violation is reported a bit
 too late because I got the impression from corresponding debug
 output that three rows were added to the shown table here
 with unique attributes.
 
 I would appreciate your explanations and further advices.

SQLAlchemy sends to the log the statement and parameters it is to send to the 
DBAPI cursor.execute() method, *before* it actually does so.  This so that if 
the DBAPI throws an exception, as is the case here, one can see what 
instructions were sent to it which were the immediate cause of this error.

The mechanism of a UNIQUE constraint is that this is a database-level 
construct, so the backend database is tasked with checking this condition and 
reporting on it at statement execution time.

-- 
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] Checking the handling of unique keys/indexes

[SF Markus Elfring]

 SQLAlchemy sends to the log the statement and parameters it is to send
 to the DBAPI cursor.execute() method, *before* it actually does so.
 This so that if the DBAPI throws an exception, as is the case here,
 one can see what instructions were sent to it which were the immediate
 cause of this error.

Thanks for your explanation.

Do I need to consider any more fine-tuning for my database session?


 The mechanism of a UNIQUE constraint is that this is a database-level
 construct, so the backend database is tasked with checking this
 this condition and reporting on it at statement execution time.

Should I get the exception sqlalchemy.exc.IntegrityError directly
after I attempted to insert a second record set with unique attributes
into a SQLite table?

Regards,
Markus

-- 
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] Checking the handling of unique keys/indexes

[Michael Bayer]

SF Markus Elfring elfr...@users.sourceforge.net wrote:

 SQLAlchemy sends to the log the statement and parameters it is to send
 to the DBAPI cursor.execute() method, *before* it actually does so.
 This so that if the DBAPI throws an exception, as is the case here,
 one can see what instructions were sent to it which were the immediate
 cause of this error.
 
 Thanks for your explanation.
 
 Do I need to consider any more fine-tuning for my database session?
 
 
 The mechanism of a UNIQUE constraint is that this is a database-level
 construct, so the backend database is tasked with checking this
 this condition and reporting on it at statement execution time.
 
 Should I get the exception sqlalchemy.exc.IntegrityError directly
 after I attempted to insert a second record set with unique attributes
 into a SQLite table?

I don’t have a stack trace here to see what the nature of the issue is but it 
is likely that the INSERT is proceeding using DBAPI executemany(), which 
receives the full set of records in one batch before any communication with the 
database is established.  SQLAlchemy doesn’t have access to at what point each 
individual series of parameters are invoked as the interface is too 
coarse-grained.

The two general techniques for dealing with unique constraints are to either 
SELECT ahead of time the rows that you know to be dealing with into a 
collection, such that you can check within this collection ahead of time for 
the existing row before proceeding, or invoking the INSERT of rows one at a 
time, catching each IntegrityError inside of a SAVEPOINT 
(http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#using-savepoint
 describes the Session’s API for SAVEPOINT).   Note that the SQLite driver has 
a bug with SAVEPOINT which you need to apply the technique at 
http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#pysqlite-serializable 
in order to overcome.


 
 Regards,
 Markus

-- 
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] Checking the handling of unique keys/indexes

[SF Markus Elfring]

 Should I get the exception sqlalchemy.exc.IntegrityError directly
 after I attempted to insert a second record set with unique attributes
 into a SQLite table?
 I don’t have a stack trace here to see what the nature of the issue is
 but it is likely that the INSERT is proceeding using DBAPI executemany(),

Yes. - It seems that this method was used in my use case.


 which receives the full set of records in one batch before any
 communication with the database is established.

Can it happen then that an error will be reported for a single SQL statement
which was submitted within an unit of more database commands?


 SQLAlchemy doesn’t have access to at what point each individual series
 of parameters are invoked as the interface is too coarse-grained.

Do you know any attempts to make the affected error reporting more precise?

Regards,
Markus

-- 
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] Checking the handling of unique keys/indexes

[Michael Bayer]

SF Markus Elfring elfr...@users.sourceforge.net wrote:

 Should I get the exception sqlalchemy.exc.IntegrityError directly
 after I attempted to insert a second record set with unique attributes
 into a SQLite table?
 I don’t have a stack trace here to see what the nature of the issue is
 but it is likely that the INSERT is proceeding using DBAPI executemany(),
 
 Yes. - It seems that this method was used in my use case.
 
 
 which receives the full set of records in one batch before any
 communication with the database is established.
 
 Can it happen then that an error will be reported for a single SQL statement
 which was submitted within an unit of more database commands?
 
 
 SQLAlchemy doesn’t have access to at what point each individual series
 of parameters are invoked as the interface is too coarse-grained.
 
 Do you know any attempts to make the affected error reporting more precise?


Here are the two options we have for invoking a statement:

try:
cursor.execute(stmt, params)   # single row
except IntegrityError:
   # …

try:
   cursor.executemany(stmt, [params, params, params, params, …])   # many rows
except IntegrityError:
   # …

There is no way to use the second form while being able to record the moment 
each parameter set is used, unless the DBAPI itself provides additional hooks 
for logging at this level.   However, this logging would defeat some of the 
purpose of executemany(), which is that of processing many parameter sets at 
maximum speed.

The SQLAlchemy Session tries to use executemany() as often as it can within a 
flush() procedure; it can be used any time there are more than one row to be 
INSERTED where we already have the primary key value available.

If you’d like to operate on individual rows, I guess I wasn’t specific enough 
from my instruction to use SAVEPOINT, you should flush individually:

for obj in all_my_objects:
session.add(obj)
try:
with session.begin_nested():
session.flush()
except IntegrityError:
# deal with this error, don’t add obj, or however it is you intend 
to deal with existing rows






 
 Regards,
 Markus
 
 -- 
 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.

-- 
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] Checking the handling of unique keys/indexes

[SF Markus Elfring]

 There is no way to use the second form while being able to record the moment
 each parameter set is used, unless the DBAPI itself provides additional hooks
 for logging at this level.  However, this logging would defeat some of the
 purpose of executemany(), which is that of processing many parameter sets
 at maximum speed.

Thanks for your helpful advice.

Regards,
Markus

-- 
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] Checking the handling of unique keys/indexes

[SF Markus Elfring]

Hello,

I am using source code like the following in one of my scripts
where I am trying the software packages SQLAlchemy 0.9.7-77.1
and SQLite 3.8.7.2-1.1 out on my openSUSE system.

...
engine = create_engine(sqlite:///:memory:, echo = False)
base = declarative_base()

class position(base):
   __tablename__ = positions
   function = Column(String, primary_key = True)
   source_file = Column(String, primary_key = True)
   line = Column(Integer, primary_key = True)
   column = Column(Integer, primary_key = True)
   void = Column(Integer, default = 0)
   static = Column(Integer, default = 0)
   data_type = Column(String)
   parameter = Column(String)
...
def store_positions(fun, type, point, places):
Add source code positions to an internal table.
...



Now I stumble on an error message like the following.
...
cursor.executemany(statement, parameters)
sqlalchemy.exc.IntegrityError: (IntegrityError) UNIQUE constraint failed: 
positions.function, 
...


The message might be appropriate in principle for my concrete use case.
But I observe that the constraint violation is reported a bit
too late because I got the impression from corresponding debug
output that three rows were added to the shown table here
with unique attributes.

I would appreciate your explanations and further advices.

Regards,
Markus

-- 
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] Checking for a unique constraint violation before inserting new records, is it recommended?

[Michael Bayer]

alchemy1 veerukrish...@hotmail.com wrote:

 I'm using ORM and I've defined a unique constraint on a column, and I want to 
 prevent inserting another row with the same value for that column. For 
 example if you don't want 2 users with the same name.
 
 One way to prevent it would be to take the provided name and query the user 
 table to see if anyone has that name, and if not then insert the record. The 
 problem with this is that in between checking for the name and inserting the 
 name, someone else could have inserted a record with that name. So is there 
 any advantage to doing this? Is there any better way to do it?
 
 If not, I suppose you'd want to handle the IntegrityError that SQLAlchemy 
 throws when it detects a unique constraint violation. But since 
 IntegrityError handles several types of types of violations, would checking 
 the string of the error message be the only way to ensure you're handling the 
 correct error condition, so that you can put a specific message like A user 
 with that name already exists?
 
 sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates 
 unique constraint name_of_constraint
 
 If so, how can I use metadata to get the name of the particular constraint I 
 have in mind, so that I can search for it in the error message string? I've 
 tried looking through the MetaData API and have got this far but I am stuck. 
 I can't seem to find the constraint name set on the name column here.
 
 Base.metadata.tables.user.c.name
 User.name.info
 

yes, the non-standardization of DBAPI errors is something SQLAlchemy as yet has 
not tried to solve, though in very recent releases it has begun to provide 
hooks so that these solutions can be rolled more liberally.   The way I’ve 
solved this in Openstack looks like this:  

https://github.com/openstack/oslo.db/blob/0265aa4e01270b8fc7cab1266b8602e1921c9ddb/oslo/db/sqlalchemy/exc_filters.py#L93

that regular expression is built upon a larger system that ultimately makes use 
of the SQLAlchemy handle_error() event, which is new in recent 0.9 series.
You can see that we pull the columns out of the exception message itself.





 -- 
 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 
 http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout 
 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] checking script validity

[Michael Bayer]

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

[sqlalchemy] checking script validity

[monosij . forums]

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.

Re: [sqlalchemy] checking if some object with the given id is in a session

[Michael Bayer]

you can check for a given identity in the session using the identity key 
functions:

from sqlalchemy.orm.util import identity_key

key= identity_key(MyClass, someid)

object_already_in_session = key in session.identity_map

but usually people use session.merge(object) instead of session.add() to handle 
all of this automatically.



On Sep 19, 2011, at 4:55 AM, Eduardo wrote:

 Hi,
 I create a certain number of objects in a loop, I check if each of the
 objects is already stored in the session and if it is not I add it to
 the session.
 
 session = Session()
 for item in items:
   item1=create_item()
   if not item1 in session:
   session.add(item1)
 session.commit()
 
 The problem is that when I add two objects to the session that are not
 the same but have some id I get the error:
 get an error sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate
 key value violates unique constraint
 Is there any function that directly checks if there two objects with
 the same id in a session?
 Thanks
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] checking if some object with the given id is in a session

[Eduardo]

Hi,
I create a certain number of objects in a loop, I check if each of the
objects is already stored in the session and if it is not I add it to
the session.

session = Session()
for item in items:
   item1=create_item()
   if not item1 in session:
   session.add(item1)
session.commit()

The problem is that when I add two objects to the session that are not
the same but have some id I get the error:
get an error sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate
key value violates unique constraint
Is there any function that directly checks if there two objects with
the same id in a session?
Thanks

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Checking the availablity of a booked Item

[Thadeus Burgess]

Here is an example of how I use outerjoin to perform a similar query.


class Survey, def get_apps(self):

Get all persons who are over 18 and do not have an appointment but have this
survey.

qry = Person.query.filter(Person.age  18)

qry = qry.outerjoin((Appointment,
(Appointment.id_person == Person.id)
(Appointment.id_survey == self.id))). \
  filter(Appointment.id == None) #: Or != None, depending on the
type of join.

#: I go and append a few more outer joins here as well,# but they look
exactly the same just different tables.

The syntax of the outerjoin arguments is a tuple containing (Table to join
to, whereclause to join on).

Hopefully this helps you. Basically, I just look at the coding horror site
for the kind of join I want to perform, and modify the syntax above to make
it match the SQL.

--
Thadeus




On Mon, Oct 18, 2010 at 10:38 AM, chaouche yacine
yacinechaou...@yahoo.comwrote:

 Hello,

 I think outerjoin is just a join with an isouter = True, and above all the
 problem was not there anyway. The problem was only about ordering the joins.
 The correct python code was :

 Face.query.outerjoin(Face.bookings).filter(cond).all()

 instead of

 Face.query.outerjoin(Booking.faces).filter(cond).all()

 Which looks more intuitive to me, because I'm doing joins between Face and
 Booking (thus putting Booking in the outerjoin, not Face again as in the
 first code). I can't understand the logic of the (correct) first one, but it
 works ! Or is it two errors cancelling each other ?

 Y.Chaouche





 --- On *Thu, 10/14/10, Thadeus Burgess thade...@thadeusb.com* wrote:


 From: Thadeus Burgess thade...@thadeusb.com
 Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
 To: sqlalchemy@googlegroups.com
 Date: Thursday, October 14, 2010, 8:42 AM

 http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join

 isouter = True

 --
 Thadeus




 On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine 
 yacinechaou...@yahoo.com 
 http://mc/compose?to=yacinechaou...@yahoo.comwrote:

 Here's the SQL I got :

 SELECT face.id AS face_id
 FROM face
 LEFT OUTER JOIN face_bookings__booking_faces AS
 face_bookings__booking_faces_1
 ON face.id = face_bookings__booking_faces_1.face_id
 LEFT OUTER JOIN booking
 ON booking.id = face_bookings__booking_faces_1.booking_id
 JOIN time_period ON booking.time_period_id = time_period.id
 WHERE
   time_period.start_date  %(start_date_1)s
   OR
   time_period.end_date  %(end_date_1)s


 With the following code :


 class Booking(BaseModel):
 
 
 using_options(tablename=booking)

 reprattr=  time_period
 faces   = ManyToMany(Face)
 # A client has one and only one booking per time period
 time_period = ManyToOne(TimePeriod)

 @classmethod
 def get_available_faces(self,time_period):
 
 Return faces that are not booked during the given time_period.
 
 from timeperiod import TimePeriod
 from face   import Face

 start_date_cond = TimePeriod.start_date  time_period.end_date
 end_date_cond   = TimePeriod.end_date   
 time_period.start_date
 unbooked= or_(start_date_cond,end_date_cond)

 # query =
 Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked)))
 # return query.all()

 query = Face.query.filter(unbooked)
 #return query.all()
 query = query.outerjoin(Face.bookings)
 #return query.all()
 query = query.join(Booking.time_period)
 return query.all()


 And still not the expected results (it should return faces with no bookings
 at all but it doesen't).

 Thanks for any help.

 Y.Chaouche

 --- On *Wed, 10/13/10, chaouche yacine 
 yacinechaou...@yahoo.comhttp://mc/compose?to=yacinechaou...@yahoo.com
 * wrote:


 From: chaouche yacine 
 yacinechaou...@yahoo.comhttp://mc/compose?to=yacinechaou...@yahoo.com
 

 Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
 To: 
 sqlalchemy@googlegroups.comhttp://mc/compose?to=sqlalch...@googlegroups.com
 Date: Wednesday, October 13, 2010, 5:25 AM


 Thank you Thadeus, I believe
 Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full
 outerjoin, or is there another way to do it ?

 Y.Chaouche





 --- On *Wed, 10/13/10, Thadeus Burgess 
 thade...@thadeusb.comhttp://mc/compose?to=thade...@thadeusb.com
 * wrote:


 From: Thadeus Burgess 
 thade...@thadeusb.comhttp://mc/compose?to=thade...@thadeusb.com
 
 Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
 To: 
 sqlalchemy@googlegroups.comhttp://mc/compose?to=sqlalch...@googlegroups.com
 Date: Wednesday, October 13, 2010, 12:04 AM

 For outer joins you need a where clause on the joined tables.


 http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

 Using a full outer join should return

Re: [sqlalchemy] Checking the availablity of a booked Item

[chaouche yacine]

Did you try :

qry = qry.outerjoin(Appointment.person).filter(Appointment.id == None) ?

I have the intuition that sqlalchemy can guess on what foreign keys the join 
should operate based on how you declared your relations.

What about (if Person.appointments exists):

qry = qry.outerjoin(Person.appointments).filter(Appointment.id == None) ?

I don't think you'd have the same results (join order matters).


Y.Chaouche



--- On Wed, 10/20/10, Thadeus Burgess thade...@thadeusb.com wrote:

From: Thadeus Burgess thade...@thadeusb.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com
Date: Wednesday, October 20, 2010, 8:27 AM

Here is an example of how I use outerjoin to perform a similar query.


class Survey, def get_apps(self):

Get all persons who are over 18 and do not have an appointment but have this 
survey.



qry = Person.query.filter(Person.age  18)

qry = qry.outerjoin((Appointment,
    (Appointment.id_person == Person.id)
    (Appointment.id_survey == self.id))). \


  filter(Appointment.id == None) #: Or != None, depending on the 
type of join.

#: I go and append a few more outer joins here as well,# but they look exactly 
the same just different tables.

The syntax of the outerjoin arguments is a tuple containing (Table to join 
to, whereclause to join on).



Hopefully this helps you. Basically, I just look at the coding horror site for 
the kind of join I want to perform, and modify the syntax above to make it 
match the SQL.

--
Thadeus





On Mon, Oct 18, 2010 at 10:38 AM, chaouche yacine yacinechaou...@yahoo.com 
wrote:


Hello,

I think outerjoin is just a join with an isouter = True, and above all the 
problem was not there anyway. The problem was only about ordering the joins. 
The correct python code was :



Face.query.outerjoin(Face.bookings).filter(cond).all()

instead of

Face.query.outerjoin(Booking.faces).filter(cond).all()

Which looks more intuitive to me, because I'm doing joins between Face and 
Booking (thus putting Booking in the outerjoin, not Face again as in the first 
code). I can't understand the logic of the (correct) first one, but it works ! 
Or is it two errors cancelling each other ?



Y.Chaouche




--- On Thu, 10/14/10, Thadeus Burgess thade...@thadeusb.com wrote:



From: Thadeus
 Burgess thade...@thadeusb.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com


Date: Thursday, October 14, 2010, 8:42 AM

http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join



isouter = True

--
Thadeus







On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.com 
wrote:




Here's the SQL I got : 

SELECT face.id AS face_id 




FROM face 
LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 
        ON face.id = face_bookings__booking_faces_1.face_id 


LEFT OUTER JOIN booking 


        ON booking.id = face_bookings__booking_faces_1.booking_id 
JOIN time_period ON booking.time_period_id = time_period.id 




WHERE 
  time_period.start_date  %(start_date_1)s 
  OR 
  time_period.end_date  %(end_date_1)s


With the following code : 






class
 Booking(BaseModel):
    




    
    using_options(tablename=booking)





    reprattr    =  time_period




   
 faces   = ManyToMany(Face)
    # A client has one and only one booking per time period




    time_period = ManyToOne(TimePeriod)
    




    @classmethod
    def
 get_available_faces(self,time_period):
    




    Return faces that are not booked during the given time_period.
    




    from timeperiod import TimePeriod
    from face   import Face




    
    start_date_cond = TimePeriod.start_date  time_period.end_date




    end_date_cond   = TimePeriod.end_date    time_period.start_date




    unbooked    = or_(start_date_cond,end_date_cond)





    # query = 
Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked)))




    # return query.all()





    query =
 Face.query.filter(unbooked)
    #return query.all()




    query = query.outerjoin(Face.bookings)
    #return query.all()




    query = query.join(Booking.time_period)
    return query.all()






And still not the expected results (it should return faces with no bookings at 
all but it doesen't).

Thanks for any help.

Y.Chaouche





--- On Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com wrote:





From: chaouche yacine yacinechaou...@yahoo.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item


To: sqlalchemy@googlegroups.com


Date: Wednesday, October 13, 2010, 5:25 AM

Thank you Thadeus, I believe
 Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full 
outerjoin, or is there another way to do it ?

Y.Chaouche





--- On Wed, 10/13/10, Thadeus

Re: [sqlalchemy] Checking the availablity of a booked Item

[Michael Bayer]

I'm not reading this super carefully, but typically the best to find X where 
no related Y is to use NOT EXISTS.With SQLA you can hit this in one step 
using ~Person.appointments.any().



On Oct 20, 2010, at 12:30 PM, chaouche yacine wrote:

 Did you try :
 
 qry = qry.outerjoin(Appointment.person).filter(Appointment.id == None) ?
 
 I have the intuition that sqlalchemy can guess on what foreign keys the join 
 should operate based on how you declared your relations.
 
 What about (if Person.appointments exists):
 
 qry = qry.outerjoin(Person.appointments).filter(Appointment.id == None) ?
 
 I don't think you'd have the same results (join order matters).
 
 
 Y.Chaouche
 
 
 
 --- On Wed, 10/20/10, Thadeus Burgess thade...@thadeusb.com wrote:
 
 From: Thadeus Burgess thade...@thadeusb.com
 Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
 To: sqlalchemy@googlegroups.com
 Date: Wednesday, October 20, 2010, 8:27 AM
 
 Here is an example of how I use outerjoin to perform a similar query.
 
 
 class Survey, def get_apps(self):
 
 Get all persons who are over 18 and do not have an appointment but have this 
 survey.
 
 
 
 qry = Person.query.filter(Person.age  18)
 
 qry = qry.outerjoin((Appointment,
 (Appointment.id_person == Person.id)
 (Appointment.id_survey == self.id))). \
 
 
   filter(Appointment.id == None) #: Or != None, depending on the 
 type of join.
 
 #: I go and append a few more outer joins here as well,# but they look 
 exactly the same just different tables.
 
 The syntax of the outerjoin arguments is a tuple containing (Table to join 
 to, whereclause to join on).
 
 
 
 Hopefully this helps you. Basically, I just look at the coding horror site 
 for the kind of join I want to perform, and modify the syntax above to make 
 it match the SQL.
 
 --
 Thadeus
 
 
 
 
 
 On Mon, Oct 18, 2010 at 10:38 AM, chaouche yacine yacinechaou...@yahoo.com 
 wrote:
 
 
 Hello,
 
 I think outerjoin is just a join with an isouter = True, and above all the 
 problem was not there anyway. The problem was only about ordering the joins. 
 The correct python code was :
 
 
 
 Face.query.outerjoin(Face.bookings).filter(cond).all()
 
 instead of
 
 Face.query.outerjoin(Booking.faces).filter(cond).all()
 
 Which looks more intuitive to me, because I'm doing joins between Face and 
 Booking (thus putting Booking in the outerjoin, not Face again as in the 
 first code). I can't understand the logic of the (correct) first one, but it 
 works ! Or is it two errors cancelling each other ?
 
 
 
 Y.Chaouche
 
 
 
 
 --- On Thu, 10/14/10, Thadeus Burgess thade...@thadeusb.com wrote:
 
 
 
 From: Thadeus
 Burgess thade...@thadeusb.com
 Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
 To: sqlalchemy@googlegroups.com
 
 
 Date: Thursday, October 14, 2010, 8:42 AM
 
 http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join
 
 
 
 isouter = True
 
 --
 Thadeus
 
 
 
 
 
 
 
 On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.com 
 wrote:
 
 
 
 
 Here's the SQL I got : 
 
 SELECT face.id AS face_id 
 
 
 
 
 FROM face 
 LEFT OUTER JOIN face_bookings__booking_faces AS 
 face_bookings__booking_faces_1 
 ON face.id = face_bookings__booking_faces_1.face_id 
 
 
 LEFT OUTER JOIN booking 
 
 
 ON booking.id = face_bookings__booking_faces_1.booking_id 
 JOIN time_period ON booking.time_period_id = time_period.id 
 
 
 
 
 WHERE 
   time_period.start_date  %(start_date_1)s 
   OR 
   time_period.end_date  %(end_date_1)s
 
 
 With the following code : 
 
 
 
 
 
 
 class
 Booking(BaseModel):
 
 
 
 
 
 
 using_options(tablename=booking)
 
 
 
 
 
 reprattr=  time_period
 
 
 
 

 faces   = ManyToMany(Face)
 # A client has one and only one booking per time period
 
 
 
 
 time_period = ManyToOne(TimePeriod)
 
 
 
 
 
 @classmethod
 def
 get_available_faces(self,time_period):
 
 
 
 
 
 Return faces that are not booked during the given time_period.
 
 
 
 
 
 from timeperiod import TimePeriod
 from face   import Face
 
 
 
 
 
 start_date_cond = TimePeriod.start_date  time_period.end_date
 
 
 
 
 end_date_cond   = TimePeriod.end_datetime_period.start_date
 
 
 
 
 unbooked= or_(start_date_cond,end_date_cond)
 
 
 
 
 
 # query = 
 Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked)))
 
 
 
 
 # return query.all()
 
 
 
 
 
 query =
 Face.query.filter(unbooked)
 #return query.all()
 
 
 
 
 query = query.outerjoin(Face.bookings)
 #return query.all()
 
 
 
 
 query = query.join(Booking.time_period)
 return query.all()
 
 
 
 
 
 
 And still not the expected results (it should return faces with no bookings 
 at all but it doesen't

Re: [sqlalchemy] Checking the availablity of a booked Item

[chaouche yacine]

Hello,

I think outerjoin is just a join with an isouter = True, and above all the 
problem was not there anyway. The problem was only about ordering the joins. 
The correct python code was :

Face.query.outerjoin(Face.bookings).filter(cond).all()

instead of

Face.query.outerjoin(Booking.faces).filter(cond).all()

Which looks more intuitive to me, because I'm doing joins between Face and 
Booking (thus putting Booking in the outerjoin, not Face again as in the first 
code). I can't understand the logic of the (correct) first one, but it works ! 
Or is it two errors cancelling each other ?

Y.Chaouche




--- On Thu, 10/14/10, Thadeus Burgess thade...@thadeusb.com wrote:

From: Thadeus Burgess thade...@thadeusb.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com
Date: Thursday, October 14, 2010, 8:42 AM

http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join

isouter = True

--
Thadeus







On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.com 
wrote:


Here's the SQL I got : 

SELECT face.id AS face_id 


FROM face 
LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 
        ON face.id = face_bookings__booking_faces_1.face_id 
LEFT OUTER JOIN booking 


        ON booking.id = face_bookings__booking_faces_1.booking_id 
JOIN time_period ON booking.time_period_id = time_period.id 


WHERE 
  time_period.start_date  %(start_date_1)s 
  OR 
  time_period.end_date  %(end_date_1)s


With the following code : 




class
 Booking(BaseModel):
    


    
    using_options(tablename=booking)



    reprattr    =  time_period


   
 faces   = ManyToMany(Face)
    # A client has one and only one booking per time period


    time_period = ManyToOne(TimePeriod)
    


    @classmethod
    def
 get_available_faces(self,time_period):
    


    Return faces that are not booked during the given time_period.
    


    from timeperiod import TimePeriod
    from face   import Face


    
    start_date_cond = TimePeriod.start_date  time_period.end_date


    end_date_cond   = TimePeriod.end_date    time_period.start_date


    unbooked    = or_(start_date_cond,end_date_cond)



    # query = 
Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked)))


    # return query.all()



    query =
 Face.query.filter(unbooked)
    #return query.all()


    query = query.outerjoin(Face.bookings)
    #return query.all()


    query = query.join(Booking.time_period)
    return query.all()




And still not the expected results (it should return faces with no bookings at 
all but it doesen't).

Thanks for any help.

Y.Chaouche



--- On Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com wrote:



From: chaouche yacine yacinechaou...@yahoo.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com


Date: Wednesday, October 13, 2010, 5:25 AM

Thank you Thadeus, I believe
 Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full 
outerjoin, or is there another way to do it ?

Y.Chaouche





--- On Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com wrote:



From: Thadeus Burgess thade...@thadeusb.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item


To: sqlalchemy@googlegroups.com
Date: Wednesday, October 13, 2010, 12:04 AM

For outer joins you need a where clause on the joined tables.



http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html



Using a full outer join should return the expected results.
--
Thadeus





On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com 
wrote:




Hello,



Here's my simple model (For simplification, consider Face as a Billboard) :



+-+          +---+       +--+

|Face |..   |Campaign   |   ...|TimePeriod|

+-+      .   +---+   .   +--+

|code |      .   |time_period|   |start_time|

+-+      .   +---+       +--+

             |faces      |       |end_time  |

                 +---+       +--+



One way to read this model is : A campaign can book multiple faces during a 
certain period of time.



What I want to do is get all the available Faces for a given period of time, to 
see what faces can I book for a new campaign that longs for that particular 
period of time. I would typically have a Face.get_available(time_period) class 
method that does the job. This method would look for all the faces that don't 
have an ongoing booking. My question is : how to write such a method ?







Here's how I figured it out (couldn't get it to work) :



class Face(Entity):

    using_options(tablename=faces)

   �...@classmethod

    def get_available(self,time_period):

        

        Return faces that are not booked (not in any campaign

Re: [sqlalchemy] Checking the availablity of a booked Item

[chaouche yacine]

Here's the SQL I got : 

SELECT face.id AS face_id 
FROM face 
LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 
        ON face.id = face_bookings__booking_faces_1.face_id 
LEFT OUTER JOIN booking 
        ON booking.id = face_bookings__booking_faces_1.booking_id 
JOIN time_period ON booking.time_period_id = time_period.id 
WHERE 
  time_period.start_date  %(start_date_1)s 
  OR 
  time_period.end_date  %(end_date_1)s


With the following code : 


class Booking(BaseModel):
    
    
    using_options(tablename=booking)

    reprattr    =  time_period
    faces   = ManyToMany(Face)
    # A client has one and only one booking per time period
    time_period = ManyToOne(TimePeriod)
    
    @classmethod
    def get_available_faces(self,time_period):
    
    Return faces that are not booked during the given time_period.
    
    from timeperiod import TimePeriod
    from face   import Face
    
    start_date_cond = TimePeriod.start_date  time_period.end_date
    end_date_cond   = TimePeriod.end_date    time_period.start_date
    unbooked    = or_(start_date_cond,end_date_cond)

    # query = 
Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked)))
    # return query.all()

    query = Face.query.filter(unbooked)
    #return query.all()
    query = query.outerjoin(Face.bookings)
    #return query.all()
    query = query.join(Booking.time_period)
    return query.all()


And still not the expected results (it should return faces with no bookings at 
all but it doesen't).

Thanks for any help.

Y.Chaouche

--- On Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com wrote:

From: chaouche yacine yacinechaou...@yahoo.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com
Date: Wednesday, October 13, 2010, 5:25 AM

Thank you Thadeus, I believe 
Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full 
outerjoin, or is there another way to do it ?

Y.Chaouche





--- On Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com wrote:

From: Thadeus Burgess thade...@thadeusb.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com
Date: Wednesday, October 13, 2010, 12:04 AM

For outer joins you need a where clause on the joined tables.

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html



Using a full outer join should return the expected results.
--
Thadeus





On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com 
wrote:


Hello,



Here's my simple model (For simplification, consider Face as a Billboard) :



+-+          +---+       +--+

|Face |..   |Campaign   |   ...|TimePeriod|

+-+      .   +---+   .   +--+

|code |      .   |time_period|   |start_time|

+-+      .   +---+       +--+

             |faces      |       |end_time  |

                 +---+       +--+



One way to read this model is : A campaign can book multiple faces during a 
certain period of time.



What I want to do is get all the available Faces for a given period of time, to 
see what faces can I book for a new campaign that longs for that particular 
period of time. I would typically have a Face.get_available(time_period) class 
method that does the job. This method would look for all the faces that don't 
have an ongoing booking. My question is : how to write such a method ?





Here's how I figured it out (couldn't get it to work) :



class Face(Entity):

    using_options(tablename=faces)

   �...@classmethod

    def get_available(self,time_period):

        

        Return faces that are not booked (not in any campaign) during the given 
time_period.

        

        # start_date_cond     = TimePeriod.start_date  time_period.end_date

        # end_date_cond       = TimePeriod.end_date    time_period.start_date

        # available_periods   = 
Campaign.time_period.has(or_(start_date_cond,end_date_cond))

        # unavailable_periods = not(available_periods)



        # I am pretty sure that the time conditions are good.

        # Here's a good way to convince yourself (read from bottom to top) :



        # L1                      
0---

        # L2 --|

        # L3 
0[]--



        # L3 represents the desired period (passed as argument) going from [ 
to ]

        # place the start date of the booked face anywhere on L2

        # place the end date of the booked face anywhere on L1

        # of course, end date must be after start date...

        # Anyway you do it, your face isn't available for the period of time in 
L3.



        start_date_cond

Re: [sqlalchemy] Checking the availablity of a booked Item

[chaouche yacine]

Here's the SQL I got : 

SELECT face.id AS face_id 
FROM face 
LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 
        ON face.id = face_bookings__booking_faces_1.face_id 
LEFT OUTER JOIN booking 
        ON booking.id = face_bookings__booking_faces_1.booking_id 
JOIN time_period ON booking.time_period_id = time_period.id 
WHERE 
  time_period.start_date  %(start_date_1)s 
  OR 
  time_period.end_date  %(end_date_1)s


With the following code : 


class Booking(BaseModel):
    
    
    using_options(tablename=booking)

    reprattr    =  time_period
    faces   = ManyToMany(Face)
    # A client has one and only one booking per time period
    time_period = ManyToOne(TimePeriod)
    
    @classmethod
    def get_available_faces(self,time_period):
    
    Return faces that are not booked during the given time_period.
    
    from timeperiod import TimePeriod
    from face   import Face
    
    start_date_cond = TimePeriod.start_date  time_period.end_date
    end_date_cond   = TimePeriod.end_date    time_period.start_date
    unbooked    = or_(start_date_cond,end_date_cond)

    # query = 
Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked)))
    # return query.all()

    query = Face.query.filter(unbooked)
    #return query.all()
    query = query.outerjoin(Face.bookings)
    #return query.all()
    query = query.join(Booking.time_period)
    return query.all()


And still not the expected results (it should return faces with no bookings at 
all but it doesen't).

Thanks for any help.

Y.Chaouche

--- On Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com wrote:

From: chaouche yacine yacinechaou...@yahoo.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com
Date: Wednesday, October 13, 2010, 5:25 AM

Thank you Thadeus, I believe 
Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full 
outerjoin, or is there another way to do it ?

Y.Chaouche





--- On Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com wrote:

From: Thadeus Burgess thade...@thadeusb.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com
Date: Wednesday, October 13, 2010, 12:04 AM

For outer joins you need a where clause on the joined tables.

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html



Using a full outer join should return the expected results.
--
Thadeus





On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com 
wrote:


Hello,



Here's my simple model (For simplification, consider Face as a Billboard) :



+-+          +---+       +--+

|Face |..   |Campaign   |   ...|TimePeriod|

+-+      .   +---+   .   +--+

|code |      .   |time_period|   |start_time|

+-+      .   +---+       +--+

             |faces      |       |end_time  |

                 +---+       +--+



One way to read this model is : A campaign can book multiple faces during a 
certain period of time.



What I want to do is get all the available Faces for a given period of time, to 
see what faces can I book for a new campaign that longs for that particular 
period of time. I would typically have a Face.get_available(time_period) class 
method that does the job. This method would look for all the faces that don't 
have an ongoing booking. My question is : how to write such a method ?





Here's how I figured it out (couldn't get it to work) :



class Face(Entity):

    using_options(tablename=faces)

   �...@classmethod

    def get_available(self,time_period):

        

        Return faces that are not booked (not in any campaign) during the given 
time_period.

        

        # start_date_cond     = TimePeriod.start_date  time_period.end_date

        # end_date_cond       = TimePeriod.end_date    time_period.start_date

        # available_periods   = 
Campaign.time_period.has(or_(start_date_cond,end_date_cond))

        # unavailable_periods = not(available_periods)



        # I am pretty sure that the time conditions are good.

        # Here's a good way to convince yourself (read from bottom to top) :



        # L1                      
0---

        # L2 --|

        # L3 
0[]--



        # L3 represents the desired period (passed as argument) going from [ 
to ]

        # place the start date of the booked face anywhere on L2

        # place the end date of the booked face anywhere on L1

        # of course, end date must be after start date...

        # Anyway you do it, your face isn't available for the period of time in 
L3.



        start_date_cond

Re: [sqlalchemy] Checking the availablity of a booked Item

[Thadeus Burgess]

http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join

isouter = True

--
Thadeus




On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine
yacinechaou...@yahoo.comwrote:

 Here's the SQL I got :

 SELECT face.id AS face_id
 FROM face
 LEFT OUTER JOIN face_bookings__booking_faces AS
 face_bookings__booking_faces_1
 ON face.id = face_bookings__booking_faces_1.face_id
 LEFT OUTER JOIN booking
 ON booking.id = face_bookings__booking_faces_1.booking_id
 JOIN time_period ON booking.time_period_id = time_period.id
 WHERE
   time_period.start_date  %(start_date_1)s
   OR
   time_period.end_date  %(end_date_1)s


 With the following code :


 class Booking(BaseModel):
 
 
 using_options(tablename=booking)

 reprattr=  time_period
 faces   = ManyToMany(Face)
 # A client has one and only one booking per time period
 time_period = ManyToOne(TimePeriod)

 @classmethod
 def get_available_faces(self,time_period):
 
 Return faces that are not booked during the given time_period.
 
 from timeperiod import TimePeriod
 from face   import Face

 start_date_cond = TimePeriod.start_date  time_period.end_date
 end_date_cond   = TimePeriod.end_date   
 time_period.start_date
 unbooked= or_(start_date_cond,end_date_cond)

 # query =
 Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked)))
 # return query.all()

 query = Face.query.filter(unbooked)
 #return query.all()
 query = query.outerjoin(Face.bookings)
 #return query.all()
 query = query.join(Booking.time_period)
 return query.all()


 And still not the expected results (it should return faces with no bookings
 at all but it doesen't).

 Thanks for any help.

 Y.Chaouche

 --- On *Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com* wrote:


 From: chaouche yacine yacinechaou...@yahoo.com

 Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
 To: sqlalchemy@googlegroups.com
 Date: Wednesday, October 13, 2010, 5:25 AM


 Thank you Thadeus, I believe
 Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full
 outerjoin, or is there another way to do it ?

 Y.Chaouche





 --- On *Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com* wrote:


 From: Thadeus Burgess thade...@thadeusb.com
 Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
 To: sqlalchemy@googlegroups.com
 Date: Wednesday, October 13, 2010, 12:04 AM

 For outer joins you need a where clause on the joined tables.


 http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

 Using a full outer join should return the expected results.
 --
 Thadeus




 On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com
  wrote:

 Hello,

 Here's my simple model (For simplification, consider Face as a Billboard) :

 +-+  +---+   +--+
 |Face |..   |Campaign   |   ...|TimePeriod|
 +-+  .   +---+   .   +--+
 |code |  .   |time_period|   |start_time|
 +-+  .   +---+   +--+
 |faces  |   |end_time  |
 +---+   +--+

 One way to read this model is : A campaign can book multiple faces during a
 certain period of time.

 What I want to do is get all the available Faces for a given period of
 time, to see what faces can I book for a new campaign that longs for that
 particular period of time. I would typically have a
 Face.get_available(time_period) class method that does the job. This method
 would look for all the faces that don't have an ongoing booking. My question
 is : how to write such a method ?

 Here's how I figured it out (couldn't get it to work) :

 class Face(Entity):
using_options(tablename=faces)
@classmethod
def get_available(self,time_period):

Return faces that are not booked (not in any campaign) during the
 given time_period.

# start_date_cond = TimePeriod.start_date  time_period.end_date
# end_date_cond   = TimePeriod.end_date   
 time_period.start_date
# available_periods   =
 Campaign.time_period.has(or_(start_date_cond,end_date_cond))
# unavailable_periods = not(available_periods)

# I am pretty sure that the time conditions are good.
# Here's a good way to convince yourself (read from bottom to top) :

# L1
  0---
# L2 --|
# L3
 0[]--

# L3 represents the desired period (passed as argument) going from
 [ to ]
# place the start date of the booked face anywhere on L2
# place the end date of the booked face anywhere on L1

Re: [sqlalchemy] Checking the availablity of a booked Item

[Thadeus Burgess]

For outer joins you need a where clause on the joined tables.

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Using a full outer join should return the expected results.
--
Thadeus




On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine
yacinechaou...@yahoo.comwrote:

 Hello,

 Here's my simple model (For simplification, consider Face as a Billboard) :

 +-+  +---+   +--+
 |Face |..   |Campaign   |   ...|TimePeriod|
 +-+  .   +---+   .   +--+
 |code |  .   |time_period|   |start_time|
 +-+  .   +---+   +--+
 |faces  |   |end_time  |
 +---+   +--+

 One way to read this model is : A campaign can book multiple faces during a
 certain period of time.

 What I want to do is get all the available Faces for a given period of
 time, to see what faces can I book for a new campaign that longs for that
 particular period of time. I would typically have a
 Face.get_available(time_period) class method that does the job. This method
 would look for all the faces that don't have an ongoing booking. My question
 is : how to write such a method ?

 Here's how I figured it out (couldn't get it to work) :

 class Face(Entity):
using_options(tablename=faces)
@classmethod
def get_available(self,time_period):

Return faces that are not booked (not in any campaign) during the
 given time_period.

# start_date_cond = TimePeriod.start_date  time_period.end_date
# end_date_cond   = TimePeriod.end_date   
 time_period.start_date
# available_periods   =
 Campaign.time_period.has(or_(start_date_cond,end_date_cond))
# unavailable_periods = not(available_periods)

# I am pretty sure that the time conditions are good.
# Here's a good way to convince yourself (read from bottom to top) :

# L1
  0---
# L2 --|
# L3
 0[]--

# L3 represents the desired period (passed as argument) going from
 [ to ]
# place the start date of the booked face anywhere on L2
# place the end date of the booked face anywhere on L1
# of course, end date must be after start date...
# Anyway you do it, your face isn't available for the period of time
 in L3.

start_date_cond = TimePeriod.start_date = time_period.end_date
end_date_cond   = TimePeriod.end_date   =
 time_period.start_date
unavailable_periods =
 Campaign.time_period.has(and_(start_date_cond,end_date_cond))
# I am not sure about what follows...
filter_cond = not_(unavailable_periods)
join_clause = Campaign.faces

return Face.query.filter(filter_cond).outerjoin(join_clause).all()


 This code returns only faces that have already been booked before or have a
 future booking, and are free for the moment. But faces with no bookings at
 all are not returned. This may be due to an incorrect outerjoin ? (I also
 tried a simple join with no success)

 Here's the generated sql for one query :

 2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec
 SELECT faces.id AS faces_id
 FROM campaigns LEFT OUTER JOIN campaigns_faces__faces AS
 campaigns_faces__faces_1 ON campaigns.id =
 campaigns_faces__faces_1.campaigns_id
 LEFT OUTER JOIN faces ON faces.id = campaigns_faces__faces_1.faces_id
 WHERE NOT (EXISTS (SELECT 1
  FROM time_periods
  WHERE campaigns.time_period_id = time_periods.id
  AND time_periods.start_date = %(start_date_1)s
  AND time_periods.end_date = %(end_date_1)s))

 2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec
 {'start_date_1': datetime.date(2010, 10, 30), 'end_date_1':
 datetime.date(2010, 10, 20)}
 [Face id=1 at 0x932218c  ]


 Any help would be very appreciated.

 Y.Chaouche

 PS : and please, don't give me that lame it's elixir excuse. The question
 is about how to construct the proper query for the desired operation in a
 sqlalchemy way. Elixir is only another Declarative approach + mapping,
 that's it.




 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
 .
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.

Re: [sqlalchemy] Checking the availablity of a booked Item

[chaouche yacine]

Thank you Thadeus, I believe 
Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full 
outerjoin, or is there another way to do it ?

Y.Chaouche





--- On Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com wrote:

From: Thadeus Burgess thade...@thadeusb.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com
Date: Wednesday, October 13, 2010, 12:04 AM

For outer joins you need a where clause on the joined tables.

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html



Using a full outer join should return the expected results.
--
Thadeus





On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com 
wrote:


Hello,



Here's my simple model (For simplification, consider Face as a Billboard) :



+-+          +---+       +--+

|Face |..   |Campaign   |   ...|TimePeriod|

+-+      .   +---+   .   +--+

|code |      .   |time_period|   |start_time|

+-+      .   +---+       +--+

             |faces      |       |end_time  |

                 +---+       +--+



One way to read this model is : A campaign can book multiple faces during a 
certain period of time.



What I want to do is get all the available Faces for a given period of time, to 
see what faces can I book for a new campaign that longs for that particular 
period of time. I would typically have a Face.get_available(time_period) class 
method that does the job. This method would look for all the faces that don't 
have an ongoing booking. My question is : how to write such a method ?





Here's how I figured it out (couldn't get it to work) :



class Face(Entity):

    using_options(tablename=faces)

   �...@classmethod

    def get_available(self,time_period):

        

        Return faces that are not booked (not in any campaign) during the given 
time_period.

        

        # start_date_cond     = TimePeriod.start_date  time_period.end_date

        # end_date_cond       = TimePeriod.end_date    time_period.start_date

        # available_periods   = 
Campaign.time_period.has(or_(start_date_cond,end_date_cond))

        # unavailable_periods = not(available_periods)



        # I am pretty sure that the time conditions are good.

        # Here's a good way to convince yourself (read from bottom to top) :



        # L1                      
0---

        # L2 --|

        # L3 
0[]--



        # L3 represents the desired period (passed as argument) going from [ 
to ]

        # place the start date of the booked face anywhere on L2

        # place the end date of the booked face anywhere on L1

        # of course, end date must be after start date...

        # Anyway you do it, your face isn't available for the period of time in 
L3.



        start_date_cond     = TimePeriod.start_date = time_period.end_date

        end_date_cond       = TimePeriod.end_date   = time_period.start_date

        unavailable_periods = 
Campaign.time_period.has(and_(start_date_cond,end_date_cond))

        # I am not sure about what follows...

        filter_cond         = not_(unavailable_periods)

        join_clause         = Campaign.faces



        return Face.query.filter(filter_cond).outerjoin(join_clause).all()





This code returns only faces that have already been booked before or have a 
future booking, and are free for the moment. But faces with no bookings at all 
are not returned. This may be due to an incorrect outerjoin ? (I also tried a 
simple join with no success)





Here's the generated sql for one query :



2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec

SELECT faces.id AS faces_id

FROM campaigns LEFT OUTER JOIN campaigns_faces__faces AS 
campaigns_faces__faces_1 ON campaigns.id = campaigns_faces__faces_1.campaigns_id

LEFT OUTER JOIN faces ON faces.id = campaigns_faces__faces_1.faces_id

WHERE NOT (EXISTS (SELECT 1

                  FROM time_periods

                  WHERE campaigns.time_period_id = time_periods.id

                  AND time_periods.start_date = %(start_date_1)s

                  AND time_periods.end_date = %(end_date_1)s))



2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec 
{'start_date_1': datetime.date(2010, 10, 30), 'end_date_1': datetime.date(2010, 
10, 20)}

[Face id=1 at 0x932218c  ]





Any help would be very appreciated.



Y.Chaouche



PS : and please, don't give me that lame it's elixir excuse. The question is 
about how to construct the proper query for the desired operation in a 
sqlalchemy way. Elixir is only another Declarative approach + mapping, that's 
it.











--

You received this message because you are subscribed to the Google Groups 
sqlalchemy group.

To post to this group, send

[sqlalchemy] Checking the availablity of a booked Item

[chaouche yacine]

Hello,

Here's my simple model (For simplification, consider Face as a Billboard) :

+-+  +---+   +--+
|Face |..   |Campaign   |   ...|TimePeriod|
+-+  .   +---+   .   +--+
|code |  .   |time_period|   |start_time|
+-+  .   +---+   +--+
 |faces  |   |end_time  |
 +---+   +--+

One way to read this model is : A campaign can book multiple faces during a 
certain period of time.

What I want to do is get all the available Faces for a given period of time, to 
see what faces can I book for a new campaign that longs for that particular 
period of time. I would typically have a Face.get_available(time_period) class 
method that does the job. This method would look for all the faces that don't 
have an ongoing booking. My question is : how to write such a method ? 

Here's how I figured it out (couldn't get it to work) :

class Face(Entity):
using_options(tablename=faces)
@classmethod
def get_available(self,time_period):

Return faces that are not booked (not in any campaign) during the given 
time_period.

# start_date_cond = TimePeriod.start_date  time_period.end_date
# end_date_cond   = TimePeriod.end_datetime_period.start_date
# available_periods   = 
Campaign.time_period.has(or_(start_date_cond,end_date_cond))
# unavailable_periods = not(available_periods)

# I am pretty sure that the time conditions are good.
# Here's a good way to convince yourself (read from bottom to top) :

# L1  
0---
# L2 --|
# L3 
0[]--

# L3 represents the desired period (passed as argument) going from [ 
to ]
# place the start date of the booked face anywhere on L2
# place the end date of the booked face anywhere on L1
# of course, end date must be after start date...
# Anyway you do it, your face isn't available for the period of time in 
L3. 

start_date_cond = TimePeriod.start_date = time_period.end_date
end_date_cond   = TimePeriod.end_date   = time_period.start_date
unavailable_periods = 
Campaign.time_period.has(and_(start_date_cond,end_date_cond))
# I am not sure about what follows...
filter_cond = not_(unavailable_periods)
join_clause = Campaign.faces

return Face.query.filter(filter_cond).outerjoin(join_clause).all()


This code returns only faces that have already been booked before or have a 
future booking, and are free for the moment. But faces with no bookings at all 
are not returned. This may be due to an incorrect outerjoin ? (I also tried a 
simple join with no success)

Here's the generated sql for one query : 

2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec 
SELECT faces.id AS faces_id 
FROM campaigns LEFT OUTER JOIN campaigns_faces__faces AS 
campaigns_faces__faces_1 ON campaigns.id = 
campaigns_faces__faces_1.campaigns_id 
LEFT OUTER JOIN faces ON faces.id = campaigns_faces__faces_1.faces_id 
WHERE NOT (EXISTS (SELECT 1 
  FROM time_periods 
  WHERE campaigns.time_period_id = time_periods.id 
  AND time_periods.start_date = %(start_date_1)s 
  AND time_periods.end_date = %(end_date_1)s))

2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec 
{'start_date_1': datetime.date(2010, 10, 30), 'end_date_1': datetime.date(2010, 
10, 20)}
[Face id=1 at 0x932218c  ]


Any help would be very appreciated.

Y.Chaouche

PS : and please, don't give me that lame it's elixir excuse. The question is 
about how to construct the proper query for the desired operation in a 
sqlalchemy way. Elixir is only another Declarative approach + mapping, that's 
it.


  

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

# ORM
from sqlalchemy.sql.expression import * 
from sqlalchemy.ormimport reconstructor
from elixirimport *
# stdlib
from datetime  import date
import sys

class TimePeriod(Entity):


using_options(tablename=time_periods)

start_date = Field(Date())
end_date   = Field(Date())
name   = Field(Unicode())

class Campaign(Entity):
using_options(tablename=campaigns)
time_period = ManyToOne(TimePeriod)
faces   = ManyToMany(Face)

class Face(Entity):

[sqlalchemy] Checking internals of query object

[Bryan]

I am writing a function that adds particular columns and groupings to
a query based on some options.  I am trying to write some unit tests
for the function, and would like to check that the correct columns are
being added/grouped.

Give a query like:

q = session.query(Employee.firstName, Employee.lastName)

How can I check later that the query object has included
Employee.firstName in the output columns?

Similarly, how can I check, for instance, that the query object is
grouping on Employee.lastName?

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] checking for C extensions at runtime

[David Gardner]

I'm actually not sure, I did a bit of googling and couldn't really find 
much.
Pep 386 talks about version comparison in distutils: 
http://www.python.org/dev/peps/pep-0386/#id10


As for scratching my itch it wouldn't have to be in the __version__ 
string, I was just wondering how
I would check if the SQLAlchemy on a system had the C extensions enabled 
on it or not.


On 07/15/2010 01:40 PM, Michael Bayer wrote:

I was wondering if there was a way to check at runtime if the C extensions were 
built with SQLAlchemy?

If not I was wondering if you would consider adding something to the 
__version__ string?
 

is there some guideline for the format on that ?


   


--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com


--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] checking for C extensions at runtime

[David Gardner]

I was wondering if there was a way to check at runtime if the C 
extensions were built with SQLAlchemy?


If not I was wondering if you would consider adding something to the 
__version__ string?


--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com


--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] checking for C extensions at runtime

[Michael Bayer]

On Jul 15, 2010, at 4:03 PM, David Gardner wrote:

 I was wondering if there was a way to check at runtime if the C extensions 
 were built with SQLAlchemy?
 
 If not I was wondering if you would consider adding something to the 
 __version__ string?

is there some guideline for the format on that ?




 
 -- 
 David Gardner
 Pipeline Tools Programmer
 Jim Henson Creature Shop
 dgard...@creatureshop.com
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] checking string’s validity for a give n type of db column

[toinbis]

Hi folks! As no one seems to answer my question in
stackoverflow(http://stackoverflow.com/questions/2303767/sqlalchemy-
checking-unicode-strings-validity-for-a-given-type-of-db-column), I'm
trying my luck here.

Am developing an extract-transform-load script with sqlalchemy.
Scenario is as follows:

* take 30+ mln text file (csv, tab-delimited or any other...).
* parse it and generate file, suitable for 'Load data infile'
mySQL import command (as described 
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
)
* From within script, disable MyISAM indexes, import file 'Load
data infile', recreate indexes.

The thing is that while parsing a text file, i'd like to check whether
the given string will be suitable for a given column's data
type(according to it's definition in mysql table), as it'd shame if
the 30 millionth row would contain a 50 symbol string while the mysql
column have been described as VARCHAR(49)...

I imagine the type checking could be implemented as follows:

types = [col.type for col in q.columns]
text = ['a','b','asdasd','','45.5','čąęąčę','2005-09-13 12:12:12']
if len(types)==len(text):
for i in range(len(types)):
assert(types[i].some_cool_type_checking_method(text[i]))

where some_cool_type_checking_method would be a simple method of the
sqlalchemy.dialects.mysql.base.(INTEGER|VARCHAR|etc) class, which
takes the string as an argument and returns True if the string will
be accepted, according to the Column metadata retrieved from DB via
reflection and False, if the given string is for some reason
misformated(too many characters for a VARCHAR, mismatching charsets,
Float where the integer is expected, etc) for a given Db column.

some_cool_type_checking_method is something i would originaly expect,
but as from the public methods avialable for
sqlalchemy.dialects.mysql.base.(INTEGER|VARCHAR|etc) classes, i don't
seem to find a similar one. Of course it's naive to hope that
developers think the same way I do:) - so the question is how is this
type of type-checking implemented following SQLAlchemy best practices?

Thanks in advance for any ideas!

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Checking if a table already has data in it.

[todd12]

Hello all,

Is there a better way to check if a table has data in it, instead of
doing a query and fetching one item?

Thanks,

t
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] checking a relationship exists

[joeformd]

Is there a simple way in SQLA to check if a relationship exists
between two rows in a many-to-many situation, where the relationship
is defined using another table?

eg. the join table might look like this:

user_towns = Table('user_towns', Base.metadata,
Column('user_id', Integer, ForeignKey('users.id')),
Column('town_id', Integer, ForeignKey('towns.id'))
)

to check if a relationship exists, the SQL would be fairly simple,
with no joins required (is there a row in this table where user.id =
user_id and town.id = town_id)

Is there a simple way of asking this using SQLA, with the query
syntax?

thanks for your time
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] checking if a column is in a list

[Jack Stahl]

Hello,

I've got a few SQL interfaces where I'd like to change my query based on the
columns required by the client.  For example, in one situation, I only join
against my User table if my client requires a photo id:

# cols is the list of columns the client would like selected
if User.c.photo_id in cols:
table = table.join(User.table)

In another situation, I'd like to include the flags column in the query even
if the client ask for it

if not cols:
cols = [cls]
elif not (cls.c.flags in cols):
# ensure flags are there so we can add is_active
cols.append(cls.c.flags)

However, my tests for membership pass regardless of whether column is
actually in the list cols.  That is,

Column('flags', BitField(), table=foo_table) in [Column('foo',
String(length=84, convert_unicode=False, assert_unicode=None),
table=foo_table), Column('bar', Text(length=None, convert_unicode=False,
table=foo_table)]

evaluates to True

Is the == operator not properly implemented for SQLAlchemy Columns?  (I'm
using version 0.42)

Thanks,
Jack

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Checking active transactions

[Christoph Zwerschke]

In TurboGears 1, requests are encapsulated between a session.begin() and 
session.commit() call (or session.rollback(), if there was an error).

Starting with SA 0.4.3, the commit() raises an exception if the 
transaction has been already rolled back in the request (explicitly or 
due to an error). So TurboGears needs to check whether the transaction 
is active before the session.commit() call. We currently do this by 
storing the transaction as the return value of session.begin() and then 
checking transaction.is_active (or transaction.session.transaction for 
earlier SA versions).

This gets complicated since the transaction may be restarted during a 
request and can change. So I think it would be handy to tell directly 
from the session whether the transaction is active or not.

We are using *scoped* sessions in TruboGears, and the problem is that 
the transaction attribute is not available in ScopedSessions, so I can 
not check for session.transaction.is_active. I can get the original 
Session by calling session, so session().transaction.is_active would 
work, but it does not feel right. I guess it is by intent that the 
session attributes are not visible in scoped sessions.

So how about adding a property is_active to the Session that would call 
session.transaction.is_active? Then you could do:

if session.is_active:
session.commit()

-- Christoph


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Checking for number of related items

[Andreas Jung]

I have a mapper defined as


mapper(HierNode, HierTable, properties={
'tools' : relation(ToolNode),

  })

Usually I am interested in iterating over all 'tools' and lazy loading
works fine. However sometimes I need to determine if one HierNode
has tools or not. Loading all tools would be too expensive for a has_tools
property. It would be enough to check for only one tools (LIMIT 1) and
return True/False. Is there any way to achieve that?

Andreas

pgp0EZ1gaasmp.pgp
Description: PGP signature

[sqlalchemy] checking the sql string.

[SamDonaldson]

How do I look to see what sql is being constructed in the query
object?  So if I have something like:

q = query.session(User)
q = q.add_column()
q = q.group_by(.).select()

How do I check what sql string has been constructed in q?

Also, say I want to do a group by a particular column name on the User
table and I have two aggregates, sum, and count?  How do I add these
two columns to q?  Do I just call add_column twice?  It's not working,
I'm getting an empty result set.

q = query.session(User)
q = q.add_column(func.sum(User.c.id).label('sum'))
q = q.add_column(func.count(User.c.id).label('count'))
q = q.group_by([User.c.blah]).select()

I'm getting an empty result set.  What exactly does label do here?

Thanks.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---