Re: [sqlalchemy] Why is there no SQLA-List class?

[c.buhtz]

On 2015-08-02 19:15  wrote:
> But what is about SQLAlchemy? I see nowhere a implementation of a
> List() type that encapsulate that VARCHAR-depending converting work?

When implementing this myself. I am not sure if I have to use
TypeDecorator
or
UserDefinedType
as baseclass for that.

I am not sure how this would look like.

I need a list (undefined length) with tree values for each entry.

  x = [(1,2,3), (1,2,3), (1,2,3)]

-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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] Re: func.substr() documentation?

[Pavel S]

You should refer documentation of your RDBMS.

'*func*' is just proxy to any function defined inside RDBMS.

If your RDBMS has function *bla* (e.g. CREATE FUNCTION bla...), then you 
can call if from python using *func.bla()*.



Dne neděle 2. srpna 2015 19:12:04 UTC+2 c.b...@posteo.jp napsal(a):
>
> I see in a lot of example code "func.substr()". But I can not find it 
> in the official docs (not in my local ones, not on the website). 
>
> Did I use the search feauter wrong? 
> -- 
> -BEGIN PGP PUBLIC KEY BLOCK- 
> Version: GnuPG v1 
>
> mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU 
> G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH 
> kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3 
> 0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe 
> GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33 
> LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0 
> eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH 
> AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj 
> DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY 
> Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK 
> D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U 
> jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi 
> du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA 
> UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0 
> KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B 
> zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+ 
> VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj 
> v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p 
> +ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU 
> CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF 
> blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h 
> 2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW 
> sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A 
> dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm 
> lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk= 
> =PGP9 
> -END PGP PUBLIC KEY BLOCK- 
>

-- 
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] Re: func.substr() documentation?

[c.buhtz]

On 2015-08-03 01:17 Pavel S  wrote:
> You should refer documentation of your RDBMS.

It is sqlite3.

> '*func*' is just proxy to any function defined inside RDBMS.
> 
> If your RDBMS has function *bla* (e.g. CREATE FUNCTION bla...), then
> you can call if from python using *func.bla()*.

Ah, nice to know. Thank you very much.
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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] Why is there no SQLA-List class?

[Mike Bayer]

On 8/3/15 3:13 AM, c.bu...@posteo.jp wrote:

On 2015-08-02 19:15  wrote:

But what is about SQLAlchemy? I see nowhere a implementation of a
List() type that encapsulate that VARCHAR-depending converting work?

When implementing this myself. I am not sure if I have to use
TypeDecorator
or
UserDefinedType
as baseclass for that.

I am not sure how this would look like.

I need a list (undefined length) with tree values for each entry.

   x = [(1,2,3), (1,2,3), (1,2,3)]



The JSON type would give you a quick way to handle the list format:

http://docs.sqlalchemy.org/en/rel_1_0/core/custom_types.html#marshal-json-strings

if you have some other string format, substitute dumps() / loads().

--
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] UnicodeDecode error on sqlalchemy select query

[Mike Bayer]

On 8/1/15 12:12 PM, Abhishek Sharma wrote:
Thanks for help. But still i have confusion over encoding and decoding 
procedure which will take place before retrieving and storing the 
results in DB.


In case if i am not using convert_unicode option and data type is 
String so python process will give str object to sqlalchemy at the 
time of insert record in DB using ORM. So will alchemy store that 
object in encoded form?. So at the time of retrieving ORM will give 
str object for String type column to python and python decode that 
object with default encoding?


Can i simply use Unicode Data type for columns where there might be 
chance of using non ascii data?


if you know that your unicode data is on specific columns then yes, the 
Unicode type plugs in an encoder/decoder for those backends that require 
it.







On Thu, Jul 30, 2015 at 2:55 AM, Mike Bayer > wrote:




On 7/29/15 2:23 PM, Abhishek Sharma wrote:

We are using sqlalchemy version 0.7, python 2.7 and oracle Database.
We have ASCII as default python encoding and DB have ISO-8052
encoding. Our DB tables contains some of characters which are out
of ASCII range. So when we are running query on those tables we
are getting Unicode Decode error saying "ASCII" codec can not
decode. This error we are getting without accessing model attributes.

How i can handle these errors without changing python default
encoding.


Oracle's client encoding is controlled by the NLS_LANG environment
variable.That has to be set correctly first off (see

http://www.oracle.com/technetwork/products/globalization/nls-lang-099431.html).
If you have non-ASCII strings encoded in datatypes that are
explicitly not of type NVARCHAR or NCLOB , or you're relying on a
lot of raw SQL, and you are still getting errors, I would set the
"coerce_to_unicode=True" flag on create_engine(), which allows
cx_Oracle's unicode facilities to take place fully for all string
data being returned, at the expense of some performance.  See
http://docs.sqlalchemy.org/en/rel_1_0/dialects/oracle.html#unicode
for background.



-- 
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 a topic in

the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/eThubIMnL4o/unsubscribe.
To unsubscribe from this group and all its topics, 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.


--
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] query with HAVING COUNT doesn't work as expected

[jurie . horneman]

I am using Python 2.7, SQLAlchemy 0.9.3, and PostgreSQL 9.4.

This:

session.query(self.db.recording_table.c.id).\
join(self.db.frame_table, self.db.recording_table.c.id == 
self.db.frame_table.c.recording_id).\
group_by(self.db.recording_table.c.id).\
having(func.coalesce(func.count(self.db.frame_table.c.frame_nr), 0) < 2).\
all()


generates the following SQL:

SELECT recordings.id AS recordings_id 
FROM recordings JOIN recording_frames ON recordings.id = 
recording_frames.recording_id GROUP BY recordings.id 
HAVING coalesce(count(recording_frames.frame_nr), %(param_1)s) < 
%(coalesce_1)s
{'param_1': 0, 'coalesce_1': 2}

and returns an empty list.

If I execute this in pgAdmin:

SELECT recordings.id
FROM recordings
LEFT JOIN recording_frames
ON recordings.id = recording_frames.recording_id
GROUP BY recordings.id
HAVING (coalesce(count(recording_frames.frame_nr), 0) < 2)

I get a list with 2 IDs in the recordings table, which is the expected 
result.

I have no idea why SQLAlchemy won't give me the same result. If I change 
the criterion to > 0 I get the same result as with raw SQL. It seems 
SQLAlchemy, with the code above, somehow filters out the rows where COUNT 
returns nothing, despite the COALESCE.

What am I doing wrong?

-- 
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] query with HAVING COUNT doesn't work as expected

[Ladislav Lenart]

Hello.

pgAdmin query uses LEFT JOIN whereas SQLAlchemy query uses (inner) JOIN. Replace
.join(...) with .outerjoin(...) in your SQLAlchemy query.

