Re: [sqlalchemy] testing/raising errors for queries that do not filter against themselves

[Mike Bayer]

On Fri, Mar 29, 2019 at 4:39 PM Jonathan Vanasco  wrote:
>
> I was going batty trying to figure out why a unit test kept passing, while 
> the functionality was clearly broken.
>
> I eventually realized the problem was a `filter` that didn't apply to the 
> actual query.
>
> The code was essentially this:
>
> result = s.query(Foo).filter(Bar.id = 1)
>
> but it was difficult to tell because the classes looked more like 
> FooVariantApples and FooVariantAardvarks
>
> Is there a way I can configure SqlAlchemy to raise exceptions to queries if 
> the filters apply to a class that is not queried or joined?

I'm assuming you mean a double equals sign there.   Typically, you
just need to have a few more rows in your table during unit tests to
ensure that unwanted rows are not matched.

However!   if you'd like to go crazy, since I had a user who refused
to stop complaining about this some years ago, I wrote them a
cartesian product linter.   Here it is!
https://gist.github.com/zzzeek/c514e2c874fca54df80fc55b680e51c5

I think I was assuming that user would come back with a great success
story and this could be either on the wiki or maybe even an ext or a
flag you can turn on in the compiler, but I guess he was so satisfied
that he never came back and I forgot all about it.So you can only
use this on the condition that you report back on its general
usability and if it's going to give me grief if I unleash it in the
docs.



>
> --
> 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] Proper labels in joins when there are multiple relationships with the same table.

[Mike Bayer]

On Fri, Mar 29, 2019 at 4:59 PM  wrote:
>
> This is definitely along the right track but it conflicts a little bit with 
> the recommended pandas api, for example:
> query = session.query(Account)
> return pd.read_sql(query.statement, query.session.bind)
>
> I can still follow your method and add each row to a dataframe instead but 
> its not as efficient as the above and speed is a concern in this case. And 
> you are right I would like to avoid working with the stringified query. Is 
> there a solution in this case? Thanks much for the help.

OK then you would not use eager loading, you want joins and columns:

from sqlalchemy.orm import aliased

customer = aliased(User)
reporter = aliased(User)

q = session.query(
Account.x.label("x_name"),
   Account.y.label("y_name"),
  customer.p.label("p_name"),
  reporter.q.label("r_name")).select_from(Account).join(customer,
Account.customer).join(reporter, Account.reporter)

pd.read_sql(q.statement, query.session.bind)

An example of using aliases with ORM joins is in the ORM tutorial at:
https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-aliases

Querying for individual columns using custom label names is also in
the tutorial, at:
https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#querying

hope this helps.




>
> On Friday, March 29, 2019 at 4:33:39 PM UTC-4, Mike Bayer wrote:
>>
>> On Fri, Mar 29, 2019 at 2:55 PM  wrote:
>> >
>> > I have a model, Account, with two foreign keys / relationships to another 
>> > model, Users.
>> >
>> > class Account(object):
>> >  @declared_attr
>> >  def customer_id(cls):
>> >   return Column(ForeignKey(User.id))
>> >  @declared_attr
>> >  def customer(cls):
>> >  return relationship(User, lazy='joined', 
>> > foreign_keys=cls.customer_id)
>> >
>> >  @declared_attr
>> >  def reporter_id(cls):
>> >   return Column(ForeignKey(User.id))
>> >  @declared_attr
>> >  def reporter(cls):
>> >  return relationship(User, lazy='joined', 
>> > foreign_keys=cls.reporter_id)
>> >
>> > session.query(Account) gives me the correct join logic for eager loading. 
>> > The issue comes when I want to load this data into a pandas dataframe. The 
>> > joined columns show up labeled as users_1_colname and users_2_colname 
>> > which makes it unclear which came from reporter and which came from 
>> > customer. I know in a one off query I can use aliases but how can I have a 
>> > certain alias dedicated to a relationship? I don't want to manually 
>> > generate the query and I don't want to change the column names in pandas. 
>> > I want users_1 to always be labeled reporter and users_2 to always be 
>> > labeled customer when I query Account. Is this possible?
>>
>> When using relationships there is no exposure of any kind of "labels"
>> to the end user, unless you are taking the stringified version of an
>> ORM query using str().   There's no need to do that, as Query can
>> execute the results for you directly where it then returns them as ORM
>> objects, without you ever having to deal with any labels:
>>
>> for acc in session.query(Account):
>> row = {"customer": acc.customer.name, "reporter":
>> acc.reporter.name}  # or whatever fields you want, this assumes there
>> is a "User.name" field
>> my_dataframe.add_row(row)  # or whatever pandas API is here
>>
>> the names you use with the results of an ORM query that uses eager
>> loading are in terms of the attribute names you put on the objects.
>>
>> There's a lot of other ways to do this, which can be more automatic,
>> but that's the simplest, feel free to share more details if this is
>> not sufficient.
>>
>>
>>
>> >
>> > --
>> > 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 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 

Re: [sqlalchemy] Re: Documentation options

[Rich Shepard]

On Sat, 30 Mar 2019, Cameron Simpson wrote:


If it is of use I have this shell function:

  open_dash () {
  open "dash://$*"
  }

and this alias:

alias //=open_dash

so that I can go "// search terms here" from the shell command line.  Avoids 
some painful touchpad/mouse mucking around.


Cameron,

Zeal is a command line tool; a bash shell script is probably not necessary.
Most of my work is in virtual terminals and I use the trackball to switch
among them. There are some GUI applications that are highly productive and
they all take keyboard commands as well as those from the pointing thingie.

Thanks for the suggestion,

Rich

--
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: Documentation options

[Rich Shepard]

On Fri, 29 Mar 2019, Rich Shepard wrote:


This looks like the ideal solution. I'll install Dash and use it.


Actually, I won't because it's an Apple product for their macOS and iOS.
However, slackbuilds.org has a package called Zeal (a simple offline
documentation browser inspired by Dash) that works with linux and that Other
OS.

Regards,

Rich

--
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] Proper labels in joins when there are multiple relationships with the same table.

[ghlstdios]

This is definitely along the right track but it conflicts a little bit with 
the recommended pandas api, for example:
query = session.query(Account)
return pd.read_sql(query.statement, query.session.bind)

I can still follow your method and add each row to a dataframe instead but 
its not as efficient as the above and speed is a concern in this case. And 
you are right I would like to avoid working with the stringified query. Is 
there a solution in this case? Thanks much for the help.

