date:20170105

Re: [sqlalchemy] Load sqlalchemy orm model from dict

2017-01-05 Thread Daniel Kraus

Hi!

I don't even quote my old message since it's just confusing.
In my head the question made sense ;)

So I try again with a code example:

I have a class `User`:
#+BEGIN_SRC python
class User(Base):
id = Column(Integer, primary_key=True)
name = Column(String(64))
email = Column(String(64))

languages = relationship('Language', secondary='user_languages')
#+END_SRC

I already have a lot of users stored in my DB.
And I know that I have, for example, this user in my DB:
#+BEGIN_SRC python
user_dict = {
'id': 23,
'name': 'foo',
'email': 'foo@bar',
}
#+END_SRC

So I have all the attributes but the relations.

Now I want to make a sqlalchemy `User` instance
and kind of register it in sqlalchemy's system
so I can get the `languages` if needed.

#+BEGIN_SRC python
user = User(**user_dict)

# Now I can access the id, name email attributes
assert user.id == 23

# but since sqlalchemy thinks it's a new model it doesn't
# lazy load any relationships
assert len(user.languages) == 0
# I want here that the languages for the user with id 23 appear

# So I want that `user` is the same as when I would have done
user_from_db =  DBSession.query(User).get(23)
assert user == user_from_db
#+END_SRC


The use-case is that I have a big model with lots of complex
relationships but 90% of the time I don't need the data from those.
So I only want to cache the direct attributes plus what else I need
and then load those from the cache like above and be able to
use the sqlalchemy model like I would have queried it from the db.


Hope it makes a bit more sense now.
Sorry for the confusing first question and wasting your time.

Thanks,
  Daniel

-- 
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] Issue with Sqlalchemy and inserting array of jsonb

2017-01-05 Thread Brian Clark

Great thanks!!