HTH,

Ladislav Lenart


On 3.8.2015 16:48, jurie.horne...@gmail.com wrote:
> I am using Python 2.7, SQLAlchemy 0.9.3, and PostgreSQL 9.4.
> 
> This:
> 
> session.query(self.db.recording_table.c.id).\
> join(self.db.frame_table, self.db.recording_table.c.id == 
> self.db.frame_table.c.recording_id).\
> group_by(self.db.recording_table.c.id).\
> having(func.coalesce(func.count(self.db.frame_table.c.frame_nr), 0) < 2).\
> all()
> 
> 
> generates the following SQL:
> 
> SELECT recordings.id AS recordings_id
> FROM recordings JOIN recording_frames ON recordings.id =
> recording_frames.recording_id GROUP BY recordings.id
> HAVING coalesce(count(recording_frames.frame_nr), %(param_1)s) < 
> %(coalesce_1)s
> {'param_1': 0, 'coalesce_1': 2}
> 
> and returns an empty list.
> 
> If I execute this in pgAdmin:
> 
> SELECT recordings.id
> FROM recordings
> LEFT JOIN recording_frames
> ON recordings.id = recording_frames.recording_id
> GROUP BY recordings.id
> HAVING (coalesce(count(recording_frames.frame_nr), 0) < 2)
> 
> I get a list with 2 IDs in the recordings table, which is the expected result.
> 
> I have no idea why SQLAlchemy won't give me the same result. If I change the
> criterion to > 0 I get the same result as with raw SQL. It seems SQLAlchemy,
> with the code above, somehow filters out the rows where COUNT returns nothing,
> despite the COALESCE.
> 
> What am I doing wrong?
> 
> -- 
> 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.

[sqlalchemy] Multiple JOINs of superclass table and potential discrepancy in chained vs on-clause

[Douglas Russell]

Hi again,

Full code: https://gist.github.com/dpwrussell/8ecca88f642cca003999

I have an structure linked together like so. A-B is a Many-To-Many and uses 
an association table. A and B are both subclasses of common base Object.

A
└── B

I also have an object X that can be linked to any type of object: A or B.

I can easily run a query that returns all objects that have a certain X 
object linked to it.

I also need to be able to run a query which gets all the B objects where 
the A parent has a certain X object linked to it.

Chained:

SELECT object.type AS object_type, b.id AS b_id, object.id AS object_id, 
object.name AS object_name
FROM object JOIN b ON object.id = b.id JOIN a_b_association AS 
a_b_association_1 ON b.id = a_b_association_1.b_id JOIN (object AS object_1 
JOIN a AS a_1 ON object_1.id = a_1.id) ON a_1.id = a_b_association_1.a_id 
JOIN x ON object.id = x.obj_id
WHERE x.name = %(name_1)s
2015-08-03 10:53:03,474 INFO sqlalchemy.engine.base.Engine {'name_1': 'x1'}

Multiple as-clause:

SELECT object.type AS object_type, b.id AS b_id, object.id AS object_id, 
object.name AS object_name
FROM object JOIN b ON object.id = b.id JOIN a_b_association AS 
a_b_association_1 ON b.id = a_b_association_1.b_id JOIN (object AS object_1 
JOIN a AS a_1 ON object_1.id = a_1.id) ON a_1.id = a_b_association_1.a_id 
JOIN x ON object_1.id = x.obj_id
WHERE x.name = %(name_1)s
2015-08-03 10:53:03,480 INFO sqlalchemy.engine.base.Engine {'name_1': 'x1'}



The difference is subtle. In the multiple on-clause case the JOIN to the x 
table is conducted using the alias (object_1) created during the previous 
JOIN. This is the behaviour that I would expect and gives the correct 
result. In the chained case, the original object reference is used, giving 
incorrect results (none in this case).

The SQLAlchemy manual seems to suggest that these should be equivalent so 
I'm wondering if there is a bug there?

If I'm reading the manual correctly, I can ordinarily use JOIN aliases to 
explicitly avoid this kind of thing, but in this case, I am not specifying 
this join myself, it is being built from the joined table inheritance.

I am going to use the on-clause technique for now to get around this, but 
it would be good to know (especially if this is not a bug) if I should be 
handling this differently in general?

Thanks a lot,

Douglas

-- 
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] query with HAVING COUNT doesn't work as expected

[jurie . horneman]

Thanks! That solved it.

I'm a JOIN newbie, so I didn't realize left join and outer join were the 
same thing.

J.



On Monday, August 3, 2015 at 5:08:44 PM UTC+2, Ladislav Lenart wrote:
>
> Hello. 
>
> pgAdmin query uses LEFT JOIN whereas SQLAlchemy query uses (inner) JOIN. 
> Replace 
> .join(...) with .outerjoin(...) in your SQLAlchemy query. 
>
> HTH, 
>
> Ladislav Lenart 
>
>
> On 3.8.2015 16:48, jurie.h...@gmail.com  wrote: 
> > I am using Python 2.7, SQLAlchemy 0.9.3, and PostgreSQL 9.4. 
> > 
> > This: 
> > 
> > session.query(self.db.recording_table.c.id).\ 
> > join(self.db.frame_table, self.db.recording_table.c.id == 
> self.db.frame_table.c.recording_id).\ 
> > group_by(self.db.recording_table.c.id).\ 
> > having(func.coalesce(func.count(self.db.frame_table.c.frame_nr), 0) 
> < 2).\ 
> > all() 
> > 
> > 
> > generates the following SQL: 
> > 
> > SELECT recordings.id AS recordings_id 
> > FROM recordings JOIN recording_frames ON recordings.id = 
> > recording_frames.recording_id GROUP BY recordings.id 
> > HAVING coalesce(count(recording_frames.frame_nr), %(param_1)s) < 
> %(coalesce_1)s 
> > {'param_1': 0, 'coalesce_1': 2} 
> > 
> > and returns an empty list. 
> > 
> > If I execute this in pgAdmin: 
> > 
> > SELECT recordings.id 
> > FROM recordings 
> > LEFT JOIN recording_frames 
> > ON recordings.id = recording_frames.recording_id 
> > GROUP BY recordings.id 
> > HAVING (coalesce(count(recording_frames.frame_nr), 0) < 2) 
> > 
> > I get a list with 2 IDs in the recordings table, which is the expected 
> result. 
> > 
> > I have no idea why SQLAlchemy won't give me the same result. If I change 
> the 
> > criterion to > 0 I get the same result as with raw SQL. It seems 
> SQLAlchemy, 
> > with the code above, somehow filters out the rows where COUNT returns 
> nothing, 
> > despite the COALESCE. 
> > 
> > What am I doing wrong? 
>
>

-- 
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] query with HAVING COUNT doesn't work as expected