On Friday, March 29, 2019 at 4:33:39 PM UTC-4, Mike Bayer wrote:
>
> On Fri, Mar 29, 2019 at 2:55 PM > wrote: 
> > 
> > I have a model, Account, with two foreign keys / relationships to 
> another model, Users. 
> > 
> > class Account(object): 
> >  @declared_attr 
> >  def customer_id(cls): 
> >   return Column(ForeignKey(User.id)) 
> >  @declared_attr 
> >  def customer(cls): 
> >  return relationship(User, lazy='joined', 
> foreign_keys=cls.customer_id) 
> > 
> >  @declared_attr 
> >  def reporter_id(cls): 
> >   return Column(ForeignKey(User.id)) 
> >  @declared_attr 
> >  def reporter(cls): 
> >  return relationship(User, lazy='joined', 
> foreign_keys=cls.reporter_id) 
> > 
> > session.query(Account) gives me the correct join logic for eager 
> loading. The issue comes when I want to load this data into a pandas 
> dataframe. The joined columns show up labeled as users_1_colname and 
> users_2_colname which makes it unclear which came from reporter and which 
> came from customer. I know in a one off query I can use aliases but how can 
> I have a certain alias dedicated to a relationship? I don't want to 
> manually generate the query and I don't want to change the column names in 
> pandas. I want users_1 to always be labeled reporter and users_2 to always 
> be labeled customer when I query Account. Is this possible? 
>
> When using relationships there is no exposure of any kind of "labels" 
> to the end user, unless you are taking the stringified version of an 
> ORM query using str().   There's no need to do that, as Query can 
> execute the results for you directly where it then returns them as ORM 
> objects, without you ever having to deal with any labels: 
>
> for acc in session.query(Account): 
> row = {"customer": acc.customer.name, "reporter": 
> acc.reporter.name}  # or whatever fields you want, this assumes there 
> is a "User.name" field 
> my_dataframe.add_row(row)  # or whatever pandas API is here 
>
> the names you use with the results of an ORM query that uses eager 
> loading are in terms of the attribute names you put on the objects. 
>
> There's a lot of other ways to do this, which can be more automatic, 
> but that's the simplest, feel free to share more details if this is 
> not sufficient. 
>
>
>
> > 
> > -- 
> > 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 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 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] testing/raising errors for queries that do not filter against themselves

[Jonathan Vanasco]

I was going batty trying to figure out why a unit test kept passing, while 
the functionality was clearly broken.

I eventually realized the problem was a `filter` that didn't apply to the 
actual query.

The code was essentially this:

result = s.query(Foo).filter(Bar.id = 1)

but it was difficult to tell because the classes looked more like 
FooVariantApples and FooVariantAardvarks

Is there a way I can configure SqlAlchemy to raise exceptions to queries if 
the filters apply to a class that is not queried or joined?

-- 
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] Proper labels in joins when there are multiple relationships with the same table.

[Mike Bayer]

On Fri, Mar 29, 2019 at 2:55 PM  wrote:
>
> I have a model, Account, with two foreign keys / relationships to another 
> model, Users.
>
> class Account(object):
>  @declared_attr
>  def customer_id(cls):
>   return Column(ForeignKey(User.id))
>  @declared_attr
>  def customer(cls):
>  return relationship(User, lazy='joined', 
> foreign_keys=cls.customer_id)
>
>  @declared_attr
>  def reporter_id(cls):
>   return Column(ForeignKey(User.id))
>  @declared_attr
>  def reporter(cls):
>  return relationship(User, lazy='joined', 
> foreign_keys=cls.reporter_id)
>
> session.query(Account) gives me the correct join logic for eager loading. The 
> issue comes when I want to load this data into a pandas dataframe. The joined 
> columns show up labeled as users_1_colname and users_2_colname which makes it 
> unclear which came from reporter and which came from customer. I know in a 
> one off query I can use aliases but how can I have a certain alias dedicated 
> to a relationship? I don't want to manually generate the query and I don't 
> want to change the column names in pandas. I want users_1 to always be 
> labeled reporter and users_2 to always be labeled customer when I query 
> Account. Is this possible?

When using relationships there is no exposure of any kind of "labels"
to the end user, unless you are taking the stringified version of an
ORM query using str().   There's no need to do that, as Query can
execute the results for you directly where it then returns them as ORM
objects, without you ever having to deal with any labels:

for acc in session.query(Account):
row = {"customer": acc.customer.name, "reporter":
acc.reporter.name}  # or whatever fields you want, this assumes there
is a "User.name" field
my_dataframe.add_row(row)  # or whatever pandas API is here

the names you use with the results of an ORM query that uses eager
loading are in terms of the attribute names you put on the objects.

There's a lot of other ways to do this, which can be more automatic,
but that's the simplest, feel free to share more details if this is
not sufficient.



>
> --
> 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: Using operators with multiple InstrumentedAttribute instances

[Mike Bayer]

On Fri, Mar 29, 2019 at 2:31 PM Ian Miller  wrote:
>
> The code that makes up the query builder I've been working on is pretty 
> extensive, so I'll go through the high-level basics.
>
> I've set up a `_base` method that augments the SQLALchemy Base model. Note 
> the column details retrived in `get_column_and_json_key_from_sql_name` class 
> method:
>
> class _base:
> """
> This class augments the default SQLAlchemy Base model
> """
>
> @classmethod
> def get_column_and_json_key_from_sql_name(cls, name):
> """
> Returns the column and, if applicable, the JSON top-level key from 
> the JSON dict.
>
> :param name: Name of field.
>
> :return:
> """
> assert type(name) == str
>
> json_key = None
> col_names = name.split(COLUMN_NAME_DELIMITER)
> if len(col_names) == 2:
> name, json_key = col_names
>
> try:
> col = cls.__table__.c[name]
> except KeyError:
> log.error("Invalid column name: %s", name)
> return None
>
> return (getattr(cls, col.name), json_key)
>
>
> We then have a `_build_column` method that essentially is responsible for 
> retrieving the details necessary to construct the column for the select 
> statement necessary for constructing the SQLAlchemy ORM query:
>
> def _build_column(self):
> field_name = self.db.get("column")
> model = self._object.get("model")
>
> column_type, column_key = self.db.get("type"), self.db.get("key")
> select_column, json_key = model.get_column_and_json_key_from_sql_name(
> field_name
> )
> select_column = self._construct_json_select_field(
> column_type, select_column, json_key, column_key
> )
>
> return select_column
>
>
> What I'm trying to figure out is how to dynamically generate SQLAlchemy ORM 
> statements based on the formula. The formulas can be any math equation using 
> +, -, /, *, and parentheses:
>
> formula1 = '"metric:1" + "metric:2" + "metric:3"'
> formula2 = '"metric:1" + "metric:2" - "metric:3"'
> formula3 = '"metric:1" + ("metric:2" * "metric:3")'
> formula4 = '"metric:1" / "metric:2"'
> formula5 = '"metric:1 / ("metric:2" * "metric:3")'
>
> The InstrumentedAttribute objects I mentioned earlier are the select fields 
> for each individual metric - what I need to figure out how to do is to be 
> able to build a SQLAlchemy ORM query by parsing the formula string, and 
> evaluating each operation in the context of the query. `func.sum` would work 
> for formula1, but I'd need to iteratively build the formula with SQLAlchemy 
> ORM helper methods for formula2 - formula5.