On Thursday, January 5, 2017 at 7:06:26 AM UTC-8, Mike Bayer wrote:
>
> I will add a new section for this but we need to use a workaround 
> similar to 
>
> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#using-enum-with-array
>  
> (but simpler).  Just the CAST is needed, see below. 
>
> from sqlalchemy import * 
> from sqlalchemy.orm import * 
> from sqlalchemy.ext.declarative import declarative_base 
> from sqlalchemy.dialects.postgresql import ARRAY, JSONB 
>
> Base = declarative_base() 
>
>
> class CastingArray(ARRAY): 
>
>  def bind_expression(self, bindvalue): 
>  return cast(bindvalue, self) 
>
>
> class A(Base): 
>  __tablename__ = 'a' 
>  id = Column(Integer, primary_key=True) 
>  data = Column(CastingArray(JSONB)) 
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug') 
> Base.metadata.drop_all(e) 
> Base.metadata.create_all(e) 
>
> s = Session(e) 
>
> s.add(A(data=[{"foo": "bar"}, {"bat": "hoho"}])) 
>
> s.commit() 
>
> a1 = s.query(A).first() 
> print a1.data 
>
>
>
> On 01/05/2017 02:00 AM, Brian Clark wrote: 
> > So i'm trying to insert an array of jsonb values into my database but I 
> > can't seem to format it right, here's my code: 
> > 
> > updated_old_passwords = [] 
> > updated_old_passwords.append({"index": 1, "password": 
> hashed_password}) 
> > user.old_passwords = updated_old_passwords 
> > user.last_password_reset = datetime.datetime.utcnow() 
> > db.session.commit() 
> > 
> > And here's the error: 
> > 
> > ProgrammingError: (psycopg2.ProgrammingError) column "old_passwords" 
> > is of type jsonb[] but expression is of type text[] 
> > LINE 1: ...-01-05T06:18:24.992968'::timestamp, 
> > old_passwords=ARRAY['"\"... 
> >  ^ 
> > HINT:  You will need to rewrite or cast the expression. 
> >  [SQL: 'UPDATE users SET password=%(password)s, 
> > last_password_reset=%(last_password_reset)s, 
> > old_passwords=%(old_passwords)s WHERE users.id = %(users_id)s'] 
> > [parameters: {'users_id': 1, 'password': 
> > 
> '$6$rounds=656000$b.LVoVb7T0WNbT.n$l9uUb1a1qk2Z5ugfpI7B.3D02sUVqhES5VhM1TvwUnMd/iZZL3gn4/zExB47/ZQYPcTMRxO1iaL4/yjXda2.P1',
>  
>
> > 'last_password_reset': datetime.datetime(2017, 1, 5, 6, 18, 24, 992968), 
> > 'old_passwords': ['"\\"{\\"index\\": 1, \\"password\\": 
> > hashed_password}\\""']}] 
> > 
> > Any idea how I format my insert for this to work? 
> > 
> > Here's my db table 
> > 
> > from sqlalchemy.dialects.postgresql import JSONB, ARRAY 
> > 
> > class User(db.Model): 
> > __tablename__ = 'users' 
> > id = db.Column(db.Integer, primary_key = True) 
> > email = db.Column(db.String(255), index = True) 
> > password = db.Column(db.String(255)) 
> > last_password_reset = db.Column(db.DateTime()) 
> > old_passwords = db.Column(ARRAY(JSONB)) 
> > 
> > 
> > I also tried this: 
> > 
> > updated_old_passwords.append(cast('{"index": 1, "password": 
> > hashed_password}', JSONB)) 
> > 
> > but got the error 
> > 
> > StatementError: (exceptions.TypeError)  > object at 0x10f3ed150> is not JSON serializable [SQL: u'UPDATE users SET 
> > password=%(password)s, last_password_reset=%(last_password_reset)s, 
> > old_passwords=%(old_passwords)s WHERE users.id = %(users_id)s'] 
> > [parameters: [{'users_id': 1, 'password': 
> > 
> '$6$rounds=656000$WYOiWMAYDSag9QIX$YSDtZle6Bd7Kz.cy7ejWq1NqgME.xUPiDHfV31FKobGu2umxoX34.ZP2MrUDxyym0X4fyzZNEIO//yS6UTPoC.',
>  
>
> > 'last_password_reset': datetime.datetime(2017, 1, 5, 6, 26, 35, 610703), 
> > 'old_passwords': [ 0x10f3ed150>]}]] 
> > 
> > -- 
> > 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+...@googlegroups.com  
> > . 
> > To post to this group, send email to sqlal...@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

Re: [sqlalchemy] Load sqlalchemy orm model from dict

2017-01-05 Thread mike bayer




On 01/03/2017 03:10 AM, Daniel Kraus wrote:

Hi,

how can I load a sqlalchemy orm model from a dictionary?

Let's say I have a `User` model with attributes `id`, `name`, `email`
and a relationship `languages`.

I have a dict with `id`, `name`, `email` from users in my cache,
but not `languages` that's rarely used.

Is it possible to create a `User` model from a `user_dict` that behaves
like I would have queried it with dbsession.query(User).get(42)?


I will interpret this literally. This would be:

d = {"id": 1, "name": "x", "email": "y"}

u1 = User(**d)

then you'd persist u1 with session.add() / commit() and you're done.


What I mean in particular is that I want that an access to
`user.languages` creates a subquery and populates the attribute.


OK, so I can't imagine any connection between "I want a model from a 
dict" and "I want user.languages to create a subquery", so again lets 
take this one phrase literally.


"user.languages" would imply that "user" is an instance of User that's 
already in memory.  So "user.languages", being a relationship, will emit 
a lazy load to the database to load the list of languages.   It's not 
exactly a subquery, it's a single SELECT statement.





I now about the dogpile caching example which is similar but looks
way too much for what I want.


right, so, this is yet another phrase that appears to have no connection 
to the previous two phrases that also appear to have no connection :). 
So yes, your SO question is unlikely to get any answers because it is 
not apparent what you're looking for.



Here's a way that might make it clear - show some pseudocode.  That is:


# "I have a dict"
d = {"id": 1, "name": "x", "email": "y"}


# I want to create a user model, something something about attributes, 
subqueries


obj = 

session.add(obj)

query = session.query().filter()

assert result == 


These are just some examples of things you can try filling in.









PS, I asked the same question on stackoverflow:
http://stackoverflow.com/questions/41158307/load-sqlalchemy-orm-model-from-dict

Maybe I didn't make clear what I want.
Please tell me if I should rephrase my question.

Thanks in advance,
  Daniel



--
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] Issue with Sqlalchemy and inserting array of jsonb

2017-01-05 Thread mike bayer

I will add a new section for this but we need to use a workaround 
similar to 
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#using-enum-with-array 
(but simpler).  Just the CAST is needed, see below.


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import ARRAY, JSONB

Base = declarative_base()


class CastingArray(ARRAY):

def bind_expression(self, bindvalue):
return cast(bindvalue, self)


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(CastingArray(JSONB))

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

s = Session(e)

s.add(A(data=[{"foo": "bar"}, {"bat": "hoho"}]))

s.commit()

a1 = s.query(A).first()
print a1.data



On 01/05/2017 02:00 AM, Brian Clark wrote:

So i'm trying to insert an array of jsonb values into my database but I
can't seem to format it right, here's my code:

updated_old_passwords = []
updated_old_passwords.append({"index": 1, "password": hashed_password})
user.old_passwords = updated_old_passwords
user.last_password_reset = datetime.datetime.utcnow()
db.session.commit()

And here's the error:

ProgrammingError: (psycopg2.ProgrammingError) column "old_passwords"
is of type jsonb[] but expression is of type text[]
LINE 1: ...-01-05T06:18:24.992968'::timestamp,
old_passwords=ARRAY['"\"...
 ^
HINT:  You will need to rewrite or cast the expression.
 [SQL: 'UPDATE users SET password=%(password)s,
last_password_reset=%(last_password_reset)s,
old_passwords=%(old_passwords)s WHERE users.id = %(users_id)s']
[parameters: {'users_id': 1, 'password':
'$6$rounds=656000$b.LVoVb7T0WNbT.n$l9uUb1a1qk2Z5ugfpI7B.3D02sUVqhES5VhM1TvwUnMd/iZZL3gn4/zExB47/ZQYPcTMRxO1iaL4/yjXda2.P1',
'last_password_reset': datetime.datetime(2017, 1, 5, 6, 18, 24, 992968),
'old_passwords': ['"\\"{\\"index\\": 1, \\"password\\":
hashed_password}\\""']}]

Any idea how I format my insert for this to work?

Here's my db table

from sqlalchemy.dialects.postgresql import JSONB, ARRAY

class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key = True)
email = db.Column(db.String(255), index = True)
password = db.Column(db.String(255))
last_password_reset = db.Column(db.DateTime())
old_passwords = db.Column(ARRAY(JSONB))


I also tried this:

updated_old_passwords.append(cast('{"index": 1, "password":
hashed_password}', JSONB))

but got the error

StatementError: (exceptions.TypeError)  is not JSON serializable [SQL: u'UPDATE users SET
password=%(password)s, last_password_reset=%(last_password_reset)s,
old_passwords=%(old_passwords)s WHERE users.id = %(users_id)s']
[parameters: [{'users_id': 1, 'password':
'$6$rounds=656000$WYOiWMAYDSag9QIX$YSDtZle6Bd7Kz.cy7ejWq1NqgME.xUPiDHfV31FKobGu2umxoX34.ZP2MrUDxyym0X4fyzZNEIO//yS6UTPoC.',
'last_password_reset': datetime.datetime(2017, 1, 5, 6, 26, 35, 610703),
'old_passwords': []}]]

--
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] Enum in Array

2017-01-05 Thread Tim-Christian Mundt

Ok, since I’m using an enum it’s practically impossible to insert invalid data 
anyways. Thanks a lot. 

> Am 05.01.2017 um 15:47 schrieb mike bayer :
> 
> the enum type should be doing that check client side at this point, which is 
> usually good enough.  I would not have done the CHECK constraint feature of 
> enum/boolean by default if it were today it has caused enormous problems.
> 
> On 01/05/2017 09:01 AM, Tim-Christian Mundt wrote:
>> That works, thanks.
>> 
>> There is no (easy) way to CHECK the elements of the array?
>> 
>> 
>>> Am 05.01.2017 um 14:53 schrieb mike bayer :
>>> 
>>> you probably want to add create_constraint=False, see if that works
>>> 
>>> http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum.params.create_constraint
>>> 
>>> 
>>> 
>>> On 01/05/2017 01:34 AM, Tim-Christian Mundt wrote:
 Hi,
 
 I've been using an array of enums with postgres and SQLAlchemy
 successfully over the past year like so:
 
 |classMyModel(BaseModel):enum_field
 =Column(postgresql.ARRAY(EnumField(MyEnum,native_enum=False)))|
 
 The |EnumField| is from the sqlalchemy_enum34
  library, a small wrapper
 around the builtin enum that uses Python enums as Python representation
 instead of strings.
 
 Although the docs say
 ,
 array of enum is not supported, I guess it worked, because I chose
 'native_enum=False'. Recently I noticed that it doesn't work anymore, I
 think it's due to the upgrade from SQLA 1.0 to 1.1, but I'm not sure.
 
 The problem is, that it generates invalid DDL:
 
 |CREATE TABLE my_model (enum_field VARCHAR(5)[3]NOT NULL CHECK (contexts
 IN ('ONE','TWO','THREE')))|
 
 The error I get is:
 
 |ERROR: malformed array literal:"ONE"DETAIL: Arrayvalue must start
 with"{"ordimension information.|
 
 Any idea how I can get back my enum array?
 By the way: when it worked, no CHECK constraint was actually created,
 just an array of varying. I'm ok with that as long as I can use enums in
 my Python code (e.g. |query.filter(enum_field==MyEnum.ONE)|)
 
 Regards
 Tim
 
 --
 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.
>> 
> 
> -- 
> 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

Re: [sqlalchemy] Enum in Array

2017-01-05 Thread mike bayer

the enum type should be doing that check client side at this point, 
which is usually good enough.  I would not have done the CHECK 
constraint feature of enum/boolean by default if it were today it has 
caused enormous problems.


On 01/05/2017 09:01 AM, Tim-Christian Mundt wrote:

That works, thanks.

There is no (easy) way to CHECK the elements of the array?



Am 05.01.2017 um 14:53 schrieb mike bayer :

you probably want to add create_constraint=False, see if that works

http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum.params.create_constraint



On 01/05/2017 01:34 AM, Tim-Christian Mundt wrote:

Hi,

I've been using an array of enums with postgres and SQLAlchemy
successfully over the past year like so:

|classMyModel(BaseModel):enum_field
=Column(postgresql.ARRAY(EnumField(MyEnum,native_enum=False)))|

The |EnumField| is from the sqlalchemy_enum34
 library, a small wrapper
around the builtin enum that uses Python enums as Python representation
instead of strings.

Although the docs say
,
array of enum is not supported, I guess it worked, because I chose
'native_enum=False'. Recently I noticed that it doesn't work anymore, I
think it's due to the upgrade from SQLA 1.0 to 1.1, but I'm not sure.

The problem is, that it generates invalid DDL:

|CREATE TABLE my_model (enum_field VARCHAR(5)[3]NOT NULL CHECK (contexts
IN ('ONE','TWO','THREE')))|

The error I get is:

|ERROR: malformed array literal:"ONE"DETAIL: Arrayvalue must start
with"{"ordimension information.|

Any idea how I can get back my enum array?
By the way: when it worked, no CHECK constraint was actually created,
just an array of varying. I'm ok with that as long as I can use enums in
my Python code (e.g. |query.filter(enum_field==MyEnum.ONE)|)

Regards
Tim

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




--
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] Enum in Array

2017-01-05 Thread Tim-Christian Mundt

That works, thanks.

There is no (easy) way to CHECK the elements of the array?


> Am 05.01.2017 um 14:53 schrieb mike bayer :
> 
> you probably want to add create_constraint=False, see if that works
> 
> http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum.params.create_constraint
> 
> 
> 
> On 01/05/2017 01:34 AM, Tim-Christian Mundt wrote:
>> Hi,
>> 
>> I've been using an array of enums with postgres and SQLAlchemy
>> successfully over the past year like so:
>> 
>> |classMyModel(BaseModel):enum_field
>> =Column(postgresql.ARRAY(EnumField(MyEnum,native_enum=False)))|
>> 
>> The |EnumField| is from the sqlalchemy_enum34
>>  library, a small wrapper
>> around the builtin enum that uses Python enums as Python representation
>> instead of strings.
>> 
>> Although the docs say
>> ,
>> array of enum is not supported, I guess it worked, because I chose
>> 'native_enum=False'. Recently I noticed that it doesn't work anymore, I
>> think it's due to the upgrade from SQLA 1.0 to 1.1, but I'm not sure.
>> 
>> The problem is, that it generates invalid DDL:
>> 
>> |CREATE TABLE my_model (enum_field VARCHAR(5)[3]NOT NULL CHECK (contexts
>> IN ('ONE','TWO','THREE')))|
>> 
>> The error I get is:
>> 
>> |ERROR: malformed array literal:"ONE"DETAIL: Arrayvalue must start
>> with"{"ordimension information.|
>> 
>> Any idea how I can get back my enum array?
>> By the way: when it worked, no CHECK constraint was actually created,
>> just an array of varying. I'm ok with that as long as I can use enums in
>> my Python code (e.g. |query.filter(enum_field==MyEnum.ONE)|)
>> 
>> Regards
>> Tim
>> 
>> --
>> 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.

-- 
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] Enum in Array

2017-01-05 Thread mike bayer


you probably want to add create_constraint=False, see if that works

http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum.params.create_constraint



On 01/05/2017 01:34 AM, Tim-Christian Mundt wrote:

Hi,

I've been using an array of enums with postgres and SQLAlchemy
successfully over the past year like so:

|classMyModel(BaseModel):enum_field
=Column(postgresql.ARRAY(EnumField(MyEnum,native_enum=False)))|

The |EnumField| is from the sqlalchemy_enum34
 library, a small wrapper
around the builtin enum that uses Python enums as Python representation
instead of strings.

Although the docs say
,
array of enum is not supported, I guess it worked, because I chose
'native_enum=False'. Recently I noticed that it doesn't work anymore, I
think it's due to the upgrade from SQLA 1.0 to 1.1, but I'm not sure.

The problem is, that it generates invalid DDL:

|CREATE TABLE my_model (enum_field VARCHAR(5)[3]NOT NULL CHECK (contexts
IN ('ONE','TWO','THREE')))|

The error I get is:

|ERROR: malformed array literal:"ONE"DETAIL: Arrayvalue must start
with"{"ordimension information.|

Any idea how I can get back my enum array?
By the way: when it worked, no CHECK constraint was actually created,
just an array of varying. I'm ok with that as long as I can use enums in
my Python code (e.g. |query.filter(enum_field==MyEnum.ONE)|)

Regards
Tim

--
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] Please mention ilike more prominently in the documentation

2017-01-05 Thread mike bayer


Hi -

A github pull request would help to remind me.

- mike



On 01/04/2017 11:10 PM, Andrew M wrote:

Hi Mike,

Thanks for SQLAlchemy. As a relatively new user I wanted
case-insensitive querying but the only reference for this that I could
find in the documentation was custom comparators. I spent a lot of time
trying to get these to work when, it turns out, ilike suited my needs
perfectly.

Could you please mention ilike more prominently in the documentation
(and in the section on custom comparators include a note which lets
people know that ilike might be all they need)?

Cheers,
Andrew


--
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] Load sqlalchemy orm model from dict

Re: [sqlalchemy] Issue with Sqlalchemy and inserting array of jsonb

Re: [sqlalchemy] Load sqlalchemy orm model from dict

Re: [sqlalchemy] Issue with Sqlalchemy and inserting array of jsonb

Re: [sqlalchemy] Enum in Array

Re: [sqlalchemy] Enum in Array

Re: [sqlalchemy] Enum in Array

Re: [sqlalchemy] Enum in Array

Re: [sqlalchemy] Please mention ilike more prominently in the documentation

9 matches

Site Navigation

Mail list logo

Footer information