[Ladislav Lenart]

On 3.8.2015 17:24, jurie.horne...@gmail.com wrote:
> Thanks! That solved it.
> 
> I'm a JOIN newbie, so I didn't realize left join and outer join were the same 
> thing.

Well, in that case this might be of some use to you...

SQL:
* JOIN is short for INNER JOIN.
* LEFT JOIN is short for LEFT OUTER JOIN.
* RIGHT JOIN is short for RIGHT OUTER JOIN.
* FULL JOIN is short for FULL OUTER JOIN.
For a detailed explanation see e.g.:
http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html

SQLAlchemy ORM supports only:
* INNER JOIN with join().
* LEFT OUTER JOIN with outerjoin().

You can simulate RIGHT OUTER JOIN with LEFT JOIN if you change the order of the
tables in the query.

HTH,

Ladislav Lenart


> J.
> 
> 
> 
> On Monday, August 3, 2015 at 5:08:44 PM UTC+2, Ladislav Lenart wrote:
> 
> Hello.
> 
> pgAdmin query uses LEFT JOIN whereas SQLAlchemy query uses (inner) JOIN.
> Replace
> .join(...) with .outerjoin(...) in your SQLAlchemy query.
> 
> HTH,
> 
> Ladislav Lenart
> 
> 
> On 3.8.2015 16:48, jurie.h...@gmail.com  wrote:
> > I am using Python 2.7, SQLAlchemy 0.9.3, and PostgreSQL 9.4.
> >
> > This:
> >
> > session.query(self.db.recording_table.c.id
> ).\
> > join(self.db.frame_table, self.db.recording_table.c.id
>  == 
> self.db.frame_table.c.recording_id).\
> > group_by(self.db.recording_table.c.id
> ).\
> > having(func.coalesce(func.count(self.db.frame_table.c.frame_nr), 0) 
> <
> 2).\
> > all()
> >
> >
> > generates the following SQL:
> >
> > SELECT recordings.id  AS recordings_id
> > FROM recordings JOIN recording_frames ON recordings.id
>  =
> > recording_frames.recording_id GROUP BY recordings.id 
> 
> > HAVING coalesce(count(recording_frames.frame_nr), %(param_1)s) <
> %(coalesce_1)s
> > {'param_1': 0, 'coalesce_1': 2}
> >
> > and returns an empty list.
> >
> > If I execute this in pgAdmin:
> >
> > SELECT recordings.id 
> > FROM recordings
> > LEFT JOIN recording_frames
> > ON recordings.id  = recording_frames.recording_id
> > GROUP BY recordings.id 
> > HAVING (coalesce(count(recording_frames.frame_nr), 0) < 2)
> >
> > I get a list with 2 IDs in the recordings table, which is the expected
> result.
> >
> > I have no idea why SQLAlchemy won't give me the same result. If I 
> change the
> > criterion to > 0 I get the same result as with raw SQL. It seems 
> SQLAlchemy,
> > with the code above, somehow filters out the rows where COUNT returns
> nothing,
> > despite the COALESCE.
> >
> > What am I doing wrong?
> 
> -- 
> 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] Multiple JOINs of superclass table and potential discrepancy in chained vs on-clause

[Mike Bayer]

all of those syntaxes are supposed to be exactly equivalent so this is a 
major issue: 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3505/join-targeting-broken-for-joined-inh




On 8/3/15 11:13 AM, Douglas Russell wrote:

Hi again,

Full code: https://gist.github.com/dpwrussell/8ecca88f642cca003999

I have an structure linked together like so. A-B is a Many-To-Many and 
uses an association table. A and B are both subclasses of common base 
Object.


A
└── B

I also have an object X that can be linked to any type of object: A or B.

I can easily run a query that returns all objects that have a certain 
X object linked to it.


I also need to be able to run a query which gets all the B objects 
where the A parent has a certain X object linked to it.


Chained:

|
SELECT object.type AS object_type,b.id AS b_id,object.id AS 
object_id,object.name AS object_name
FROM objectJOIN b ON object.id =b.id JOIN a_b_association AS 
a_b_association_1 ON b.id =a_b_association_1.b_id JOIN (objectAS 
object_1 JOIN a AS a_1 ON object_1.id =a_1.id)ON a_1.id 
=a_b_association_1.a_id JOIN x ON object.id =x.obj_id

WHERE x.name =%(name_1)s
2015-08-0310:53:03,474INFO sqlalchemy.engine.base.Engine{'name_1':'x1'}
|

Multiple as-clause:

|
SELECT object.type AS object_type,b.id AS b_id,object.id AS 
object_id,object.name AS object_name
FROM objectJOIN b ON object.id =b.id JOIN a_b_association AS 
a_b_association_1 ON b.id =a_b_association_1.b_id JOIN (objectAS 
object_1 JOIN a AS a_1 ON object_1.id =a_1.id)ON a_1.id 
=a_b_association_1.a_id JOIN x ON object_1.id =x.obj_id

WHERE x.name =%(name_1)s
2015-08-0310:53:03,480INFO sqlalchemy.engine.base.Engine{'name_1':'x1'}

|


The difference is subtle. In the multiple on-clause case the JOIN to 
the x table is conducted using the alias (object_1) created during the 
previous JOIN. This is the behaviour that I would expect and gives the 
correct result. In the chained case, the original object reference is 
used, giving incorrect results (none in this case).


The SQLAlchemy manual seems to suggest that these should be equivalent 
so I'm wondering if there is a bug there?


If I'm reading the manual correctly, I can ordinarily use JOIN aliases 
to explicitly avoid this kind of thing, but in this case, I am not 
specifying this join myself, it is being built from the joined table 
inheritance.


I am going to use the on-clause technique for now to get around this, 
but it would be good to know (especially if this is not a bug) if I 
should be handling this differently in general?


Thanks a lot,

Douglas
--
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] UnicodeDecode error on sqlalchemy select query

[Abhishek Sharma]

what about CLOB type?  Unicode only handles String type. Do i need to use
convert_unicode there?

On Mon, Aug 3, 2015 at 6:56 PM, Mike Bayer  wrote:

>
>
> On 8/1/15 12:12 PM, Abhishek Sharma wrote:
>
> Thanks for help. But still i have confusion over encoding and decoding
> procedure which will take place before retrieving and storing the results
> in DB.
>
> In case if i am not using convert_unicode option and data type is String
> so python process will give str object to sqlalchemy at the time of insert
> record in DB using ORM. So will alchemy store that object in encoded form?.
> So at the time of retrieving ORM will give str object for String type
> column to python and python decode that object with default encoding?
>
> Can i simply use Unicode Data type for columns where there might be chance
> of using non ascii data?
>
>
> if you know that your unicode data is on specific columns then yes, the
> Unicode type plugs in an encoder/decoder for those backends that require
> it.
>
>
>
>
>
> On Thu, Jul 30, 2015 at 2:55 AM, Mike Bayer 
> wrote:
>
>>
>>
>> On 7/29/15 2:23 PM, Abhishek Sharma wrote:
>>
>> We are using sqlalchemy version 0.7, python 2.7 and oracle Database.
>> We have ASCII as default python encoding and DB have ISO-8052 encoding.
>> Our DB tables contains some of characters which are out of ASCII range. So
>> when we are running query on those tables we are getting Unicode Decode
>> error saying "ASCII" codec can not decode. This error we are getting
>> without accessing model attributes.
>>
>> How i can handle these errors without changing python default encoding.
>>
>>
>> Oracle's client encoding is controlled by the NLS_LANG environment
>> variable.That has to be set correctly first off (see
>> http://www.oracle.com/technetwork/products/globalization/nls-lang-099431.html).
>> If you have non-ASCII strings encoded in datatypes that are explicitly not
>> of type NVARCHAR or NCLOB , or you're relying on a lot of raw SQL, and you
>> are still getting errors, I would set the "coerce_to_unicode=True" flag on
>> create_engine(), which allows cx_Oracle's unicode facilities to take place
>> fully for all string data being returned, at the expense of some
>> performance.  See
>> 
>> http://docs.sqlalchemy.org/en/rel_1_0/dialects/oracle.html#unicode for
>> background.
>>
>>
>>
>> --
>> 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 a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> 
>> https://groups.google.com/d/topic/sqlalchemy/eThubIMnL4o/unsubscribe.
>> To unsubscribe from this group and all its topics, 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.
>
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/eThubIMnL4o/unsubscribe.
> To unsubscribe from this group and all its topics, 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] Multiple JOINs of superclass table and potential discrepancy in chained vs on-clause

[Mike Bayer]

On 8/3/15 1:01 PM, Mike Bayer wrote:
all of those syntaxes are supposed to be exactly equivalent so this is 
a major issue: 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3505/join-targeting-broken-for-joined-inh


OK, this is not as much of a bug as I thought, though I might be able to 
do something.


The join here has no choice but to auto-alias the "A" target when it 
joins on B.a_list.   When you then join with a second call to .join(), 
it assumes you want to again join from B.   You need to be using 
from_joinpoint here:


q = q.join(A.x_list, from_joinpoint=True)


it then knows you want to join from A and not B.







On 8/3/15 11:13 AM, Douglas Russell wrote:

Hi again,

Full code: https://gist.github.com/dpwrussell/8ecca88f642cca003999

I have an structure linked together like so. A-B is a Many-To-Many 
and uses an association table. A and B are both subclasses of common 
base Object.


A
└── B

I also have an object X that can be linked to any type of object: A or B.

I can easily run a query that returns all objects that have a certain 
X object linked to it.


I also need to be able to run a query which gets all the B objects 
where the A parent has a certain X object linked to it.


Chained:

|
SELECT object.type AS object_type,b.id AS b_id,object.id AS 
object_id,object.name AS object_name
FROM objectJOIN b ON object.id =b.id JOIN a_b_association AS 
a_b_association_1 ON b.id =a_b_association_1.b_id JOIN (objectAS 
object_1 JOIN a AS a_1 ON object_1.id =a_1.id)ON a_1.id 
=a_b_association_1.a_id JOIN x ON object.id =x.obj_id

WHERE x.name =%(name_1)s
2015-08-0310:53:03,474INFO sqlalchemy.engine.base.Engine{'name_1':'x1'}
|

Multiple as-clause:

|
SELECT object.type AS object_type,b.id AS b_id,object.id AS 
object_id,object.name AS object_name
FROM objectJOIN b ON object.id =b.id JOIN a_b_association AS 
a_b_association_1 ON b.id =a_b_association_1.b_id JOIN (objectAS 
object_1 JOIN a AS a_1 ON object_1.id =a_1.id)ON a_1.id 
=a_b_association_1.a_id JOIN x ON object_1.id =x.obj_id

WHERE x.name =%(name_1)s
2015-08-0310:53:03,480INFO sqlalchemy.engine.base.Engine{'name_1':'x1'}

|


The difference is subtle. In the multiple on-clause case the JOIN to 
the x table is conducted using the alias (object_1) created during 
the previous JOIN. This is the behaviour that I would expect and 
gives the correct result. In the chained case, the original object 
reference is used, giving incorrect results (none in this case).


The SQLAlchemy manual seems to suggest that these should be 
equivalent so I'm wondering if there is a bug there?


If I'm reading the manual correctly, I can ordinarily use JOIN 
aliases to explicitly avoid this kind of thing, but in this case, I 
am not specifying this join myself, it is being built from the joined 
table inheritance.


I am going to use the on-clause technique for now to get around this, 
but it would be good to know (especially if this is not a bug) if I 
should be handling this differently in general?


Thanks a lot,

Douglas
--
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.


--
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] UnicodeDecode error on sqlalchemy select query

[Mike Bayer]

On 8/3/15 1:04 PM, Abhishek Sharma wrote:
what about CLOB type?  Unicode only handles String type. Do i need to 
use convert_unicode there?


if your CLOB expects non-ascii characters then yes.

though on Oracle I thought you really need to be using NCLOB for a col 
that stores unicode.





On Mon, Aug 3, 2015 at 6:56 PM, Mike Bayer > wrote:




On 8/1/15 12:12 PM, Abhishek Sharma wrote:

Thanks for help. But still i have confusion over encoding and
decoding procedure which will take place before retrieving and
storing the results in DB.

In case if i am not using convert_unicode option and data type is
String so python process will give str object to sqlalchemy at
the time of insert record in DB using ORM. So will alchemy store
that object in encoded form?. So at the time of retrieving ORM
will give str object for String type column to python and python
decode that object with default encoding?

Can i simply use Unicode Data type for columns where there might
be chance of using non ascii data?


if you know that your unicode data is on specific columns then
yes, the Unicode type plugs in an encoder/decoder for those
backends that require it.






On Thu, Jul 30, 2015 at 2:55 AM, Mike Bayer
mailto:mike...@zzzcomputing.com>> wrote:



On 7/29/15 2:23 PM, Abhishek Sharma wrote:

We are using sqlalchemy version 0.7, python 2.7 and oracle
Database.
We have ASCII as default python encoding and DB have
ISO-8052 encoding. Our DB tables contains some of characters
which are out of ASCII range. So when we are running query
on those tables we are getting Unicode Decode error saying
"ASCII" codec can not decode. This error we are getting
without accessing model attributes.

How i can handle these errors without changing python
default encoding.