im still finding it strange when you say InstrumentedAttribute, as the
table.c[name] objects are Column objects, but I don't see what
_construct_json_select_field is doing but I would again hope that you
are not progammatically creating InstrumentedAttribute objects as they
are not intended to be publicly constructable nor is there any reason
to do so.

Here's a simplified version of what you describe that uses a short
series of functions to parse the strings into expression fragments
directly from a given model class and to run an ORM query, while the
parsing is not robust against syntactical problems and may likely have
other bugs outside of the four test expressions, it will hopefully
illustrate the basic parts of the full front-to-back for this kind of
thing:

import operator
import re

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session


def parse_formula(model, formula):
return _parse_tokens(model, _tokenize(formula))


def _tokenize(strval):
return [tok for tok in re.split(r"\s*([\(\)\+\*\/\-])\s*", strval) if tok]


_operator_lookup = {
"+": operator.add,
"-": operator.sub,
"*": operator.mul,
"/": operator.truediv,
}


def _parse_tokens(model, tokens):
if not tokens:
return None

expr = None

while tokens:
if expr is None:
tok = tokens.pop(0)
if tok == "(":
left_expr = _parse_tokens(model, tokens)
assert tokens.pop(0) == ")"
else:
left_expr = _resolve_model(model, tok)
else:
left_expr = expr

tok = tokens.pop(0)
if tok == ")":
tokens.insert(0, tok)
return expr

operator = _operator_lookup[tok]
tok = tokens.pop(0)
if tok == "(":
right_expr = _parse_tokens(model, tokens)
assert tokens.pop(0) == ")"
else:
right_expr = _resolve_model(model, tok)

expr = operator(left_expr, right_expr)

return expr


def _resolve_model(model, expr):
m = re.match(r'^"(\w+)\:(\d)+"$', expr)
if not m:
raise ValueError("Can't resolve symbol: %s" % expr)

attrname = 

Re: [sqlalchemy] Re: Using operators with multiple InstrumentedAttribute instances

[Mike Bayer]

I'm not at a computer right now but what you need to do is write a simple
recursive descent parser, which makes use of the objects in
sqlalchemy.sql.operators for the operators as it parses the string into an
expression tree.  This is actually a fun classic computer problem I'm sure
someone can show here or I can demo one later.

On Fri, Mar 29, 2019, 2:31 PM Ian Miller  wrote:

> The code that makes up the query builder I've been working on is pretty
> extensive, so I'll go through the high-level basics.
>
> I've set up a `_base` method that augments the SQLALchemy Base model. Note
> the column details retrived in `get_column_and_json_key_from_sql_name` class
> method:
>
> class _base:
> """
> This class augments the default SQLAlchemy Base model
> """
>
> @classmethod
> def get_column_and_json_key_from_sql_name(cls, name):
> """
> Returns the column and, if applicable, the JSON top-level key from 
> the JSON dict.
>
> :param name: Name of field.
>
> :return:
> """
> assert type(name) == str
>
> json_key = None
> col_names = name.split(COLUMN_NAME_DELIMITER)
> if len(col_names) == 2:
> name, json_key = col_names
>
> try:
> col = cls.__table__.c[name]
> except KeyError:
> log.error("Invalid column name: %s", name)
> return None
>
> return (getattr(cls, col.name), json_key)
>
>
> We then have a `_build_column` method that essentially is responsible for
> retrieving the details necessary to construct the column for the select
> statement necessary for constructing the SQLAlchemy ORM query:
>
> def _build_column(self):
> field_name = self.db.get("column")
> model = self._object.get("model")
>
> column_type, column_key = self.db.get("type"), self.db.get("key")
> select_column, json_key = model.get_column_and_json_key_from_sql_name(
> field_name
> )
> select_column = self._construct_json_select_field(
> column_type, select_column, json_key, column_key
> )
>
> return select_column
>
>
> What I'm trying to figure out is how to dynamically generate SQLAlchemy
> ORM statements based on the formula. The formulas can be any math equation
> using +, -, /, *, and parentheses:
>
> formula1 = '"metric:1" + "metric:2" + "metric:3"'
> formula2 = '"metric:1" + "metric:2" - "metric:3"'
> formula3 = '"metric:1" + ("metric:2" * "metric:3")'
> formula4 = '"metric:1" / "metric:2"'
> formula5 = '"metric:1 / ("metric:2" * "metric:3")'
>
> The InstrumentedAttribute objects I mentioned earlier are the select
> fields for each individual metric - what I need to figure out how to do is
> to be able to build a SQLAlchemy ORM query by parsing the formula string,
> and evaluating each operation in the context of the query. `func.sum` would
> work for formula1, but I'd need to iteratively build the formula with
> SQLAlchemy ORM helper methods for formula2 - formula5.
>
> Per +Jonathan Vanasco's last comment, I've already figured out Phase 1.
> I'm stuck on Phase 2.
>
> On Thursday, March 28, 2019 at 4:43:56 PM UTC-4, Ian Miller wrote:
>>
>> Hello all,
>>
>> I am in the process of trying to create a dynamic expression query engine
>> in an application I'm working on.
>>
>> So there is a formula that gets defined like so:
>>
>> formula = '"metric:123" + "metric:456" + "metric:789"'
>>
>> Each metric maps to a column in the database tables - long story short,
>> I'm able to retrieve the metric by ID, and instantiate an
>> InstrumentedAttribute object that has the SQLAlchemy metadata for the
>> associated column. What I'm trying to achieve is to be able to iterate
>> through the formula, and dynamically build a SQLALchemy query that maps to
>> the formula.
>>
>> For example, the formula defined above would look something like this in
>> SQL:
>>
>> SELECT post.id + campaign.id + asset.id
>> FROM post, campaign, asset
>> WHERE ..;
>>
>> The idea is to translate the above to something like:
>>
>> session.query(> 0x7ff9269f92b0> + > at 0x7ff9269c5990> + > object at 0x7ff926896048>).all()
>>
>> I've tried a couple of approaches of dynamically generating the
>> SQLAlchemy ORM query, but I haven't been able to find anything that works.
>> Would anyone have any idea or tips on how to accomplish this?
>>
>> Thank you!
>>
>> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at 

