Re: [sqlalchemy] Returning Postgres JSON view as JSON string without serialization

[Stephan Hügel]

Thanks Mike, wrapping the view declaration with ()::TEXT worked perfectly, 
but good to know I can do it from SQLA at load time if need be.

On Saturday 30 October 2021 at 02:17:49 UTC+1 Mike Bayer wrote:

> oh also, if this is a view, much easier, just put the CAST to TEXT in your 
> CREATE VIEW statement.  that way you will definitely get strings back and 
> nothing json related will kick in client side.
>
>
>
> On Fri, Oct 29, 2021, at 9:16 PM, Mike Bayer wrote:
>
> psycopg2 driver (if that's what you're using) jumps in to do the JSON so 
> if you dont want json you need to cast as a string, like 
> cast(table.c.json_col, String).
>
> if you are using a Table with autoload you'd want to override this type 
> using the technique detailed at 
> https://docs.sqlalchemy.org/en/14/core/reflection.html#overriding-reflected-columns
>  
> .
>
> On Fri, Oct 29, 2021, at 7:52 PM, Stephan Hügel wrote:
>
> I’m querying a Postgres view which returns JSON (SELECT 
> json_build_object(…)) which I’ve declared as a view in my db:
>
> allinfra = Table("allinfra", db.metadata, autoload_with=db.engine)
> res = db.session.query(allinfra).scalar()
>
> But this gives me a Python dict, when what I want is the JSON string 
> returned by the view - serializing it with json.dumps() on the Python side 
> defeats the point (getting Postgres to do the heavy lifting). Apologies if 
> this question has an obvious answer, but I’ve found it impossible to search 
> for.
>
>
> -- 
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/5f84f3a9-aace-43ab-b7d7-1f1a143f1a53n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/5f84f3a9-aace-43ab-b7d7-1f1a143f1a53n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>
>

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/97d62485-ba5b-4b7b-9153-edb7e8e7bf2cn%40googlegroups.com.

[sqlalchemy] Returning Postgres JSON view as JSON string without serialization

[Stephan Hügel]

I’m querying a Postgres view which returns JSON (SELECT 
json_build_object(…)) which I’ve declared as a view in my db:

allinfra = Table("allinfra", db.metadata, autoload_with=db.engine)
res = db.session.query(allinfra).scalar()

But this gives me a Python dict, when what I want is the JSON string 
returned by the view - serializing it with json.dumps() on the Python side 
defeats the point (getting Postgres to do the heavy lifting). Apologies if 
this question has an obvious answer, but I’ve found it impossible to search 
for.

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/5f84f3a9-aace-43ab-b7d7-1f1a143f1a53n%40googlegroups.com.

[sqlalchemy] Calculate rank of single row using subquery

[Stephan Hügel]

I'm trying to calculate the rank of a particular (unique) row id by using a 
subquery:

I first calculate the total ranking for a table, Game (using 1.4.23):

sq = (
session.query(
Game.id,
Game.score,
func.rank().over(order_by=Game.score.desc()).label("rank"),
)
.filter(Game.complete == True)
.subquery()
)

Then filter by the row ID I want (gameid):

gamerank = (
session.query(
sq.c.id, sq.c.score, sq.c.rank
)
.filter(sq.c.id == gameid)
.limit(1)
.one()
)

Game.score is a Float column. Is this the most efficient way to do this, or 
am I over-complicating it?

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/e0373046-8379-499f-b584-37eb032636b9n%40googlegroups.com.

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Stephan Hügel]