Oracle's client encoding is controlled by the NLS_LANG
environment variable.That has to be set correctly first
off (see

http://www.oracle.com/technetwork/products/globalization/nls-lang-099431.html).
If you have non-ASCII strings encoded in datatypes that are
explicitly not of type NVARCHAR or NCLOB , or you're relying
on a lot of raw SQL, and you are still getting errors, I
would set the "coerce_to_unicode=True" flag on
create_engine(), which allows cx_Oracle's unicode facilities
to take place fully for all string data being returned, at
the expense of some performance.  See
http://docs.sqlalchemy.org/en/rel_1_0/dialects/oracle.html#unicode
for background.



-- 
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 a

topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/eThubIMnL4o/unsubscribe.
To unsubscribe from this group and all its topics, 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.


-- 
You received this message because you are subscribed to a topic in

the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/eThubIMnL4o/unsubscribe.
To unsubscribe from this group and all its topics, send an email
to sqlalchemy+unsubscr...@googlegroups.com
.
To post to th

Re: [sqlalchemy] Why is there no SQLA-List class?

[Adam Tauno Williams]

Quoting c.bu...@posteo.jp:

I know that in most all RDBMS are no implementations of lists or arrays.


On the contrary - and emphatically - just about every modern RDMBS  
supports collection types including lists, sets, multi-sets, and  
key-value data.  This support has been in most databases for a decade  
now; most developers just don't know how to use it or are on platforms  
[like PHP] whose bindings go all bajiggidy when then encounter such a  
thing.


SQLAlchemy has excellent support for 'advanced' data-types.



When I want that I can use VARCHAR or something like that. And I can
query for substr() in that field.


Ick.  Better to use an HSTORE/JSON datatype - you can even index on  
them these days.


From 2013 - so the situation is much better now - but to get the  
general idea 



This is ok on a SQL-level.


No.  It may 'work', but it is a HACK.


But what is about SQLAlchemy? I see nowhere a implementation of a
List() type that encapsulate that VARCHAR-depending converting work?
And I can not found something like thist over .
But I imagine that some persons in the past invited something like that.
Any hints?




--
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] alembic: downgrade raises KeyError

[Dirk Makowski]

Hi all,

after upgrading alembic to 0.7.7 and SQLAlchemy to 1.0.8, when I try to 
downgrade one step from head, I get the below KeyError. The allegedly 
missing key is the revision ID of head. It is correctly stored in the 
alembic table, and the file and its predecessor are also present.

Other commands however do find that key:

{{{
$ alembic -c etc/Morrigan/development/alembic.ini heads --verbose
Rev: 32f69b44319 (head)
Parent: 1644537aa7
Path: 
/home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py

20150714_feat_mdv_mgmt

Revision ID: 32f69b44319
Revises: 1644537aa7
Create Date: 2015-07-20 14:52:40.227436
}}}

and

{{{
$ alembic -c etc/Morrigan/development/alembic.ini show  32f69b44319 
Rev: 32f69b44319 (head)
Parent: 1644537aa7
Path: 
/home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py

20150714_feat_mdv_mgmt

Revision ID: 32f69b44319
Revises: 1644537aa7
Create Date: 2015-07-20 14:52:40.227436
}}}

But downgrading gives:

{{{
$ alembic -c etc/Morrigan/development/alembic.ini  downgrade  -1
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
Traceback (most recent call last):
  File "/home/dm/myprojects/atrial-py34-venv/bin/alembic", line 9, in 

load_entry_point('alembic==0.7.7', 'console_scripts', 'alembic')()
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py",
 
line 439, in main
CommandLine(prog=prog).main(argv=argv)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py",
 
line 433, in main
self.run_cmd(cfg, options)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py",
 
line 416, in run_cmd
**dict((k, getattr(options, k)) for k in kwarg)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/command.py",
 
line 193, in downgrade
script.run_env()
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/script.py",
 
line 390, in run_env
util.load_python_file(self.dir, 'env.py')
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/util.py",
 
line 244, in load_python_file
module = load_module_py(module_id, path)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/compat.py",
 
line 68, in load_module_py
module_id, path).load_module(module_id)
  File "", line 539, in _check_name_wrapper
  File "", line 1614, in load_module
  File "", line 596, in _load_module_shim
  File "", line 1220, in load
  File "", line 1200, in _load_unlocked
  File "", line 1129, in _exec
  File "", line 1471, in exec_module
  File "", line 321, in 
_call_with_frames_removed
  File "alembic/env.py", line 97, in 
run_migrations_online()
  File "alembic/env.py", line 92, in run_migrations_online
context.run_migrations()
  File "", line 7, in run_migrations
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/environment.py",
 
line 738, in run_migrations
self.get_context().run_migrations(**kw)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/migration.py",
 
line 302, in run_migrations
for step in self._migrations_fn(heads, self):
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/command.py",
 
line 182, in downgrade
return script._downgrade_revs(revision, rev)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/script.py",
 
line 315, in _downgrade_revs
current_rev, destination)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/revision.py",
 
line 512, in iterate_revisions
inclusive, assert_relative_length
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/revision.py",
 
line 450, in _relative_iterate
inclusive=inclusive, implicit_base=implicit_base))
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/revision.py",
 
line 672, in _iterate_revisions
total_space.remove(rev.revision)
KeyError: '32f69b44319'
}}}

Thanks for your help,
Dirk

-- 
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] alembic: downgrade raises KeyError

[Mike Bayer]

can you confirm no issue with Alembic 0.7.6?Can you please send 
along all of your migration scripts; feel free to erase everything but 
the version headers and empty upgrade() / downgrade() functions, I just 
need to see the structure you're working with.  thanks.




On 8/3/15 4:59 PM, Dirk Makowski wrote:

Hi all,

after upgrading alembic to 0.7.7 and SQLAlchemy to 1.0.8, when I try 
to downgrade one step from head, I get the below KeyError. The 
allegedly missing key is the revision ID of head. It is correctly 
stored in the alembic table, and the file and its predecessor are also 
present.


Other commands however do find that key:

{{{
$ alembic -c etc/Morrigan/development/alembic.ini heads --verbose
Rev: 32f69b44319 (head)
Parent: 1644537aa7
Path: 
/home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py


20150714_feat_mdv_mgmt
Revision ID: 32f69b44319
Revises: 1644537aa7
Create Date: 2015-07-20 14:52:40.227436
}}}

and

{{{
$ alembic -c etc/Morrigan/development/alembic.ini show  32f69b44319
Rev: 32f69b44319 (head)
Parent: 1644537aa7
Path: 
/home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py


20150714_feat_mdv_mgmt
Revision ID: 32f69b44319
Revises: 1644537aa7
Create Date: 2015-07-20 14:52:40.227436
}}}