[sqlalchemy] Proper labels in joins when there are multiple relationships with the same table.

[ghlstdios]

I have a model, Account, with two foreign keys / relationships to another 
model, Users.

class Account(object):
 @declared_attr
 def customer_id(cls):
  return Column(ForeignKey(User.id))
 @declared_attr
 def customer(cls):
 return relationship(User, lazy='joined', foreign_keys=cls.customer_id)

 @declared_attr
 def reporter_id(cls):
  return Column(ForeignKey(User.id))
 @declared_attr
 def reporter(cls):
 return relationship(User, lazy='joined', foreign_keys=cls.reporter_id)

session.query(Account) gives me the correct join logic for eager loading. 
The issue comes when I want to load this data into a pandas dataframe. The 
joined columns show up labeled as users_1_colname and users_2_colname which 
makes it unclear which came from reporter and which came from customer. I 
know in a one off query I can use aliases but how can I have a certain 
alias dedicated to a relationship? I don't want to manually generate the 
query and I don't want to change the column names in pandas. I want users_1 to 
always be labeled reporter and users_2 to always be labeled customer when I 
query Account. Is this possible?

-- 
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] Re: Using operators with multiple InstrumentedAttribute instances

[Ian Miller]

The code that makes up the query builder I've been working on is pretty 
extensive, so I'll go through the high-level basics.

I've set up a `_base` method that augments the SQLALchemy Base model. Note 
the column details retrived in `get_column_and_json_key_from_sql_name` class 
method:

class _base:
"""
This class augments the default SQLAlchemy Base model
"""

@classmethod
def get_column_and_json_key_from_sql_name(cls, name):
"""
Returns the column and, if applicable, the JSON top-level key from the 
JSON dict.

:param name: Name of field.

:return:
"""
assert type(name) == str

json_key = None
col_names = name.split(COLUMN_NAME_DELIMITER)
if len(col_names) == 2:
name, json_key = col_names

try:
col = cls.__table__.c[name]
except KeyError:
log.error("Invalid column name: %s", name)
return None

return (getattr(cls, col.name), json_key)


We then have a `_build_column` method that essentially is responsible for 
retrieving the details necessary to construct the column for the select 
statement necessary for constructing the SQLAlchemy ORM query:

def _build_column(self):
field_name = self.db.get("column")
model = self._object.get("model")

column_type, column_key = self.db.get("type"), self.db.get("key")
select_column, json_key = model.get_column_and_json_key_from_sql_name(
field_name
)
select_column = self._construct_json_select_field(
column_type, select_column, json_key, column_key
)

return select_column


What I'm trying to figure out is how to dynamically generate SQLAlchemy ORM 
statements based on the formula. The formulas can be any math equation 
using +, -, /, *, and parentheses:

formula1 = '"metric:1" + "metric:2" + "metric:3"'
formula2 = '"metric:1" + "metric:2" - "metric:3"'
formula3 = '"metric:1" + ("metric:2" * "metric:3")'
formula4 = '"metric:1" / "metric:2"'
formula5 = '"metric:1 / ("metric:2" * "metric:3")'

The InstrumentedAttribute objects I mentioned earlier are the select fields 
for each individual metric - what I need to figure out how to do is to be 
able to build a SQLAlchemy ORM query by parsing the formula string, and 
evaluating each operation in the context of the query. `func.sum` would 
work for formula1, but I'd need to iteratively build the formula with 
SQLAlchemy ORM helper methods for formula2 - formula5.

Per +Jonathan Vanasco's last comment, I've already figured out Phase 1. I'm 
stuck on Phase 2.

On Thursday, March 28, 2019 at 4:43:56 PM UTC-4, Ian Miller wrote:
>
> Hello all,
>
> I am in the process of trying to create a dynamic expression query engine 
> in an application I'm working on.
>
> So there is a formula that gets defined like so: 
>
> formula = '"metric:123" + "metric:456" + "metric:789"'
>
> Each metric maps to a column in the database tables - long story short, 
> I'm able to retrieve the metric by ID, and instantiate an 
> InstrumentedAttribute object that has the SQLAlchemy metadata for the 
> associated column. What I'm trying to achieve is to be able to iterate 
> through the formula, and dynamically build a SQLALchemy query that maps to 
> the formula.
>
> For example, the formula defined above would look something like this in 
> SQL:
>
> SELECT post.id + campaign.id + asset.id
> FROM post, campaign, asset
> WHERE ..;
>
> The idea is to translate the above to something like:
>
> session.query( 0x7ff9269f92b0> +  at 0x7ff9269c5990> +  object at 0x7ff926896048>).all()
>
> I've tried a couple of approaches of dynamically generating the SQLAlchemy 
> ORM query, but I haven't been able to find anything that works. Would 
> anyone have any idea or tips on how to accomplish this?
>
> Thank you!
>
>

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Can't get join() to work

['Neil Youngman' via sqlalchemy]

On Friday, 29 March 2019 15:30:39 UTC, Neil Youngman wrote:
>
>
> That needs to be:
> supplying_dealer = relationship(Dealer, 
> primaryjoin=supplying_dealer_id == Dealer.dealer_id)
> servicing_dealer = relationship(Dealer, 
> primaryjoin=servicing_dealer_id == Dealer.dealer_id)
>

and now:

>>> for supplier, phone, registration in 
session.query(Dealer.name,Dealer.phone_number,Vehicle.registration).select_from(Vehicle).join(u'supplying_dealer'):
... print( supplier, phone, registration )
... 

returns:

(u'Trumpton Vehicles', u'123-987-4567', u'ABC123D')
(u'Lazytown Vehicles', u'987-123-7654', u'DEF456G')
(u'Lazytown Vehicles', u'987-123-7654', u'GHI789J')

Thanks for the help.

Neil

-- 
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] Can't get join() to work

['Neil Youngman' via sqlalchemy]

On Friday, 29 March 2019 15:10:27 UTC, Neil Youngman wrote:
>
>
> supplying_dealer = relationship(Dealer, supplying_dealer_id == 
> Dealer.dealer_id)
> servicing_dealer = relationship(Dealer, servicing_dealer_id == 
> Dealer.dealer_id)
>
>
That needs to be:
supplying_dealer = relationship(Dealer, primaryjoin=supplying_dealer_id 
== Dealer.dealer_id)
servicing_dealer = relationship(Dealer, primaryjoin=servicing_dealer_id 
== Dealer.dealer_id)

-- 
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] user-defined simple fields in a multi-tenant flask app with ORM

[Jonathan Vanasco]