On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote:
>
>
>
> On Sun, Jan 19, 2020, at 11:10 AM, Mike Bayer wrote:
>
>
>
> On Sun, Jan 19, 2020, at 10:54 AM, Stephan Hügel wrote:
>
> I'm trying to define a GenericFunction that calls a PostGIS 3.0 function (
> ST_AsGeoJson <https://postgis.net/docs/ST_AsGeoJSON.html>). The latest 
> version can be called in two different ways:
>
> SELECT ST_AsGeoJSON(t.geom) FROM foo as t WHERE t.id = 1;
> SELECT ST_AsGeoJSON(t.*) FROM foo as t WHERE t.id = 1;
>
> where the first example returns a GeoJSON Geometry, and the second returns 
> a GeoJSON Feature. I'm only interested in making use of the second type / 
> am happy to define the two variants separately.
>
> I should be able to subclass GenericFunction in order to do this, but I'm 
> missing two details:
>
> 1. What should the return type of my subclass be?
> 2. How can I specify that I want to pass the record / row / all columns, 
> as opposed to just the geom column to the underlying function? Ordinarily, 
> attempting something like 
>
> sess.query(func.MyGeojson(Foo)).all()
>
> Will give me an error:
>
> Object  is not legal as a SQL literal value
>
>
>
> by "return type" I think you're referring to the "type" attribute, so in 
> SQL if you were to make a database column that stores the result of the 
> ST_AsGeoJSON function, a quick google shows it as "text", so for SQLAlchemy 
> you could make the return type String, VARCHAR, TEXT, etc.
>
>
> OK well looking at  https://postgis.net/docs/ST_AsGeoJSON.html  it says 
> "Return the geometry as a GeoJSON "geometry" object, or the row as a 
> GeoJSON "feature" object", even though the signatures say "text". OK so 
> I see you want to change the type based on the arguments.  It's safe to do 
> that in your constructor as well;  "if this_looks_like_a_row(args):  
> self.type = Feature  else: self.type=Geometry".
>
>
>
>
> for the column expansion you override the constructor and inspect the 
> object(s) given and do what you want with them.SQLAlchemy has some 
> built-in systems for "how to expand an ORM model or Selectable into Table 
> columns" but only in 1.4 is there a new system for this that is organized 
> and predictable, so for the moment you'd want to roll it yourself:
>
> from sqlalchemy import inspect
>
> class MyFunction(GenericFunction):
> def __init__(self, *args, **kw):
> pass_args = []
> for arg in args:
> insp = inspect(arg)
> if hasattr(insp, "selectable"):
> pass_args.extend(selectable.c.)
>else:
> pass_args.append(arg)
> super(MyFunction, self).__init__(*pass_args, **kw)
>
> also note there's been a lot of call for Geo support lately (not sure if 
> you were the person that filed an issue re: SQL Server) but we are really 
> looking for help to get geoalchemy2 up to date and supporting more backends.
>
>
>
>
>
>
> --
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>
>
Thanks for the quick response Mike!

OK so far I've got: 
https://gist.github.com/urschrei/782989d0b3bee8e4208eb2bea945b65b. Requires 
PostGIS 3.0.x as an extension, and requires GeoAlchemy2.

On line 15, I fixed what I assume was a typo for selectable in your 
original suggestion. I still get the expected

Object  is not legal as a SQL literal value


error though. I don't follow your suggestion about


"if this_looks_like_a_row(args):  self.type = Feature  else: 
self.type=Geometry"


possibly because I'm not well-versed in SQLA's internals – is there an easy 
way to distinguish between a row and a column? That still leaves the issue 
of the "Geometry" vs "Feature" distinction in self.types: Geoalchemy2 
defines a "Geometry" type, but not a "Feature" type, because "Feature

[sqlalchemy] Define a GenericFunction that passes all columns

[Stephan Hügel]

I'm trying to define a GenericFunction that calls a PostGIS 3.0 function (
ST_AsGeoJson ). The latest 
version can be called in two different ways:

SELECT ST_AsGeoJSON(t.geom) FROM foo as t WHERE t.id = 1;
SELECT ST_AsGeoJSON(t.*) FROM foo as t WHERE t.id = 1;

where the first example returns a GeoJSON Geometry, and the second returns 
a GeoJSON Feature. I'm only interested in making use of the second type / 
am happy to define the two variants separately.

I should be able to subclass GenericFunction in order to do this, but I'm 
missing two details:

1. What should the return type of my subclass be?
2. How can I specify that I want to pass the record / row / all columns, as 
opposed to just the geom column to the underlying function? Ordinarily, 
attempting something like 

sess.query(func.MyGeojson(Foo)).all()

Will give me an error:

Object  is not legal as a SQL literal value



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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com.

Re: [sqlalchemy] Do I need a composite Secondary join for my hierarchical models?

[Stephan Hügel]

On Sunday, 26 November 2017 02:24:29 UTC, Mike Bayer wrote:
>
> If you wanted Widgets.themes.sections to refer to some kind of object 
> that had just one Content on it, you need a much more complicated 
> mapping; some kind of object called a ContentTheme and a 
> ContentSection, which relate to both a Theme and a Section as they are 
> related to a specific ontent object. 
>

This seems like it's not worth the trouble to figure out, and it would (I 
think) only be available when I supplied a Widgets instance, so from a 
query. Instead, I've implemented a simpler query as a method on Sections – 
it takes Widgets and Themes instances as arguments, and joins those to 
Content on the ID fields, along with the Sections instance – it's not as 
neat, but it's more practical and less complex. Thanks for your help with 
the rest of the structure, everything's working perfectly now.

