[sqlalchemy] Re: insertion into association table, giving integrityerror

[shrey . chauhan]

Thanks Mike for suggestions, was able to solve both the issues with slight 
changes in the db models

added lazy='dynamic' on both sides where back_populates is being used

-- 
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] [alembic] Batch migrations with sqlite and naming conventions

[Peter Erickson]

I'm sure that I'm missing a subtle point with batch migrations and 
naming conventions for SQLite databases, but I can't figure out what I'm 
doing wrong. After renaming a table, I'm using a batch migration to 
update a foreign key in a child table. However, after recreating the 
child table 2 CHECK constraints (being created from a Boolean) aren't 
being named according to the specified naming convention.


To ensure that this post doesn't get too long, here are the parts I 
think are relevant. I can add more if needed.


naming_convention = {
"ix": "ix_%(column_0_label)s",
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s"
}

class RulesetMatch(Base):
__tablename__ = 'ruleset_matches'
...
viewed = Column(Boolean(name='view_bool'), default=False)
hide = Column(Boolean(name='hide'), default=False)

# sqlite3 data-dev.sqlite .schema

CREATE TABLE ruleset_matches (
...
viewed BOOLEAN,
hide BOOLEAN,
CONSTRAINT pk_ruleset_matches PRIMARY KEY (id),
CONSTRAINT ck_ruleset_matches_view_bool CHECK (viewed IN (0, 1)),
CONSTRAINT ck_ruleset_matches_hide CHECK (hide IN (0, 1))
);

# migration script

def upgrade():
with op.batch_alter_table(
'ruleset_matches',
naming_convention=naming_convention,
reflect_args=[
Column('viewed', Boolean(name='view_bool')),
Column('hide', Boolean(name='hide'))]) as batch_op:
# drop_constraint
# create_foreign_key

# sqlite3 data-dev.sqlite .schema

CREATE TABLE IF NOT EXISTS "ruleset_matches" (
...
viewed BOOLEAN,
hide BOOLEAN,
CONSTRAINT pk_ruleset_matches PRIMARY KEY (id),
CONSTRAINT view_bool CHECK (viewed IN (0, 1)),
CONSTRAINT hide CHECK (hide IN (0, 1)),
...
);

As shown in the last schema, the CHECKS are named "view_bool" and "hide" 
unlike what it was previously "ck_ruleset_matches_view_bool" and 
"ck_ruleset_matches_hide." If I remove the "name" attribute in 
reflect_args or remove reflect_args all together, the CHECK constraints 
are unnamed. Removing the naming_convention doesn't seem to affect 
anything. What do I need to do to ensure that the constraints are named 
appropriately?


alembic 0.9.1
sqlalchemy 1.1.9

Thanks in advance.

--
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] postgresql tuple as function argument

[mike bayer]

I will say that the psycopg2 driver is not supporting this, however, it 
isn't parsing out the tuple. Using postgresql.ARRAY we get an answer 
like:


['{', '"', '(', 'x', ',', 'y', ')', '"', ',', '"', '(', 'x', ',', 'y', 
')', '"', '}']


that is, the string coming back is being interpreted as an array by 
SQLAlchemy, which here it is not.you might need to use some of the 
psycopg2 extension points to support this like 
http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.new_array_type.




On 04/11/2017 09:43 AM, mike bayer wrote:

can't reproduce (though the ARRAY(unicode) type is not what psycopg2
returns, and there seems to be a difference in behavior between
sqlalchemy.ARRAY and sqlalchemy.dialects.postgresql.ARRAY).

please provide a complete example based on the below test script and
stack traces

also my PG database doesn't know about the jsonb_object_agg function

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

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
x = Column(Unicode)
y = Column(Unicode)

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

s = Session(e)
s.add(A(x="x", y="y"))
s.commit()

row = s.query(func.array_agg(tuple_(A.x, A.y),
type_=ARRAY(Unicode))).scalar()
print row





On 04/11/2017 09:04 AM, Антонио Антуан wrote:

Hi
I want to build such query with sqlalchemy:
|
SELECT array_agg((column1,column2))fromtable
|

Using psql it works perfectly and returns such result:
|
{"(col1_row1_value, col2_row1_value)","(col1_row2_value,
col2_row2_value)"...}
|

I tried several forms of SQLA-query:

|>> from sqlalchemy.dialects.postgresql import ARRAY

from sqlalchemy.sql.elements import Tuple
...
func.array_agg(Tuple(Model.col1,Model.col2),type_=ARRAY(Unicode))...

...
TypeError: unhashable type: 'list'

... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Unicode,

as_tuple=True))...
...# returns value like this: ('{', '"', '(', 'c',...)

... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Tuple,

as_tuple=True))...
...
AttributeError: Neither 'Tuple' object nor 'Comparator' object has an
attribute 'dialect_impl'
|