On Friday, March 29, 2019 at 4:39:40 AM UTC-4, Xavier Bustamante Talavera 
wrote:
>
>
> @Ibrahima and @Jonathan, as I understand you are talking about something 
> like the Entity–Attribute–Value model 
> , 
> adapted to the multi-tenant case. In my case the data comes form an API in 
> JSON, so although there is not a special strong case towards this pattern, 
> I think I will be using a JSON type.
>

Yes! That's the name, I could not remember it.  I would definitely use JSON 
instead of EAV due to it's ease of use and overall performance.

FWIW, There are two main variations of the EAV pattern I've seen: 
normalizing the values into their own table & using a table that just has 
attribute id + value id (Ibrahima's suggestion, i think), and just doing a 
an attribute id + value table (what I was alluding to).  in my experience, 
the scale of multi tenant applications tends to make the fully normalized 
implementation incredibly slow, so i just don't bother with it anymore.

-- 
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] Can't get join() to work

['Neil Youngman' via sqlalchemy]

On Friday, 29 March 2019 13:48:33 UTC, Simon King wrote:
>
>
> You haven't created a relationship() between Vehicle and Dealer, which 
> reduces your options a little bit. This should work: 
>
> session.query(Vehicle).join(Dealer, Vehicle.supplying_dealer == 
> Dealer.id) 
>
> ie. you need to be explicit about the class you are joining to, and 
> the join condition. The join condition is necessary because you have 2 
> foreign keys between Vehicle and Dealer. 
>
> You might want to consider creating relationships between the two 
> classes, something like this: 
>
> class Vehicle(Base): 
> __tablename__ = 'vehicles' 
>
> registration = Column(String(10), primary_key=True, nullable=False) 
> # note that I've renamed these columns 
> supplying_dealer_id = Column(String(64), 
> ForeignKey(u'dealers.id'), nullable=False) 
> servicing_dealer_id = Column(String(64), 
> ForeignKey(u'dealers.id'), nullable=False) 
>
> supplying_dealer = relationship(Dealer, supplying_dealer_id == 
> Dealer.id) 
> servicing_dealer = relationship(Dealer, servicing_dealer_id == 
> Dealer.id) 
>

I'd missed the need to set a relationship in addition to the foreign key. 

I've done that and changed Dealer.id to Dealer.dealer_id.

I can't now load any data. It says

"sqlalchemy.exc.NoForeignKeysError: Could not determine join condition 
between parent/child tables on relationship Vehicle.supplying_dealer - 
there are no foreign keys linking these tables via secondary table 
'vehicles.supplying_dealer_id = dealers.dealer_id'.  Ensure that 
referencing columns are associated with a ForeignKey or 
ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' 
expressions."

It looks OK to me.

The current table definitions are:

class Dealer(Base):
__tablename__ = 'dealers'

dealer_id = Column(String(64), primary_key=True, nullable=False)
name = Column(String(100), nullable=False)
phone_number = Column(String(64), nullable=False)

def __repr__(self):
   return "" % (
self.supplying_dealer, self.registration, 
self.servicing_dealer)


class Vehicle(Base):
__tablename__ = 'vehicles'

registration = Column(String(10), primary_key=True, nullable=False)
supplying_dealer_id = Column(String(64), 
ForeignKey(u'dealers.dealer_id'), nullable=False)
servicing_dealer_id = Column(String(64), 
ForeignKey(u'dealers.dealer_id'), nullable=False)
supplying_dealer = relationship(Dealer, supplying_dealer_id == 
Dealer.dealer_id)
servicing_dealer = relationship(Dealer, servicing_dealer_id == 
Dealer.dealer_id)

def __repr__(self):
   return "" % (
   self.registration, self.supplying_dealer, self.servicing_dealer)

 

-- 
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] Documentation options

[Rich Shepard]

On Fri, 29 Mar 2019, Mike Bayer wrote:


I'd prefer any code samples are shared on-list here for the benefit of all
to see, thanks.


Mike,

I was unsure about the proper protocol. I'll post the module here Real Soon
Now.

Thanks,

Rich

--
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] Documentation options

[Mike Bayer]

On Tue, Mar 26, 2019 at 6:28 PM Rich Shepard  wrote:
>
> On Thu, 21 Mar 2019, Mike Bayer wrote:
>
> > sounds like automap:
> > https://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html
>
> Mike,
>
> May I send you models.py (off the mail list) to check that I have correctly
> applied automap?

Hi Rich -

I'd prefer any code samples are shared on-list here for the benefit of
all to see, thanks.


>
> TIA,
>
> Rich
>
> --
> 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] MSSQL String columns often incorrectly encoded as NVARCHAR

[Mike Bayer]

Also note pymssql is not well maintained right now due to lack of
funding, please confirm you reproduce your performance concerns using
PyODBC with Microsofts ODBC drivers ?  That should be considered to be
the canonically supported driver right now, works on all platforms
very well now.