-- 
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] Do I need a composite Secondary join for my hierarchical models?

[Stephan Hügel]

I'm trying to model a series of hierarchical relationships:

   - Widgets can have many Themes, which can be shared by many Widgets
   - Themes can have many Sections, which can be shared by many Themes:

Widgets - n:m - Themes - n:m - Sections

I'd also like to define a Content table with a single Unicode field, which 
depends on a *unique* combination of Widget, Theme and Section IDs, and 
which I can access as a scalar from Sections.

I've defined Widgets, Themes and Sections, and join tables to make these 
work:

# Theme-Section many-to-many join table Ts_Assoc = Table('ts_assoc', 
metadata, Column('themes_id', Integer, ForeignKey('themes.id')), 
Column('sections_id', Integer, ForeignKey('sections.id')), ) # Widget-Theme 
many-to-many join table Wt_Assoc = Table('wt_assoc', metadata, 
Column('widgets_id', Integer, ForeignKey('widgets.id')), 
Column('themes_id', Integer, ForeignKey('themes.id')) ) class 
Sections(Base): __tablename__ = "sections" id = Column(Integer, 
primary_key=True) section = Column(Unicode(100), index=False, unique=False) 
themes = relationship("Themes", secondary="ts_assoc", 
back_populates="sections") class Themes(Base): __tablename__ = "themes" id 
= Column(Integer, primary_key=True) theme = Column(Unicode(100), 
index=True, unique=True) widgets = relationship("Widgets", 
secondary="wt_assoc", back_populates="themes") sections = 
relationship("Sections", secondary="ts_assoc", back_populates="themes") 
class Widgets(Base): __tablename__ = "widgets" id = Column(Integer, 
primary_key=True) name = Column(Unicode(200), index=True, unique=True) 
themes = relationship("Themes", secondary=Wt_Assoc, 
back_populates="widgets")

This seems to work correctly; I can query like so for a given Widget:

# select Widget A only q = sess.query(Widgets) \ .join(Themes, 
Widgets.themes) \ .join(Sections, Themes.sections) \ .filter(Widgets.name 
== u'Widget A') \ .one()

And loop through its Themes, and for each Theme, loop through its sections.

The difficulty arises when I'm defining Content. So far, I've got:

class Content(Base): __tablename__ = "content" txt = Column(Unicode(100), 
primary_key=True) widgets_id = Column(Integer, ForeignKey('widgets.id'), 
nullable=False) themes_id = Column(Integer, ForeignKey('themes.id'), 
nullable=False) ForeignKeyConstraint(['widgets_id', 'themes_id'], 
['wt_assoc.widget_id', 'wt_assoc.themes_id']) sections_id = Column(Integer, 
ForeignKey('sections.id'))

I'm not sure that it's correct, and I'm not sure how to:

- Define the relationship to Sections such that the three foreign key 
values are correctly constraining it

- Modify the query above to take account of the new Content table

- Add Content records via Section, and how the __init__ method would look 
more generally.

In short, I'm tying myself in knots trying to figure out how to set up the 
join conditions between Content and Sections.

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

IndexError when attempting to drop a constraint on MySQL

[Stephan Hügel]

I'm attempting to drop a unique constraint on MySQL 5.6.20, 64-bit, Python 
2.7.6, SQLA 0.9.7, Alembic 0.6.6:

The table's very simple:

CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(75) CHARACTER SET utf8 NOT NULL,
  `foo` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

But when I attempt to drop the unique constraint on foo using op.
drop_constraint(u'uq_author_foo', 'author', type_='unique'), I get an error:

https://gist.github.com/urschrei/541fec05a3a82d71cbe9

Manually removing it, both on the command line and in Sequel Pro, works 
fine.

-- 
s



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy-alembic group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Alembic: adding a partial index to a Postgres table

[Stephan Hügel]

This will probably be an obvious answer, but I'm struggling to figure out 
how to add a partial index using an Alembic (0.5.0) revision.

from alembic import op
import sqlalchemy as sa


def upgrade():
op.create_index(
'geocoded',
'locations',
['coordinates'],
postgresql_where=locations.coordinates != Null)


def downgrade():
op.drop_index(geocoded)

Isn't working – is it valid to pass that keyword to create_index, and if 
so, what string should I be passing?

-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Alembic: adding a partial index to a Postgres table

[Stephan Hügel]

Yep, that worked. Thanks!

