[sqlalchemy] Re: flask-sqlalchemy - auto-complete in editor?

[Jonathan Vanasco]

You're going to have better luck asking this question in a flask community 
or, if this is in relation to your IDE, a community for that.

-- 
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] .distinct() silently ignored for DBs other than Postgres

[mike bayer]

On 05/30/2017 07:42 AM, Christopher Wilson wrote:

Dear Michael and fellow SQLAlchemy users/developers,

I think I have found an issue with the use of distinct() over specified 
columns on a Query:


http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.distinct

As the Postgres documentation notes, this is a non-standard Postgres 
extension to SQL, so we should not expect other databases to support it:


https://www.postgresql.org/docs/9.0/static/sql-select.html (see 
Nonstandard Clauses at the end of the page).


And the SQLAlchemy documentation describes the observed behaviour, but 
that is still surprising behaviour:


*distinct*(/*criterion/)

Apply a DISTINCT to the query and return the newly resulting Query.

*Note*

The distinct() 
 call 
includes logic that will automatically add columns from the ORDER BY of 
the query to the columns clause of the SELECT statement, to satisfy the 
common need of the database backend that ORDER BY columns be part of the 
SELECT list when DISTINCT is used. These columns /are not/ added to the 
list of columns actually fetched by the Query 
, 
however, so would not affect results. The columns are passed through 
when using the Query.statement 
 accessor, 
however.


*Parameters:*



**expr* – optional column expressions. When present, the PostgreSQL 
dialect will render a DISTINCTON (>) construct.


It doesn’t explicitly say it, but this statement allows the DISTINCT 
columns to be completely ignored by other database backends, and indeed 
it appears that they are. For example when using the MS SQL dialect, the 
following code:


s = Session()

s.bind.echo = True

s.query(DS2CtryQtInfo).distinct(DS2CtryQtInfo.info_code).first()

Runs this query:

SELECT DISTINCT TOP 1 [DS2CtryQtInfo].[InfoCode] AS 
[DS2CtryQtInfo_InfoCode], …


FROM [DS2CtryQtInfo]

Therefore the engine silently executed a query that was different than 
the requested one (i.e. wrong), as though I had requested .distinct() 
instead of .distinct(DS2CtryQtInfo.info_code).


Please could I request that engines raise an exception if the user tries 
to use a feature which isn’t supported by the engine?


the best we can do is warn as this has been there forever, 
https://bitbucket.org/zzzeek/sqlalchemy/issues/4002/warn-when-postgresql-distinct-columns-used 
is created.










Thanks, Chris.





/This email is confidential. If you are not the intended recipient, 
please advise us immediately and delete this message. The registered 
name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. 
See - http://www.gam.com/en/Legal/Email+disclosures+EU for further 
information on confidentiality, the risks of non-secure electronic 
communication, and certain disclosures which we are required to make in 
accordance with applicable legislation and regulations. If you cannot 
access this link, please notify us by reply message and we will send the 
contents to you.


///

--
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] insert performance vs executemany and max_allowed_packet limit

[mike bayer]

On 05/29/2017 11:34 PM, Михаил Доронин wrote:

Umm, what I've meant is how to use postgresql

on_conflict_do_update in such a way that sqlalchemy would use executemany 
behind the scenes.

In examples it usage looks like this.

stmt = insert(table, values)
stmt = stmt.on_conflict_do_update(set_=dict(a=stmt.excluded.a))

excluded is generated from values, right? If I don't pass values to the 
statement, how can I use excluded?



you would not use insert(table, values), which as we reviewed earlier is 
not "executemany" syntax.   the values are passed to execute() as the 
second argument, and is the list of values which are invoked for the 
statement one at a time.   .excluded is a server side collection 
generated by Postgresql and is based on the current row being operated 
upon.   These values are not returned to the client.  They are only used 
in context of the statement.


think of executemany like this:


stmt = 
table.insert().on_conflict_do_update(set_=dict(a=stmt.excluded.a)).values(x 
= bindparam('x'), y=bindparam('y'))


def executemany(stmt, values):
for value in values:
conn.execute(stmt, value)


executemany(stmt, [{"x": 1, "y": 2}, {"x": 3, "y": 4}, ...])


e.g. if your statement works for one execute() and one set of 
parameters, it will work for any number of individual sets of parameters.



--
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] .distinct() silently ignored for DBs other than Postgres

[Christopher Wilson]

Dear Michael and fellow SQLAlchemy users/developers,

I think I have found an issue with the use of distinct() over specified columns 
on a Query:
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.distinct

As the Postgres documentation notes, this is a non-standard Postgres extension 
to SQL, so we should not expect other databases to support it:
https://www.postgresql.org/docs/9.0/static/sql-select.html (see Nonstandard 
Clauses at the end of the page).

And the SQLAlchemy documentation describes the observed behaviour, but that is 
still surprising behaviour:
distinct(*criterion)
Apply a DISTINCT to the query and return the newly resulting Query.
Note
The 
distinct()
 call includes logic that will automatically add columns from the ORDER BY of 
the query to the columns clause of the SELECT statement, to satisfy the common 
need of the database backend that ORDER BY columns be part of the SELECT list 
when DISTINCT is used. These columns are not added to the list of columns 
actually fetched by the 
Query,
 however, so would not affect results. The columns are passed through when 
using the 
Query.statement
 accessor, however.
Parameters:

*expr - optional column expressions. When present, the PostgreSQL dialect will 
render a DISTINCTON (>) construct.


It doesn't explicitly say it, but this statement allows the DISTINCT columns to 
be completely ignored by other database backends, and indeed it appears that 
they are. For example when using the MS SQL dialect, the following code:

s = Session()
s.bind.echo = True
s.query(DS2CtryQtInfo).distinct(DS2CtryQtInfo.info_code).first()

Runs this query:

SELECT DISTINCT TOP 1 [DS2CtryQtInfo].[InfoCode] AS [DS2CtryQtInfo_InfoCode], 
...
FROM [DS2CtryQtInfo]

Therefore the engine silently executed a query that was different than the 
requested one (i.e. wrong), as though I had requested .distinct() instead of 
.distinct(DS2CtryQtInfo.info_code).

Please could I request that engines raise an exception if the user tries to use 
a feature which isn't supported by the engine?

Thanks, Chris.



This email is confidential. If you are not the intended recipient, please 
advise us immediately and delete this message. 
The registered name of Cantab- part of GAM Systematic is Cantab Capital 
Partners LLP. 
See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information 
on confidentiality, the risks of non-secure electronic communication, and 
certain disclosures which we are required to make in accordance with applicable 
legislation and regulations. 
If you cannot access this link, please notify us by reply message and we will 
send the contents to you.

-- 
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] Purpose of assertion in table reflection test (1.1.9)

[jan.karstens via sqlalchemy]

Thank you so much for the detailed answer. Very much appreciated, this 
should get me on the right track to fix this for the eXAsoL dialect. 

The ExaSolution database (this is the original product name, Exasol being 
the company name - but no user cares and calls the DB Exasol) was 
originally a drop-in replacement for Oracle DBs when extra performance on 
OLTP is required. So yes, my assumption is that they emulated Oracle 
behavior.

But this is one of my next quests to figure out how closely they match ORA. 
Again, a big thank you for the pointers into documentation and code!

Am Freitag, 19. Mai 2017 22:03:47 UTC+2 schrieb Mike Bayer:
>
>
>
> On 05/18/2017 06:56 PM, jan.karstens via sqlalchemy wrote: 
> > Upgrading a specific dialect (EXASOL) to 1.1.9 made me stumble across 
> > this test (part of test_reflection.py): 
> > 
> > 
> https://github.com/zzzeek/sqlalchemy/blob/rel_1_1_9/lib/sqlalchemy/testing/suite/test_reflection.py#L737
>  
> > 
> > def test_get_table_names(self): 
> > tablenames = [ 
> > t for t in inspect(testing.db).get_table_names() 
> > if t.lower() in ("t1", "t2")] 
> >  
> > eq_(tablenames[0].upper(), tablenames[0].lower()) 
> > eq_(tablenames[1].upper(), tablenames[1].lower()) 
> > 
> > 
> > The test case fails and this does not look too unexpected to me. The 
> > tables T1 and T2 are added to the tablenames array, and u"t1".upper() is 
> > always different from u"t1".lower(). 
> > 
> > Am I missing the purpose of the test case or is this always bound to 
> fail? 
>
>
> we have two provided dialects, Oracle and Firebird, for which the 
> backend databases behave like this: 
>
> 1. create a table called "table1": 
>
> CREATE TABLE tablw1 (some_col integer) 
>
> 2. ask the database what the name of "table1" is: 
>
> SQL> SELECT table_name FROM  of "table1" 
> TABLE_NAME 
> -- 
>
> TABLE1 
>
>
> Now what just happened.  We created the table with the name "table1" and 
> then it insisted we just named it "TABLE1".  What's going on here? 
> Basically, we didn't quote the name "table1" in our CREATE TABLE 
> statement, which means in virtually all databases that the name is case 
> insensitive.  It means this table will respond to: 
>
> SELECT * FROM TABLE1 
> SELECT * FROM table1 
> SELECT * FROM TaBLe1 
>
> etc. 
>
>
> In SQLAlchemy, we first wrote things with SQLite, Mysql, Postgresql in 
> mind.  In these databases, if you don't quote the name, the system 
> catalogs give you back the name *in lowercase*.  Plus, lowercase names 
> are much more of a Python thing. 
>
> What all this means is that *SQLAlchemy considers an all lower case 
> table name to be **case insensitive**, and any other combination casings 
> to be **case sensitive**.  This could really use a whole chapter in 
> the docs, but for now you can see it at: 
>
>
> http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=table%20name#sqlalchemy.schema.Table.params.name
>  
>
> "Names which contain no upper case characters will be treated as case 
> insensitive names, and will not be quoted unless they are a reserved 
> word or contain special characters. A name with any number of upper case 
> characters is considered to be case sensitive, and will be sent as 
> quoted." 
>
>
> So now we have this round trip case that everyone expects to work: 
>
> t = Table("table1", metadata, autoload_with=engine) 
>
> t.create(some_other_engine) 
>
> t2 = Table("table1", some_other_metadata, autoload_with=some_other_engine) 
>
> That is, we can go back and forth, using "table1", and we get that same 
> name back.  If we got back "TABLE1", then it would create the table as 
> follows: 
>
> CREATE TABLE "TABLE1" (some_col integer) 
>
> and now we have a **case sensitive** name.  It means we can only SELECT 
> from it like this: 
>
> SELECT * FROM "TABLE1" 
>
> These SQL statements OTOH will fail: 
>
> SELECT * FROM table1 
> SELECT * FROM TaBlE1 
> SELECT * FROM "table1" 
>
> this statement *might* fail depending on backend: 
>
> SELECT * FROM TABLE1 
>
>
>
> So that's a lot of detail. Let's talk about EXASOL, which I have noted, 
> you have called EXASOL, and not "Exasol", which while this is a database 
> I know nothing about, leads to the impression that EXASOL LIKES THINGS 
> TO BE CAPITALIZED, basically the way ORACLE and FIREBIRD SEEM TO THINK 
> IS GREAT. 
>
> If that's the case, then you need to take the steps that the Oracle / 
> Firebird DBs take which is to "normalize" / "denormalize" the names that 
> you get back from the server.To do that, start with the top-level 
> dialect flag: 
>
> class EXASOLDialect(default.DefaultDialect): 
>  # ... 
>
>  requires_name_normalize = True 
>
> then you're going to want to denormalize names every time you pass them 
> to a system catalog query, and normalize names every time you receive 
> rows back from the system catalogs - look inside of 
> lib/sqlalchemy/oracle/base.py for