On Fri, Mar 29, 2019 at 10:07 AM Mike Bayer  wrote:
>
> OK so I saw that the "N" prefix is not generated with your test case
> either, so I re-read your email.  Can you clarify what you mean by
> "always encoded as NVARCHAR"?   are you referring to the simple fact
> that a Python string object is passed to the driver, and that the
> driver is behind the scenes applying the "N" in any case or is
> otherwise binding it as unicode in such a way that performance is
> impacted?   SQLAlchemy for many years passed bytestrings to drivers
> like pyodbc because they would simply crash if you passed them a
> unicode object, but once they supported it, SQLAlchemy was eager to
> get out of the business of doing this encoding.In 1.3 we've just
> deprecated all the flags that allow it to do this
> (convert_unicode=True).Using that flag would be your quickest way
> to get it back for now but we'd have to develop an all new behavior
> for 1.4 if we are to start encoding these binds again, however current
> behavior has been this way for many years and this is the first it's
> being reported in this way.  I would want to look into driver
> configuration options for this as well.
>
>
>
>
> On Fri, Mar 29, 2019 at 9:56 AM Mike Bayer  wrote:
> >
> > On Fri, Mar 29, 2019 at 6:20 AM Ian Wagner  wrote:
> > >
> > > Hello all,
> > >
> > > I'm trying to get to the bottom of an issue in which Python 3 (unicode by 
> > > definition) strings are always encoded as NVARCHAR for at least two 
> > > backends (pymssql and pyodbc). Using bytstrings as comparison arguments 
> > > (for example Table.column == value.encode('utf-8')) sends a regular 
> > > string literal as expected, but regular strings are encoded as NVARCHAR 
> > > literals.
> > >
> > > This behavior is fairly logical at the underlying driver (pymssql or 
> > > pyodbc) level, which is why I'm posting here. I believe the the use of a 
> > > String column (as opposed to a Unicode column) type should not pass an 
> > > NVARCHAR literal. Doing so has disastrous performance implications, as 
> > > SQL Server ends up casting the whole column up. This will wreak havoc 
> > > when regularly dealing with large-ish tables (1.7 million rows or so in 
> > > our case).
> > >
> > > I have previously posted with a LOT more details on StackOverflow 
> > > (https://stackoverflow.com/questions/55098426/strings-used-in-query-always-sent-with-nvarchar-syntax-even-if-the-underlying-c).
> > >  I also have an MCVE over on GitHub 
> > > (https://github.com/ianthetechie/pymssql_sqlalchemy_55098426).
> > >
> > > In my MCVE, I outline a possible approach for fixing this, but it appears 
> > > to have some problems. I'm posting here asking for feedback on what's 
> > > wrong with my approach, and what would be the best way to go about 
> > > getting this fixed.
> >
> > seems like we will need some documentation for this as it is confusing
> > a lot of people.   The issue that introduced this behavior is
> > https://github.com/sqlalchemy/sqlalchemy/issues/4442 and then that
> > same user felt it was happening too often in
> > https://github.com/sqlalchemy/sqlalchemy/issues/4561, however I
> > clarified that the N prefix only generates if there is no other
> > context to determine that this is not a non-unicode context.
> >
> > the NVARCHAR thing should not happen if you are comparing to a
> > non-NVARCHAR column.   it only occurs when there is no other context
> > that SQLAlchemy can determine the correct datatype for the Unicode
> > object being passed.
> >
> > However, the example case you have on github there seems to be using a
> > unicode in a VARCHAR comparison so should not see the N.  If it does,
> > it's a bug.  I will try your test case now.
> >
> > In the future, please go straight to the SQLAlchemy github issue
> > tracker with a succinct test case, as this N thing is obviously still
> > ongoing.
> >
> >
> >
> > >
> > > Thanks!
> > > Ian
> > >
> > > --
> > > 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] MSSQL String columns often incorrectly encoded as NVARCHAR

[Mike Bayer]

OK so I saw that the "N" prefix is not generated with your test case
either, so I re-read your email.  Can you clarify what you mean by
"always encoded as NVARCHAR"?   are you referring to the simple fact
that a Python string object is passed to the driver, and that the
driver is behind the scenes applying the "N" in any case or is
otherwise binding it as unicode in such a way that performance is
impacted?   SQLAlchemy for many years passed bytestrings to drivers
like pyodbc because they would simply crash if you passed them a
unicode object, but once they supported it, SQLAlchemy was eager to
get out of the business of doing this encoding.In 1.3 we've just
deprecated all the flags that allow it to do this
(convert_unicode=True).Using that flag would be your quickest way
to get it back for now but we'd have to develop an all new behavior
for 1.4 if we are to start encoding these binds again, however current
behavior has been this way for many years and this is the first it's
being reported in this way.  I would want to look into driver
configuration options for this as well.




On Fri, Mar 29, 2019 at 9:56 AM Mike Bayer  wrote:
>
> On Fri, Mar 29, 2019 at 6:20 AM Ian Wagner  wrote:
> >
> > Hello all,
> >
> > I'm trying to get to the bottom of an issue in which Python 3 (unicode by 
> > definition) strings are always encoded as NVARCHAR for at least two 
> > backends (pymssql and pyodbc). Using bytstrings as comparison arguments 
> > (for example Table.column == value.encode('utf-8')) sends a regular string 
> > literal as expected, but regular strings are encoded as NVARCHAR literals.
> >
> > This behavior is fairly logical at the underlying driver (pymssql or 
> > pyodbc) level, which is why I'm posting here. I believe the the use of a 
> > String column (as opposed to a Unicode column) type should not pass an 
> > NVARCHAR literal. Doing so has disastrous performance implications, as SQL 
> > Server ends up casting the whole column up. This will wreak havoc when 
> > regularly dealing with large-ish tables (1.7 million rows or so in our 
> > case).
> >
> > I have previously posted with a LOT more details on StackOverflow 
> > (https://stackoverflow.com/questions/55098426/strings-used-in-query-always-sent-with-nvarchar-syntax-even-if-the-underlying-c).
> >  I also have an MCVE over on GitHub 
> > (https://github.com/ianthetechie/pymssql_sqlalchemy_55098426).
> >
> > In my MCVE, I outline a possible approach for fixing this, but it appears 
> > to have some problems. I'm posting here asking for feedback on what's wrong 
> > with my approach, and what would be the best way to go about getting this 
> > fixed.
>
> seems like we will need some documentation for this as it is confusing
> a lot of people.   The issue that introduced this behavior is
> https://github.com/sqlalchemy/sqlalchemy/issues/4442 and then that
> same user felt it was happening too often in
> https://github.com/sqlalchemy/sqlalchemy/issues/4561, however I
> clarified that the N prefix only generates if there is no other
> context to determine that this is not a non-unicode context.
>
> the NVARCHAR thing should not happen if you are comparing to a
> non-NVARCHAR column.   it only occurs when there is no other context
> that SQLAlchemy can determine the correct datatype for the Unicode
> object being passed.
>
> However, the example case you have on github there seems to be using a
> unicode in a VARCHAR comparison so should not see the N.  If it does,
> it's a bug.  I will try your test case now.
>
> In the future, please go straight to the SQLAlchemy github issue
> tracker with a succinct test case, as this N thing is obviously still
> ongoing.
>
>
>
> >
> > Thanks!
> > Ian
> >
> > --
> > 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 

Re: [sqlalchemy] MSSQL String columns often incorrectly encoded as NVARCHAR

[Mike Bayer]

On Fri, Mar 29, 2019 at 6:20 AM Ian Wagner  wrote:
>
> Hello all,
>
> I'm trying to get to the bottom of an issue in which Python 3 (unicode by 
> definition) strings are always encoded as NVARCHAR for at least two backends 
> (pymssql and pyodbc). Using bytstrings as comparison arguments (for example 
> Table.column == value.encode('utf-8')) sends a regular string literal as 
> expected, but regular strings are encoded as NVARCHAR literals.
>
> This behavior is fairly logical at the underlying driver (pymssql or pyodbc) 
> level, which is why I'm posting here. I believe the the use of a String 
> column (as opposed to a Unicode column) type should not pass an NVARCHAR 
> literal. Doing so has disastrous performance implications, as SQL Server ends 
> up casting the whole column up. This will wreak havoc when regularly dealing 
> with large-ish tables (1.7 million rows or so in our case).
>
> I have previously posted with a LOT more details on StackOverflow 
> (https://stackoverflow.com/questions/55098426/strings-used-in-query-always-sent-with-nvarchar-syntax-even-if-the-underlying-c).
>  I also have an MCVE over on GitHub 
> (https://github.com/ianthetechie/pymssql_sqlalchemy_55098426).
>
> In my MCVE, I outline a possible approach for fixing this, but it appears to 
> have some problems. I'm posting here asking for feedback on what's wrong with 
> my approach, and what would be the best way to go about getting this fixed.

seems like we will need some documentation for this as it is confusing
a lot of people.   The issue that introduced this behavior is
https://github.com/sqlalchemy/sqlalchemy/issues/4442 and then that
same user felt it was happening too often in
https://github.com/sqlalchemy/sqlalchemy/issues/4561, however I
clarified that the N prefix only generates if there is no other
context to determine that this is not a non-unicode context.

the NVARCHAR thing should not happen if you are comparing to a
non-NVARCHAR column.   it only occurs when there is no other context
that SQLAlchemy can determine the correct datatype for the Unicode
object being passed.

However, the example case you have on github there seems to be using a
unicode in a VARCHAR comparison so should not see the N.  If it does,
it's a bug.  I will try your test case now.

In the future, please go straight to the SQLAlchemy github issue
tracker with a succinct test case, as this N thing is obviously still
ongoing.



>
> Thanks!
> Ian
>
> --
> 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] Can't get join() to work

[Simon King]

On Fri, Mar 29, 2019 at 12:17 PM 'Neil Youngman' via sqlalchemy
 wrote:
>
> I'm trying to do a select of columns from a join and I simply can't get it to 
> work. I have tried various different ways to specify the join, with and 
> without a select_from() and I can't find a combination that works.
>
> the tables look like
>
> class Dealer(Base):
> __tablename__ = 'dealers'
>
> id = Column(String(64), primary_key=True, nullable=False)
> name =Column(String(100), nullable=False)
> phone_number = Column(String(64), nullable=False)
>
> def __repr__(self):
>return " servicing_dealer='%s')>" % (
> self.supplying_dealer, self.registration, 
> self.servicing_dealer)
>
>
> class Vehicle(Base):
> __tablename__ = 'vehicles'
>
> registration = Column(String(10), primary_key=True, nullable=False)
> supplying_dealer = Column(String(64), ForeignKey(u'dealers.id'), 
> nullable=False)
> servicing_dealer = Column(String(64), ForeignKey(u'dealers.id'), 
> nullable=False)
>
> def __repr__(self):
>return " servicing_dealer='%s')>" % (
>self.registration, self.supplying_dealer, self.servicing_dealer)
>
> and I have tried lots of variations on:
>
> for supplier, phone, registration in   
> session.query(Dealer.name,Dealer.phone_number,Vehicle.registration).select_from(Vehicle).join('supplying_dealer'):
> print( supplier, phone, registration )
>
> I haven't found a combination that works. Am I missing the obvious?

You haven't created a relationship() between Vehicle and Dealer, which
reduces your options a little bit. This should work:

session.query(Vehicle).join(Dealer, Vehicle.supplying_dealer == Dealer.id)

ie. you need to be explicit about the class you are joining to, and
the join condition. The join condition is necessary because you have 2
foreign keys between Vehicle and Dealer.

You might want to consider creating relationships between the two
classes, something like this:

class Vehicle(Base):
__tablename__ = 'vehicles'

registration = Column(String(10), primary_key=True, nullable=False)
# note that I've renamed these columns
supplying_dealer_id = Column(String(64),
ForeignKey(u'dealers.id'), nullable=False)
servicing_dealer_id = Column(String(64),
ForeignKey(u'dealers.id'), nullable=False)

supplying_dealer = relationship(Dealer, supplying_dealer_id == Dealer.id)
servicing_dealer = relationship(Dealer, servicing_dealer_id == Dealer.id)

With those relationships in place, either of these joins should work:

session.query(Vehicle).join("supplying_dealer")
session.query(Vehicle).join(Vehicle.supplying_dealer)

Hope that helps,

Simon

-- 
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: Documentation options

[Rich Shepard]

On Fri, 29 Mar 2019, Xavier Bustamante Talavera wrote:


I use Dash  to read SQLAlchemy documentation,
which is great to search through it and works offline (it downloads the
whole docs).


Xavier,

This looks like the ideal solution. I'll install Dash and use it.

Many thanks,

Rich

--
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] Can't get join() to work

['Neil Youngman' via sqlalchemy]

I'm trying to do a select of columns from a join and I simply can't get it 
to work. I have tried various different ways to specify the join, with and 
without a select_from() and I can't find a combination that works.

the tables look like

class Dealer(Base):
__tablename__ = 'dealers'

id = Column(String(64), primary_key=True, nullable=False)
name =Column(String(100), nullable=False)
phone_number = Column(String(64), nullable=False)

def __repr__(self):
   return "" % (
self.supplying_dealer, self.registration, 
self.servicing_dealer)


class Vehicle(Base):
__tablename__ = 'vehicles'

registration = Column(String(10), primary_key=True, nullable=False)
supplying_dealer = Column(String(64), ForeignKey(u'dealers.id'), 
nullable=False)
servicing_dealer = Column(String(64), ForeignKey(u'dealers.id'), 
nullable=False)

def __repr__(self):
   return "" % (
   self.registration, self.supplying_dealer, self.servicing_dealer)

and I have tried lots of variations on:

for supplier, phone, registration in   
session.query(Dealer.name,Dealer.phone_number,Vehicle.registration).select_from(Vehicle).join('supplying_dealer'):
print( supplier, phone, registration )

I haven't found a combination that works. Am I missing the obvious?

-- 
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] Re: can't update images