On Wednesday, 24 April 2013 15:37:26 UTC+1, Michael Bayer wrote:

 actually, the issue is that it requires a SQL expression, this should be 
 improved, but for now do this:

 from sqlalchemy.sql import text

 op.create_index(
 'geocoded',
 'locations',
 ['coordinates'],
 postgresql_where=text(locations.coordinates != Null))





 On Apr 24, 2013, at 10:35 AM, Michael Bayer 
 mik...@zzzcomputing.comjavascript: 
 wrote:

 are you getting a stack trace (I am) ?  that would be a bug which needs 
 repair.


 On Apr 24, 2013, at 10:24 AM, Stephan Hügel ursc...@gmail.comjavascript: 
 wrote:

 This will probably be an obvious answer, but I'm struggling to figure out 
 how to add a partial index using an Alembic (0.5.0) revision.

 from alembic import op
 import sqlalchemy as sa


 def upgrade():
 op.create_index(
 'geocoded',
 'locations',
 ['coordinates'],
 postgresql_where=locations.coordinates != Null)


 def downgrade():
 op.drop_index(geocoded)

 Isn't working – is it valid to pass that keyword to create_index, and if 
 so, what string should I be passing?

 -- 
 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 javascript:.
 To post to this group, send email to sqlal...@googlegroups.comjavascript:
 .
 Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
 For more options, visit https://groups.google.com/groups/opt_out.
  
  





-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Creating classes and many-to-many relations dynamically

[Stephan Hügel]

On Thursday, 26 July 2012 00:45:09 UTC+1, Michael Bayer wrote:


 my_list_of_classes = make_classes(my_list_of_1000_names)

 you've got this use case where you have 1000 names to make into classes.   
 Do you want to use them by name in a dynamic way ?  OK, put them in a dict:

 my_dict = dict((c.__name__, c) for c in my_list_of_classes)

 session.query(my_dict[SomeName]).all()

 do you want to import them from a module ?  OK, then put them in the 
 module:

 from myapp import somemodule
 for cls in my_list_of_classes:
 setattr(somemodule, cls.__name__, cls)

 # later

 from myapp.somemodule import Cls1234
 session.query(Cls1234).all()



Yes, this is exactly what I was looking for. Thanks, and sorry about the 
confusion. 

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

[sqlalchemy] Creating classes and many-to-many relations dynamically

[Stephan Hügel]

I need to create 20 identical (in structure) tables, each of which will 
have a many-to-many relationship with a particular table (Table_A).

I've thought a bit about this, and there doesn't seem to be a better way to 
structure the setup; it's a canonical reference (Table_A), each entry of 
which can have multiple overlapping entries in a particular book (each of 
the 20 tables represents references in a particular book).
Is there a sensible, compact way for me to instantiate the 20 classes and 
association tables? Their structure is extremely simple; just a primary key 
column and a string column.

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

Re: [sqlalchemy] Creating classes and many-to-many relations dynamically

[Stephan Hügel]

On Wednesday, 25 July 2012 16:44:16 UTC+1, Michael Bayer wrote:

just build a function:

 def create_my_class(x, y, z, ...):
 class MyClass(Base):
 __tablename__ = '...'
 # ...

MyClass.__name__ = 'SomeName%s%s' % (q, p)
return MyClass


 On Jul 25, 2012, at 5:49 AM, Stephan Hügel wrote:

 I need to create 20 identical (in structure) tables, each of which will 
 have a many-to-many relationship with a particular table (Table_A).

 I've thought a bit about this, and there doesn't seem to be a better way 
 to structure the setup; it's a canonical reference (Table_A), each entry of 
 which can have multiple overlapping entries in a particular book (each of 
 the 20 tables represents references in a particular book).
 Is there a sensible, compact way for me to instantiate the 20 classes and 
 association tables? Their structure is extremely simple; just a primary key 
 column and a string column.

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



 OK, I've done the following

def create_signlist(sl):
class SignList(db.Model):

__tablename__ = listname.lower()
 
id = db.Column(id, db.Integer(), primary_key=True)
reference = db.Column(db.String(50), nullable=False, unique=True)

def __init__(self, reference):
self.reference = reference

SignList.__name__ = listname
return SignList

signlists = ['lka', 'kal']
for s in signlists:
create_signlists(s)

But this gives me a warning:

SAWarning: The classname 'SignList' is already in the registry of this 
declarative base, mapped to class 'glyph.models.lka'
  _as_declarative(cls, classname, cls.__dict__)