At first, I wanted to use /`func.jsonb_object_agg(Model.col1,
Model.col2)`/, but it raises */"unhashable type: dict"/*
*/
/*
Could you point to solution?

--
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] postgresql tuple as function argument

[mike bayer]

can't reproduce (though the ARRAY(unicode) type is not what psycopg2 
returns, and there seems to be a difference in behavior between 
sqlalchemy.ARRAY and sqlalchemy.dialects.postgresql.ARRAY).


please provide a complete example based on the below test script and 
stack traces


also my PG database doesn't know about the jsonb_object_agg function

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

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
x = Column(Unicode)
y = Column(Unicode)

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

s = Session(e)
s.add(A(x="x", y="y"))
s.commit()

row = s.query(func.array_agg(tuple_(A.x, A.y), 
type_=ARRAY(Unicode))).scalar()

print row





On 04/11/2017 09:04 AM, Антонио Антуан wrote:

Hi
I want to build such query with sqlalchemy:
|
SELECT array_agg((column1,column2))fromtable
|

Using psql it works perfectly and returns such result:
|
{"(col1_row1_value, col2_row1_value)","(col1_row2_value,
col2_row2_value)"...}
|

I tried several forms of SQLA-query:

|>> from sqlalchemy.dialects.postgresql import ARRAY

from sqlalchemy.sql.elements import Tuple
... func.array_agg(Tuple(Model.col1,Model.col2),type_=ARRAY(Unicode))...

...
TypeError: unhashable type: 'list'

... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Unicode,

as_tuple=True))...
...# returns value like this: ('{', '"', '(', 'c',...)

... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Tuple,

as_tuple=True))...
...
AttributeError: Neither 'Tuple' object nor 'Comparator' object has an
attribute 'dialect_impl'
|

At first, I wanted to use /`func.jsonb_object_agg(Model.col1,
Model.col2)`/, but it raises */"unhashable type: dict"/*
*/
/*
Could you point to solution?

--
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] postgresql tuple as function argument

[Антонио Антуан]

Hi
I want to build such query with sqlalchemy:
SELECT array_agg((column1, column2)) from table

Using psql it works perfectly and returns such result:
{"(col1_row1_value, col2_row1_value)", "(col1_row2_value, col2_row2_value)"
...}

I tried several forms of SQLA-query:

>> from sqlalchemy.dialects.postgresql import ARRAY 
>> from sqlalchemy.sql.elements import Tuple
>> ... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Unicode
))...
...
TypeError: unhashable type: 'list'
>> ... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Unicode, 
as_tuple=True))...
...# returns value like this: ('{', '"', '(', 'c',...)
>> ... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Tuple, 
as_tuple=True))...
...
AttributeError: Neither 'Tuple' object nor 'Comparator' object has an 
attribute 'dialect_impl'

At first, I wanted to use *`func.jsonb_object_agg(Model.col1, Model.col2)`*, 
but it raises *"unhashable type: dict"*

Could you point to solution?

-- 
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] Re: insertion into association table, giving integrityerror

[mike bayer]

On 04/11/2017 02:37 AM, shrey.chau...@invicto.in wrote:

Yes Mike will do that, mostly its marshmallow model schema which is
creating the object, as I am using Flask + marshmallow for APIs,


p.groups=[] //when i try to empty it, i get this exception
*AssertionError: Collection was loaded during event handling.
 *//though the action is getting performed,
putting a try catch is solving it, but why is it coming?



that error happens means there is an append() or remove() event handler, 
or alternatively a @validates hook for that attribute (which is the same 
thing), which is running probably in the context of a backref being set; 
when the backref is being set, p.groups is not yet loaded.   when the 
handler runs, it is doing something that is causing p.groups to be 
lazy-loaded from the database, rather than just operating upon the 
incoming values.   this is confusing the attribute system with state it 
doesn't expect.   as always, stack trace will tell a lot more here.





though,
g.packages=[]this is working fine with no exception



Thanks for any kind of help, in advance

--
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] Re: insertion into association table, giving integrityerror

[shrey . chauhan]

Yes Mike will do that, mostly its marshmallow model schema which is 
creating the object, as I am using Flask + marshmallow for APIs,

apart from that I have another issue, which i am not able to uderstand:
I have a many-to-many relationship between 2 models, same as posted above, 
still ill put the models here

package model:
class Package(db.Model, BaseMixin):
__tablename__ =  'packages'
__bind_key__  =  'broker_db'
__repr_attrs__=  ["id","name","deletepkg"]
__track_attrs__   =  ["name","versions"]
#attributes
id= db.Column(db.Integer, primary_key=True, 
autoincrement=True)
name  = db.Column(db.String(100),  
index=True,unique=True, nullable=False)
deletepkg = db.Column(db.Boolean,  
index=True,nullable=False)
instances = db.relationship('Instance', 
cascade="all,delete",secondary=PACKAGE_INSTANCE_RELATION,back_populates="packages")
groups= db.relationship('Group',cascade="all,delete", 
secondary=PACKAGE_GROUP_RELATION,back_populates="packages")
versions  = db.relationship('Version',lazy='dynamic')
events= db.relationship('Event', 
backref=db.backref('package', uselist=False),lazy='dynamic')

group model:
PACKAGE_GROUP_RELATION = db.Table('package_group_relation',
db.Column('package_id', db.Integer, db.ForeignKey('packages.id')),
db.Column('groupt_id', db.Integer, db.ForeignKey('groupts.id')),
info={'bind_key': 'broker_db'}
)



class Group(db.Model,BaseMixin):
__tablename__ = 'groupts'
__repr_attrs__= ["id","name"]
__bind_key__  = 'broker_db'
__track_attrs__   = ["name","packages"]
id= db.Column(db.Integer, primary_key=True, 
autoincrement=True)
name  = db.Column(db.String(100),unique=True)
packages  = 
db.relationship("Package",cascade="all,delete",secondary=PACKAGE_GROUP_RELATION,back_populates="groups")
events= db.relationship('Event', 
backref=db.backref('group', uselist=False), lazy='dynamic')
def __init__(self, name):
self.name = name


in this model when i do something like this:
p = Package.query.filter_by(name='firefox').first()   //here p is 
Package object, and it has some groups mapped to it

p.groups=[] //when i try to empty it, i get this exception 
*AssertionError: Collection was loaded during event 
handling. *//though the action is getting 
performed, putting a try catch is solving it, but why is it coming?

though,
g.packages=[]this is working fine with no exception



Thanks for any kind of help, in advance

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