[Scheck David]

Ok solved it in postgresql thanks

-- 
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] MSSQL String columns often incorrectly encoded as NVARCHAR

[Ian Wagner]

Hello all,

I'm trying to get to the bottom of an issue in which Python 3 (unicode by 
definition) strings are always encoded as NVARCHAR for at least two 
backends (pymssql and pyodbc). Using bytstrings as comparison arguments 
(for example Table.column == value.encode('utf-8')) sends a regular string 
literal as expected, but regular strings are encoded as NVARCHAR literals.

This behavior is fairly logical at the underlying driver (pymssql or 
pyodbc) level, which is why I'm posting here. I believe the the use of a 
String column (as opposed to a Unicode column) type should not pass an 
NVARCHAR literal. Doing so has disastrous performance implications, as SQL 
Server ends up casting the whole column up. This will wreak havoc when 
regularly dealing with large-ish tables (1.7 million rows or so in our 
case).

I have previously posted with a LOT more details on StackOverflow 
(https://stackoverflow.com/questions/55098426/strings-used-in-query-always-sent-with-nvarchar-syntax-even-if-the-underlying-c).
 
I also have an MCVE over on GitHub 
(https://github.com/ianthetechie/pymssql_sqlalchemy_55098426).

In my MCVE, I outline a possible approach for fixing this, but it appears 
to have some problems. I'm posting here asking for feedback on what's wrong 
with my approach, and what would be the best way to go about getting this 
fixed.

Thanks!
Ian

-- 
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] user-defined simple fields in a multi-tenant flask app with ORM

[Ibrahima Gaye]

Thanks Xavier,
i did not know the pattern had a name :)  ( the Entity–Attribute–Value model

,)
Ibrahima GAYE