And e.g. lka.query.all() fails, presumably because there are no instances 
of it yet.

Sorry, I'm missing something completely obvious.

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

Re: [sqlalchemy] Creating classes and many-to-many relations dynamically

[Stephan Hügel]

On Wednesday, 25 July 2012 22:32:34 UTC+1, Michael Bayer wrote:


 On Jul 25, 2012, at 4:29 PM, Stephan Hügel wrote:


  OK, I've done the following

 def create_signlist(sl):
 class SignList(db.Model):

 __tablename__ = listname.lower()
  
 id = db.Column(id, db.Integer(), primary_key=True)
 reference = db.Column(db.String(50), nullable=False, unique=True)

 def __init__(self, reference):
 self.reference = reference

 SignList.__name__ = listname
 return SignList

 signlists = ['lka', 'kal']
 for s in signlists:
 create_signlists(s)

 But this gives me a warning:

 SAWarning: The classname 'SignList' is already in the registry of this 
 declarative base, mapped to class 'glyph.models.lka'
   _as_declarative(cls, classname, cls.__dict__)


 that's only a warning, you can ignore it.   It means if you have some 
 class which makes a relationship() to SignList using just the name, you 
 won't get this new class.

 if you want to make a new class that has a new name from the start, use 
 type():

 def __init__(self, reference):
 self.reference = reference

 d = dict(
 __tablename__ = listname.lower()

 id = db.Column(id, db.Integer(), primary_key=True)
 reference = db.Column(db.String(50), nullable=False, unique=True)

 __init__ = __init__
 )
 my_class = type(listname, (db.Model,), d)



Doing this creates classes (e.g. class 'flask_sqlalchemy.lak') , but is 
there some way for me to instantiate them without resorting to some eval magic 
in my for loop? What I'd like to end up with is programmatically created 
instances (myapp.models.lak) that I can use the same way as my normally 
instantiated classes.

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

Re: [sqlalchemy] Creating classes and many-to-many relations dynamically

[Stephan Hügel]

On Wednesday, 25 July 2012 23:52:18 UTC+1, Michael Bayer wrote:


 instantiate, as in, instance of the new class?  my_class above is a 
 regular Python class,  just instantiate - myclass(x, y, z).   


 What I'd like to end up with is programmatically created instances (
 myapp.models.lak) that I can use the same way as my normally 
 instantiated classes.


 that's what type() gives you.  Its the exact same thing as using class 
 Foo().



Hmm. I'm explaining this badly.

from myapp import db


class Foo(db.Model):
__tablename__ = 'foo'
id = db.Column(db.Integer, primary_key=True)
othercol = db.Column(db.String(50), nullable=False, unique=True)

# I can import this, and immediately run a query like Foo.query.all()
# no manual instantiation required


def create_signlist(listname):
def __init__(self, reference=None):
self.reference = reference
d = dict(
__tablename__ = listname.lower(),
__table_args__ = {'mysql_engine': 'InnoDB'},
__mapper_args__ = {'always_refresh': True},
id = db.Column(id, db.Integer(), primary_key=True),
reference = db.Column(db.String(50), nullable=False, unique=True),
__init__ = __init__
)
cls = type(listname, (db.Model,), d)
return cls()

various = ['Abc', 'Def', 'Ghi']
for v in various:
v = create_signlist(v)

# what do I have to do now to end up with instances Abc, Def, Ghi that I 
can query within my app in the same way as Foo?
 

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

Re: [sqlalchemy] KeyError when adding objects using an association proxy

[Stephan Hügel]

 Mike,

 no-dep python file and stack trace are at 
 https://gist.github.com/174a76acf27ffef1b66d 
 In order to reproduce the issue, I query for two objects:
 tab = Tablet.query.first()
 cor = Correspondent.query.first()

 If I do tab.correspondents or cor.tablets I just get back an empty list 
 in both cases (as expected)
 If I do tab.correspondents.append(cor), I get the KeyError you see in 
 line 3 of the stack trace.

 -- 
 steph


As has been pointed out here http://stackoverflow.com/a/6291/416626, 
I wasn't using the creator lambda while attempting to create the objects in 
the association object. Problem would have been solved by RTFM, as per 
usual, and everything's working as expected now.

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

Re: [sqlalchemy] Combine attributes from two objects in parent object

[Stephan Hügel]

On Saturday, 26 May 2012 00:36:54 UTC+1, Michael Bayer wrote:


 usually @property achieves this:

 class Correspondent(Base):
 @property
 def email_address(self):
 return self.corr_1.email_address if self.corr_1 else 
 self.corr_2.email_address

 This worked perfectly, but I'm not sure how to query the email_address 