But downgrading gives:

{{{
$ alembic -c etc/Morrigan/development/alembic.ini  downgrade  -1
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
Traceback (most recent call last):
  File "/home/dm/myprojects/atrial-py34-venv/bin/alembic", line 9, in 


load_entry_point('alembic==0.7.7', 'console_scripts', 'alembic')()
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 439, in main

CommandLine(prog=prog).main(argv=argv)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 433, in main

self.run_cmd(cfg, options)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 416, in run_cmd

**dict((k, getattr(options, k)) for k in kwarg)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/command.py", 
line 193, in downgrade

script.run_env()
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/script.py", 
line 390, in run_env

util.load_python_file(self.dir, 'env.py')
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/util.py", 
line 244, in load_python_file

module = load_module_py(module_id, path)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/compat.py", 
line 68, in load_module_py

module_id, path).load_module(module_id)
  File "", line 539, in _check_name_wrapper
  File "", line 1614, in load_module
  File "", line 596, in _load_module_shim
  File "", line 1220, in load
  File "", line 1200, in _load_unlocked
  File "", line 1129, in _exec
  File "", line 1471, in exec_module
  File "", line 321, in 
_call_with_frames_removed

  File "alembic/env.py", line 97, in 
run_migrations_online()
  File "alembic/env.py", line 92, in run_migrations_online
context.run_migrations()
  File "", line 7, in run_migrations
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/environment.py", 
line 738, in run_migrations

self.get_context().run_migrations(**kw)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/migration.py", 
line 302, in run_migrations

for step in self._migrations_fn(heads, self):
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/command.py", 
line 182, in downgrade

return script._downgrade_revs(revision, rev)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/script.py", 
line 315, in _downgrade_revs

current_rev, destination)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/revision.py", 
line 512, in iterate_revisions

inclusive, assert_relative_length
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/revision.py", 
line 450, in _relative_iterate

inclusive=inclusive, implicit_base=implicit_base))
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/revision.py", 
line 672, in _iterate_revisions

total_space.remove(rev.revision)
KeyError: '32f69b44319'
}}}

Thanks for your help,
Dirk
--
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://gr

Re: [sqlalchemy] Why is there no SQLA-List class?

[c.buhtz]

I never used JSON before or know something about it. I will check that.

On 2015-08-03 14:38 Adam Tauno Williams  wrote:
> On the contrary - and emphatically - just about every modern RDMBS  
> supports collection types including lists, sets, multi-sets, and  
> key-value data.

Can you specify that. I couldn't find something like that e.g. for
sqlite3 and didn't remeber seeing something like that in PostgreSQL.

When I understand JSON right: On RDBMS level it is just a string.
JSON-data/string in a RDBMS field sounds for me like a hack, too.

If this works it is ok for me.
But I doesn't sound like real(!) support for lists or something like
that.

I have to say I worked a lot with real(!) ODBMS in the past. So my
thinking structure about data is always in that paradigma. This makes
it kind of hard for me to work with an RDBMS. :)
Lits and things like that are natural in ODBMS.

> SQLAlchemy has excellent support for 'advanced' data-types.

I looked there, too. Couldn't find a SQLAlchemy data type that looks
like a list. Maybe I missunderstand someting.
I overread "JSON" because I didn't know what this was.

btw: Am I right if I say I can build querys for specific fields in a
JSON-field?
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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] alembic: downgrade raises KeyError

[Dirk Makowski]

I had do revert back to alembic 0.6.7 for the down-migration to work again 
(upgraded today from 0.6.6). Please find attached the bare scripts.