Le ven. 29 mars 2019 à 09:39, Xavier Bustamante Talavera 
a écrit :

> Hello everyone and thanks for your answers,
>
> Simpler solutions would be just using hstore or JSON types, but I would be
> loosing the goodies of SQLAlchemy / Postgres schemas and consistency.
>
>
> this is totally how I'd want to do it unless your clients are given
> access to program in SQL and SQLAlchemy. What is a real-world
> scenario where you have given a tenant three additional columns on one
> of the database tables and the tenants need to use that data ?  what
> would that look like and what would you be doing that is any different
> from pulling those values from an hstore ?
>
>
> @Mike: To answer you the user-defined data would only be used for CRUD
> operations, no specific coding logic involved, so the only benefit of going
> to using extra fields in a table rather than a hstore / json type would be
> data and access consistency —probably not enough for the problems you
> mention this approach would take.
>
> As I understand then it is better to just define a Postgres JSON type as
> the custom field and provide our own schema validation.
>
> For the client-defined inheritance, as those tables are going to lack
> custom logic more than the field definitions, we will value other
> approaches not touching table definitions.
>
> On 29 Mar 2019, at 00:32, Ibrahima Gaye  wrote:
>
> Hi Jonathan,
> i would do it like this:
> - add in your global model  tables named attributs, attributs_value and
> values,
> - any table (let's call it XTable) that will eventually has need extra
> column per client will be linked to attributs_value via a table
> XTable_Attributs (For maximum flexibility).
> Hope that helps,
> Best regards
>
>
> @Ibrahima and @Jonathan, as I understand you are talking about something
> like the Entity–Attribute–Value model
> ,
> adapted to the multi-tenant case. In my case the data comes form an API in
> JSON, so although there is not a special strong case towards this pattern,
> I think I will be using a JSON type.
>
> Thank you again for your comments; it has been very enlightening!
>
>
>
> Ibrahima GAYE
>
>
>
> Le jeu. 28 mars 2019 à 21:14, Jonathan Vanasco  a
> écrit :
>
>>
>>
>> On Thursday, March 28, 2019 at 9:19:51 AM UTC-4, Mike Bayer wrote:
>>>
>>>
>>> > Simpler solutions would be just using hstore or JSON types, but I
>>> would be loosing the goodies of SQLAlchemy / Postgres schemas and
>>> consistency.
>>>
>>> this is totally how I'd want to do it unless your clients are
>>> given access to program in SQL and SQLAlchemy.
>>>
>>
>> wile I would handle this as JSON data too, there is also a database
>> pattern for doing this in multi tenant applications where you use a table
>> to allocate and store the allowable keys for each tenant , and another
>> table to store the key values for the tenants objects. but i would do this
>> in JSON.
>>
>>
>>
>> --
>> 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.
>
> Best regards,
> Xavier Bustamante Talavera.
> Linkedin  | +34 634 541 887
>
> --
> 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 

[sqlalchemy] Re: Documentation options

[Xavier Bustamante Talavera]

Hello Rich,

I use Dash  to read SQLAlchemy documentation, 
which is great to search through it and works offline (it downloads the 
whole docs).

Xavier.

El dijous, 21 març de 2019 21:20:33 UTC+1, Rich va escriure:
>
> Are there PDF versions of the docs available for downloading and reading? 
> I 
> don't find an answer on the web site. 
>
> TIA, 
>
> Rich 
>

-- 
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] user-defined simple fields in a multi-tenant flask app with ORM

[Xavier Bustamante Talavera]

Hello everyone and thanks for your answers,

>> Simpler solutions would be just using hstore or JSON types, but I would be 
>> loosing the goodies of SQLAlchemy / Postgres schemas and consistency.
> 
> this is totally how I'd want to do it unless your clients are given
> access to program in SQL and SQLAlchemy. What is a real-world
> scenario where you have given a tenant three additional columns on one
> of the database tables and the tenants need to use that data ?  what
> would that look like and what would you be doing that is any different
> from pulling those values from an hstore ?

@Mike: To answer you the user-defined data would only be used for CRUD 
operations, no specific coding logic involved, so the only benefit of going to 
using extra fields in a table rather than a hstore / json type would be data 
and access consistency —probably not enough for the problems you mention this 
approach would take.

As I understand then it is better to just define a Postgres JSON type as the 
custom field and provide our own schema validation.

For the client-defined inheritance, as those tables are going to lack custom 
logic more than the field definitions, we will value other approaches not 
touching table definitions.

> On 29 Mar 2019, at 00:32, Ibrahima Gaye  wrote:
> 
> Hi Jonathan,
> i would do it like this:
> - add in your global model  tables named attributs, attributs_value and 
> values,
> - any table (let's call it XTable) that will eventually has need extra column 
> per client will be linked to attributs_value via a table XTable_Attributs 
> (For maximum flexibility).
> Hope that helps,
> Best regards

@Ibrahima and @Jonathan, as I understand you are talking about something like 
the Entity–Attribute–Value model 
, 
adapted to the multi-tenant case. In my case the data comes form an API in 
JSON, so although there is not a special strong case towards this pattern, I 
think I will be using a JSON type.

Thank you again for your comments; it has been very enlightening!

> 
> 
> Ibrahima GAYE
> 
>  
> 
> 
> Le jeu. 28 mars 2019 à 21:14, Jonathan Vanasco  > a écrit :
> 
> 
> On Thursday, March 28, 2019 at 9:19:51 AM UTC-4, Mike Bayer wrote:
> 
> > Simpler solutions would be just using hstore or JSON types, but I would be 
> > loosing the goodies of SQLAlchemy / Postgres schemas and consistency. 
> 
> this is totally how I'd want to do it unless your clients are given access to 
> program in SQL and SQLAlchemy. 
> 
> wile I would handle this as JSON data too, there is also a database pattern 
> for doing this in multi tenant applications where you use a table to allocate 
> and store the allowable keys for each tenant , and another table to store the 
> key values for the tenants objects. but i would do this in JSON.
> 
> 
> 
> -- 
> 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 
> .
Best regards,
Xavier Bustamante Talavera.
Linkedin  | +34 634 541 887

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