property with the correct join. I've created one-to-many relationships to 
Corr_1 and Corr_2, and If I do e.g. 
Correspondent.query.join(Corr_1).filter(Correspondent.email_address==f...@bar.com).all(),
 
it works fine. However, if I omit the join clause, I get an error:

Correspondent.query.filter(Correspondent.email_address==f...@bar.com).all()
ArgumentError: filter() argument must be of type 
sqlalchemy.sql.ClauseElement or string

Is there a way to perform this query in a single step?

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

Re: [sqlalchemy] KeyError when adding objects using an association proxy

[Stephan Hügel]

On Tuesday, 19 June 2012 00:14:57 UTC+1, Michael Bayer wrote:

 At the very least I would need a full stack trace, exactly how you are 
 appending objects, and preferably the means to actually run it.   Nothing 
 is obviously wrong.Can you distill your failure into a no-dependencies 
 .py file?  For example I don't have GlyphMixin here, the formatting for 
 __init__ seems to be off in this paste, etc, I don't see how you're 
 manipulating the collection, etc.   No engine is needed since this is just 
 an association proxy creation issue.


Mike,

no-dep python file and stack trace are at 
https://gist.github.com/174a76acf27ffef1b66d 
In order to reproduce the issue, I query for two objects:
tab = Tablet.query.first()
cor = Correspondent.query.first()

If I do tab.correspondents or cor.tablets I just get back an empty list in 
both cases (as expected)
If I do tab.correspondents.append(cor), I get the KeyError you see in line 
3 of the stack trace.

-- 
steph

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

[sqlalchemy] KeyError when adding objects using an association proxy

[Stephan Hügel]

I have two objects, which I'm joining using an association proxy:
(doing this using Flask-sqlalchemy, so Base, engine etc are implicit)

class Correspondent(db.Model, GlyphMixin):
# PK column and tablename etc. come from the mixin
name = db.Column(db.String(100), nullable=False, unique=True)
# association proxy
tablets = association_proxy('correspondent_tablets', 'tablet')

def __init__(self, name, tablets=None):
self.name = name
if tablets:
self.tablets = tablets


class Tablet(db.Model, GlyphMixin):
# PK column and tablename etc. come from the mixin
area = db.Column(db.String(100), nullable=False, unique=True)
# association proxy
correspondents = association_proxy('tablet_correspondents', 'correspondent')

def __init__(self, area, correspondents=None):
self.area = area
if correspondents:
self.correspondents = correspondents


class Tablet_Correspondent(db.Model):

__tablename__ = tablet_correspondent
tablet_id = db.Column(tablet_id,
db.Integer(), db.ForeignKey(tablet.id), primary_key=True)
correspondent_id = db.Column(correspondent_id,
db.Integer(), db.ForeignKey(correspondent.id), primary_key=True)
# relations
tablet = db.relationship(
Tablet,
backref=tablet_correspondents,
cascade=all, delete-orphan,
single_parent=True)
correspondent = db.relationship(
Correspondent,
backref=correspondent_tablets,
cascade=all, delete-orphan,
single_parent=True)

def __init__(self, tablet=None, correspondent=None):
self.tablet = tablet

self.correspondent = correspondent


However, I get a KeyError when I try to append an object (new or existing) 
to the Tablet.correspondents or Correspondent.tablets collection. I assume 
I've done something wrong when implementing the relationships and backrefs 
on the Tablet_Correspondent model – I was following the association proxy 
examples, but wanted it to be more symmetrical than the User.keywords 
example, which is where I'm going wrong, I suspect.


 

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

[sqlalchemy] Combine attributes from two objects in parent object

[Stephan Hügel]

I'd like to represent a Correspondent object, which can refer to two 
separate objects

Corr_1
- id
- email_address

Corr_2
- id
- email_address

Correspondent
- id
- corr_1.id
- corr_2.id

But I have two problems:
I'm using MySQL, so I can't use a check constraint to ensure that only one 
of the corr_*.id fields can be populated. Is it it a bad idea to enforce 
the constraint at the SQLAlchemy level, by doing 
if all(corr1.id, corr2.id) or not any(corr_1.id, corr2.id):
raise … 
in my __init__?

My second problem relates to then being able to retrieve the related 
.email_address attribute. My naïve relationship above obviously won't work 
for that. Any pointers would be greatly appreciated.

-- 
steph

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