On Monday, August 3, 2015 at 11:27:40 PM UTC+2, Michael Bayer wrote:
>
> can you confirm no issue with Alembic 0.7.6?Can you please send along 
> all of your migration scripts; feel free to erase everything but the 
> version headers and empty upgrade() / downgrade() functions, I just need to 
> see the structure you're working with.  thanks.
>
>
>
> On 8/3/15 4:59 PM, Dirk Makowski wrote:
>
> Hi all, 
>
> after upgrading alembic to 0.7.7 and SQLAlchemy to 1.0.8, when I try to 
> downgrade one step from head, I get the below KeyError. The allegedly 
> missing key is the revision ID of head. It is correctly stored in the 
> alembic table, and the file and its predecessor are also present.
>
> Other commands however do find that key:
>
> {{{
> $ alembic -c etc/Morrigan/development/alembic.ini heads --verbose
> Rev: 32f69b44319 (head)
> Parent: 1644537aa7
> Path: 
> /home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py
>
> 20150714_feat_mdv_mgmt
> 
> Revision ID: 32f69b44319
> Revises: 1644537aa7
> Create Date: 2015-07-20 14:52:40.227436
> }}}
>
> and
>
> {{{
> $ alembic -c etc/Morrigan/development/alembic.ini show  32f69b44319 
> Rev: 32f69b44319 (head)
> Parent: 1644537aa7
> Path: 
> /home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py
>
> 20150714_feat_mdv_mgmt
> 
> Revision ID: 32f69b44319
> Revises: 1644537aa7
> Create Date: 2015-07-20 14:52:40.227436
> }}}
>
> But downgrading gives:
>
> {{{
> $ alembic -c etc/Morrigan/development/alembic.ini  downgrade  -1
> INFO  [alembic.migration] Context impl PostgresqlImpl.
> INFO  [alembic.migration] Will assume transactional DDL.
> Traceback (most recent call last):
>   File "/home/dm/myprojects/atrial-py34-venv/bin/alembic", line 9, in 
> 
> load_entry_point('alembic==0.7.7', 'console_scripts', 'alembic')()
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py",
>  
> line 439, in main
> CommandLine(prog=prog).main(argv=argv)
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py",
>  
> line 433, in main
> self.run_cmd(cfg, options)
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py",
>  
> line 416, in run_cmd
> **dict((k, getattr(options, k)) for k in kwarg)
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/command.py",
>  
> line 193, in downgrade
> script.run_env()
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/script.py",
>  
> line 390, in run_env
> util.load_python_file(self.dir, 'env.py')
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/util.py",
>  
> line 244, in load_python_file
> module = load_module_py(module_id, path)
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/compat.py",
>  
> line 68, in load_module_py
> module_id, path).load_module(module_id)
>   File "", line 539, in _check_name_wrapper
>   File "", line 1614, in load_module
>   File "", line 596, in _load_module_shim
>   File "", line 1220, in load
>   File "", line 1200, in _load_unlocked
>   File "", line 1129, in _exec
>   File "", line 1471, in exec_module
>   File "", line 321, in 
> _call_with_frames_removed
>   File "alembic/env.py", line 97, in 
> run_migrations_online()
>   File "alembic/env.py", line 92, in run_migrations_online
> context.run_migrations()
>   File "", line 7, in run_migrations
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/environment.py",
>  
> line 738, in run_migrations
> self.get_context().run_migrations(**kw)
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/migration.py",
>  
> line 302, in run_migrations
> for step in self._migrations_fn(heads, self):
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/command.py",
>  
> line 182, in downgrade
> return script._downgrade_revs(revision, rev)
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/script.py",
>  
> line 315, in _downgrade_revs
> current_rev, destination)
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/revision.py",
>  
> line 512, in iterate_revisions
> inclusive, assert_relative_length
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/revision.py",
>  
> line 450, in _relative_iterate
> inclusive=inclusive, implicit_base=implicit_base))
>   File 
> "/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/revision.py",
>  
> line 672, in _iterate_revisions
> total_space.remove(rev.revision

Re: [sqlalchemy] alembic: downgrade raises KeyError

[Mike Bayer]

On 8/3/15 4:59 PM, Dirk Makowski wrote:

Hi all,

after upgrading alembic to 0.7.7 and SQLAlchemy to 1.0.8, when I try 
to downgrade one step from head, I get the below KeyError. The 
allegedly missing key is the revision ID of head. It is correctly 
stored in the alembic table, and the file and its predecessor are also 
present.


I'm not able to reproduce any issue.   I'm using Python 3 like you are 
and even turning on PYTHONHASHSEED=random, I can upgrade, downgrade, -1, 
+1, whatever, does everything perfectly.   This is of course with a 
plain vanilla env.py file as i don't have your special libraries available.


Are you able to reproduce your issue using a brand new Alembic 
environment and using just these version files ?  there is nothing 
unusual here and you have a perfectly linear ordering.   The error 
message indicates the internal organization of revisions as it places 
them into dependency order is failing but I can't see how that would 
happen here. Also, the 0.7 series has been out for months and if the 
revision logic were capable of failing on a perfectly straight line of 
files without even any branch or mergepoints, it seems like that would 
have been noticed.  I can simulate an internal condition that produces 
this stack trace, but I don't see how this condition can occur.


Also, what is the *exact* version of Python 3 you're using ?  Are there 
any .pyc files from the older Alembic 0.6.7 lying around in the virtual 
environment or is this a fresh environment ?











Other commands however do find that key:

{{{
$ alembic -c etc/Morrigan/development/alembic.ini heads --verbose
Rev: 32f69b44319 (head)
Parent: 1644537aa7
Path: 
/home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py


20150714_feat_mdv_mgmt
Revision ID: 32f69b44319
Revises: 1644537aa7
Create Date: 2015-07-20 14:52:40.227436
}}}

and

{{{
$ alembic -c etc/Morrigan/development/alembic.ini show  32f69b44319
Rev: 32f69b44319 (head)
Parent: 1644537aa7
Path: 
/home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py


20150714_feat_mdv_mgmt
Revision ID: 32f69b44319
Revises: 1644537aa7
Create Date: 2015-07-20 14:52:40.227436
}}}

But downgrading gives:

{{{
$ alembic -c etc/Morrigan/development/alembic.ini  downgrade  -1
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
Traceback (most recent call last):
  File "/home/dm/myprojects/atrial-py34-venv/bin/alembic", line 9, in 


load_entry_point('alembic==0.7.7', 'console_scripts', 'alembic')()
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 439, in main

CommandLine(prog=prog).main(argv=argv)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 433, in main

self.run_cmd(cfg, options)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 416, in run_cmd

**dict((k, getattr(options, k)) for k in kwarg)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/command.py", 
line 193, in downgrade

script.run_env()
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/script.py", 
line 390, in run_env

util.load_python_file(self.dir, 'env.py')
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/util.py", 
line 244, in load_python_file

module = load_module_py(module_id, path)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/compat.py", 
line 68, in load_module_py

module_id, path).load_module(module_id)
  File "", line 539, in _check_name_wrapper
  File "", line 1614, in load_module
  File "", line 596, in _load_module_shim
  File "", line 1220, in load
  File "", line 1200, in _load_unlocked
  File "", line 1129, in _exec
  File "", line 1471, in exec_module
  File "", line 321, in 
_call_with_frames_removed

  File "alembic/env.py", line 97, in 
run_migrations_online()
  File "alembic/env.py", line 92, in run_migrations_online
context.run_migrations()
  File "", line 7, in run_migrations
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/environment.py", 
line 738, in run_migrations

self.get_context().run_migrations(**kw)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/migration.py", 
line 302, in run_migrations

for step in self._migrations_fn(heads, self):
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/command.py", 
line 182, in downgrade

return script._downgrade_revs(revision, rev)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/script.py", 
line 315, in _downgrade_revs

current_rev, destination)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/revision.py", 
line 512, in iterate_rev

Re: [sqlalchemy] alembic: downgrade raises KeyError

[Mike Bayer]

On 8/3/15 6:51 PM, Mike Bayer wrote:


On 8/3/15 4:59 PM, Dirk Makowski wrote:

Hi all,

after upgrading alembic to 0.7.7 and SQLAlchemy to 1.0.8, when I try 
to downgrade one step from head, I get the below KeyError. The 
allegedly missing key is the revision ID of head. It is correctly 
stored in the alembic table, and the file and its predecessor are 
also present.


I'm not able to reproduce any issue.   I'm using Python 3 like you are 
and even turning on PYTHONHASHSEED=random, I can upgrade, downgrade, 
-1, +1, whatever, does everything perfectly.   This is of course with 
a plain vanilla env.py file as i don't have your special libraries 
available.


Are you able to reproduce your issue using a brand new Alembic 
environment and using just these version files ?  there is nothing 
unusual here and you have a perfectly linear ordering.   The error 
message indicates the internal organization of revisions as it places 
them into dependency order is failing but I can't see how that would 
happen here. Also, the 0.7 series has been out for months and if 
the revision logic were capable of failing on a perfectly straight 
line of files without even any branch or mergepoints, it seems like 
that would have been noticed.  I can simulate an internal condition 
that produces this stack trace, but I don't see how this condition can 
occur.


Also, what is the *exact* version of Python 3 you're using ?  Are 
there any .pyc files from the older Alembic 0.6.7 lying around in the 
virtual environment or is this a fresh environment ?


OK.   Here's what will cause this exactly:

sqlite> select * from alembic_version;
32f69b44319
32f69b44319

duplicate revisions in alembic_version.  Do you have that going on? If 
so, was that via manual intervention?  I'll add a protection for this 
that will prevent the issue but the alembic_version table should not 
have duplicate rows in it.

















Other commands however do find that key:

{{{
$ alembic -c etc/Morrigan/development/alembic.ini heads --verbose
Rev: 32f69b44319 (head)
Parent: 1644537aa7
Path: 
/home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py


20150714_feat_mdv_mgmt
Revision ID: 32f69b44319
Revises: 1644537aa7
Create Date: 2015-07-20 14:52:40.227436
}}}

and

{{{
$ alembic -c etc/Morrigan/development/alembic.ini show  32f69b44319
Rev: 32f69b44319 (head)
Parent: 1644537aa7
Path: 
/home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py


20150714_feat_mdv_mgmt
Revision ID: 32f69b44319
Revises: 1644537aa7
Create Date: 2015-07-20 14:52:40.227436
}}}

But downgrading gives:

{{{
$ alembic -c etc/Morrigan/development/alembic.ini  downgrade  -1
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
Traceback (most recent call last):
  File "/home/dm/myprojects/atrial-py34-venv/bin/alembic", line 9, in 


load_entry_point('alembic==0.7.7', 'console_scripts', 'alembic')()
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 439, in main

CommandLine(prog=prog).main(argv=argv)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 433, in main

self.run_cmd(cfg, options)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 416, in run_cmd

**dict((k, getattr(options, k)) for k in kwarg)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/command.py", 
line 193, in downgrade

script.run_env()
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/script.py", 
line 390, in run_env

util.load_python_file(self.dir, 'env.py')
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/util.py", 
line 244, in load_python_file

module = load_module_py(module_id, path)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/compat.py", 
line 68, in load_module_py

module_id, path).load_module(module_id)
  File "", line 539, in _check_name_wrapper
  File "", line 1614, in load_module
  File "", line 596, in _load_module_shim
  File "", line 1220, in load
  File "", line 1200, in _load_unlocked
  File "", line 1129, in _exec
  File "", line 1471, in exec_module
  File "", line 321, in 
_call_with_frames_removed

  File "alembic/env.py", line 97, in 
run_migrations_online()
  File "alembic/env.py", line 92, in run_migrations_online
context.run_migrations()
  File "", line 7, in run_migrations
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/environment.py", 
line 738, in run_migrations

self.get_context().run_migrations(**kw)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/migration.py", 
line 302, in run_migrations

for step in self._migrations_fn(heads, self):
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/pytho

Re: [sqlalchemy] alembic: downgrade raises KeyError

[Mike Bayer]

On 8/3/15 7:02 PM, Mike Bayer wrote:



On 8/3/15 6:51 PM, Mike Bayer wrote:


On 8/3/15 4:59 PM, Dirk Makowski wrote:

Hi all,

after upgrading alembic to 0.7.7 and SQLAlchemy to 1.0.8, when I try 
to downgrade one step from head, I get the below KeyError. The 
allegedly missing key is the revision ID of head. It is correctly 
stored in the alembic table, and the file and its predecessor are 
also present.


I'm not able to reproduce any issue.   I'm using Python 3 like you 
are and even turning on PYTHONHASHSEED=random, I can upgrade, 
downgrade, -1, +1, whatever, does everything perfectly.   This is of 
course with a plain vanilla env.py file as i don't have your special 
libraries available.


Are you able to reproduce your issue using a brand new Alembic 
environment and using just these version files ?  there is nothing 
unusual here and you have a perfectly linear ordering. The error 
message indicates the internal organization of revisions as it places 
them into dependency order is failing but I can't see how that would 
happen here. Also, the 0.7 series has been out for months and if 
the revision logic were capable of failing on a perfectly straight 
line of files without even any branch or mergepoints, it seems like 
that would have been noticed.  I can simulate an internal condition 
that produces this stack trace, but I don't see how this condition 
can occur.


Also, what is the *exact* version of Python 3 you're using ? Are 
there any .pyc files from the older Alembic 0.6.7 lying around in the 
virtual environment or is this a fresh environment ?


OK.   Here's what will cause this exactly:

sqlite> select * from alembic_version;
32f69b44319
32f69b44319

duplicate revisions in alembic_version.  Do you have that going on?  
If so, was that via manual intervention?  I'll add a protection for 
this that will prevent the issue but the alembic_version table should 
not have duplicate rows in it.


OK that is 
https://bitbucket.org/zzzeek/alembic/issues/314/dupe-entries-in-alembic_versions-can-break 
and that's fixed in the latest master, for 0.8.   I can backport to 0.7 
but I'm not sure there's going to be another 0.7 release.   For now you 
just need to remove the duplicate entries from your alembic_version table.





















Other commands however do find that key:

{{{
$ alembic -c etc/Morrigan/development/alembic.ini heads --verbose
Rev: 32f69b44319 (head)
Parent: 1644537aa7
Path: 
/home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py


20150714_feat_mdv_mgmt
Revision ID: 32f69b44319
Revises: 1644537aa7
Create Date: 2015-07-20 14:52:40.227436
}}}

and

{{{
$ alembic -c etc/Morrigan/development/alembic.ini show  32f69b44319
Rev: 32f69b44319 (head)
Parent: 1644537aa7
Path: 
/home/dm/myprojects/Foo/alembic/versions/32f69b44319_20150714_feat_mdv_mgmt.py


20150714_feat_mdv_mgmt
Revision ID: 32f69b44319
Revises: 1644537aa7
Create Date: 2015-07-20 14:52:40.227436
}}}

But downgrading gives:

{{{
$ alembic -c etc/Morrigan/development/alembic.ini  downgrade  -1
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
Traceback (most recent call last):
  File "/home/dm/myprojects/atrial-py34-venv/bin/alembic", line 9, 
in 

load_entry_point('alembic==0.7.7', 'console_scripts', 'alembic')()
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 439, in main

CommandLine(prog=prog).main(argv=argv)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 433, in main

self.run_cmd(cfg, options)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/config.py", 
line 416, in run_cmd

**dict((k, getattr(options, k)) for k in kwarg)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/command.py", 
line 193, in downgrade

script.run_env()
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/script.py", 
line 390, in run_env

util.load_python_file(self.dir, 'env.py')
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/util.py", 
line 244, in load_python_file

module = load_module_py(module_id, path)
  File 
"/home/dm/myprojects/atrial-py34-venv/lib/python3.4/site-packages/alembic/compat.py", 
line 68, in load_module_py

module_id, path).load_module(module_id)
  File "", line 539, in _check_name_wrapper
  File "", line 1614, in load_module
  File "", line 596, in _load_module_shim
  File "", line 1220, in load
  File "", line 1200, in _load_unlocked
  File "", line 1129, in _exec
  File "", line 1471, in exec_module
  File "", line 321, in 
_call_with_frames_removed

  File "alembic/env.py", line 97, in 
run_migrations_online()
  File "alembic/env.py", line 92, in run_migrations_online
context.run_migrations()
  File "", line 7, in run_migrations
  File 
"/home/dm/myprojects/atr