[sqlalchemy] NestedType for clickhouse-sqlalchemy dialect

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

Greetings.

I want to implement NestedType

for the dialect  of
ClickHouse database.

At the first sight it looks like copmosite type, but not not completely:
1. Each subtype considers like separate column
2. `NestedType` is just syntax sugar:
CREATE TABLE test_table1 (
date Date,
object Nested (
type String,
content String

)

)...


CREATE TABLE test_table2 (
date Date,
object.type String,
object.content String
)...
Both of DDL's create tables with the same structure.
3. Queries can be done only using full format, like that:
SELECT count(*), objects.type FROM test_table group by objects.type
or
SELECT count(*), test_table.objects.type FROM test_table group by test_table
.objects.type



My question is how can I implement that behaviour? I've tried to adjust
sqlachemy_utils.CompositeType, but it looks like there are a lot of
differences.

Looks like I need to use separated DeclarativeMeta class and register each
member of nested field (with
sqlalchemy.ext.declarative.base._add_attribute).
Another problem: append parent column name to nested column name, because I
want to make that:


class Test(Base):
__tablename__ = 'test'
object = Column(
Nested(
 Column('type', String),
 Column('content', String),
)

)


Session.query(Test).filter(Test.object.type == 'type')...
# generates that query...
select ... from test where test.object.type = 'type'

-- 
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: jsonb_agg issue

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

Ok, that works almost fine:
>>> func.jsonb_agg(literal_column(models.OrderUINTable.name + '.*'))
'jsonb_agg(OrderUIN.*)'
The problem is OrderUIN. It must be quoted: jsonb_agg("OrderUIN".*)

чт, 6 сент. 2018 г. в 17:44, Антонио Антуан :

> Another one fail:
> func.jsonb_agg(*models.OrderUINTable.c.values()) - PG error: "function
> jsonb_agg(integer, text, numeric, text, text, text, text) does not exist"
>
>
> четверг, 6 сентября 2018 г., 17:28:37 UTC+3 пользователь Антонио Антуан
> написал:
>
>> Hi guys.
>> I'm using sqlalchemy 1.1.13, want to construct that query:
>> select o.*, jsonb_agg(ou.*) from "Orders" o left join "OrderUIN" ou on
>> ou."OrderID" = o."OrderID" group by o."OrderID"
>>
>> I tried that options:
>> - func.jsonb_agg(OrderUINTable), got select o.*, jsonb_agg() from
>> "Orders"... Empty argument for the function, as you can see.
>> - func.jsonb_agg(OrderUINTable.columns.values()), got can't adapt type
>> 'Column'
>> - func.jsonb_agg(OrderUINTable.alias), got TypeError: self_group() got an
>> unexpected keyword argument 'against'
>>
>> How can I implement 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 post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
-- 

Антон

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

http://www.sqlalchemy.org/

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

[sqlalchemy] Re: jsonb_agg issue

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

Another one fail:
func.jsonb_agg(*models.OrderUINTable.c.values()) - PG error: "function 
jsonb_agg(integer, text, numeric, text, text, text, text) does not exist"


четверг, 6 сентября 2018 г., 17:28:37 UTC+3 пользователь Антонио Антуан 
написал:
>
> Hi guys.
> I'm using sqlalchemy 1.1.13, want to construct that query:
> select o.*, jsonb_agg(ou.*) from "Orders" o left join "OrderUIN" ou on 
> ou."OrderID" = o."OrderID" group by o."OrderID"
>
> I tried that options:
> - func.jsonb_agg(OrderUINTable), got select o.*, jsonb_agg() from 
> "Orders"... Empty argument for the function, as you can see.
> - func.jsonb_agg(OrderUINTable.columns.values()), got can't adapt type 
> 'Column'
> - func.jsonb_agg(OrderUINTable.alias), got TypeError: self_group() got an 
> unexpected keyword argument 'against'
>
> How can I implement 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 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] jsonb_agg issue

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

Hi guys.
I'm using sqlalchemy 1.1.13, want to construct that query:
select o.*, jsonb_agg(ou.*) from "Orders" o left join "OrderUIN" ou on 
ou."OrderID" = o."OrderID" group by o."OrderID"

I tried that options:
- func.jsonb_agg(OrderUINTable), got select o.*, jsonb_agg() from 
"Orders"... Empty argument for the function, as you can see.
- func.jsonb_agg(OrderUINTable.columns.values()), got can't adapt type 
'Column'
- func.jsonb_agg(OrderUINTable.alias), got TypeError: self_group() got an 
unexpected keyword argument 'against'

How can I implement 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 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] Queries comparison

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

Hi there!

I have a very complicated queries generator and I want to test it. 
Currently it looks like that:

def test():
query = generate_query()
compiled = compile_query(query)
assert compiled == 'SELECT * FROM table'

Problems begin when I add some filters to query:

def test():
query = generate_query_with_filters()
compiled = compile_query(query)
assert compiled == 'SELECT * FROM table WHERE a = 1 and b = 2'

This test can fail sometimes because condition in compiled query can be 
swaped: "SELECT * FROM table WHERE b = 2 and a = 1". 
Is there any way to compare Query objects?

I tried to do this (does't work):
a = Model.query
b = Model.query
# assert a == b, 'failed'

a = Model.query.filter(Model.col == 1)
b = Model.query.filter(Model.col == 1)
# assert a.statement == b.statement

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

render impl for custom dialect

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

Hi.
I use [that](https://github.com/xzkostyan/clickhouse-sqlalchemy) library 
and tries to make migrations using alembic.

Here is simple implementation for that dialect:
```
class ClickHOuseImpl(postgresql.PostgresqlImpl):
__dialect__ = 'clickhouse'
transactional_ddl = False
```

Here is what I got after running `alembic init`:
```
op.create_table('order_logs',
sa.Column('ts_spawn', clickhouse_sqlalchemy.types.UInt32(), 
nullable=False),
sa.Column('platform_id', clickhouse_sqlalchemy.types.UInt32(), 
nullable=False),
sa.Column('slave_prefix', clickhouse_sqlalchemy.types.String(), 
nullable=False),
sa.Column('int_orders_ids', clickhouse_sqlalchemy.types.Array(), nullable=False),
sa.Column('state', clickhouse_sqlalchemy.types.UInt8(), nullable=False),
sa.Column('step', clickhouse_sqlalchemy.types.UInt8(), nullable=False),
sa.Column('ext_orders_ids', clickhouse_sqlalchemy.types.Array(), nullable=True),
sa.Column('url', clickhouse_sqlalchemy.types.String(), nullable=True),
sa.Column('request', clickhouse_sqlalchemy.types.String(), 
nullable=True),
sa.Column('response', clickhouse_sqlalchemy.types.String(), 
nullable=True),
sa.PrimaryKeyConstraint('ts_spawn', 'platform_id', 'slave_prefix', 
'int_orders_ids', 'state', 'step')
)
```
As you can see array-elements generated not correct: nested types have 
`` substring.

`alembic.autogenerate.render._repr_type` shows me, that custom 
representation of types uses only if module_name startswith 
`sqlalchemy.dialects`:
```
...
if hasattr(autogen_context.migration_context, 'impl'):
impl_rt = autogen_context.migration_context.impl.render_type(
type_, autogen_context)
else:
impl_rt = None

mod = type(type_).__module__
imports = autogen_context.imports
if mod.startswith("sqlalchemy.dialects"):
dname = re.match(r"sqlalchemy\.dialects\.(\w+)", mod).group(1)
if imports is not None:
imports.add("from sqlalchemy.dialects import %s" % dname)
if impl_rt:
return impl_rt
...
```

Is there any ways to fix the problem?

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

Re: [sqlalchemy] Redefine operator compilation for specific dialect

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

Perfect, thanks

чт, 19 апр. 2018 г., 20:17 Mike Bayer <mike...@zzzcomputing.com>:

> On Thu, Apr 19, 2018 at 9:52 AM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> > So, anyway, I must implement BitAnd class? Or I can check if 'element.op
> ==
> > &'  inside compile function?
>
>
> uh sure you can do that, it would be sqlalchemy.sql.operators.and_op
> or something like that
>
>
>
> >
> > чт, 19 апр. 2018 г., 16:31 Mike Bayer <mike...@zzzcomputing.com>:
> >>
> >> On Thu, Apr 19, 2018 at 5:21 AM, Антонио Антуан <a.ch@gmail.com>
> >> wrote:
> >> > Hi guys.
> >> >
> >> > Is there any mechanism in SQLAlchemy which allows to redefine operator
> >> > compilation? I want to the same thing as already exists for functions.
> >> > An example for functions:
> >> >
> >> >
> >> > class IntDiv(GenericFunction):
> >> >  type = Integer
> >> >  package = 'adc_custom'
> >> >  name = 'div'
> >> >  identifier = 'div'
> >> >
> >> >
> >> > @compiles(IntDiv)
> >> > def visit_div_default(element, compiler, **kwargs):
> >> >  params = [compiler.process(c.self_group()) for c in
> >> > element.clause_expr.element.get_children()]
> >> >  return '%s / %s' % (params[0], params[1])
> >> >
> >> >
> >> > @compiles(IntDiv, 'clickhouse')
> >> > def visit_div_ch(element, compiler, **kwargs):
> >> >  return 'intDiv(%s)' % compiler.process(element.clause_expr.element)
> >> >
> >> > What I want to do:
> >> >
> >> > class BitAdd(CustomOp):
> >> >  pass
> >> >
> >> > @compiles(BitAdd)
> >> > def visit_bit_add_default(element, compiler, **kwargs):
> >> >  return '%s & %s' % (element.left, element.right)
> >> >
> >> >
> >> > @compiles(IntDiv, 'clickhouse')
> >> > def visit_bit_add_ch(element, compiler, **kwargs):
> >> >  return 'bitAnd(%s, %s)' % (element.left, element.right)
> >> >
> >> > Statement looks like that:
> >> > some_col.op('&')(some_int_flag)
> >> >
> >> >
> >> > I understand that I can define generic function and use it instead of
> >> > `.op('&')`, but at first I want to find another way.
> >>
> >> So in this case you are looking to override the compilation for
> >> BinaryElement in any case since you want to run a function around them
> >> in one case, that is the whole left/operator/right object.
> >>
> >> from sqlalchemy.sql.expression import BinaryExpression
> >> @compiles(BinaryExpression, "clickhouse")
> >> def visit_binary(element, compiler, **kw):
> >> if isinstance(element.operator, BitAdd):
> >> return "bitAnd(%s, %s)" % (compiler.process(element.left,
> >> **kw), compiler.process(element.right, **kw))
> >> else:
> >> return compiler.visit_binary(element, **kw)
> >>
> >> For the part where you want op('&') to be magic, it can't work that
> >> way, though you can get at the '&' operator directly but you normally
> >> need to use a subclass of Integer:
> >>
> >>
> >>
> http://docs.sqlalchemy.org/en/latest/core/custom_types.html#redefining-and-creating-new-operators
> >>
> >> so here you'd be saying:
> >>
> >> from sqlalchemy import Integer
> >>
> >> class MyInt(Integer):
> >> class comparator_factory(Integer.Comparator):
> >> def __and__(self, other):
> >> return BitAnd()(self.expr, other)
> >>
> >> then you'd use the MyInt datatype in your mappings where you want your
> >> special operator to take place.
> >>
> >> If you want the base Integer to do this, you'd need to monkeypatch the
> >> comparator, this is less recommended as it can cause surprises:
> >>
> >>
> >> class comparator_factory_mixin(object):
> >> def __and__(self, other):
> >> return BitAnd()(self.expr, other)
> >>
> >> Integer.Comparator.__bases__ = (comparator_factory_mixin, ) +
> >> Integer.Comparator.__bases__
> >>
> >>
> >>
> >>
> >>
> >> > --
> >> > SQLAlchemy -
> >> > The Python SQL Toolkit and Object Relational Mapper
> >> >
> >>

Re: [sqlalchemy] Redefine operator compilation for specific dialect

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

So, anyway, I must implement BitAnd class? Or I can check if 'element.op ==
&'  inside compile function?

чт, 19 апр. 2018 г., 16:31 Mike Bayer <mike...@zzzcomputing.com>:

> On Thu, Apr 19, 2018 at 5:21 AM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> > Hi guys.
> >
> > Is there any mechanism in SQLAlchemy which allows to redefine operator
> > compilation? I want to the same thing as already exists for functions.
> > An example for functions:
> >
> >
> > class IntDiv(GenericFunction):
> >  type = Integer
> >  package = 'adc_custom'
> >  name = 'div'
> >  identifier = 'div'
> >
> >
> > @compiles(IntDiv)
> > def visit_div_default(element, compiler, **kwargs):
> >  params = [compiler.process(c.self_group()) for c in
> > element.clause_expr.element.get_children()]
> >  return '%s / %s' % (params[0], params[1])
> >
> >
> > @compiles(IntDiv, 'clickhouse')
> > def visit_div_ch(element, compiler, **kwargs):
> >  return 'intDiv(%s)' % compiler.process(element.clause_expr.element)
> >
> > What I want to do:
> >
> > class BitAdd(CustomOp):
> >  pass
> >
> > @compiles(BitAdd)
> > def visit_bit_add_default(element, compiler, **kwargs):
> >  return '%s & %s' % (element.left, element.right)
> >
> >
> > @compiles(IntDiv, 'clickhouse')
> > def visit_bit_add_ch(element, compiler, **kwargs):
> >  return 'bitAnd(%s, %s)' % (element.left, element.right)
> >
> > Statement looks like that:
> > some_col.op('&')(some_int_flag)
> >
> >
> > I understand that I can define generic function and use it instead of
> > `.op('&')`, but at first I want to find another way.
>
> So in this case you are looking to override the compilation for
> BinaryElement in any case since you want to run a function around them
> in one case, that is the whole left/operator/right object.
>
> from sqlalchemy.sql.expression import BinaryExpression
> @compiles(BinaryExpression, "clickhouse")
> def visit_binary(element, compiler, **kw):
> if isinstance(element.operator, BitAdd):
> return "bitAnd(%s, %s)" % (compiler.process(element.left,
> **kw), compiler.process(element.right, **kw))
> else:
> return compiler.visit_binary(element, **kw)
>
> For the part where you want op('&') to be magic, it can't work that
> way, though you can get at the '&' operator directly but you normally
> need to use a subclass of Integer:
>
>
> http://docs.sqlalchemy.org/en/latest/core/custom_types.html#redefining-and-creating-new-operators
>
> so here you'd be saying:
>
> from sqlalchemy import Integer
>
> class MyInt(Integer):
> class comparator_factory(Integer.Comparator):
> def __and__(self, other):
> return BitAnd()(self.expr, other)
>
> then you'd use the MyInt datatype in your mappings where you want your
> special operator to take place.
>
> If you want the base Integer to do this, you'd need to monkeypatch the
> comparator, this is less recommended as it can cause surprises:
>
>
> class comparator_factory_mixin(object):
> def __and__(self, other):
> return BitAnd()(self.expr, other)
>
> Integer.Comparator.__bases__ = (comparator_factory_mixin, ) +
> Integer.Comparator.__bases__
>
>
>
>
>
> > --
> > 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...@

[sqlalchemy] Redefine operator compilation for specific dialect

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

Hi guys.

Is there any mechanism in SQLAlchemy which allows to redefine operator 
compilation? I want to the same thing as already exists for functions.
An example for functions:


class IntDiv(GenericFunction):
 type = Integer
 package = 'adc_custom'
 name = 'div'
 identifier = 'div'


@compiles(IntDiv)
def visit_div_default(element, compiler, **kwargs):
 params = [compiler.process(c.self_group()) for c in 
element.clause_expr.element.get_children()]
 return '%s / %s' % (params[0], params[1])


@compiles(IntDiv, 'clickhouse')
def visit_div_ch(element, compiler, **kwargs):
 return 'intDiv(%s)' % compiler.process(element.clause_expr.element)

What I want to do:

class BitAdd(CustomOp):
 pass

@compiles(BitAdd)
def visit_bit_add_default(element, compiler, **kwargs):
 return '%s & %s' % (element.left, element.right)


@compiles(IntDiv, 'clickhouse')
def visit_bit_add_ch(element, compiler, **kwargs):
 return 'bitAnd(%s, %s)' % (element.left, element.right)

Statement looks like that:
some_col.op('&')(some_int_flag)


I understand that I can define generic function and use it instead of 
`.op('&')`, but at first I want to find another way.

-- 
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] Emit INSERT query with float values reducing number of decimal digits

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

You can specify column precision


пн, 5 мар. 2018 г. в 18:42, Ruben Di Battista :

> I have a table that is storing a huge amount of numerical details about my
> application. I have huge INSERT queries (also millions of rows for each of
> them) of float values that are made with core API, while the rest of
> application logic is ORM. The precision I need on each float is not big,
> two decimal digits is enough. I was thinking about reducing the volume of
> each query trying to emit INSERT queries directly with floats with a
> limited number of digits.
>
> To better explain, what I have now:
>
> 2018-02-27 15:42:42,253 - INFO - sqlalchemy.engine.base.Engine - base - 
> INSERT INTO passage_data (time, azimuth, elevation, doppler, slant, 
> passage_id) VALUES (%s, %s, %s, %s, %s, %s)
> 2018-02-27 15:42:42,253 - INFO - sqlalchemy.engine.base.Engine - base - 
> ((datetime.datetime(2018, 2, 28, 9, 15, 1, 63752), 36.496509331447605, 
> 1.2611702704468281e-08, 1.8684261690630526e-05, 2585.2088123511294,
> 14L), (datetime.datetime(2018, 2, 28, 9, 15, 2, 63752), 36.60203082082902, 
> 0.05023170345696884, 1.8660941451945825e-05, 2579.610896504773, 14L), 
> (datetime.datetime(2018, 2, 28, 9, 15, 3, 63752), 36.70799537639
> 969, 0.10050903526080569, 1.8637443765193708e-05, 2574.019998391197, 14L), 
> (datetime.datetime(2018, 2, 28, 9, 15, 4, 63752), 36.81440550887081, 
> 0.15083186067307605, 1.8613767355120377e-05, 2568.4361714766696, 1
> 4L), (datetime.datetime(2018, 2, 28, 9, 15, 5, 63752), 36.921263739618595, 
> 0.20120002680977034, 1.8589910938324356e-05, 2562.8594695400034, 14L), 
> (datetime.datetime(2018, 2, 28, 9, 15, 6, 63752), 37.02857260192
> 68, 0.2516133749806172, 1.8565873222763086e-05, 2557.289946693618, 14L), 
> (datetime.datetime(2018, 2, 28, 9, 15, 7, 63752), 37.136334642317216, 
> 0.3020717396984603, 1.8541652907053198e-05, 2551.727657483191, 14L)
> , (datetime.datetime(2018, 2, 28, 9, 15, 8, 63752), 37.244552421268985, 
> 0.3525749481349419, 1.85172486800893e-05, 2546.172656937015, 14L)  ... 
> displaying 10 of 562 total bound parameter sets ...  (datetime.date
> time(2018, 2, 28, 9, 24, 21, 63752), 147.15663042736335, 0.08665444173526915, 
> -1.8705826550795816e-05, 2564.7906146486603, 14L), (datetime.datetime(2018, 
> 2, 28, 9, 24, 22, 63752), 147.26246413819342, 0.03587018
> 554496605, -1.873029089372862e-05, 2570.402148180257, 14L))
>
>
> What I was thinking to reduce the volume, was to coerce the float values
> to 2 decimal digits, and emit a SQL insert with values that are smaller in
> terms of string bytes. E.g.
>
> datetime(datetime.datetime(2018, 2, 28, 9, 15, 1, 63752), 36.50, 0.00, 0.00, 
> 2585.21,
> 14L)
>
> # Instead of:
>
> datetime(datetime.datetime(2018, 2, 28, 9, 15, 1, 63752), 36.496509331447605, 
> 1.2611702704468281e-08, 1.8684261690630526e-05, 2585.2088123511294,
>
> 14L)
>
>
> How should I attack this problem? I would like to keep the `Float` type
> for the column, but to emit "smaller queries". Do I need a custom type?
>
>
>
> --
> 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] pass additional kwargs to execute into BulkUD _do_exec method

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

Hello Mike.
Finally got to this. Works perfectly with synchronize_session equals False
or 'evaluate'.

Got a problem when synchronize_session='fetch'. In that case used
BulkFetch._do_pre_synchronize (as you know :)). There is query instance
with set `_bind_id` attribute, but it don't passed through `execute`.

Here is an example that can illustrate that problem:
https://gist.github.com/aCLr/a992ca92138aeee86bf9432693be6d6c
It contains Query and Session implementations, which used in my project:
MultiBoundQuery and MultiBoundSession.
Also, it contains `delete` and `update` execution and checking for update
and delete operation. Tracebacks included.



вт, 13 февр. 2018 г. в 20:37, Антонио Антуан <a.ch@gmail.com>:

> I understand. Excuse me, drown in work. Going to test your code in a
> couple of days.
>
> вт, 13 февр. 2018 г., 20:32 Mike Bayer <mike...@zzzcomputing.com>:
>
>> i don't plan to move on this until I get feedback.
>>
>> On Tue, Feb 6, 2018 at 7:32 PM, Mike Bayer <mike...@zzzcomputing.com>
>> wrote:
>> > code review at
>> https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/656
>> > and ideally would include basic ShardedQuery support
>> >
>> > On Tue, Feb 6, 2018 at 7:23 PM, Mike Bayer <mike...@zzzcomputing.com>
>> wrote:
>> >> On Tue, Feb 6, 2018 at 5:09 PM, Антонио Антуан <a.ch@gmail.com>
>> wrote:
>> >>> Hello Mike!
>> >>> First of all, thank you for your help with this problem. Me and my
>> crew
>> >>> appreciate it.
>> >>>
>> >>>
>> >>> I have a question in case of `identity_token` for `update` and
>> `delete`
>> >>> methods of `Query` instances.
>> >>>
>> >>> If we take a look on sqlalchemy.orm.persistence.BulkDelete._do_exec,
>> we see,
>> >>> that there is no additonal kwargs passed to `execute` method. So I
>> don't see
>> >>> any way to pass additional kwargs to Session.get_bind. Here is code
>> of 1.0
>> >>> version:
>> >>>
>> >>> def _do_exec(self):
>> >>> delete_stmt = sql.delete(self.primary_table,
>> >>>  self.context.whereclause)
>> >>>
>> >>> self.result = self.query.session.execute(
>> >>> delete_stmt,
>> >>> params=self.query._params,
>> >>> mapper=self.mapper,
>> >>> # need to pass here additional kwargs
>> >>> )
>> >>> self.rowcount = self.result.rowcount
>> >>>
>> >>> Code from master branch (almost the same):
>> >>>
>> >>> def _execute_stmt(self, stmt):
>> >>> self.result = self.query.session.execute(
>> >>> stmt, params=self.query._params,
>> >>> mapper=self.mapper)
>> >>> self.rowcount = self.result.rowcount
>> >>>
>> >>>
>> >>>
>> >>>
>> >>> As you may remember, I have some kind of `CustomQuery` with `set_bind`
>> >>> method (like the same into
>> sqlalchemy.ext.horizontal_shard.ShardedQuery). So
>> >>> I have `_bind_id` attribute within instances of that class.
>> >>> My question is: Is there any way to pass `self.query._bind_id` to
>> >>> `self.query.session.execute` (see above)?
>> >>
>> >> for bulk operations to work with sharding it would need to be able to
>> >> emit the UPDATE or DELETE statement across multiple shards and work in
>> >> a similar way to ShardedQuery._execute_and_instances, giving it the
>> >> chance to consult with query_chooser.
>> >>
>> >> can you please see if you can work with this? thanks
>> >>
>> >> diff --git a/lib/sqlalchemy/orm/persistence.py
>> >> b/lib/sqlalchemy/orm/persistence.py
>> >> index dc0ae1c38..6c55dee92 100644
>> >> --- a/lib/sqlalchemy/orm/persistence.py
>> >> +++ b/lib/sqlalchemy/orm/persistence.py
>> >> @@ -1327,9 +1327,8 @@ class BulkUD(object):
>> >>  self._do_post()
>> >>
>> >>  def _execute_stmt(self, stmt):
>> >> -self.result = self.query.session.execute(
>> >> -stmt, params=self.query._params,
>> >> -mapper=self.mapper)
>> >> +self.result = self.query._execute_crud(stmt, self.mapper)
>> >> +
>

Re: [sqlalchemy] How right use session/scoped_session in web app?

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

Maybe you try to change same rows within several concurrent requests? I do
not use mssql, but in postgres it is normal.
Anyway it is normal behavior. One transaction tries to change a row,
another one tries to change the same. When the first transaction commited,
the second can continue its job.

пт, 16 февр. 2018 г. в 13:02, <eugene.de...@gmail.com>:

> This option does not suit me.
> My app based on CherryPy 3.2.2.
> I add more logging, and all session closing by
> session.close()
> in finally section.
> I can't understand why this not work...
>
> четверг, 15 февраля 2018 г., 18:07:49 UTC+3 пользователь Антонио Антуан
> написал:
>>
>> You need just that:
>> from proj.core import Session
>>
>> @app.teardown_request
>> def clear_session():
>> Session.remove()
>>
>> Session created with scoper_session, of course. We do not use
>> flask-sqlalchemy package, just flask and separated sqlalchemy.
>>
>> чт, 15 февр. 2018 г., 16:28 Simon King <si...@simonking.org.uk>:
>>
>>> When you say your first execute function doesn't work, what do you
>>> mean? Do you get an error? Do the results not show up in the database?
>>>
>>> I don't think there's any need for session.begin()
>>>
>>>
>>> http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.begin
>>>
>>> Simon
>>>
>>> On Thu, Feb 15, 2018 at 12:33 PM,  <eugene...@gmail.com> wrote:
>>> > Hello, Simon!
>>> > Where did you read that I was using Flask?
>>> > I just write about it like example few posts ago.
>>> > Anyway.
>>> > I try another variant without decorator - just execute function
>>> > def execute(statement, **kwargs):
>>> > session = SESSION()
>>> > session.begin(subtransactions=True)
>>> > kwargs['tries'] = kwargs.get('tries', 0)
>>> > fetch = kwargs.get('fetch', 'all')
>>> > try:
>>> > result = session.execute(statement)
>>> > if fetch == 'all':
>>> > return result.fetchall()
>>> > elif fetch is False:
>>> > return True
>>> > elif fetch == 'count':
>>> > return result
>>> > return result.fetchone()
>>> > except ResourceClosedError:
>>> > session.rollback()
>>> > session.close()
>>> > time.sleep(.1)
>>> > print('try number {}'.format(kwargs['tries']))
>>> > if kwargs['tries'] < 100:
>>> > kwargs['tries'] += 1
>>> > return execute(statement, **kwargs)
>>> > return list()
>>> > except Exception as e:
>>> > session.rollback()
>>> > session.close()
>>> > print(e)
>>> > print(statement)
>>> > finally:
>>> > if session.is_active:
>>> > session.commit()
>>> > session.close()
>>> > but that not help too. Idk why that not work.
>>> >
>>> > I try another variant:
>>> >
>>> > def execute(statement, **kwargs):
>>> > fetch = kwargs.get('fetch', 'all')
>>> > with engine.connect() as session:
>>> > result = session.execute(statement)
>>> > if fetch == 'all':
>>> > return result.fetchall()
>>> > elif fetch is False:
>>> > return list()
>>> > elif fetch == 'count':
>>> > return result
>>> > return result.fetchone()
>>> >
>>> > But same error. That variant must always close connection after
>>> return, but
>>> > problem in something another.
>>> >
>>> >
>>> >
>>> > четверг, 15 февраля 2018 г., 13:21:51 UTC+3 пользователь Simon King
>>> написал:
>>> >>
>>> >> Personally I wouldn't use decorators for this. I would make every
>>> >> function that needs to interact with the database take an explicit
>>> >> session parameter, and I would use the facilities of the web framework
>>> >> to create the session at the beginning of the request and close it at
>>> >> the end. I've never used Flask, but I see it has a "signals" mechanism
>>> >> (http://flask.pocoo.org/docs/0.12/a

Re: [sqlalchemy] How right use session/scoped_session in web app?

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

You need just that:
from proj.core import Session

@app.teardown_request
def clear_session():
Session.remove()

Session created with scoper_session, of course. We do not use
flask-sqlalchemy package, just flask and separated sqlalchemy.

чт, 15 февр. 2018 г., 16:28 Simon King :

> When you say your first execute function doesn't work, what do you
> mean? Do you get an error? Do the results not show up in the database?
>
> I don't think there's any need for session.begin()
>
>
> http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.begin
>
> Simon
>
> On Thu, Feb 15, 2018 at 12:33 PM,   wrote:
> > Hello, Simon!
> > Where did you read that I was using Flask?
> > I just write about it like example few posts ago.
> > Anyway.
> > I try another variant without decorator - just execute function
> > def execute(statement, **kwargs):
> > session = SESSION()
> > session.begin(subtransactions=True)
> > kwargs['tries'] = kwargs.get('tries', 0)
> > fetch = kwargs.get('fetch', 'all')
> > try:
> > result = session.execute(statement)
> > if fetch == 'all':
> > return result.fetchall()
> > elif fetch is False:
> > return True
> > elif fetch == 'count':
> > return result
> > return result.fetchone()
> > except ResourceClosedError:
> > session.rollback()
> > session.close()
> > time.sleep(.1)
> > print('try number {}'.format(kwargs['tries']))
> > if kwargs['tries'] < 100:
> > kwargs['tries'] += 1
> > return execute(statement, **kwargs)
> > return list()
> > except Exception as e:
> > session.rollback()
> > session.close()
> > print(e)
> > print(statement)
> > finally:
> > if session.is_active:
> > session.commit()
> > session.close()
> > but that not help too. Idk why that not work.
> >
> > I try another variant:
> >
> > def execute(statement, **kwargs):
> > fetch = kwargs.get('fetch', 'all')
> > with engine.connect() as session:
> > result = session.execute(statement)
> > if fetch == 'all':
> > return result.fetchall()
> > elif fetch is False:
> > return list()
> > elif fetch == 'count':
> > return result
> > return result.fetchone()
> >
> > But same error. That variant must always close connection after return,
> but
> > problem in something another.
> >
> >
> >
> > четверг, 15 февраля 2018 г., 13:21:51 UTC+3 пользователь Simon King
> написал:
> >>
> >> Personally I wouldn't use decorators for this. I would make every
> >> function that needs to interact with the database take an explicit
> >> session parameter, and I would use the facilities of the web framework
> >> to create the session at the beginning of the request and close it at
> >> the end. I've never used Flask, but I see it has a "signals" mechanism
> >> (http://flask.pocoo.org/docs/0.12/api/#signals) with request_started
> >> and request_finished events. You could use the request_started signal
> >> to create the session and attach it to the request object, and clean
> >> it up in request_finished.
> >>
> >> Looking at the Flask docs, I think you could also write a simple
> >> extension. They have a SQLite example at
> >> http://flask.pocoo.org/docs/0.12/extensiondev/#the-extension-code,
> >> which you ought to be able to adapt for SQLAlchemy. The app context is
> >> per-request, so it should be safe
> >> (http://flask.pocoo.org/docs/0.12/appcontext/#locality-of-the-context).
> >>
> >> Hope that helps,
> >>
> >> Simon
> >>
> >> On Thu, Feb 15, 2018 at 9:52 AM,   wrote:
> >> > Hello, Simon!
> >> > So what better way?
> >> > Something like this?
> >> > SESSION = sessionmaker(bind=engine, autocommit=True)
> >> >
> >> >
> >> >
> >> > @decorator_with_args
> >> > def session_decorator(func, default=None):
> >> > def wrapper(*a, **kw):
> >> > session = SESSION()
> >> > session.begin(subtransactions=True)
> >> > if 'session' not in kw:
> >> > kw['session'] = session
> >> > try:
> >> > return func(*a, **kw)
> >> > except Exception as e:
> >> > session.rollback()
> >> > logging.error(e)
> >> > return default
> >> > finally:
> >> > if session.is_active:
> >> > session.commit()
> >> > session.close()
> >> > return wrapper
> >> >
> >> > I try create additional function
> >> >
> >> > @session_decorator()
> >> > def execute(statement, **kwargs):
> >> > session = kwargs['session']
> >> > fetch = kwargs.get('fetch', 'all')
> >> > result = session.execute(statement)
> >> > if fetch == 'all':
> >> > return result.fetchall()
> >> > elif fetch is False:
> >> > 

Re: [sqlalchemy] pass additional kwargs to execute into BulkUD _do_exec method

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

I understand. Excuse me, drown in work. Going to test your code in a couple
of days.

вт, 13 февр. 2018 г., 20:32 Mike Bayer <mike...@zzzcomputing.com>:

> i don't plan to move on this until I get feedback.
>
> On Tue, Feb 6, 2018 at 7:32 PM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
> > code review at https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/656
> > and ideally would include basic ShardedQuery support
> >
> > On Tue, Feb 6, 2018 at 7:23 PM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
> >> On Tue, Feb 6, 2018 at 5:09 PM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> >>> Hello Mike!
> >>> First of all, thank you for your help with this problem. Me and my crew
> >>> appreciate it.
> >>>
> >>>
> >>> I have a question in case of `identity_token` for `update` and `delete`
> >>> methods of `Query` instances.
> >>>
> >>> If we take a look on sqlalchemy.orm.persistence.BulkDelete._do_exec,
> we see,
> >>> that there is no additonal kwargs passed to `execute` method. So I
> don't see
> >>> any way to pass additional kwargs to Session.get_bind. Here is code of
> 1.0
> >>> version:
> >>>
> >>> def _do_exec(self):
> >>> delete_stmt = sql.delete(self.primary_table,
> >>>  self.context.whereclause)
> >>>
> >>> self.result = self.query.session.execute(
> >>> delete_stmt,
> >>> params=self.query._params,
> >>> mapper=self.mapper,
> >>> # need to pass here additional kwargs
> >>> )
> >>> self.rowcount = self.result.rowcount
> >>>
> >>> Code from master branch (almost the same):
> >>>
> >>> def _execute_stmt(self, stmt):
> >>> self.result = self.query.session.execute(
> >>> stmt, params=self.query._params,
> >>> mapper=self.mapper)
> >>> self.rowcount = self.result.rowcount
> >>>
> >>>
> >>>
> >>>
> >>> As you may remember, I have some kind of `CustomQuery` with `set_bind`
> >>> method (like the same into
> sqlalchemy.ext.horizontal_shard.ShardedQuery). So
> >>> I have `_bind_id` attribute within instances of that class.
> >>> My question is: Is there any way to pass `self.query._bind_id` to
> >>> `self.query.session.execute` (see above)?
> >>
> >> for bulk operations to work with sharding it would need to be able to
> >> emit the UPDATE or DELETE statement across multiple shards and work in
> >> a similar way to ShardedQuery._execute_and_instances, giving it the
> >> chance to consult with query_chooser.
> >>
> >> can you please see if you can work with this? thanks
> >>
> >> diff --git a/lib/sqlalchemy/orm/persistence.py
> >> b/lib/sqlalchemy/orm/persistence.py
> >> index dc0ae1c38..6c55dee92 100644
> >> --- a/lib/sqlalchemy/orm/persistence.py
> >> +++ b/lib/sqlalchemy/orm/persistence.py
> >> @@ -1327,9 +1327,8 @@ class BulkUD(object):
> >>  self._do_post()
> >>
> >>  def _execute_stmt(self, stmt):
> >> -self.result = self.query.session.execute(
> >> -stmt, params=self.query._params,
> >> -mapper=self.mapper)
> >> +self.result = self.query._execute_crud(stmt, self.mapper)
> >> +
> >>  self.rowcount = self.result.rowcount
> >>
> >>  @util.dependencies("sqlalchemy.orm.query")
> >> diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
> >> index 4f7b22cc9..91adfb79d 100644
> >> --- a/lib/sqlalchemy/orm/query.py
> >> +++ b/lib/sqlalchemy/orm/query.py
> >> @@ -2901,6 +2901,12 @@ class Query(object):
> >>  result = conn.execute(querycontext.statement, self._params)
> >>  return loading.instances(querycontext.query, result,
> querycontext)
> >>
> >> +def _execute_crud(self, stmt, mapper):
> >> +conn = self._connection_from_session(
> >> +mapper=mapper, clause=stmt, close_with_result=True)
> >> +
> >> +return conn.execute(stmt, self._params)
> >> +
> >>  def _get_bind_args(self, querycontext, fn, **kw):
> >>  return fn(
> >>  mapper=self._bind_mapper(),
> >>
> >>
> >

[sqlalchemy] pass additional kwargs to execute into BulkUD _do_exec method

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

Hello Mike!
First of all, thank you for your help with this 
 problem. 
Me and my crew appreciate it.


I have a question in case of `identity_token` for `update` and `delete` 
methods of `Query` instances.

If we take a look on sqlalchemy.orm.persistence.BulkDelete._do_exec, we 
see, that there is no additonal kwargs passed to `execute` method. So I 
don't see any way to pass additional kwargs to Session.get_bind. Here is 
code of 1.0 version:

def _do_exec(self):
delete_stmt = sql.delete(self.primary_table,
 self.context.whereclause)

self.result = self.query.session.execute(
delete_stmt,
params=self.query._params,
mapper=self.mapper,
# need to pass here additional kwargs
)
self.rowcount = self.result.rowcount

Code from master branch (almost the same):

def _execute_stmt(self, stmt):
self.result = self.query.session.execute(
stmt, params=self.query._params,
mapper=self.mapper)
self.rowcount = self.result.rowcount




As you may remember, I have some kind of `CustomQuery` with `set_bind` 
method (like the same into sqlalchemy.ext.horizontal_shard.ShardedQuery). 
So I have `_bind_id` attribute within instances of that class.
My question is: Is there any way to pass `self.query._bind_id` to 
`self.query.session.execute` (see above)?

-- 
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: Not able to filter json column filter in flask-sqlalchemy

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

What is the version of your PostgreSQL?
Here is an example. Works perfectly for me (pg9.6 and pg10).
https://gist.github.com/aCLr/7d794eabbf972a60a15f40b2d3965508

чт, 7 дек. 2017 г. в 16:05, :

> Hi,
>
> I tried using direct  plain JSON:
>
> my model
> class Student(db.Model):
> __tablename__= 'students'
> id=db.Column(db.Integer, primary_key=True,autoincrement=True)
> name=db.Column(db.String(200))
> roll_no=db.Column(db.Integer)
> data_test=db.Column(db.JSON)
>
>
> I tried this:
> *a = Student.query.filter(cast(Student.__table__.c.data_test["foo"],
> String) =="bar").**first**()*
>
> *error*:
> sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does
> not exist: json -> unknown
> LINE 3: WHERE CAST((students.data_test -> 'foo') AS VARCHAR) = 'bar'
>^
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>  [SQL: 'SELECT students.id AS students_id, students.name AS
> students_name, students.roll_no AS students_roll_no, students.data_test AS
> students_data_test \nFROM students \nWHERE CAST((students.data_test ->
> %(data_test_1)s) AS VARCHAR) = %(param_1)s'] [parameters: {'param_1':
> 'bar', 'data_test_1': 'foo'}]
>
>
> tried this:
> *a = Student.query.filter(Student.data_test.op('->>')('foo') ==
> 'bar').first()*
>
> *error*:
> sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does
> not exist: json ->> unknown
> LINE 3: WHERE (students.data_test ->> 'foo') = 'bar'
>   ^
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>  [SQL: 'SELECT students.id AS students_id, students.name AS
> students_name, students.roll_no AS students_roll_no, students.data_test AS
> students_data_test \nFROM students \nWHERE (students.data_test ->>
> %(data_test_1)s) = %(param_1)s \n LIMIT %(param_2)s'] [parameters:
> {'param_1': 'bar', 'data_test_1': 'foo', 'param_2': 1}]
>
>
>
> is this some versioning issue?
> I am not able to understand, where am i going wrong?
>
> Thanks for any help in this
>
> --
> 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: Not able to filter json column filter in flask-sqlalchemy

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

you can call "->>" (and any other) operator directly:
Student.data_test.op('->>')('foo') == 'bar'

if you want to call cast use this:
cast(Student.data_test['foo'], String) == 'bar'


"c" (the shortcut for "columns") allows for "Table" instances. If you use
declarative style, you can not to use it directly, but if you want:

Student.__table__.c.data_test

Should repeat: there is no need to use __table__ attribute directly with
declarative style in most cases.

чт, 7 дек. 2017 г., 9:15 :

> Hi Mike,
>
> as you said I tried this:
>
> *from sqlalchemy.dialects import postgresql *
>
>
>
> *class Student(db.Model): # ...
>  data_test=db.Column(postgresql.JSON) *
>
>
> and I tried querying like this:
>
> *a = Student.query.filter(Student.data_test["foo"].astext =="bar").first()*
>
> tried this as well:
> *a =
> Student.query.filter(Student.data_test["foo"].astext.cast(String)=="bar").first()*
>
> But still I am getting error:
> Traceback (most recent call last):
>   File "sqlalchemyjson.py", line 46, in 
> a = Student.query.filter(Student.data_test["foo"].astext
> =="bar").first()
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line
> 2690, in first
> ret = list(self[0:1])
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line
> 2482, in __getitem__
> return list(res)
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line
> 2790, in __iter__
> return self._execute_and_instances(context)
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line
> 2813, in _execute_and_instances
> result = conn.execute(querycontext.statement, self._params)
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py",
> line 945, in execute
> return meth(self, multiparams, params)
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py",
> line 263, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py",
> line 1053, in _execute_clauseelement
> compiled_sql, distilled_params
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py",
> line 1189, in _execute_context
> context)
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py",
> line 1393, in _handle_dbapi_exception
> exc_info
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py",
> line 203, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=cause)
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py",
> line 1182, in _execute_context
> context)
>   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py",
> line 470, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does
> not exist: json ->> unknown
> LINE 3: WHERE (students.data_test ->> 'foo') = 'bar'
>
>
>
> When I use plain json, what should be my query?
>
> *a = Student.query.filter(cast(**Student**.c.data_test["foo"], String)
> =="bar").first()*
>
> I am getting this:
> AttributeError: type object 'Student' has no attribute 'c'
>
>
>
> Where am I going wrong?
>
> --
> 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] Disable `session_identity_map` for `_instance_processor`

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

чт, 23 нояб. 2017 г. в 20:27, Mike Bayer <mike...@zzzcomputing.com>:

> On Thu, Nov 23, 2017 at 8:44 AM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> >
> >> A Query can have lots of entities in it, and if you're doing sharding a
> >> single result set can refer to any number of shard identifiers within
> >> not just a single result set but within a single row; they might have
> >> come from dozens of different databases at once
> >
> > In my case it is not possible: all entities in query can be gotten only
> from
> > one particular shard. We have totally the same database structure for
> each
> > shard. The difference is just data stored into database. No `shard_id` or
> > any other key as part of primary key for any table.
>
>
> so just to note, these aren't "shards", they're tenants.  you have a
> multi-tenant application, which is normally a really easy thing.  but
> you have a few side applications that want to "cheat" and use the
> per-tenant object model across multiple tenants simultaneously in the
> scope of a single Session.
>
> > If I want to make query
> > for particular database I always want to retrieve data ONLY from that
> > database. And even more than that: ONLY one database during one session
> > transaction (or, in other words, one http-request to our app).
>
> if you have one "tenant id" per HTTP request, the standard HTTP
> request pattern is one Session per request.There's no problem in
> that case.  You mentioned you have some non-flask applications that
> want to communicate with multiple tenants in one Session.
>

Yes, you're right. We have some offline actions, when we want to ask each
tenant about something specific.
I see, that currently the most safe way is to call `commit`, `rollback`,
`remove` or `expunge_all` on session instance: all this methods drops
identity map. Please let me know if I'm wrong.

>
> >
> >> This could only be suited with a very open plugin point that is
> carefully
> >> architected, tested, and documented and I don't have the resources to
> >> envision this for a short-term use case.
> >
> > I've seen a lot of questions (for example in stackoverflow) "how to
> manage
> > several binds with sqlalchemy" and all answers are "Use separated
> sessions".
>
> it's probably the best answer feel free to show specifics and I can
> determine if their request would fit this hypothetical feature
> otherwise.
>
> > I really not understand, what is the problem to implement "multibound"
> > session.
>
> The Session has always supported multiple binds.  There are two
> levels supported.  One is per table/mapper:
>
> http://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html#simple-vertical-partitioning
>
> you could probably adapt your multiple tenants into individual
> mappings if there are a limited number, see the approach at
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName.
>
> The other level is per primary key, that is, each primary key in the
> identity map has a different bind.  that's horizontal sharding.
>
> You're looking for a new level, which is, multiple binds for *one*
> primary key.   This is an intricate feature request that is feasible
> but not in the short term.
>
>
> > Don't have ideal vocabulary to explain how our team like it :)
> > Currently I see only one problem: loading instances. Of course, after
> fixing
> > other problems may appear...
> >
> > I can (and want) make it as part of SQLAlchemy library. Fully-tested
> part,
> > of course. If you say that it is bad idea, ok then.
>
> it's not a bad idea.  It's just difficult, and I can't do it right now.
>
>
> >I can make it as a
> > plugin, but there is a problem: functions in `loading` module are
> monolithic
> > and it needs some refactor for the plugin. May I suggest refactor as pull
> > request?
>
> you can do a pull request but note that the PR process for SQLAlchemy
> is not quick.   90% of code-related pull requests I get have no tests,
> no documentation, or anything.More elaborate feature requests
> typically involve that I end up doing the whole thing myself in any
> case, using the submitter's original code as just a sketch, which
> means that more involved PRs are usually just another form of feature
> request.These PRs are almost always for Core level features as the
> Core is easier for outside contributors to work on.   ORM-level
> contributions are extremely rare these days but of course I welcome
> contributors for the ORM.
>
> And if so: could it be mer

Re: [sqlalchemy] Disable `session_identity_map` for `_instance_processor`

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

See, that `Query.get` using `get_from_identity` instead of `instances` with
key, constructed into `_get_impl`.
Could you point to another places where same problem (described into my
first message) can be appeared?

чт, 23 нояб. 2017 г. в 16:44, Антонио Антуан <a.ch@gmail.com>:

>
> > A Query can have lots of entities in it, and if you're doing sharding a
> > single result set can refer to any number of shard identifiers within
> > not just a single result set but within a single row; they might have
> > come from dozens of different databases at once
>
> In my case it is not possible: all entities in query can be gotten only
> from one particular shard. We have totally the same database structure for
> each shard. The difference is just data stored into database. No `shard_id`
> or any other key as part of primary key for any table. If I want to make
> query for particular database I always want to retrieve data ONLY from that
> database. And even more than that: ONLY one database during one session
> transaction (or, in other words, one http-request to our app).
>
>
> > This could only be suited with a very open plugin point that is carefully
> > architected, tested, and documented and I don't have the resources to
> > envision this for a short-term use case.
>
> I've seen a lot of questions (for example in stackoverflow) "how to manage
> several binds with sqlalchemy" and all answers are "Use separated
> sessions". I really not understand, what is the problem to implement
> "multibound" session. I make it in my project and it's really beautiful,
> clear and... Don't have ideal vocabulary to explain how our team like it :)
> Currently I see only one problem: loading instances. Of course, after
> fixing other problems may appear...
>
> I can (and want) make it as part of SQLAlchemy library. Fully-tested part,
> of course. If you say that it is bad idea, ok then. I can make it as a
> plugin, but there is a problem: functions in `loading` module are
> monolithic and it needs some refactor for the plugin. May I suggest
> refactor as pull request? And if so: could it be merged not only for major
> release but for, at least, 1.0.* (yes, in our project we use 1.0.19 :) )?
> I really don't want (of course!) to copy entire `loading` module for
> additional logic of `identitykey` construction. But currently I do not see
> any other way to implement it for my project :(
>
> > when you query two different databases, you are using
> > two independent transactions in any case;
>
> So, what is the difference, if there are two transactions in any case? :)
>
> > I don't understand why you can't use independent sessions
>
> The first problem is `query` property of `Base` instances. If we use
> several sessions, we need to use the same amount of `Base` classes and,
> consequently, the same amount of models, don't we?
> Another problem for us is already existed code. We can use sessions
> registry, but it take a lot of month to override entire project. Another
> way:  append into `Query.__iter__` such a code:
> >> self.with_session(sessions.get(self._shard_id))
> >> return super(Query, self).__iter__()
> But it has no effect for UPDATE and INSERT queries. Also, I'm not sure
> that there is no problems in that way...
>
>
> I have one more thought. Don't you think that it is some kind of bug: I
> make query for one bind and got entity from another. Yes, that behavior is
> not foreseen by library. But from other point of view, library docs have
> examples how to use several binds within one session. So, problem may
> happens not only in my case.
>
> Anyway, can my suggestion (
> https://gist.github.com/aCLr/746f92dedb4d303a49033c0db22beced) has any
> effect for classic one-bound `Session`? If it can't, so, what's the
> problem? :)
>
>
> Excuse me for wasting your time.
> And excuse me if my suggestions are idiotic :)
>
> Appreciate your help.
>
> чт, 23 нояб. 2017 г. в 0:20, Mike Bayer <mike...@zzzcomputing.com>:
>
>> On Wed, Nov 22, 2017 at 4:56 AM, Антонио Антуан <a.ch@gmail.com>
>> wrote:
>> > Glad to see that you remember my messages :)
>> >
>> > I've dived into `loading` module and I see that currently it is really
>> > complicated to store additional data into pkey for each instance.
>> >
>> > Unfortunately, suggested solutions not good for my project.
>> > Also, I think that `shard` meaning in my case is not the same as usual.
>> >
>> > I want to describe structure of out project, maybe it can help.
>> >
>> > Here is definition of our databases structure:
>> > http://joxi.ru/nAyJV

Re: [sqlalchemy] Disable `session_identity_map` for `_instance_processor`

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

> A Query can have lots of entities in it, and if you're doing sharding a
> single result set can refer to any number of shard identifiers within
> not just a single result set but within a single row; they might have
> come from dozens of different databases at once

In my case it is not possible: all entities in query can be gotten only
from one particular shard. We have totally the same database structure for
each shard. The difference is just data stored into database. No `shard_id`
or any other key as part of primary key for any table. If I want to make
query for particular database I always want to retrieve data ONLY from that
database. And even more than that: ONLY one database during one session
transaction (or, in other words, one http-request to our app).

> This could only be suited with a very open plugin point that is carefully
> architected, tested, and documented and I don't have the resources to
> envision this for a short-term use case.

I've seen a lot of questions (for example in stackoverflow) "how to manage
several binds with sqlalchemy" and all answers are "Use separated
sessions". I really not understand, what is the problem to implement
"multibound" session. I make it in my project and it's really beautiful,
clear and... Don't have ideal vocabulary to explain how our team like it :)
Currently I see only one problem: loading instances. Of course, after
fixing other problems may appear...

I can (and want) make it as part of SQLAlchemy library. Fully-tested part,
of course. If you say that it is bad idea, ok then. I can make it as a
plugin, but there is a problem: functions in `loading` module are
monolithic and it needs some refactor for the plugin. May I suggest
refactor as pull request? And if so: could it be merged not only for major
release but for, at least, 1.0.* (yes, in our project we use 1.0.19 :) )?
I really don't want (of course!) to copy entire `loading` module for
additional logic of `identitykey` construction. But currently I do not see
any other way to implement it for my project :(

> when you query two different databases, you are using
> two independent transactions in any case;

So, what is the difference, if there are two transactions in any case? :)

> I don't understand why you can't use independent sessions

The first problem is `query` property of `Base` instances. If we use
several sessions, we need to use the same amount of `Base` classes and,
consequently, the same amount of models, don't we?
Another problem for us is already existed code. We can use sessions
registry, but it take a lot of month to override entire project. Another
way:  append into `Query.__iter__` such a code:
>> self.with_session(sessions.get(self._shard_id))
>> return super(Query, self).__iter__()
But it has no effect for UPDATE and INSERT queries. Also, I'm not sure that
there is no problems in that way...


I have one more thought. Don't you think that it is some kind of bug: I
make query for one bind and got entity from another. Yes, that behavior is
not foreseen by library. But from other point of view, library docs have
examples how to use several binds within one session. So, problem may
happens not only in my case.

Anyway, can my suggestion (
https://gist.github.com/aCLr/746f92dedb4d303a49033c0db22beced) has any
effect for classic one-bound `Session`? If it can't, so, what's the
problem? :)


Excuse me for wasting your time.
And excuse me if my suggestions are idiotic :)

Appreciate your help.

чт, 23 нояб. 2017 г. в 0:20, Mike Bayer <mike...@zzzcomputing.com>:

> On Wed, Nov 22, 2017 at 4:56 AM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> > Glad to see that you remember my messages :)
> >
> > I've dived into `loading` module and I see that currently it is really
> > complicated to store additional data into pkey for each instance.
> >
> > Unfortunately, suggested solutions not good for my project.
> > Also, I think that `shard` meaning in my case is not the same as usual.
> >
> > I want to describe structure of out project, maybe it can help.
> >
> > Here is definition of our databases structure:
> > http://joxi.ru/nAyJVvGiXMv0Dr.
> > We got master db and several geo databases. Catalogs like `users`,
> `groups`,
> > `offers` and other are replicating to geo databases, so that data is
> always
> > the same.
> > But also we have tables like `clicks` and `leads`. Each app instance
> > contains the data about them in database, related to its geo:
> > europe-instance into europe-db, usa-instance into usa-database and so on.
> > Periodically master-app pulls clicks and leads to master-database. Synced
> > objects always have different ids into master- and get-db, so it is ok.
> >
> > But one time project owner came and said: "I need SAAS".

Re: [sqlalchemy] Disable `session_identity_map` for `_instance_processor`

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

Glad to see that you remember my messages :)

I've dived into `loading` module and I see that currently it is really
complicated to store additional data into pkey for each instance.

Unfortunately, suggested solutions not good for my project.
Also, I think that `shard` meaning in my case is not the same as usual.

I want to describe structure of out project, maybe it can help.

Here is definition of our databases structure:
http://joxi.ru/nAyJVvGiXMv0Dr. <http://joxi.ru/nAyJVvGiXMv0Dr>
We got master db and several geo databases. Catalogs like `users`,
`groups`, `offers` and other are replicating to geo databases, so that data
is always the same.
But also we have tables like `clicks` and `leads`. Each app instance
contains the data about them in database, related to its geo:
europe-instance into europe-db, usa-instance into usa-database and so on.
Periodically master-app pulls clicks and leads to master-database. Synced
objects always have different ids into master- and get-db, so it is ok.

But one time project owner came and said: "I need SAAS".
We see, that in current structure it's very hard (and really ugly) to
implement saas-solution. Amount of `Base*`, `Session*`, `Order*` and other
models will be multiplied with tenants amount.

I discovered that I can override `get_bind` with another logic and it was
great: we can remove several `Base` classes, several Sessions and several
`Orders`.

Mechanism looks like this:
- we use one instance on each geo for all tenants.
- we create separated databases for each tenant: this will be multiplied
with tenants amount: http://joxi.ru/nAyJVvGiXMv0Dr.
<http://joxi.ru/nAyJVvGiXMv0Dr>
- we detect `tenant_id` using `request.host` (we use flask): each domain
binds with particular tenant;
- we store `tenant_id` into global storage.
- we use stored `tenant_id` into `Session.get_bind`:
https://gist.github.com/aCLr/9f578a2eeb225e7d65099ffb49aa8f3a
- into flask `teardown_request` we clear `tenant_id` storage and call
`Session.remove()`
- if we need to read from another get, just write `query =
query.set_shard_id(GEO)` `tenant_id`

For celery we use this:
https://gist.github.com/aCLr/d8c5ac38956947da092375b2f89d7b50
Clear to.

All this leads us only to pros, without cons: any developer has no need to
think about database chosing, just write code like there is only one
database. If you need to read from another geo-database, just call
`query.set_shard(GEO)`, tenant will be appended automatically to it.

Problems begin when we tried to test non-flask and non-celery scripts, like
cron tasks: we may want to query several tenant-databases during one
SQLA-transaction, somethins like in my first example:
https://gist.github.com/aCLr/ff9462b634031ee6bccbead8d913c41f
(`assert_got_correct_objects_with_remove`
and `assert_got_cached_objects_without_remove`). The result you know.


During writing this message, I found out, that we need only one additional
data for primary key: `connection.bind.url`. I see, that SQLA already have
it inside `_instance_processor`, so it exists inside `_instance`. I think,
that `identity_key` should be constructed (in my case) with this code:
https://gist.github.com/aCLr/746f92dedb4d303a49033c0db22beced. Clear, don't
you think so?



вт, 21 нояб. 2017 г. в 19:15, Mike Bayer <mike...@zzzcomputing.com>:

> I've looked to see how hard it would be to allow "supplemental"
> attributes to form part of the mapper's primary key tuple, and it
> would be pretty hard.   The "easy" part is getting the mapper to set
> itself up with some extra attributes that can deliver some kind of
> supplemental value to the identity key.  the harder part is then in
> loading.py where we get new rows from the DB and need this
> value...which means some whole new kind of system would need to
> deliver this for any arbitrary part of the result set given a mapping
> and the selectable we're looking at (keep in mind a Query can have
> lots of the same mapping in a single row with aliases).   This would
> be very complicated to implement and test.   I am not seeing any quick
> way to suit this use case, which has also not ever been requested
> before.
>
>
>
> On Tue, Nov 21, 2017 at 10:12 AM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
> > On Tue, Nov 21, 2017 at 7:39 AM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> >> Hi guys.
> >>
> >> I got this code example:
> >> https://gist.github.com/aCLr/ff9462b634031ee6bccbead8d913c41f.
> >>
> >> Here I make custom `Session` and custom `Query`. As you see, `Session`
> has
> >> several binds.
> >>
> >> Also, you can see that there are two functions:
> >> `assert_got_correct_objects_with_remove` and
> >> `assert_got_cached_objects_without_remove`.
> >>
> >> The first checks that we got correct result

[sqlalchemy] Disable `session_identity_map` for `_instance_processor`

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

Hi guys.

I got this code example: 
https://gist.github.com/aCLr/ff9462b634031ee6bccbead8d913c41f.

Here I make custom `Session` and custom `Query`. As you see, `Session` has 
several binds.

Also, you can see that there are two functions: 
`assert_got_correct_objects_with_remove` and 
`assert_got_cached_objects_without_remove`.

The first checks that we got correct results if `Session.remove` called.
The second checks, that we got incorrect results if `Session.remove` not 
called.

I understand, that behavior is correct: we don't remove session - so, we 
got same result from "cache-like" 
`sqlalchemy.orm.loading._instance_processor.session_identity_map`.

I want to avoid that mechanism and don't want to use `session_identity_map` 
for different binds. In ideal, bind should be used as part of key for 
`session_identity_map`, but I guess, that it is not possible.
Another way, acceptable for me: disable this mechanism. But I do not found 
ways to achieve this.
And the third option: construct instances manually. Looks like I should 
copy code from `loading` module and add that method to `CustomSession`:

def instances(self, cursor, __context=None):
context = __context
if context is None:
context = QueryContext(self)
return self._custom_instances(self, cursor, context)



def custom_instances(query, cursor, context):
 """copied from `loading.instances` code with disabled 
`session_identity_map`"""



The third way is the most ugly and I want to avoid it.

Could you help me with my hard choice and, maybe, suggest any other ways 
and options? :)

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] mapper existance checks possibly wrong

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

Yes, it works.
I'm grateful for your help :)

ср, 25 окт. 2017 г. в 16:56, Mike Bayer <mike...@zzzcomputing.com>:

> On Wed, Oct 25, 2017 at 9:25 AM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> > As I said befire, sqlalchemy version: 1.0.19 and code is here:
> > https://gist.github.com/aCLr/113ac292c05bdb01e964d8e9884d6e5f
>
> I apologize, did not see that link.
>
> Use the execute_and_instances() implemenation from
> lib/sqlalchemy/orm/query.py, which means here use _bind_mapper():
>
> def _execute_and_instances(self, querycontext):
> self._check_bound_to_shard()
> querycontext.attributes['shard_id'] = self._shard_id
> result = self._connection_from_session(
> mapper=self._bind_mapper(),
> shard_id=self._shard_id).execute(
> querycontext.statement,
> self._params
> )
> return self.instances(result, querycontext)
>
>
>
>
> >
> > Currently I can't use another version. In that case, I think that here is
> > the right decision:
> > "Looks like I have to check if "_mapper_zero()" returns real mapper.
> > Otherwise I should pass "None" to "_connection_from_session()" as value
> of
> > "mapper" argument"
> >
> > Am I right?
> >
> >
> > ср, 25 окт. 2017 г. в 16:18, Mike Bayer <mike...@zzzcomputing.com>:
> >>
> >> On Wed, Oct 25, 2017 at 6:52 AM, Антонио Антуан <a.ch@gmail.com>
> >> wrote:
> >> > Any news here?
> >>
> >> We would require a self-contained demonstration case that illustrates
> >> your error as well as complete information on what specific SQLAlchemy
> >> version you are targeting.  As mentioned in a different reply,
> >> _mapper_zero has been fixed in 1.1 to always return a mapper.  I
> >> would strongly advise targeting the 1.1 series for any new development
> >> as that's where issues can still be fixed.
> >>
> >>
> >>
> >>
> >>
> >> >
> >> > суббота, 21 октября 2017 г., 18:42:47 UTC+3 пользователь Антонио
> Антуан
> >> > написал:
> >> >>
> >> >> I see that it is not happened when "bind" passed directly to
> >> >> "sessionmaker"
> >> >>
> >> >> сб, 21 окт. 2017 г. в 18:33, Антонио Антуан <a.ch@gmail.com>:
> >> >>>
> >> >>>
> >> >>>
> >> >>> пятница, 20 октября 2017 г., 20:50:52 UTC+3 пользователь Mike Bayer
> >> >>> написал:
> >> >>>>
> >> >>>> On Fri, Oct 20, 2017 at 11:05 AM, Simon King <
> si...@simonking.org.uk>
> >> >>>> wrote:
> >> >>>> > The "is not None" is important when checking a variable that may
> >> >>>> > contain a ClauseElement, precisely because ClauseElement defines
> >> >>>> > that
> >> >>>> > __bool__ method.
> >> >>>> >
> >> >>>> > However, in Session.get_bind(), "mapper" is not supposed to
> contain
> >> >>>> > a
> >> >>>> > ClauseElement. It should either be an instance of
> >> >>>> > sqlalchemy.orm.mapper.Mapper, or None, in which case "if mapper:"
> >> >>>> > is a
> >> >>>> > valid and concise way to write it. Comparing to None might be
> >> >>>> > strictly
> >> >>>> > more accurate, but it shouldn't be necessary.
> >> >>>>
> >> >>>> agree, "mapper" means "mapper" and it is not supposed to be a
> >> >>>> ClauseElement.   This sounds like arguments are not being passed
> >> >>>> correctly somewhere.   Would need a demonstration script if
> something
> >> >>>> in SQLAlchemy itself is claimed to be making the mistake.
> >> >>>
> >> >>>
> >> >>> Ok, that code produces mentioned error:
> >> >>> https://gist.github.com/aCLr/113ac292c05bdb01e964d8e9884d6e5f
> >> >>> Traceback (most recent call last):
> >> >>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 100, in
> >> >>> 
> >> >>> Session.query(with_recursive).set_shard('default').all()
> >> &

Re: [sqlalchemy] mapper existance checks possibly wrong

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

As I said befire, sqlalchemy version: 1.0.19 and code is here:
https://gist.github.com/aCLr/113ac292c05bdb01e964d8e9884d6e5f

Currently I can't use another version. In that case, I think that here is
the right decision:
"Looks like I have to check if "_mapper_zero()" returns real mapper.
Otherwise I should pass "None" to "_connection_from_session()" as value of
"mapper" argument"

Am I right?


ср, 25 окт. 2017 г. в 16:18, Mike Bayer <mike...@zzzcomputing.com>:

> On Wed, Oct 25, 2017 at 6:52 AM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> > Any news here?
>
> We would require a self-contained demonstration case that illustrates
> your error as well as complete information on what specific SQLAlchemy
> version you are targeting.  As mentioned in a different reply,
> _mapper_zero has been fixed in 1.1 to always return a mapper.  I
> would strongly advise targeting the 1.1 series for any new development
> as that's where issues can still be fixed.
>
>
>
>
>
> >
> > суббота, 21 октября 2017 г., 18:42:47 UTC+3 пользователь Антонио Антуан
> > написал:
> >>
> >> I see that it is not happened when "bind" passed directly to
> >> "sessionmaker"
> >>
> >> сб, 21 окт. 2017 г. в 18:33, Антонио Антуан <a.ch@gmail.com>:
> >>>
> >>>
> >>>
> >>> пятница, 20 октября 2017 г., 20:50:52 UTC+3 пользователь Mike Bayer
> >>> написал:
> >>>>
> >>>> On Fri, Oct 20, 2017 at 11:05 AM, Simon King <si...@simonking.org.uk>
> >>>> wrote:
> >>>> > The "is not None" is important when checking a variable that may
> >>>> > contain a ClauseElement, precisely because ClauseElement defines
> that
> >>>> > __bool__ method.
> >>>> >
> >>>> > However, in Session.get_bind(), "mapper" is not supposed to contain
> a
> >>>> > ClauseElement. It should either be an instance of
> >>>> > sqlalchemy.orm.mapper.Mapper, or None, in which case "if mapper:"
> is a
> >>>> > valid and concise way to write it. Comparing to None might be
> strictly
> >>>> > more accurate, but it shouldn't be necessary.
> >>>>
> >>>> agree, "mapper" means "mapper" and it is not supposed to be a
> >>>> ClauseElement.   This sounds like arguments are not being passed
> >>>> correctly somewhere.   Would need a demonstration script if something
> >>>> in SQLAlchemy itself is claimed to be making the mistake.
> >>>
> >>>
> >>> Ok, that code produces mentioned error:
> >>> https://gist.github.com/aCLr/113ac292c05bdb01e964d8e9884d6e5f
> >>> Traceback (most recent call last):
> >>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 100, in
> >>> 
> >>> Session.query(with_recursive).set_shard('default').all()
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line
> 2654,
> >>> in all
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line
> 2802,
> >>> in __iter__
> >>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 28, in
> >>> _execute_and_instances
> >>> shard_id=self._shard_id).execute(
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line
> 2806,
> >>> in _connection_from_session
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line
> >>> 984, in connection
> >>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 40, in
> >>> get_bind
> >>> original_bind = super(RoutingSession, self).get_bind(mapper,
> clause)
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line
> >>> 1336, in get_bind
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line
> >>> 539, in __bool__
> >>> TypeError: Boolean value of this clause is not defined
> >>>
> >>>>
> >>>> >
> >>>> > Simon
> >>>>
> >>>> >
> >>>> >
> >>>> >
> >>>> > On Fri, Oct 20, 2017 at 3:17 PM, Антонио Антуан <a.ch...@gmail.com>
> >>>> > wrote:
> >>>> >&

Re: [sqlalchemy] mapper existance checks possibly wrong

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

Looks like I have to check if _mapper_zero() returns real mapper. Otherwise
I should pass None to "_connection_from_session()" as value of "mapper"
argument. Right?

ср, 25 окт. 2017 г. в 15:00, Антонио Антуан <a.ch@gmail.com>:

> As I mentioned before, " group_getter>".
>
> ср, 25 окт. 2017 г. в 14:19, Simon King <si...@simonking.org.uk>:
>
>> What does self._mapper_zero() return in your
>> RoutingQuery._execute_and_instances method?
>>
>> Simon
>>
>> On Wed, Oct 25, 2017 at 11:52 AM, Антонио Антуан <a.ch@gmail.com>
>> wrote:
>> > Any news here?
>> >
>> > суббота, 21 октября 2017 г., 18:42:47 UTC+3 пользователь Антонио Антуан
>> > написал:
>> >>
>> >> I see that it is not happened when "bind" passed directly to
>> >> "sessionmaker"
>> >>
>> >> сб, 21 окт. 2017 г. в 18:33, Антонио Антуан <a.ch@gmail.com>:
>> >>>
>> >>>
>> >>>
>> >>> пятница, 20 октября 2017 г., 20:50:52 UTC+3 пользователь Mike Bayer
>> >>> написал:
>> >>>>
>> >>>> On Fri, Oct 20, 2017 at 11:05 AM, Simon King <si...@simonking.org.uk
>> >
>> >>>> wrote:
>> >>>> > The "is not None" is important when checking a variable that may
>> >>>> > contain a ClauseElement, precisely because ClauseElement defines
>> that
>> >>>> > __bool__ method.
>> >>>> >
>> >>>> > However, in Session.get_bind(), "mapper" is not supposed to
>> contain a
>> >>>> > ClauseElement. It should either be an instance of
>> >>>> > sqlalchemy.orm.mapper.Mapper, or None, in which case "if mapper:"
>> is a
>> >>>> > valid and concise way to write it. Comparing to None might be
>> strictly
>> >>>> > more accurate, but it shouldn't be necessary.
>> >>>>
>> >>>> agree, "mapper" means "mapper" and it is not supposed to be a
>> >>>> ClauseElement.   This sounds like arguments are not being passed
>> >>>> correctly somewhere.   Would need a demonstration script if something
>> >>>> in SQLAlchemy itself is claimed to be making the mistake.
>> >>>
>> >>>
>> >>> Ok, that code produces mentioned error:
>> >>> https://gist.github.com/aCLr/113ac292c05bdb01e964d8e9884d6e5f
>> >>> Traceback (most recent call last):
>> >>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 100, in
>> >>> 
>> >>> Session.query(with_recursive).set_shard('default').all()
>> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line
>> 2654,
>> >>> in all
>> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line
>> 2802,
>> >>> in __iter__
>> >>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 28, in
>> >>> _execute_and_instances
>> >>> shard_id=self._shard_id).execute(
>> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line
>> 2806,
>> >>> in _connection_from_session
>> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line
>> >>> 984, in connection
>> >>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 40, in
>> >>> get_bind
>> >>>     original_bind = super(RoutingSession, self).get_bind(mapper,
>> clause)
>> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line
>> >>> 1336, in get_bind
>> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line
>> >>> 539, in __bool__
>> >>> TypeError: Boolean value of this clause is not defined
>> >>>
>> >>>>
>> >>>> >
>> >>>> > Simon
>> >>>>
>> >>>> >
>> >>>> >
>> >>>> >
>> >>>> > On Fri, Oct 20, 2017 at 3:17 PM, Антонио Антуан <a.ch...@gmail.com
>> >
>> >>>> > wrote:
>> >>>> >> Really, `mapper` contains this: > >>>> >> 0x7fe673fb0a50; group_getter

Re: [sqlalchemy] mapper existance checks possibly wrong

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

As I mentioned before, "".

ср, 25 окт. 2017 г. в 14:19, Simon King <si...@simonking.org.uk>:

> What does self._mapper_zero() return in your
> RoutingQuery._execute_and_instances method?
>
> Simon
>
> On Wed, Oct 25, 2017 at 11:52 AM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> > Any news here?
> >
> > суббота, 21 октября 2017 г., 18:42:47 UTC+3 пользователь Антонио Антуан
> > написал:
> >>
> >> I see that it is not happened when "bind" passed directly to
> >> "sessionmaker"
> >>
> >> сб, 21 окт. 2017 г. в 18:33, Антонио Антуан <a.ch@gmail.com>:
> >>>
> >>>
> >>>
> >>> пятница, 20 октября 2017 г., 20:50:52 UTC+3 пользователь Mike Bayer
> >>> написал:
> >>>>
> >>>> On Fri, Oct 20, 2017 at 11:05 AM, Simon King <si...@simonking.org.uk>
> >>>> wrote:
> >>>> > The "is not None" is important when checking a variable that may
> >>>> > contain a ClauseElement, precisely because ClauseElement defines
> that
> >>>> > __bool__ method.
> >>>> >
> >>>> > However, in Session.get_bind(), "mapper" is not supposed to contain
> a
> >>>> > ClauseElement. It should either be an instance of
> >>>> > sqlalchemy.orm.mapper.Mapper, or None, in which case "if mapper:"
> is a
> >>>> > valid and concise way to write it. Comparing to None might be
> strictly
> >>>> > more accurate, but it shouldn't be necessary.
> >>>>
> >>>> agree, "mapper" means "mapper" and it is not supposed to be a
> >>>> ClauseElement.   This sounds like arguments are not being passed
> >>>> correctly somewhere.   Would need a demonstration script if something
> >>>> in SQLAlchemy itself is claimed to be making the mistake.
> >>>
> >>>
> >>> Ok, that code produces mentioned error:
> >>> https://gist.github.com/aCLr/113ac292c05bdb01e964d8e9884d6e5f
> >>> Traceback (most recent call last):
> >>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 100, in
> >>> 
> >>> Session.query(with_recursive).set_shard('default').all()
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line
> 2654,
> >>> in all
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line
> 2802,
> >>> in __iter__
> >>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 28, in
> >>> _execute_and_instances
> >>> shard_id=self._shard_id).execute(
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line
> 2806,
> >>> in _connection_from_session
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line
> >>> 984, in connection
> >>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 40, in
> >>> get_bind
> >>> original_bind = super(RoutingSession, self).get_bind(mapper,
> clause)
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line
> >>> 1336, in get_bind
> >>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line
> >>> 539, in __bool__
> >>> TypeError: Boolean value of this clause is not defined
> >>>
> >>>>
> >>>> >
> >>>> > Simon
> >>>>
> >>>> >
> >>>> >
> >>>> >
> >>>> > On Fri, Oct 20, 2017 at 3:17 PM, Антонио Антуан <a.ch...@gmail.com>
> >>>> > wrote:
> >>>> >> Really, `mapper` contains this:  >>>> >> 0x7fe673fb0a50; group_getter>. But does it changes something? I
> >>>> >> already
> >>>> >> encountered this problem in my code, when I checked sqla-objects
> >>>> >> existance
> >>>> >> without "is not None", project was broken. Is it normal to omit
> that
> >>>> >> condition in sqlalchemy code?
> >>>> >>
> >>>> >> I use:
> >>>> >>>>> sqlalchemy.__version__
> >>>> >> '1.0.19'
> >>>> >>
> >>>> >> пятница, 20 октября 201

Re: [sqlalchemy] mapper existance checks possibly wrong

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

Any news here?

суббота, 21 октября 2017 г., 18:42:47 UTC+3 пользователь Антонио Антуан 
написал:
>
> I see that it is not happened when "bind" passed directly to 
> "sessionmaker" 
>
> сб, 21 окт. 2017 г. в 18:33, Антонио Антуан <a.ch@gmail.com>:
>
>>
>>
>> пятница, 20 октября 2017 г., 20:50:52 UTC+3 пользователь Mike Bayer 
>> написал:
>>
>>> On Fri, Oct 20, 2017 at 11:05 AM, Simon King <si...@simonking.org.uk> 
>>> wrote: 
>>> > The "is not None" is important when checking a variable that may 
>>> > contain a ClauseElement, precisely because ClauseElement defines that 
>>> > __bool__ method. 
>>> > 
>>> > However, in Session.get_bind(), "mapper" is not supposed to contain a 
>>> > ClauseElement. It should either be an instance of 
>>> > sqlalchemy.orm.mapper.Mapper, or None, in which case "if mapper:" is a 
>>> > valid and concise way to write it. Comparing to None might be strictly 
>>> > more accurate, but it shouldn't be necessary. 
>>>
>>> agree, "mapper" means "mapper" and it is not supposed to be a 
>>> ClauseElement.   This sounds like arguments are not being passed 
>>> correctly somewhere.   Would need a demonstration script if something 
>>> in SQLAlchemy itself is claimed to be making the mistake. 
>>>
>>
>> Ok, that code produces mentioned error: 
>> https://gist.github.com/aCLr/113ac292c05bdb01e964d8e9884d6e5f
>> Traceback (most recent call last):
>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 100, in 
>> 
>> Session.query(with_recursive).set_shard('default').all()
>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2654, 
>> in all
>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2802, 
>> in __iter__
>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 28, in 
>> _execute_and_instances
>> shard_id=self._shard_id).execute(
>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2806, 
>> in _connection_from_session
>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 
>> 984, in connection
>>   File "/home/anton/Projects/proj/core/run/stuff.py", line 40, in get_bind
>> original_bind = super(RoutingSession, self).get_bind(mapper, clause)
>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 
>> 1336, in get_bind
>>   File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 
>> 539, in __bool__
>> TypeError: Boolean value of this clause is not defined
>>  
>>
>>> > 
>>> > Simon 
>>>
>> > 
>>> > 
>>> > 
>>> > On Fri, Oct 20, 2017 at 3:17 PM, Антонио Антуан <a.ch...@gmail.com> 
>>> wrote: 
>>> >> Really, `mapper` contains this: >> >> 0x7fe673fb0a50; group_getter>. But does it changes something? I 
>>> already 
>>> >> encountered this problem in my code, when I checked sqla-objects 
>>> existance 
>>> >> without "is not None", project was broken. Is it normal to omit that 
>>> >> condition in sqlalchemy code? 
>>> >> 
>>> >> I use: 
>>> >>>>> sqlalchemy.__version__ 
>>> >> '1.0.19' 
>>> >> 
>>> >> пятница, 20 октября 2017 г., 16:42:23 UTC+3 пользователь Simon King 
>>> написал: 
>>> >>> 
>>> >>> On Fri, Oct 20, 2017 at 2:15 PM, Антонио Антуан <a.ch...@gmail.com> 
>>> wrote: 
>>> >>> > Hi. 
>>> >>> > I use my own `RoutingSession` and `RoutingQuery` implementation, 
>>> most of 
>>> >>> > it 
>>> >>> > inspired by `sqlalchemy.ext.horizontal_shard`: 
>>> >>> > 
>>> >>> > class RoutingSession(Session): 
>>> >>> > def get_bind(self, mapper=None, clause=None, shard_id=None, 
>>> >>> > **kwargs): 
>>> >>> > original_bind = None 
>>> >>> > try: 
>>> >>> > original_bind = super(RoutingSession, 
>>> self).get_bind(mapper, 
>>> >>> > clause) 
>>> >>> > except UnboundExecutionError: 
>>> >>> > 

Re: [sqlalchemy] custom compiler for GenericFunction

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

Yes, it works. Thank you.

пн, 23 окт. 2017 г. в 17:43, Mike Bayer <mike...@zzzcomputing.com>:

> On Mon, Oct 23, 2017 at 6:40 AM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> > Hello guys.
> >
> > I try to make my own GenericFunction with default compiler and custom
> > compiler for particular dialect. As you see below, always used custom
> > compiler:
> >
> > from sqlalchemy import func, create_engine, Integer
> > from sqlalchemy.ext.compiler import compiles
> > from sqlalchemy.orm import sessionmaker
> > from sqlalchemy.sql.functions import GenericFunction
> >
> > engine_ch = create_engine('clickhouse+native://log:pas@127.0.0.1:9000/db
> ')
> > engine_pg = create_engine('postgresql://log:pas@127.0.0.1:9000/db')
> >
> > ses_pg = sessionmaker(bind=engine_pg)()
> > ses_ch = sessionmaker(bind=engine_ch)()
> >
> >
> > class IntDiv(GenericFunction):
> > type = Integer
> > package = 'custom'
> > name = 'div'
> > identifier = 'div'
> >
> >
> > @compiles(IntDiv)
> > def visit_div_default(element, compiler, **kwargs):
> > return 'div(%s)' % compiler.process(element.clause_expr.element)
> >
> >
> > @compiles(IntDiv, 'clickhouse')
> > def visit_div_ch(element, compiler, **kwargs):
> > return 'intDiv(%s)' % compiler.process(element.clause_expr.element)
> >
> >
> > print(ses_ch.bind.dialect.name)
> > print(ses_ch.query(func.custom.div(1, 2)))
> >
> > print(ses_pg.bind.dialect.name)
> > print(ses_pg.query(func.custom.div(1, 2)))
> >
> >
> > If I not make "@compiles(IntDiv)" (for default dialct), make only
> > @compiles(IntDiv, 'clickhouse) I got this error:
> >
> > Traceback (most recent call last):
> >   File "/home/anton/Projects/proj/core/run/stuff.py", line 31, in
> 
> > print(ses_ch.query(func.custom.div(1, 2)))
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
> > line 3457, in __str__
> > return str(self._compile_context().statement)
>
>
> Based on that line number, I can see you are on a 1.0.x version of
> SQLAlchemy.   Query.__str__() takes into account the bound dialect
> automatically as of 1.1:
>
>
> http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#stringify-of-query-will-consult-the-session-for-the-correct-dialect
>
> if you are on 1.0.x or earlier you need to compile from the statement:
>
> query.statement.compile(my_engine)
>
>
>
>
>
>
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
> > line 506, in __str__
> > return unicode(self.compile()).encode('ascii', 'backslashreplace')
> >   File "", line 1, in 
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
> > line 494, in compile
> > return self._compiler(dialect, bind=bind, **kw)
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
> > line 500, in _compiler
> > return dialect.statement_compiler(dialect, self, **kw)
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
> > line 395, in __init__
> > Compiled.__init__(self, dialect, statement, **kwargs)
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
> > line 190, in __init__
> > self.string = self.process(self.statement, **compile_kwargs)
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
> > line 213, in process
> > return obj._compiler_dispatch(self, **kwargs)
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py",
> > line 81, in _compiler_dispatch
> > return meth(self, **kw)
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
> > line 1584, in visit_select
> > for name, column in select._columns_plus_names
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
> > line 1357, in _label_select_column
> > **column_clause_args
> >   File
> >
> "/home/anton/Projects/proj/.venv/lib/python2.7/s

[sqlalchemy] custom compiler for GenericFunction

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

Hello guys.

I try to make my own GenericFunction with default compiler and custom 
compiler for particular dialect. As you see below, always used custom 
compiler:

from sqlalchemy import func, create_engine, Integer
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.functions import GenericFunction

engine_ch = create_engine('clickhouse+native://log:pas@127.0.0.1:9000/db')
engine_pg = create_engine('postgresql://log:pas@127.0.0.1:9000/db')

ses_pg = sessionmaker(bind=engine_pg)()
ses_ch = sessionmaker(bind=engine_ch)()


class IntDiv(GenericFunction):
type = Integer
package = 'custom'
name = 'div'
identifier = 'div'


@compiles(IntDiv)
def visit_div_default(element, compiler, **kwargs):
return 'div(%s)' % compiler.process(element.clause_expr.element)


@compiles(IntDiv, 'clickhouse')
def visit_div_ch(element, compiler, **kwargs):
return 'intDiv(%s)' % compiler.process(element.clause_expr.element)


print(ses_ch.bind.dialect.name)
print(ses_ch.query(func.custom.div(1, 2)))

print(ses_pg.bind.dialect.name)
print(ses_pg.query(func.custom.div(1, 2)))


If I not make "@compiles(IntDiv)" (for default dialct), make only 
@compiles(IntDiv, 'clickhouse) I got this error:

Traceback (most recent call last):
  File "/home/anton/Projects/proj/core/run/stuff.py", line 31, in 
print(ses_ch.query(func.custom.div(1, 2)))
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
 line 3457, in __str__
return str(self._compile_context().statement)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
 line 506, in __str__
return unicode(self.compile()).encode('ascii', 'backslashreplace')
  File "", line 1, in 
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
 line 494, in compile
return self._compiler(dialect, bind=bind, **kw)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
 line 500, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
 line 395, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
 line 190, in __init__
self.string = self.process(self.statement, **compile_kwargs)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
 line 213, in process
return obj._compiler_dispatch(self, **kwargs)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py",
 line 81, in _compiler_dispatch
return meth(self, **kw)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
 line 1584, in visit_select
for name, column in select._columns_plus_names
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
 line 1357, in _label_select_column
**column_clause_args
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py",
 line 93, in _compiler_dispatch
return meth(self, **kw)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py",
 line 615, in visit_label
OPERATORS[operators.as_] + \
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/ext/compiler.py",
 line 423, in 
lambda *arg, **kw: existing(*arg, **kw))
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/ext/compiler.py",
 line 460, in __call__
"compilation handler." % type(element))
sqlalchemy.exc.CompileError:  construct has no default 
compilation handler.


Is it possible to override default GenericFunction compiler? 

-- 
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] mapper existance checks possibly wrong

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

I see that it is not happened when "bind" passed directly to "sessionmaker"

сб, 21 окт. 2017 г. в 18:33, Антонио Антуан <a.ch@gmail.com>:

>
>
> пятница, 20 октября 2017 г., 20:50:52 UTC+3 пользователь Mike Bayer
> написал:
>
>> On Fri, Oct 20, 2017 at 11:05 AM, Simon King <si...@simonking.org.uk>
>> wrote:
>> > The "is not None" is important when checking a variable that may
>> > contain a ClauseElement, precisely because ClauseElement defines that
>> > __bool__ method.
>> >
>> > However, in Session.get_bind(), "mapper" is not supposed to contain a
>> > ClauseElement. It should either be an instance of
>> > sqlalchemy.orm.mapper.Mapper, or None, in which case "if mapper:" is a
>> > valid and concise way to write it. Comparing to None might be strictly
>> > more accurate, but it shouldn't be necessary.
>>
>> agree, "mapper" means "mapper" and it is not supposed to be a
>> ClauseElement.   This sounds like arguments are not being passed
>> correctly somewhere.   Would need a demonstration script if something
>> in SQLAlchemy itself is claimed to be making the mistake.
>>
>
> Ok, that code produces mentioned error:
> https://gist.github.com/aCLr/113ac292c05bdb01e964d8e9884d6e5f
> Traceback (most recent call last):
>   File "/home/anton/Projects/proj/core/run/stuff.py", line 100, in 
> Session.query(with_recursive).set_shard('default').all()
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2654,
> in all
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2802,
> in __iter__
>   File "/home/anton/Projects/proj/core/run/stuff.py", line 28, in
> _execute_and_instances
> shard_id=self._shard_id).execute(
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2806,
> in _connection_from_session
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 984,
> in connection
>   File "/home/anton/Projects/proj/core/run/stuff.py", line 40, in get_bind
> original_bind = super(RoutingSession, self).get_bind(mapper, clause)
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line
> 1336, in get_bind
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line
> 539, in __bool__
> TypeError: Boolean value of this clause is not defined
>
>
>> >
>> > Simon
>>
> >
>> >
>> >
>> > On Fri, Oct 20, 2017 at 3:17 PM, Антонио Антуан <a.ch...@gmail.com>
>> wrote:
>> >> Really, `mapper` contains this: > >> 0x7fe673fb0a50; group_getter>. But does it changes something? I
>> already
>> >> encountered this problem in my code, when I checked sqla-objects
>> existance
>> >> without "is not None", project was broken. Is it normal to omit that
>> >> condition in sqlalchemy code?
>> >>
>> >> I use:
>> >>>>> sqlalchemy.__version__
>> >> '1.0.19'
>> >>
>> >> пятница, 20 октября 2017 г., 16:42:23 UTC+3 пользователь Simon King
>> написал:
>> >>>
>> >>> On Fri, Oct 20, 2017 at 2:15 PM, Антонио Антуан <a.ch...@gmail.com>
>> wrote:
>> >>> > Hi.
>> >>> > I use my own `RoutingSession` and `RoutingQuery` implementation,
>> most of
>> >>> > it
>> >>> > inspired by `sqlalchemy.ext.horizontal_shard`:
>> >>> >
>> >>> > class RoutingSession(Session):
>> >>> > def get_bind(self, mapper=None, clause=None, shard_id=None,
>> >>> > **kwargs):
>> >>> > original_bind = None
>> >>> > try:
>> >>> > original_bind = super(RoutingSession,
>> self).get_bind(mapper,
>> >>> > clause)
>> >>> > except UnboundExecutionError:
>> >>> > # may not be bound
>> >>> > pass
>> >>> > if shard_id is None:
>> >>> > shard_id = TenantIDStorage.get_shard_id()  # just
>> global
>> >>> > storage
>> >>> > bind_for_shard = self.__binds[shard_id]
>> >>> > if original_bind is not None and original_bind.url ==
>> >>> > bind_for_shard.url:
>> >>> > return original_bind
>> >>> > e

Re: [sqlalchemy] mapper existance checks possibly wrong

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

пятница, 20 октября 2017 г., 20:50:52 UTC+3 пользователь Mike Bayer написал:
>
> On Fri, Oct 20, 2017 at 11:05 AM, Simon King <si...@simonking.org.uk 
> > wrote: 
> > The "is not None" is important when checking a variable that may 
> > contain a ClauseElement, precisely because ClauseElement defines that 
> > __bool__ method. 
> > 
> > However, in Session.get_bind(), "mapper" is not supposed to contain a 
> > ClauseElement. It should either be an instance of 
> > sqlalchemy.orm.mapper.Mapper, or None, in which case "if mapper:" is a 
> > valid and concise way to write it. Comparing to None might be strictly 
> > more accurate, but it shouldn't be necessary. 
>
> agree, "mapper" means "mapper" and it is not supposed to be a 
> ClauseElement.   This sounds like arguments are not being passed 
> correctly somewhere.   Would need a demonstration script if something 
> in SQLAlchemy itself is claimed to be making the mistake. 
>

Ok, that code produces mentioned 
error: https://gist.github.com/aCLr/113ac292c05bdb01e964d8e9884d6e5f
Traceback (most recent call last):
  File "/home/anton/Projects/proj/core/run/stuff.py", line 100, in 
Session.query(with_recursive).set_shard('default').all()
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2654, 
in all
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2802, 
in __iter__
  File "/home/anton/Projects/proj/core/run/stuff.py", line 28, in 
_execute_and_instances
shard_id=self._shard_id).execute(
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2806, 
in _connection_from_session
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 984, 
in connection
  File "/home/anton/Projects/proj/core/run/stuff.py", line 40, in get_bind
original_bind = super(RoutingSession, self).get_bind(mapper, clause)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 1336, 
in get_bind
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 539, 
in __bool__
TypeError: Boolean value of this clause is not defined
 

> > 
> > Simon 
> > 
> > 
> > 
> > On Fri, Oct 20, 2017 at 3:17 PM, Антонио Антуан <a.ch...@gmail.com 
> > wrote: 
> >> Really, `mapper` contains this:  >> 0x7fe673fb0a50; group_getter>. But does it changes something? I already 
> >> encountered this problem in my code, when I checked sqla-objects 
> existance 
> >> without "is not None", project was broken. Is it normal to omit that 
> >> condition in sqlalchemy code? 
> >> 
> >> I use: 
> >>>>> sqlalchemy.__version__ 
> >> '1.0.19' 
> >> 
> >> пятница, 20 октября 2017 г., 16:42:23 UTC+3 пользователь Simon King 
> написал: 
> >>> 
> >>> On Fri, Oct 20, 2017 at 2:15 PM, Антонио Антуан <a.ch...@gmail.com> 
> wrote: 
> >>> > Hi. 
> >>> > I use my own `RoutingSession` and `RoutingQuery` implementation, 
> most of 
> >>> > it 
> >>> > inspired by `sqlalchemy.ext.horizontal_shard`: 
> >>> > 
> >>> > class RoutingSession(Session): 
> >>> > def get_bind(self, mapper=None, clause=None, shard_id=None, 
> >>> > **kwargs): 
> >>> > original_bind = None 
> >>> > try: 
> >>> > original_bind = super(RoutingSession, 
> self).get_bind(mapper, 
> >>> > clause) 
> >>> > except UnboundExecutionError: 
> >>> > # may not be bound 
> >>> > pass 
> >>> > if shard_id is None: 
> >>> > shard_id = TenantIDStorage.get_shard_id()  # just global 
> >>> > storage 
> >>> > bind_for_shard = self.__binds[shard_id] 
> >>> > if original_bind is not None and original_bind.url == 
> >>> > bind_for_shard.url: 
> >>> > return original_bind 
> >>> > else: 
> >>> > return bind_for_shard 
> >>> > 
> >>> > def __init__(self, shards=None, query_cls=CachingQuery, 
> >>> > engines_factory=None, **kwargs): 
> >>> > super(RoutingSession, self).__init__(query_cls=query_cls, 
> >>> > **kwargs) 
> >>> > self.__binds = {} 
> >>> > self.engines_factory = engines_factory 
> >>> > if shards is no

Re: [sqlalchemy] mapper existance checks possibly wrong

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

Really, `mapper` contains this: . But does it changes something? I already 
encountered this problem in my code, when I checked sqla-objects existance 
without "is not None", project was broken. Is it normal to omit that 
condition in sqlalchemy code?

I use:
>>> sqlalchemy.__version__
'1.0.19'

пятница, 20 октября 2017 г., 16:42:23 UTC+3 пользователь Simon King написал:
>
> On Fri, Oct 20, 2017 at 2:15 PM, Антонио Антуан <a.ch...@gmail.com 
> > wrote: 
> > Hi. 
> > I use my own `RoutingSession` and `RoutingQuery` implementation, most of 
> it 
> > inspired by `sqlalchemy.ext.horizontal_shard`: 
> > 
> > class RoutingSession(Session): 
> > def get_bind(self, mapper=None, clause=None, shard_id=None, 
> **kwargs): 
> > original_bind = None 
> > try: 
> > original_bind = super(RoutingSession, self).get_bind(mapper, 
> > clause) 
> > except UnboundExecutionError: 
> > # may not be bound 
> > pass 
> > if shard_id is None: 
> > shard_id = TenantIDStorage.get_shard_id()  # just global 
> storage 
> > bind_for_shard = self.__binds[shard_id] 
> > if original_bind is not None and original_bind.url == 
> > bind_for_shard.url: 
> > return original_bind 
> > else: 
> > return bind_for_shard 
> > 
> > def __init__(self, shards=None, query_cls=CachingQuery, 
> > engines_factory=None, **kwargs): 
> > super(RoutingSession, self).__init__(query_cls=query_cls, 
> **kwargs) 
> > self.__binds = {} 
> > self.engines_factory = engines_factory 
> > if shards is not None: 
> > self.update_shards(**shards) 
> > 
> > def _add_bind(self, key, bind): 
> > self.__binds[key] = bind 
> > 
> > 
> > 
> > 
> > class RoutingQuery(Query): 
> > def __init__(self, *args, **kwargs): 
> > super(RoutingQuery, self).__init__(*args, **kwargs) 
> > self._shard_id = TenantIDStorage.get_shard_id() 
> > 
> > def get(self, ident): 
> > self._check_bound_to_shard() 
> > return super(CachingQuery, self).get(ident) 
> > 
> > def _check_bound_to_shard(self): 
> > if self._shard_id is None: 
> > raise ValueError('query not bound to any shard') 
> > 
> > def _execute_and_instances(self, querycontext): 
> > self._check_bound_to_shard() 
> > querycontext.attributes['shard_id'] = self._shard_id 
> > result = self._connection_from_session( 
> > mapper=self._mapper_zero(), 
> > shard_id=self._shard_id).execute( 
> > querycontext.statement, 
> > self._params 
> > ) 
> > return self.instances(result, querycontext) 
> > 
> > 
> > 
> > 
> > 
> > 
> > Sometimes I got this error: 
> > 
> >   File "/home/anton/Projects/proj/admin/proj/admin/views/stats.py", line 
> > 898, in _get_filters_from_request 
> > control_groups = [g.id for g in 
> > User.get_own_groups_query(current_user.id)] 
> >   File 
> > 
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
>  
>
> > line 2802, in __iter__ 
> > return self._execute_and_instances(context) 
> >   File 
> > 
> "/home/anton/Projects/proj/core/proj/core/orm_extensions/rouing_session.py", 
>
> > line 105, in _execute_and_instances 
> > shard_id=self._shard_id).execute( 
> >   File 
> > 
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
>  
>
> > line 2806, in _connection_from_session 
> > **kw) 
> >   File 
> > 
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>  
>
> > line 984, in connection 
> > bind = self.get_bind(mapper, clause=clause, **kw) 
> >   File 
> > 
> "/home/anton/Projects/proj/core/proj/core/orm_extensions/cachingquery.py", 
> > line 279, in get_bind 
> > original_bind = super(RoutingSession, self).get_bind(mapper, clause) 
> >   File 
> > 
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>  
>
> > line 1336, in get_bind 
> > if mapper and mapper.mapped_table.bind: 
> >   File 
> > 
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/s

[sqlalchemy] mapper existance checks possibly wrong

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

Hi.
I use my own `RoutingSession` and `RoutingQuery` implementation, most of it 
inspired by `sqlalchemy.ext.horizontal_shard`:

class RoutingSession(Session):
def get_bind(self, mapper=None, clause=None, shard_id=None, **kwargs):
original_bind = None
try:
original_bind = super(RoutingSession, self).get_bind(mapper, clause)
except UnboundExecutionError:
# may not be bound
pass
if shard_id is None:
shard_id = TenantIDStorage.get_shard_id()  # just global storage
bind_for_shard = self.__binds[shard_id]
if original_bind is not None and original_bind.url == 
bind_for_shard.url:
return original_bind
else:
return bind_for_shard

def __init__(self, shards=None, query_cls=CachingQuery, 
engines_factory=None, **kwargs):
super(RoutingSession, self).__init__(query_cls=query_cls, **kwargs)
self.__binds = {}
self.engines_factory = engines_factory
if shards is not None:
self.update_shards(**shards)

def _add_bind(self, key, bind):
self.__binds[key] = bind




class RoutingQuery(Query):
def __init__(self, *args, **kwargs):
super(RoutingQuery, self).__init__(*args, **kwargs)
self._shard_id = TenantIDStorage.get_shard_id()

def get(self, ident):
self._check_bound_to_shard()
return super(CachingQuery, self).get(ident)

def _check_bound_to_shard(self):
if self._shard_id is None:
raise ValueError('query not bound to any shard')

def _execute_and_instances(self, querycontext):
self._check_bound_to_shard()
querycontext.attributes['shard_id'] = self._shard_id
result = self._connection_from_session(
mapper=self._mapper_zero(),
shard_id=self._shard_id).execute(
querycontext.statement,
self._params
)
return self.instances(result, querycontext)






Sometimes I got this error:

  File "/home/anton/Projects/proj/admin/proj/admin/views/stats.py", line 898, 
in _get_filters_from_request
control_groups = [g.id for g in User.get_own_groups_query(current_user.id)]
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
 line 2802, in __iter__
return self._execute_and_instances(context)
  File 
"/home/anton/Projects/proj/core/proj/core/orm_extensions/rouing_session.py", 
line 105, in _execute_and_instances
shard_id=self._shard_id).execute(
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
 line 2806, in _connection_from_session
**kw)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
 line 984, in connection
bind = self.get_bind(mapper, clause=clause, **kw)
  File 
"/home/anton/Projects/proj/core/proj/core/orm_extensions/cachingquery.py", line 
279, in get_bind
original_bind = super(RoutingSession, self).get_bind(mapper, clause)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
 line 1336, in get_bind
if mapper and mapper.mapped_table.bind:
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
 line 539, in __bool__
raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined




I found that `mapper` in original `get_bind` always checks with `is not 
None` condition. There is only one place where condition omitted: `if 
mapper and mapper.mapped_table.bind...`. 
Possibly it is not correct? Or my code does not do something important? 

-- 
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] generate raw insert statement

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

Excuse me, missed your response.
Found this soultiuon:

>>> print(compile_query(Foo.__table__.insert().values(bar='zoo').compile(
Session.bind.dialect))
INSERT INTO foos (bar) VALUES ('zoo')





пятница, 13 октября 2017 г., 19:41:29 UTC+3 пользователь Mike Bayer написал:
>
> On Fri, Oct 13, 2017 at 11:21 AM, Антонио Антуан <a.ch...@gmail.com 
> > wrote: 
> > I've seen it and thought that some ways available... 
>
>
> you can maybe "capture" it via a logger but that's only as it runs. 
> The INSERT statement has to also return data to the ORM so it's not 
> tailored for "offline" use. 
>
> if you can give me a clue what you want to do I might have suggestions. 
>
>
>
> > пт, 13 окт. 2017 г. в 17:30, Mike Bayer <mik...@zzzcomputing.com 
> >: 
> >> 
> >> On Fri, Oct 13, 2017 at 8:43 AM, Антонио Антуан <a.ch...@gmail.com 
> > 
> >> wrote: 
> >> > I use such code to generate raw SQL from SQLA-queries: 
> >> > 
> >> > def compile_statement(statement): 
> >> > comp = statement.compile(dialect=postgresql.dialect()) 
> >> > params = {} 
> >> > for k, v in comp.params.items(): 
> >> > if isinstance(v, unicode): 
> >> > v = v.encode(cls.encoding) 
> >> > params[k] = adapt(v) 
> >> > return (comp.string.encode(cls.encoding) % 
> >> > params).decode(cls.encoding) 
> >> > 
> >> > 
> >> > def compile_query(query): 
> >> > return cls.compile_statement(query.statement) 
> >> > 
> >> >>>> print(compile_query(Foo.query.filter(Foo.bar == 
> >> >>>> 'zoo').with_entities(Foo.bar)) 
> >> > SELECT foos.bar as foos_bar 
> >> > FROM foos 
> >> > WHERE foos.bar = 'zoo' 
> >> > 
> >> > 
> >> > Is there any way to generate raw SQL insert statement, generated by 
> this 
> >> > code? 
> >> > Foo(bar='zoo').save() 
> >> 
> >> if you mean that save() is the flask-sqlalchemy thing that calls 
> >> flush(), and you want to get the INSERT that the ORM uses directly, 
> >> there's not really a way to get that on the fly.   The insert is 
> >> generated internally based on a scan of what's in the object and it 
> >> occurs across several private functions which pass lots of internal 
> >> state around. 
> >> 
> >> 
> >> 
> >> > 
> >> > -- 
> >> > SQLAlchemy - 
> >> > The Python SQL Toolkit and Object Relational Mapper 
> >> > 
> >> > http://www.sqlalchemy.org/ 
> >> > 
> >> > To post example code, please provide an MCVE: Minimal, Complete, and 
> >> > Verifiable Example. See http://stackoverflow.com/help/mcve for a 
> full 
> >> > description. 
> >> > --- 
> >> > You received this message because you are subscribed to the Google 
> >> > Groups 
> >> > "sqlalchemy" group. 
> >> > To unsubscribe from this group and stop receiving emails from it, 
> send 
> >> > an 
> >> > email to sqlalchemy+...@googlegroups.com . 
> >> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> >> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> >> > For more options, visit https://groups.google.com/d/optout. 
> >> 
> >> -- 
> >> SQLAlchemy - 
> >> The Python SQL Toolkit and Object Relational Mapper 
> >> 
> >> http://www.sqlalchemy.org/ 
> >> 
> >> To post example code, please provide an MCVE: Minimal, Complete, and 
> >> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a 
> full 
> >> description. 
> >> --- 
> >> You received this message because you are subscribed to the Google 
> Groups 
> >> "sqlalchemy" group. 
> >> To unsubscribe from this group and stop receiving emails from it, send 
> an 
> >> email to sqlalchemy+...@googlegroups.com . 
> >> To post to this group, send email to sqlal...@googlegroups.com 
> . 
> >> Visit this group at https://groups.google.com/group/sqlalchemy. 
> >> For more options, visit https://groups.google.com/d/optout. 
> > 
> > -- 
> > 
> > Антон 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> >

Re: [sqlalchemy] generate raw insert statement

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

I've seen it and thought that some ways available...
пт, 13 окт. 2017 г. в 17:30, Mike Bayer <mike...@zzzcomputing.com>:

> On Fri, Oct 13, 2017 at 8:43 AM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> > I use such code to generate raw SQL from SQLA-queries:
> >
> > def compile_statement(statement):
> > comp = statement.compile(dialect=postgresql.dialect())
> > params = {}
> > for k, v in comp.params.items():
> > if isinstance(v, unicode):
> > v = v.encode(cls.encoding)
> > params[k] = adapt(v)
> > return (comp.string.encode(cls.encoding) %
> params).decode(cls.encoding)
> >
> >
> > def compile_query(query):
> > return cls.compile_statement(query.statement)
> >
> >>>> print(compile_query(Foo.query.filter(Foo.bar ==
> >>>> 'zoo').with_entities(Foo.bar))
> > SELECT foos.bar as foos_bar
> > FROM foos
> > WHERE foos.bar = 'zoo'
> >
> >
> > Is there any way to generate raw SQL insert statement, generated by this
> > code?
> > Foo(bar='zoo').save()
>
> if you mean that save() is the flask-sqlalchemy thing that calls
> flush(), and you want to get the INSERT that the ORM uses directly,
> there's not really a way to get that on the fly.   The insert is
> generated internally based on a scan of what's in the object and it
> occurs across several private functions which pass lots of internal
> state around.
>
>
>
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send an
> > email to sqlalchemy+unsubscr...@googlegroups.com.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
-- 

Антон

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

http://www.sqlalchemy.org/

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

[sqlalchemy] generate raw insert statement

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

I use such code to generate raw SQL from SQLA-queries:

def compile_statement(statement):
comp = statement.compile(dialect=postgresql.dialect())
params = {}
for k, v in comp.params.items():
if isinstance(v, unicode):
v = v.encode(cls.encoding)
params[k] = adapt(v)
return (comp.string.encode(cls.encoding) % params).decode(cls.encoding)


def compile_query(query):
return cls.compile_statement(query.statement)

>>> print(compile_query(Foo.query.filter(Foo.bar == 
>>> 'zoo').with_entities(Foo.bar))
SELECT foos.bar as foos_bar
FROM foos
WHERE foos.bar = 'zoo'


Is there any way to generate raw SQL insert statement, generated by this 
code?
Foo(bar='zoo').save()

-- 
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] several questions about connection pooling behaviour

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

Ok, thank you for the answer, appreciate it.

пятница, 1 сентября 2017 г., 21:11:26 UTC+3 пользователь Mike Bayer написал:
>
> On Fri, Sep 1, 2017 at 12:35 PM, Антонио Антуан <a.ch...@gmail.com 
> > wrote: 
> > Yes, I tried it and it is worked fine. 
> > Another thing that I tried to understand: "Are connections, which used 
> by 
> > `session`, `session.bind`, compiled_query and engine, the same?". 
> > `pg_stat_activity` shows me, that I am right, 
> > Can you confirm this statement? 
>
> *if* you are using StaticPool and AssertionPool, and you are *not* 
> using multiple threads against it as in particular AssertionPool is 
> not really threadsafe (StaticPool is mostly threadsafe though I can 
> see it might race on the first connect), and also you haven't called 
> engine.dispose(), then there should be just the one connection hanging 
> around, yes.DBAPI connections themselves are not generally 
> threadsafe so these pools assume single-threaded use. 
>
>
>
> In such case a warning about deadlocks with 
> > "reset_on_return=None" not so horrible with StaticPool or AssertionPool. 
> > Here is code snippet: 
> > https://gist.github.com/aCLr/61896fc652a0a61a21dbe3531ed695a8 
>
> I'm not sure what you're ultimately trying to accomplish but I would 
> definitely not recommend patterns like StaticPool, 
> reset_on_return=None for anything significant.   You will leave the 
> connection hanging open in a transaction while the application is not 
> doing anything.   I don't see why any of the patterns you are 
> illustrating should ever be necessary. 
>
>
> >> 
> >> And when I specify 
> >> > that parameter with `None` value, failed only first asserion ('failed 
> on 
> >> > compiled query execution') because, as I think, another connection is 
> >> > used. 
> >> > My question is the same: isn't it wrong? And if it is, how can I 
> avoid 
> >> > AsserionError on compiled query execution? Should I use private 
> >> > `_connection_for_bind` method of Session class or there are any other 
> >> > options? 
> >> > 
> >> > 
> >> > 
> >> > -- 
> >> > SQLAlchemy - 
> >> > The Python SQL Toolkit and Object Relational Mapper 
> >> > 
> >> > http://www.sqlalchemy.org/ 
> >> > 
> >> > To post example code, please provide an MCVE: Minimal, Complete, and 
> >> > Verifiable Example. See http://stackoverflow.com/help/mcve for a 
> full 
> >> > description. 
> >> > --- 
> >> > You received this message because you are subscribed to the Google 
> >> > Groups 
> >> > "sqlalchemy" group. 
> >> > To unsubscribe from this group and stop receiving emails from it, 
> send 
> >> > an 
> >> > email to sqlalchemy+...@googlegroups.com. 
> >> > To post to this group, send email to sqlal...@googlegroups.com. 
> >> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> >> > For more options, visit https://groups.google.com/d/optout. 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > You received this message because you are subscribed to the Google 
> Groups 
> > "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an 
> > email to sqlalchemy+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to 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] several questions about connection pooling behaviour

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

пятница, 1 сентября 2017 г., 18:08:35 UTC+3 пользователь Mike Bayer написал:
>
> On Fri, Sep 1, 2017 at 8:26 AM, Антонио Антуан <a.ch...@gmail.com 
> > wrote: 
> > Hi guys. 
> > I have several questions and, possibly, misunderstandings about 
> sqlalchemy 
> > connection pooling behavior 
> > 
> > 1. I try to figure out if this is normal: 
> > https://gist.github.com/aCLr/be78f3495892978ee868c9b5adcef0e6 
> > 
> > As you can see, I checked that `bind` is the same on `get_bind()` 
> invokation 
> > and on attribute call. 
> > I see, that each connection into session binds only for session, not for 
> > bind... 
> > Is it correct? I expected that each connection is bound for `bind` 
> > (`engine`). 
>
> that's normal because your exception is when you try to execute 
> directly from the Engine. 
>
> this is equivalent: 
>
> engine = create_engine(conn_string, 
>poolclass=pool.AssertionPool) 
> conn = engine.connect() 
> conn.scalar("select 1")  # ok 
> conn.execute("select 1").scalar()  # ok 
> engine.execute("select 1") # not OK, the engine doesn't know about 
> your "conn", it checks out a new connection 
>
>
> > 
> > 2. Here is the problem, on which I started my research: 
> > https://gist.github.com/aCLr/b642c822b0bd3c9ec1fe775452479acd 
> > I don't use commit here, only flush. But I expected that results on each 
> > execution will be the same, because I used same bind on query 
> compilation 
> > and on `Session.query...` execution. 
> > 
> > Yes, I've read about `reset_on_return` parameter of Pool. 
>
> right, there's some odd patterns here, but it is "reset_on_return" 
> that is ultimately hitting the rollback here. 
>
> The query is wrong too, needs a FROM: 
>
> _query = 
> Session.query(func.count(literal('*'))).select_from(Test).limit(1).statement.compile(bind=Session.bind,
>  
>
> dialect=Session.bind.dialect) 
>
> and the assertions need to use scalar(): 
>
> assert (_query.scalar() == amount), 'failed on compiled query execution' 
>
>
> reset on return, as well as some nice debugging so you can see things 
> happening: 
>
> engine = create_engine(conn_string, 
>poolclass=pool.StaticPool, echo='debug', 
> echo_pool='debug', 
>pool_reset_on_return=None) 
>
>
> Yes, I tried it and it is worked fine.
Another thing that I tried to understand: "Are connections, which used by 
`session`, `session.bind`, compiled_query and engine, the same?". 
`pg_stat_activity` shows me, that I am right,
Can you confirm this statement? In such case a warning about deadlocks with 
"reset_on_return=None" not so horrible with StaticPool or AssertionPool.
Here is code snippet:
https://gist.github.com/aCLr/61896fc652a0a61a21dbe3531ed695a8

> And when I specify 
> > that parameter with `None` value, failed only first asserion ('failed on 
> > compiled query execution') because, as I think, another connection is 
> used. 
> > My question is the same: isn't it wrong? And if it is, how can I avoid 
> > AsserionError on compiled query execution? Should I use private 
> > `_connection_for_bind` method of Session class or there are any other 
> > options? 
> > 
> > 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > You received this message because you are subscribed to the Google 
> Groups 
> > "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an 
> > email to sqlalchemy+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to 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: several questions about connection pooling behaviour

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

пятница, 1 сентября 2017 г., 15:26:11 UTC+3 пользователь Антонио Антуан 
написал:
>
> Hi guys. 
> I have several questions and, possibly, misunderstandings about sqlalchemy 
> connection pooling behavior
>
> 1. I try to figure out if this is normal:
> https://gist.github.com/aCLr/be78f3495892978ee868c9b5adcef0e6
>
> As you can see, I checked that `bind` is the same on `get_bind()` 
> invokation and on attribute call.
> I see, that each connection into session binds only for session, not for 
> bind...
> Is it correct? I expected that each connection is bound for `bind` 
> (`engine`).
>
>
>
>
> 2. Here is the problem, on which I started my research:
> https://gist.github.com/aCLr/b642c822b0bd3c9ec1fe775452479acd
> I don't use commit here, only flush. But I expected that results on each 
> execution will be the same, because I used same bind on query compilation 
> and on `Session.query...` execution.
>
> Yes, I've read about `reset_on_return` parameter of Pool. And when I 
> specify that parameter with `None` value, failed only first asserion 
> ('failed on compiled query execution') because, as I think, another 
> connection is used. 
> My question is the same: isn't it wrong? And if it is, how can I avoid 
> AsserionError on compiled query execution? Should I use private `
> _connection_for_bind` method of Session class or there are any other 
> options?
>

My fault, compiled query was wrong.
Fixed it:
https://gist.github.com/aCLr/22d6ed61c1dd49530d30c218a65a2680
Now the second raw sql fails without `pool_reset_on_return=None`

>
>

-- 
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] several questions about connection pooling behaviour

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

Hi guys. 
I have several questions and, possibly, misunderstandings about sqlalchemy 
connection pooling behavior

1. I try to figure out if this is normal:
https://gist.github.com/aCLr/be78f3495892978ee868c9b5adcef0e6

As you can see, I checked that `bind` is the same on `get_bind()` 
invokation and on attribute call.
I see, that each connection into session binds only for session, not for 
bind...
Is it correct? I expected that each connection is bound for `bind` 
(`engine`).




2. Here is the problem, on which I started my research:
https://gist.github.com/aCLr/b642c822b0bd3c9ec1fe775452479acd
I don't use commit here, only flush. But I expected that results on each 
execution will be the same, because I used same bind on query compilation 
and on `Session.query...` execution.

Yes, I've read about `reset_on_return` parameter of Pool. And when I 
specify that parameter with `None` value, failed only first asserion 
('failed on compiled query execution') because, as I think, another 
connection is used. 
My question is the same: isn't it wrong? And if it is, how can I avoid 
AsserionError on compiled query execution? Should I use private `
_connection_for_bind` method of Session class or there are any other 
options?



-- 
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] horizontal sharding and bulk_insert

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

Great!
Thank you, Mike.
I really hope I can find time to figure out sqlalchemy source code and 
contribute into it. Currently I have not succeeded in this :(
But the library is amazing :)

среда, 23 августа 2017 г., 16:58:20 UTC+3 пользователь Mike Bayer написал:
>
> On Wed, Aug 23, 2017 at 2:01 AM, Антонио Антуан <a.ch...@gmail.com 
> > wrote: 
> > 
> > 
>
> > 
> > So, and now we always make queries without shard_id chosing: we always 
> make 
> > queries on 'default' database. One exception: when we run app on master 
> and 
> > read statistics from geo-slaves, we point it explicitly: 
> > "Session.query(...).set_shard_id(NODE_PREFIX)". So, yes, we use sharding 
> > mechanizm only for that. 
> > 
> > My question still the same: can I override "connection_callable" member 
> with 
> > None on my "ShardedSessionWithDefaultBind" class? 
>
> Sure, the ShardedSession is supposed to be more of an example class 
> that you can hack on and connection_callable is a simple None/ 
> not-None thing that is used when you flush() to get a list of 
> connections per row, where each connection is decided based on the 
> sharding conditionals. 
>
>
> > 
> > Also, if you can advise any other mechanism to make things as simple as 
> we 
> > have them now (with horizontal_sharding mechanism), it would be great, 
> our 
> > team will appreciate it :) 
>
> My suggestion was that bulk_insert_mappings() / bulk_update_mappings / 
> bulk_save_objects don't actually make the objects as any part of the 
> session's state, nor does the existing object state of the session 
> have any bearing on the operation of bulk_insert/bulk_update other 
> than being part of the same transaction.   So if you don't want to 
> tinker with the state of your sharded_session, just use a different 
> Session. 
>
> If you have code like this: 
>
>  some_stuff = my_sharded_session.query(Stuff).filter(...).all() 
>
>  my_sharded_session.bulk_save_objects(objects) 
>
> you could change it to this: 
>
>  some_stuff = my_sharded_session.query(Stuff).filter(...).all() 
>
>  ad_hoc_session = 
> Session(bind=my_sharded_session.connection(shard_id='default')) 
>  ad_hoc_session.bulk_save_objects(objects) 
>
> that way you don't have to change the state of my_sharded_session. 
> It probably doesn't matter much either way, e.g. 
> session.connection_callable=None or this, assuming there is no 
> concurrent sharing of the sharded_session instance. 
>
>
>
> > 
> >> 
> >> > 
> >> > If it can help: I ALWAYS perform UPDATE/INSERT/DELETE and most of 
> SELECT 
> >> > queries in only one database. So, I wrote such subclass: 
> >> > 
> >> > 
> >> > class ShardedSessionWithDefaultBind(ShardedSession): 
> >> > def get_bind(self, mapper, shard_id=None, instance=None, 
> >> > clause=None, 
> >> > **kw): 
> >> > if shard_id is None: 
> >> > shard_id = default_shard_id 
> >> > return super(ShardedSessionWithDefaultBind, 
> >> > self).get_bind(mapper, 
> >> > shard_id, instance, clause, **kw) 
> >> > 
> >> > 
> >> > Maybe I can override "__init__" for my class and write 
> >> > "self.connnection_callable = None"? 
> >> > My research of sqlalchemy code didn't make me sure that it is safe 
> >> > enough. 
> >> > But I see, that "connection_callable" used only for checking into 
> >> > "_bulk_insert" and "_bulk_update" functions in 
> >> > sqlalchemy.orm.persistence 
> >> > module. 
> >> > So, if I 100% sure, that I ALWAYS perform INSERT/UPDATE/DELETE 
> queries 
> >> > in 
> >> > only one database, can I make 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 
> >> >

Re: [sqlalchemy] horizontal sharding and bulk_insert

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

вторник, 22 августа 2017 г., 17:29:41 UTC+3 пользователь Mike Bayer написал:
>
> On Tue, Aug 22, 2017 at 3:43 AM, Антонио Антуан <a.ch...@gmail.com 
> > wrote: 
> > Hi guys 
> > I tried to implement horizontal sharding in my project. Everything is 
> ok, 
> > except bulk_inserts. 
> > When I run tests, I got this error: 
> >   File "/home/anton/Projects/proj/core/model/messages.py", line 210, in 
> > create 
> > Session.bulk_insert_mappings(MessageEntity, to_commit) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/scoping.py", line 
> 157, 
> > in do 
> > return getattr(self.registry(), name)(*args, **kwargs) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 
> 2345, 
> > in bulk_insert_mappings 
> > mapper, mappings, False, False, return_defaults, False) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 
> 2416, 
> > in _bulk_save_mappings 
> > transaction.rollback(_capture_exception=True) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py", 
> line 
> > 60, in __exit__ 
> > compat.reraise(exc_type, exc_value, exc_tb) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 
> 2411, 
> > in _bulk_save_mappings 
> > mapper, mappings, transaction, isstates, return_defaults) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/persistence.py", 
> line 
> > 35, in _bulk_insert 
> > "connection_callable / per-instance sharding " 
> > NotImplementedError: connection_callable / per-instance sharding not 
> > supported in bulk_insert() 
> > 
> > I do not understand what 'connection_callable' does, but I really need 
> to 
> > implement it. Is there any ways to do it? 
>
> The idea of bulk_insert() is strictly one of performance with some 
> degree of convenience; you don't need it to actually accomplish 
> anything as there are many other ways to achieve what it does.   the 
> "connection_callable" thing is specifically so that an ORM persistence 
> operation can get at the appropriate database connection for a 
> particular INSERT/UPDATE/DELETE, however this adds complexity so is 
> not part of bulk_insert(). 
>
> In the case of application side horizontal sharding, you'd want to 
> take the data you're passing to it and pre-sort it into individual 
> per-shard sets, then use a Session per shard to run the inserts. 
>
> Also, application side horizontal sharding is not a great way to do 
> sharding in any case.  If you're on Postgresql I'd strongly recommend 
> using table inheritance instead. 
>
 
Yes, Postgresql used into a project. But I can't use inheritance, because I 
have several servers, each of them located in separate country. 
Yes, I could use inheritance and FDW, but in that case system become more 
complicated.

When application instance runs on master node - it can read and write to 
master database and can only read from geo-slaves.
When application instance runs on geo-slave - it can read from master, read 
from replicated table on current node and write data (some statistics) to 
some table on current node.
Periodically master read data-tables (statistics) from each geo-slave and 
write new data on master database.


Previously my project used something like that:
class ClusterMeta(object):
_pg_config = {}
_pg_sessions = defaultdict(list)
is_slave = config.get('default', 'is_slave')
prefix = config.get('default', 'prefix')

def _get_pg_conf(self):
for name, conn_string in config['sqlalchemy'].items():
if name.startswith('conn_string_slave_'):
sp = name.replace('conn_string_slave_', '').lower()
self._pg_config[sp] = conn_string
elif name in ['conn_string_master', 'conn_string']:
sp = 'default' if name == 'conn_string' else 'master'
if sp in self._pg_config:
raise ValueError('{} conn_string already 
configured!'.format(sp.upper()))
else:
self._pg_config[sp] = conn_string

def _create_pg_session(self, conn_string):
"""
:rtype: tuple(base, session)
"""
engine = create_engine(conn_string,
   convert_unicode=True)

session = ScopedSession(sessionmaker(bind=engine))
base = declarative_base()
base.query = session.query_property()

return base, session

   def get_session(self, prefix, with_base=False):
if prefix not in self._pg_sessions:
conn_string = self._pg_config.get(

[sqlalchemy] horizontal sharding and bulk_insert

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

Hi guys
I tried to implement horizontal sharding 
 
in my project. Everything is ok, except bulk_inserts.
When I run tests, I got this error:
  File "/home/anton/Projects/proj/core/model/messages.py", line 210, in 
create
Session.bulk_insert_mappings(MessageEntity, to_commit)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/scoping.py", line 157, 
in do
return getattr(self.registry(), name)(*args, **kwargs)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 2345, 
in bulk_insert_mappings
mapper, mappings, False, False, return_defaults, False)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 2416, 
in _bulk_save_mappings
transaction.rollback(_capture_exception=True)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py", line 
60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 2411, 
in _bulk_save_mappings
mapper, mappings, transaction, isstates, return_defaults)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/persistence.py", line 
35, in _bulk_insert
"connection_callable / per-instance sharding "
NotImplementedError: connection_callable / per-instance sharding not 
supported in bulk_insert()

I do not understand what 'connection_callable' does, but I really need to 
implement it. Is there any ways to do it?

If it can help: I ALWAYS perform UPDATE/INSERT/DELETE and most of SELECT 
queries in only one database. So, I wrote such subclass:


class ShardedSessionWithDefaultBind(ShardedSession):
def get_bind(self, mapper, shard_id=None, instance=None, clause=None, **kw):
if shard_id is None:
shard_id = default_shard_id
return super(ShardedSessionWithDefaultBind, self).get_bind(mapper, 
shard_id, instance, clause, **kw)


Maybe I can override "__init__" for my class and write 
"self.connnection_callable = None"? 
My research of sqlalchemy code didn't make me sure that it is safe enough. 
But I see, that "connection_callable" used only for checking into 
"_bulk_insert" and "_bulk_update" functions in sqlalchemy.orm.persistence 
module. 
So, if I 100% sure, that I ALWAYS perform INSERT/UPDATE/DELETE queries in 
only one database, can I make 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 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] enable/disable event listeners

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

suggested solution (stackoverslow 
<https://stackoverflow.com/questions/44947831/way-to-disable-sqlalchemy-event-listeners/44985148#44985148>
):

import ctypes
from sqlalchemy import event

def clear_event_listeners(model_or_session):
keys = [k for k in event.registry._key_to_collection if k[0] == 
id(model_or_session)]
for key in keys:
target = model_or_session
identifier = key[1]
fn = ctypes.cast(key[2], ctypes.py_object).value  # get function by id
event.remove(target, identifier, fn)



суббота, 8 июля 2017 г., 11:13:17 UTC+3 пользователь Антонио Антуан написал:
>
>  
>
>>
>> On Fri, Jul 7, 2017 at 3:07 PM, Антонио Антуан <a.ch...@gmail.com> 
>> wrote: 
>> > Looks like it is not possible, isn't 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+...@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. 
>>
>
>
> суббота, 8 июля 2017 г., 0:00:02 UTC+3 пользователь Mike Bayer написал:
>>
>> oh, sorry, forgot this. 
>>
>> you need to register a cleanup for each event you add, such as: 
>>
>> def setUp(self): 
>> event.listen(SomeThing, "some_event", my_handler) 
>> self.addCleanup(event.remove, SomeThing, "some_event", my_handler) 
>>
>> or whatever mechanism your test suite provides. 
>>
>> There of course is a way to clear out event handlers globally but 
>> SQLAlchemy reserves the right to use its own handlers internally and 
>> these can't be cleared out without breaking the library. 
>>
>
> I don't know (and in case of tests, don't want to know) what events are 
> bound to my model, session and query. 
> So, I want to call something like that:
>
>
> from my_project.model import Session, UsedModel
>
>
> @pytest.fixture(scope='session', autouse=True)
> def disable_sqla_events():
> # setup
> Session.disable_all_bound_events()
> UsedModel.disable_all_bound_events()
> yield
> # teardown
> Session.enable_events()
> UsedModel.enable_events()
>
> Looks like such mechanism does not available, so I should implement it. 
> I'd tried to understand, where and how events-objects stored and bouned 
> with particular sessions and models, but I've failed :)
>

-- 
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] enable/disable event listeners

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

 

>
> On Fri, Jul 7, 2017 at 3:07 PM, Антонио Антуан <a.ch...@gmail.com 
> > wrote: 
> > Looks like it is not possible, isn't 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+...@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. 
>


суббота, 8 июля 2017 г., 0:00:02 UTC+3 пользователь Mike Bayer написал:
>
> oh, sorry, forgot this. 
>
> you need to register a cleanup for each event you add, such as: 
>
> def setUp(self): 
> event.listen(SomeThing, "some_event", my_handler) 
> self.addCleanup(event.remove, SomeThing, "some_event", my_handler) 
>
> or whatever mechanism your test suite provides. 
>
> There of course is a way to clear out event handlers globally but 
> SQLAlchemy reserves the right to use its own handlers internally and 
> these can't be cleared out without breaking the library. 
>

I don't know (and in case of tests, don't want to know) what events are 
bound to my model, session and query. 
So, I want to call something like that:


from my_project.model import Session, UsedModel


@pytest.fixture(scope='session', autouse=True)
def disable_sqla_events():
# setup
Session.disable_all_bound_events()
UsedModel.disable_all_bound_events()
yield
# teardown
Session.enable_events()
UsedModel.enable_events()

Looks like such mechanism does not available, so I should implement it. 
I'd tried to understand, where and how events-objects stored and bouned 
with particular sessions and models, but I've failed :)

-- 
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] enable/disable event listeners

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

Looks like it is not possible, isn't 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 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] enable/disable event listeners

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

Hi all.

In case of unit-testing I need to disable all event listeners on 
model/session/etc. When particular test finished I need to enable all 
listeners.
Is there any ways to achieve this?

-- 
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] how not to add tablename as column prefix

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

вторник, 6 июня 2017 г., 19:46:43 UTC+3 пользователь Mike Bayer написал:
>
>
>
> On 06/06/2017 11:14 AM, Антонио Антуан wrote: 
> > Hi. 
> > I want to generate query without tablename as columns prefix. How can I 
> > do this? 
> > For example, query /Session.query(table.id).filter(table.time > 
> > func.now())/ compiles to /SELECT table.id FROM table WHERE table.time > 
> > now(). /I want to get /SELECT id FROM table WHERE time > now(). / 
> > / 
> > / 
> > I want it, because ClickHouse has its own syntax, and in some cases it 
> > is far from SQL standards. 
> > Here is my model and query, they as same as my real model and real 
> query: 
> > 
> > | 
> > fromsqlalchemy importcoalesce,case,func, Column 
> > from clickhouse_sqlalchemy import types, engines 
>
>
> what is "clickhouse_sqlalchemy" ?  Are you writing a custom dialect for 
> a database called "clickhouse"? 


> if so, then you'd want to customize how the column name is generated in 
> the visit_column() method in your compiler.  Make sure the 
> "include_table" keyword argument is False: 
>
>  
No, that is not my library, but now I see what was wrong :)
Thanks for your help.
 

> class MyClickhouseCompiler(SQLCompiler): 
>  def visit_column(self, *arg, **kw): 
>  kw['include_table'] = False 
>  super(MyClickhouseCompiler, self).visit_column(*arg, **kw) 
>
>
>
>
> > 
> > from project import Base 
> > 
> > class Model(Base): 
> >  __table__ = 'analytics' 
> > 
> >  ts_spawn = Column(types.UInt32, primary_key=True) 
> >   entity_id = Column(types.UInt32) 
> >   condition = Column(types.UInt32) 
> > 
> >   clicks = Column(types.UInt32) 
> > 
> > 
> Session.query(Model.entity_id.label('group_by'),coalesce(func.sum(case([(Model.condition
>  
>
> >  >0,Model.clicks)],else_=0)).label('clicks')).group_by(Model.entity_id) 
> > 
> > | 
> > 
> > Logs: 
> > 
> > | 
> > 2017-06-06 18:09:04,270 INFO sqlalchemy.engine.base.Engine BEGIN 
> (implicit) 
> > 2017-06-06 18:09:04,270 | sqlalchemy.engine.base.Engine | info:109 | 
> > INFO | BEGIN (implicit) 
> > 2017-06-06 18:09:04,274 INFO sqlalchemy.engine.base.Engine SELECT 
> > analytics.entity_id AS group_by, coalesce(sum(CASE WHEN 
> > (analytics.condition > 0) THEN analytics.clicks ELSE 0 END), 0) AS 
> clicks 
> > FROM analytics 
> > GROUP BY analytics.entity_id FORMAT TabSeparatedWithNamesAndTypes 
> > 2017-06-06 18:09:04,274 | sqlalchemy.engine.base.Engine | info:109 | 
> > INFO | SELECT analytics.entity_id AS group_by, coalesce(sum(CASE WHEN 
> > (analytics.condition > 0) THEN analytics.clicks ELSE 0 END), 0) AS 
> clicks 
> > FROM analytics 
> > GROUP BY analytics.entity_id FORMAT TabSeparatedWithNamesAndTypes 
> > 2017-06-06 18:09:04,276 INFO sqlalchemy.engine.base.Engine {} 
> > 2017-06-06 18:09:04,276 | sqlalchemy.engine.base.Engine | info:109 | 
> > INFO | {} 
> > Traceback (most recent call last): 
> >File "/opt/pycharm-professional/helpers/pydev/pydevd.py", line 1585, 
> > in  
> >  globals = debugger.run(setup['file'], None, None, is_module) 
> >File "/opt/pycharm-professional/helpers/pydev/pydevd.py", line 1015, 
> > in run 
> >  pydev_imports.execfile(file, globals, locals)  # execute the script 
> >File "/home/anton/Projects/project/core/project/core/run/stuff.py", 
> > line 10, in  
> >  print Statistics(Statistics.groups.entity_id, 0, int(time.time()), 
> > Statistics.columns.clicks).total 
> >File 
> > "/home/anton/Projects/project/core/project/core/util/stat/stat.py", line 
> > 612, in total 
> >  self.get_data() 
> >File 
> > "/home/anton/Projects/project/core/project/core/util/stat/stat.py", line 
> > 452, in get_data 
> >  self.calculate() 
> >File 
> > "/home/anton/Projects/project/core/project/core/util/stat/stat.py", line 
> > 512, in calculate 
> >  self._get_raw_data() 
> >File 
> > "/home/anton/Projects/project/core/project/core/util/stat/stat.py", line 
> > 798, in _get_raw_data 
> >  for row in query.session.execute(compiled_query): 
> >File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 
> > 1097, in execute 
> >File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 
> > 914, in execute 
> >File "bu

[sqlalchemy] how not to add tablename as column prefix

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

Hi.
I want to generate query without tablename as columns prefix. How can I do 
this?
For example, query *Session.query(table.id).filter(table.time > func.now())* 
compiles 
to *SELECT table.id FROM table WHERE table.time > now(). *I want to get *SELECT 
id FROM table WHERE time > now(). *

I want it, because ClickHouse has its own syntax, and in some cases it is 
far from SQL standards. 
Here is my model and query, they as same as my real model and real query:

from sqlalchemy import coalesce, case, func, Column
from clickhouse_sqlalchemy import types, engines

from project import Base

class Model(Base):
__table__ = 'analytics'

ts_spawn = Column(types.UInt32, primary_key=True)
 entity_id = Column(types.UInt32)
 condition = Column(types.UInt32)

 clicks = Column(types.UInt32)

Session.query(Model.entity_id.label('group_by'), coalesce(func.sum(case([(
Model.condition > 0, Model.clicks)], else_=0)).label('clicks')).group_by(
Model.entity_id)


Logs:

2017-06-06 18:09:04,270 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-06-06 18:09:04,270 | sqlalchemy.engine.base.Engine | info:109 | INFO | 
BEGIN (implicit)
2017-06-06 18:09:04,274 INFO sqlalchemy.engine.base.Engine SELECT analytics.
entity_id AS group_by, coalesce(sum(CASE WHEN (analytics.condition > 0) 
THEN analytics.clicks ELSE 0 END), 0) AS clicks 
FROM analytics 
GROUP BY analytics.entity_id FORMAT TabSeparatedWithNamesAndTypes
2017-06-06 18:09:04,274 | sqlalchemy.engine.base.Engine | info:109 | INFO | 
SELECT analytics.entity_id AS group_by, coalesce(sum(CASE WHEN 
(analytics.condition > 0) THEN analytics.clicks ELSE 0 END), 0) AS clicks 
FROM analytics 
GROUP BY analytics.entity_id FORMAT TabSeparatedWithNamesAndTypes
2017-06-06 18:09:04,276 INFO sqlalchemy.engine.base.Engine {}
2017-06-06 18:09:04,276 | sqlalchemy.engine.base.Engine | info:109 | INFO | 
{}
Traceback (most recent call last):
  File "/opt/pycharm-professional/helpers/pydev/pydevd.py", line 1585, in 

globals = debugger.run(setup['file'], None, None, is_module)
  File "/opt/pycharm-professional/helpers/pydev/pydevd.py", line 1015, in 
run
pydev_imports.execfile(file, globals, locals)  # execute the script
  File "/home/anton/Projects/project/core/project/core/run/stuff.py", line 
10, in 
print Statistics(Statistics.groups.entity_id, 0, int(time.time()), 
Statistics.columns.clicks).total
  File "/home/anton/Projects/project/core/project/core/util/stat/stat.py", 
line 612, in total
self.get_data()
  File "/home/anton/Projects/project/core/project/core/util/stat/stat.py", 
line 452, in get_data
self.calculate()
  File "/home/anton/Projects/project/core/project/core/util/stat/stat.py", 
line 512, in calculate
self._get_raw_data()
  File "/home/anton/Projects/project/core/project/core/util/stat/stat.py", 
line 798, in _get_raw_data
for row in query.session.execute(compiled_query):
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 1097, 
in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 914, 
in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 323, 
in _execute_on_connection
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1010, 
in _execute_clauseelement
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1146, 
in _execute_context
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1344, 
in _handle_dbapi_exception
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1139, 
in _execute_context
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 
450, in do_execute
  File 
"/home/anton/Projects/project/.venv/lib/python2.7/site-packages/clickhouse_sqlalchemy/connector.py",
 
line 98, in execute
self._process_response(response_gen)
  File 
"/home/anton/Projects/project/.venv/lib/python2.7/site-packages/clickhouse_sqlalchemy/connector.py",
 
line 185, in _process_response
self._columns = next(response, None)
  File 
"/home/anton/Projects/project/.venv/lib/python2.7/site-packages/clickhouse_sqlalchemy/transport.py",
 
line 36, in execute
r = self._send(query, params=params, stream=True)
  File 
"/home/anton/Projects/project/.venv/lib/python2.7/site-packages/clickhouse_sqlalchemy/transport.py",
 
line 66, in _send
raise DatabaseException(r.text)
clickhouse_sqlalchemy.exceptions.DatabaseException: Code: 47, 
e.displayText() = DB::Exception: Unknown identifier: analytics.condition, 
e.what() = DB::Exception

And that query works normally:

SELECT entity_id AS group_by, coalesce(sum(CASE WHEN (condition > 0) THEN 
clicks ELSE 0 END), 0) AS clicks 
FROM analytics 
GROUP BY entity_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.
--- 

Re: [sqlalchemy] autogenerate migrations with alembic for inherited tables

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

пятница, 21 апреля 2017 г., 17:07:34 UTC+3 пользователь Mike Bayer написал:
>
>
>
> On 04/21/2017 09:16 AM, Антонио Антуан wrote: 
> > Helllo. 
> > I have a model, with specified __tablename__ = 'base_table'. 
> > In postgresql, the table has trigger, which executes before each insert: 
> > it creates partition for current month (if it not exist yet), specifies 
> > "INHERITS (base_table)" for new partition and insert data into it. 
> > 
> > Is there any way to autodetect table inheritance and generate migration 
> > script for it? 
>
>
> What's the actual migration you want to generate?   E.g. start with A, 
> change to Bare you starting with Table(), and then adding 
> "postgresql_inherits" to it after the fact?  it's not clear. 
>
> if you can illustrate what this migration would be I'd have some idea 
> what you are actually trying to do.The "trigger" you refer to seems 
> to be something that emits CREATE TABLE upon INSERT, which would be 
> outside the realm of Alembic. 
>

For example, here is my model:
import time
from sqlalchemy import Column, Integer, Unicode, event, DDL
from myproject import Base

class Foo(Base):
__tablename__ = 'foos'
id = Column(Integer, primary_key=True, autoincrement=True)
ts_spawn = Column(Integer, nullable=False)
name = Column(Unicode, nullable=False)
data_id = Column(Integer, index=True)

def __init__(self):
self.ts_spawn = int(time.time())

event.listen(Foo.__table__, 'after_create', 
 DDL("""
CREATE OR REPLACE FUNCTION foos_insert_trigger() RETURNS TRIGGER AS 
$BODY$
DECLARE
table_master VARCHAR(255) := 'foos';
table_part VARCHAR(255) := '';
ts_spawn_date DATE := to_timestamp(NEW.ts_spawn);
ts_start INT := date_part('epoch', date_trunc('day', 
ts_spawn_date))::INT;
ts_end INT := date_part('epoch', date_trunc('day', ts_spawn_date + 
INTERVAL '1day))::INT;
BEGIN

-- Giving name for partition --
table_part := table_master
  || '_y' || DATE_PART('year', ts_spawn_date)::TEXT
  || '_m' || DATE_PART('month', ts_spawn_date)::TEXT
  || '_d' || DATE_PART('day', ts_spawn_date)::TEXT;

-- Trying to insert into partition 
EXECUTE 'INSERT INTO ' || quote_ident(table_part) || ' SELECT ($1).*' USING NEW;
RETURN NULL;
-- If not then creating it 
EXCEPTION WHEN UNDEFINED_TABLE
THEN
BEGIN
-- Creating partition ---

EXECUTE 'CREATE TABLE ' || table_part || ' (
CHECK ( ts_spawn >= ' || ts_start || ' AND ts_spawn < ' || 
ts_end || '),

CONSTRAINT ' || table_part || '_pk PRIMARY KEY
(id)
) INHERITS ( ' || table_master || ' ) WITH ( OIDS=FALSE )';
EXECUTE 'CREATE INDEX ids_' || table_part || '_data_id
ON ' || table_part || ' USING btree (data_id);'

EXCEPTION WHEN DUPLICATE_TABLE THEN
-- Do nothing

EXECUTE 'INSERT INTO ' || quote_ident(table_part) || ' SELECT ($1).*' USING NEW;
RETURN NULL;

END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

CREATE TRIGGER insert_foo_trigger BEFORE INSERT
ON foos
FOR EACH ROW
EXECUTE PROCEDURE foos_insert_trigger();

"""))


Here is definition.
PostgreSQL ensures that all columns that are in "table_master" will be in 
the inherited tables, but the indexes won't: I should create them manually 
by describing it in procedure code. So, if I add new index, I should add 
them into trigger code.

That is my migration:
from alembic importop
import sqlalchemy as sa

revision = '77e958e7e1bd'
down_revision = 'e47e752436d1'

def upgrade():
   op.add_column('foos', sa.Column('new_column', sa.Integer))
   op.create_index('ix_foos_name', 'foos', ['name'])


After execution, table "foos" and its children will have new column 
("new_column"), but new index will be only into "foos" table.
So, currently I should get all partitions and execute "CREATE INDEX" 
manually, something like that:

conn = op.get_bind()

for child_name in conn.execute(text("select child.relname from 
pg_catelog.pg_ingerits 
INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid = 
child.oid WHERE inhparent = 'foos'::regclass")):
   op.create_index('ix_{table}_name'.format(table=child_name), child_name, [
'name'])


First of all, it would be cool, If I did not have to insert "CREATE INDEX" 
statement manually in trigger code. But it seems to be impossible now :)
Also, as you can see, I should manually find each partition of master table 
and manually create index for it. Can alembic do it himself?




>
> > 
> > If autodetection not working

[sqlalchemy] autogenerate migrations with alembic for inherited tables

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

Helllo.
I have a model, with specified __tablename__ = 'base_table'.
In postgresql, the table has trigger, which executes before each insert: it 
creates partition for current month (if it not exist yet), specifies 
"INHERITS (base_table)" for new partition and insert data into it.

Is there any way to autodetect table inheritance and generate migration 
script for it?

If autodetection not working... 
I can get list of inherited tables with such query:
SELECT child.relname, parent.relname 
FROM pg_catalog.pg_inherits 
 INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid = 
child.oid) 
 INNER JOIN pg_catalog.pg_class as parent ON (pg_inherits.inhparent = 
parent.oid) 
WHERE inhparent = 'base_table'::regclass;

Returned names can be specified as parameter "only" in "reflect()" method 
of MetaData instance. Can I specify target table for each table in metadata 
for migration?
I found just such solution:
for table_name in inherit_table_names:
meta_data.tables[table_name].name = "base_table"

Is my solution safe?

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

http://www.sqlalchemy.org/

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

Re: [sqlalchemy] postgresql tuple as function argument

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

Ok, I'l try it, thank you.

вторник, 11 апреля 2017 г., 17:26:18 UTC+3 пользователь Mike Bayer написал:
>
> I will say that the psycopg2 driver is not supporting this, however, it 
> isn't parsing out the tuple. Using postgresql.ARRAY we get an answer 
> like: 
>
> ['{', '"', '(', 'x', ',', 'y', ')', '"', ',', '"', '(', 'x', ',', 'y', 
> ')', '"', '}'] 
>
> that is, the string coming back is being interpreted as an array by 
> SQLAlchemy, which here it is not.you might need to use some of the 
> psycopg2 extension points to support this like 
>
> http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.new_array_type.
>  
>
>
>
>
> On 04/11/2017 09:43 AM, mike bayer wrote: 
> > can't reproduce (though the ARRAY(unicode) type is not what psycopg2 
> > returns, and there seems to be a difference in behavior between 
> > sqlalchemy.ARRAY and sqlalchemy.dialects.postgresql.ARRAY). 
> > 
> > please provide a complete example based on the below test script and 
> > stack traces 
> > 
> > also my PG database doesn't know about the jsonb_object_agg function 
> > 
> > from sqlalchemy import * 
> > from sqlalchemy.orm import * 
> > from sqlalchemy.ext.declarative import declarative_base 
> > 
> > Base = declarative_base() 
> > 
> > 
> > class A(Base): 
> > __tablename__ = 'a' 
> > id = Column(Integer, primary_key=True) 
> > x = Column(Unicode) 
> > y = Column(Unicode) 
> > 
> > e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) 
> > Base.metadata.drop_all(e) 
> > Base.metadata.create_all(e) 
> > 
> > s = Session(e) 
> > s.add(A(x="x", y="y")) 
> > s.commit() 
> > 
> > row = s.query(func.array_agg(tuple_(A.x, A.y), 
> > type_=ARRAY(Unicode))).scalar() 
> > print row 
> > 
> > 
> > 
> > 
> > 
> > On 04/11/2017 09:04 AM, Антонио Антуан wrote: 
> >> Hi 
> >> I want to build such query with sqlalchemy: 
> >> | 
> >> SELECT array_agg((column1,column2))fromtable 
> >> | 
> >> 
> >> Using psql it works perfectly and returns such result: 
> >> | 
> >> {"(col1_row1_value, col2_row1_value)","(col1_row2_value, 
> >> col2_row2_value)"...} 
> >> | 
> >> 
> >> I tried several forms of SQLA-query: 
> >> 
> >> |>> from sqlalchemy.dialects.postgresql import ARRAY 
> >>>> from sqlalchemy.sql.elements import Tuple 
> >>>> ... 
> >>>> func.array_agg(Tuple(Model.col1,Model.col2),type_=ARRAY(Unicode))... 
> >> ... 
> >> TypeError: unhashable type: 'list' 
> >>>> ... func.array_agg(Tuple(Model.col1, Model.col2), 
> type_=ARRAY(Unicode, 
> >> as_tuple=True))... 
> >> ...# returns value like this: ('{', '"', '(', 'c',...) 
> >>>> ... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Tuple, 
> >> as_tuple=True))... 
> >> ... 
> >> AttributeError: Neither 'Tuple' object nor 'Comparator' object has an 
> >> attribute 'dialect_impl' 
> >> | 
> >> 
> >> At first, I wanted to use /`func.jsonb_object_agg(Model.col1, 
> >> Model.col2)`/, but it raises */"unhashable type: dict"/* 
> >> */ 
> >> /* 
> >> Could you point to solution? 
> >> 
> >> -- 
> >> SQLAlchemy - 
> >> The Python SQL Toolkit and Object Relational Mapper 
> >> 
> >> http://www.sqlalchemy.org/ 
> >> 
> >> To post example code, please provide an MCVE: Minimal, Complete, and 
> >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> >> description. 
> >> --- 
> >> You received this message because you are subscribed to the Google 
> >> Groups "sqlalchemy" group. 
> >> To unsubscribe from this group and stop receiving emails from it, send 
> >> an email to sqlalchemy+...@googlegroups.com  
> >> <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> >> To post to this group, send email to sqlal...@googlegroups.com 
>  
> >> <mailto: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.

Re: [sqlalchemy] postgresql tuple as function argument

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

your imports can't provide ARRAY class, I've added: from 
sqlalchemy.dialects.postgresql import ARRAY. My version is 1.0.14.
Output:
/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py:185:
 
SAWarning: Unicode type received non-unicode bind param value 'y'. (this 
warning may be suppressed after 10 occurrences)
  (util.ellipses_string(value),))
/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py:185:
 
SAWarning: Unicode type received non-unicode bind param value 'x'. (this 
warning may be suppressed after 10 occurrences)
  (util.ellipses_string(value),))
['{', '"', '(', 'x', ',', 'y', ')', '"', '}']


And about jsonb_object_agg. I use PostgreSQL 9.5. If you use older version, 
you can try to replace it with json_object_agg.
I wrote such code:



from sqlalchemy import *
from sqlalchemy.dialects.postgresql import ARRAY

from hasoffers.core.model import Base
from hasoffers.core.model import Session


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


class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True, autoincrement=True)
a_keys = Column(ARRAY(Integer))
col1 = Column(Unicode)
col2 = Column(Unicode)

Base.metadata.bind = Session.bind
Base.metadata.create_all()

Session.add(A(x="x", y="y"))
Session.add(A(x="f", y="j"))
Session.add(A(x="b", y="s"))
Session.add(B(a_keys=[1], col1="qaz", col2="qwe"))
Session.add(B(a_keys=[2,3], col1="zaq", col2="fds"))
Session.add(B(a_keys=[2,3], col1="gtr", col2="ascs"))
Session.commit()

for row in Session.query(A, func.jsonb_object_agg(B.col1, B.col2)).join(B, 
A.id == func.any(B.a_keys)).group_by(A.id):

print row


After execution I got such traceback:

Traceback (most recent call last):
  File "/home/anton/Projects/.../core/hasoffers/core/run/stuff.py", line 
33, in 
for row in Session.query(A, func.jsonb_object_agg(B.col1, 
B.col2)).join(B, A.id == func.any(B.a_keys)).group_by(A.id):
  File 
"/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
 
line 86, in instances
util.raise_from_cause(err)
  File 
"/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
 
line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File 
"/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
 
line 77, in instances
rows = util.unique_list(rows, filter_fn)
  File 
"/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/util/_collections.py",
 
line 757, in unique_list
if hashfunc(x) not in seen
TypeError: unhashable type: 'dict'




вторник, 11 апреля 2017 г., 16:43:20 UTC+3 пользователь Mike Bayer написал:
>
> can't reproduce (though the ARRAY(unicode) type is not what psycopg2 
> returns, and there seems to be a difference in behavior between 
> sqlalchemy.ARRAY and sqlalchemy.dialects.postgresql.ARRAY). 
>
> please provide a complete example based on the below test script and 
> stack traces 
>
> also my PG database doesn't know about the jsonb_object_agg function 
>
> from sqlalchemy import * 
> from sqlalchemy.orm import * 
> from sqlalchemy.ext.declarative import declarative_base 
>
> Base = declarative_base() 
>
>
> class A(Base): 
>  __tablename__ = 'a' 
>  id = Column(Integer, primary_key=True) 
>  x = Column(Unicode) 
>  y = Column(Unicode) 
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) 
> Base.metadata.drop_all(e) 
> Base.metadata.create_all(e) 
>
> s = Session(e) 
> s.add(A(x="x", y="y")) 
> s.commit() 
>
> row = s.query(func.array_agg(tuple_(A.x, A.y), 
> type_=ARRAY(Unicode))).scalar() 
> print row 
>
>
>
>
>
> On 04/11/2017 09:04 AM, Антонио Антуан wrote: 
> > Hi 
> > I want to build such query with sqlalchemy: 
> > | 
> > SELECT array_agg((column1,column2))fromtable 
> > | 
> > 
> > Using psql it works perfectly and returns such result: 
> > | 
> > {"(col1_row1_value, col2_row1_value)","(col1_row2_value, 
> > col2_row2_value)"...} 
> > | 
> > 
> > I tried several forms of SQLA-query: 
> > 
> > |>> from sqlalchemy.dialects.postgresql import ARRAY 
> >>> from sqlalchemy.sql.elements import Tuple 
> >>> ... 
> func.array_agg(Tuple(Model.col1,Model.col2),type_=ARRAY(Unicode))... 
> > ... 
> > TypeError: unhashable type: 'list' 
> >>> ... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Unicode, 
> > as_tuple=

[sqlalchemy] postgresql tuple as function argument

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

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

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

I tried several forms of SQLA-query:

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

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

Could you point to solution?

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

http://www.sqlalchemy.org/

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

Re: [sqlalchemy] How to get `func` attribute from hybrid_method

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

вторник, 14 марта 2017 г., 17:15:25 UTC+3 пользователь Mike Bayer написал:
>
>
>
> On 03/14/2017 10:02 AM, Антонио Антуан wrote: 
> > Hi. 
> > I use sqlalchemy 1.0.17, thanks for you great library :) 
> > 
> > I have such code: 
> > | 
> > 
> > 
> > classFlagsMixin(object): 
> > @hybrid_method 
> > defexist_flag(self,flag): 
> > ifnotself.flags ornotflag: 
> > returnFalse 
> > returnself.flags  ==flag 
> > 
> > @exist_flag.expression 
> > defexist_flag(self,flag): 
> > returnand_(flag !=0,self.flags !=0,self.flags.op('&')(flag)!=0) 
> > 
> > 
> > classSomeModel(Base,FlagsMixin,AnyOtherClass): 
> > # class attrs and methods 
> > 
> > | 
> > 
> > 
> > In case of optimization, I want to create sqlalchemy-free class, 
>
> what sort of optimization?  are you having a performance problem? 
>


Just hardcode optimization, no preformance problems, don't worry :)

>
>
> which 
> > contains all methods and attributes of class. So, i wrote that code: 
> > 
> > | 
> > 
> > 
> > @staticmethod 
> > def_get_sa_free_class(klass,with_mro=False): 
> > sa_free_cls =getattr(klass,'__sa_free_cls',None) 
> > 
> > ifsa_free_cls isNone: 
> > items ={} 
> > klasses =[klass]ifnotwith_mro elseklass.__mro__ 
> > fork inklasses: 
> > forname,val ink.__dict__.items(): 
> > ifname notinitems andname 
> > 
> notin['__mapper__','__dict__','__class__','_sa_class_manager','query','metadata']:
>  
>
> > ifisinstance(val,InstrumentedAttribute): 
> > items[name]=None 
> > else: 
> > items[name]=val 
> > formethod_name in('exist_flag',): 
> > items[method_name]=getattr(klass,method_name) 
> > sa_free_cls 
> =type('Unbound'+klass.__name__,(object,),dict(items)) 
> > sa_free_cls._sa_free =True 
> > 
> > sa_free_cls.__init__ 
> =klass._sa_class_manager.original_init.im_func 
> > 
> > setattr(klass,'__sa_free_cls',sa_free_cls) 
> > returnsa_free_cls 
>
> So what you're trying to do is:  1. create a class   2. map it   3. then 
> extract it into something unmapped. this seems backwards.  Why not 
> create your class as "unmapped", then make a subclass of it that is 
> mapped? 
>
>
> class MyOptimizedClass(FlagsMixin): 
>  #  stuff 
>
>
> class MyMappedClass(MyOptimizedClass, Base): 
>  __tablename__ = ... 
>
>  # stuff 
>
>
> Or something like that, where you make use of your descriptor 
> declaration directly instead of reverse engineering it. 
>
> > Result class should has `exist_flag()` method, and it has to be used 
> > always with `func` attribute of hybrid_property, neither `expr` (as I 
> > want to have SQLA-free class). 
> > 
> > As you can see, I want to ensure `exist_flag()` method existence in 
> > result `klass` object, and if method called with `with_mro=False`. 
> > But, of course, such a way can't allow this, because of descriptor logic 
> > - return `expr` if instance not specified: 
> > 
> > | 
> > klass.exist_flag(1) 
> >  
> > 
> > | 
> > 
> > Also, I tried to use this versions with same result - 
> AttributeError(''): 
> > 
> > 
> > | 
> > klass.exist_flag.func 
> > klass.exist_flag.__class__.func 
>
> well when you access the hybrid like that it gets invoked.   you'd need 
> to get it either as: 
>
> hybrid = klass.__class__.__dict__['exist_flag'] 
>
> >>> getattr(klass, 'exist_flag') 
> 
>>>  
>>> klass.__class__.__dict__['exist_flag']  
Traceback (most recent call last): 
 File "", line 1, in  
KeyError: 'exist_flag'

 

> or 
>
> from sqlalchemy import inspect 
> mapper = inspect(klass) 
> hybrid = mapper.all_orm_descriptors.exist_flag 
>
>
> you probably want to be using mapper.all_orm_descriptors to help with 
> your inspection. 
>

Yes, it helps, thank you :) 

>
> > ... 
> > 
> > AttributeError: 'function' object has no attribute 'func' 
> > 
> > | 
> > 
> > Is there any way to get `func` attribute of hybrid_method? 
> > And also, maybe sqlalchemy already has some alternative to get 
> > sqla-free-class? 
> > 
> > Thank you again. 
> > Anton. 
> > 
> > -- 
> > SQLAlchemy - 
> > The P

[sqlalchemy] How to get `func` attribute from hybrid_method

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

Hi.
I use sqlalchemy 1.0.17, thanks for you great library :)

I have such code:


class FlagsMixin(object):
@hybrid_method
def exist_flag(self, flag):
if not self.flags or not flag:
return False
return self.flags & flag == flag

@exist_flag.expression
def exist_flag(self, flag):
return and_(flag != 0, self.flags != 0, self.flags.op('&')(flag) != 0)


class SomeModel(Base, FlagsMixin, AnyOtherClass):
# class attrs and methods



In case of optimization, I want to create sqlalchemy-free class, which 
contains all methods and attributes of class. So, i wrote that code:


@staticmethod
def _get_sa_free_class(klass, with_mro=False):
sa_free_cls = getattr(klass, '__sa_free_cls', None)

if sa_free_cls is None:
items = {}
klasses = [klass] if not with_mro else klass.__mro__
for k in klasses:
for name, val in k.__dict__.items():
if name not in items and name not in ['__mapper__', '__dict__', 
'__class__', '_sa_class_manager', 'query', 'metadata']:
if isinstance(val, InstrumentedAttribute):
items[name] = None
else:
items[name] = val
for method_name in ('exist_flag', ):
items[method_name] = getattr(klass, method_name)
sa_free_cls = type('Unbound' + klass.__name__, (object, ), dict(items))
sa_free_cls._sa_free = True

sa_free_cls.__init__ = klass._sa_class_manager.original_init.im_func

setattr(klass, '__sa_free_cls', sa_free_cls)
return sa_free_cls



Result class should has `exist_flag()` method, and it has to be used always 
with `func` attribute of hybrid_property, neither `expr` (as I want to have 
SQLA-free class).

As you can see, I want to ensure `exist_flag()` method existence in result 
`klass` object, and if method called with `with_mro=False`.
But, of course, such a way can't allow this, because of descriptor logic - 
return `expr` if instance not specified:

klass.exist_flag(1)



Also, I tried to use this versions with same result - AttributeError(''):


klass.exist_flag.func
klass.exist_flag.__class__.func
...

AttributeError: 'function' object has no attribute 'func'


Is there any way to get `func` attribute of hybrid_method?
And also, maybe sqlalchemy already has some alternative to get 
sqla-free-class?

Thank you again.
Anton.

-- 
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] Set query timeout

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

Ok, for example, I'm talking about PostgreSQL (psycopg2). Which 
attribute/method should I use for setting query timeout?

пятница, 2 декабря 2016 г., 17:40:49 UTC+3 пользователь Mike Bayer написал:
>
> query timeouts are a database / driver dependent feature.It depends 
> on what kind of database you're using and you'd need to check the 
> documentation for that database on how that would be done. 
>
>
> On 12/02/2016 02:32 AM, Антонио Антуан wrote: 
> > Hi 
> > Is there any ways to set timeout for particular query, instead of the 
> > whole session/connection? 
> > 
> > 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+...@googlegroups.com  
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > <mailto: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] Set query timeout

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

Hi
Is there any ways to set timeout for particular query, instead of the whole 
session/connection?

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.

Re: [sqlalchemy] SAWarning shows not immediately, but after some time of app execution

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

Looks like `my_ids` become empty 'after some time of app execution'. How do
you initialize the variable?

вт, 19 июля 2016 г., 18:51 TomS. :

> Hi,
>
> We have Flask app which uses SQLAlchemy. Weird error started to happen
> recently. The difficulty is that we can't reproduce the error (/figure
> out conditions causing issue) - maybe someone could help. Any hints/tips
> would be appreciated.
>
> There is a part in the code which constructs IN in SQL:
>
> MyModel.id.in_(my_ids)
>
> For some cases my_ids is an empty list. It works without any problems,
> but after some time the same query (using empty list) starts to raise an
> exception:
>
> SAWarning: The IN-predicate on "MyModel.id" was invoked with an empty
> sequence. This results in a contradiction, which nonetheless can be
> expensive to evaluate.  Consider alternative strategies for improved
> performance.
>
> After restarting app, everything works again.
>
> The question is - why this exception is not risen always (although we
> tried to run app with empty list directly), but after some time of app
> execution (~1 day)?
>
> Details:
> Flask==0.10.1
> Flask-SQLAlchemy==2.1
> SQLAlchemy==1.0.14
> MySQL DB
>
> Cheers
>
>
> --
> 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.
>
-- 

Антон

-- 
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] Recursive CTE failing with (pysqlite2.dbapi2.OperationalError) no such table

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

`cteq_alias.union_all(...`

Also, you do not need to create cteq_alias, you can use cteq, like in your
query, but you have to replace cteq_alias.c.id with cteq.c.id

ср, 13 июл. 2016 г. в 15:20, Jonathan Underwood <
jonathan.underw...@gmail.com>:

> Hi,
>
> I am struggling to get a simple recursive CTE query to work with
> sqlalchemy 1.0.14, sqlite backend (3.8.10.2) and pysqlite 2.8.2, python
> 2.7.12. Below is a reproducer:
>
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import Column, String
> from sqlalchemy import orm
> from sqlalchemy import create_engine
> from sqlalchemy.orm import sessionmaker
>
> Base = declarative_base()
>
> class Obj(Base):
> __tablename__ = 'obj'
> id = Column(String, primary_key=True)
> parent_id = Column(String)
>
>
> engine = create_engine('sqlite:///')
> Base.metadata.create_all(engine)
> Session = sessionmaker(bind=engine)
> Session.configure(bind=engine)
> session = Session()
>
> a = Obj(id=u'1234')
> b = Obj(id=u'2345', parent_id='1234')
> c = Obj(id=u'3456', parent_id='1234')
> d = Obj(id=u'5678', parent_id='3456')
>
> session.add(a)
> session.add(b)
> session.add(c)
> session.add(d)
>
> session.commit()
>
> cteq = session.query(Obj).filter(Obj.parent_id == a.id).cte(recursive=True,
> name='cteq_n')
> cteq_alias = orm.aliased(cteq, name='cteq_alias_n')
> rekey_alias = orm.aliased(Obj, name='rekey_alias_n')
> r = cteq.union_all(
> session.query(rekey_alias).filter(rekey_alias.parent_id ==
> cteq_alias.c.id)
> )
>
> objs = session.query(Obj).select_from(r).all()
>
> print objs.all()
>
>
> which results in..
>
> sqlalchemy.exc.OperationalError: (pysqlite2.dbapi2.OperationalError) no
> such table: cteq_alias_n [SQL: u'WITH RECURSIVE cteq_n(id, parent_id) AS
> \n(SELECT obj.id AS id, obj.parent_id AS parent_id \nFROM obj \nWHERE
> obj.parent_id = ? UNION ALL SELECT rekey_alias_n.id AS rekey_alias_n_id,
> rekey_alias_n.parent_id AS rekey_alias_n_parent_id \nFROM obj AS
> rekey_alias_n, cteq_alias_n AS cteq_alias_n \nWHERE rekey_alias_n.parent_id
> = cteq_alias_n.id)\n SELECT obj.id AS obj_id, obj.parent_id AS
> obj_parent_id \nFROM obj, cteq_n'] [parameters: (u'1234',)]
>
>
> Any pointers gratefully received! I must be missing something stupid.
>
> Thanks,
> Jonathan
>
> --
> 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.
>
-- 

Антон

-- 
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: Specify query_cls for the one query

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

Amazing! :)
Big thanks.

ср, 13 июл. 2016 г. в 12:59, Simon King <si...@simonking.org.uk>:

> OK, I have to admit that I haven't fully understood the details of
> what you are doing. However, it sounds like you are implementing
> horizontal sharding, which SQLAlchemy has a bit of support for:
>
> http://docs.sqlalchemy.org/en/latest/orm/extensions/horizontal_shard.html
>
>
> http://docs.sqlalchemy.org/en/latest/_modules/examples/sharding/attribute_shard.html
>
> In the example, the query_chooser function is inspecting the query to
> see which backend database the query should be issued against, which I
> guess is what your "_analyze_base_query" method does.
>
> Would this work for you?
>
> Simon
>
>
> On Tue, Jul 12, 2016 at 3:03 PM, Антонио Антуан <a.ch@gmail.com>
> wrote:
> > Ok, I define my situation.
> > At first, I'm appologizing for my accent :)
> >
> > I have two databases, postgresql and vertica.
> > I have some amount of large tables, besides other tables.
> >
> > Postgres stores only last two hours (or last week, depends on table) of
> > large tables, vertica stores all other data.
> >
> > I implemented SplittedDataQuery, subclass of Query, which used with my
> > Session like query_cls.
> > The subclass overrides __iter__(), all(), count(), first(), one()
> methods.
> > It has attribute _base_query, which refers on self, _pg_query and
> > _vertica_query.
> >
> > Before invoking them, SplittedDataQuery checks necessity (by whereclause
> > inside query and `splitting_time` - moment when the data splitted between
> > databases) of getting data only from Pg, or only from vertica, or from
> both
> > with data union.
> >
> > Data from vertica returns by _vertica_query, data from postgres - by
> > _pg_query
> > _vertica_query binds with SessionVertica.
> > all other queries (_pg_query and _base_query) binds with Session
> >
> > Function, creates SessionVertica, has nothing unusual.
> >
> > Session for pg creates like this:
> > def _create_session(self, conn_string, splitted_query_cls=True):
> > engine = create_engine(conn_string)
> >
> > from core.splitted_data import SplittedDataQuery
> > session = ScopedSession(sessionmaker(bind=engine,
> > query_cls=SplittedDataQuery if splitted_query_cls else Query))  #
> query_cls
> > choosing only for SplittedDataQuery._pg_query
> >
> > return session
> >
> >
> > There is overrided all():
> > def all(self):
> > return self._get_result()
> >
> > Implementation of _get_result() looks like this:
> > def _get_result(self):
> > self._analyze_base_query()
> > self._split_query()
> > self._initialize_db_queries()
> > result = self._vertica_query.all()
> > if self._needs_to_get_from_pg:
> >result.extend(self._pg_query.all())
> >
> > _db_queries initialization inside SplittedDataQuery class:
> > def __init_pg_query(self):
> > original_session =
> > _create_session(config['sqlalchemy']['conn_string'],
> > splitted_query_cls=False)
> > self._pg_query = self._query.filter() \
> > .with_session(original_session)
> >
> >
> > def __init_vertica_query(self):
> > from hasoffers.core.model.meta import SessionVertica
> > self._vertica_query = self._query.filter() \
> > .with_session(SessionVertica)
> >
> > If I don't create _pg_query using splitted_query_cls=False (with std
> Query
> > class), then _pg_query invokation of methods (all(), one() and other)
> will
> > always apply overrided methods (because it uses SplittedDataQuery).
> > But, if I create _pg_query by defined way, it creates new session and,
> so,
> > new connection to Postgres.
> >
> > As you see, I don't want nor the first, neither the second option :)
> > вторник, 12 июля 2016 г., 13:35:48 UTC+3 пользователь Simon King написал:
> >>
> >> Could you describe what you are trying to achieve? There's nothing
> >> about Mike's suggestion that means you need to create a new session -
> >> you can reuse any existing session.
> >>
> >> What does your CustomQueryCls do? Perhaps there's another way of doing
> >> what you want?
> >>
> >> Simon
> >>
> >> On Tue, Jul 12, 2016 at 11:09 AM, Антонио Антуан <a.ch...@gmail.com>
> >> wrote:
> >> > But it means that I should create one more session with one more
> >> > conn

Re: [sqlalchemy] Re: Specify query_cls for the one query

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

Ok, I define my situation.
At first, I'm appologizing for my accent :)

I have two databases, postgresql and vertica.
I have some amount of large tables, besides other tables.

Postgres stores only last two hours (or last week, depends on table) of 
large tables, vertica stores all other data.

I implemented *SplittedDataQuery*, subclass of *Query*, which used with my 
*Session* like *query_cls*.
The subclass overrides *__iter__(), all(), count(), first(), one()* methods.
It has attribute *_base_query*, which refers on *self*, *_pg_query* and 
*_vertica_query*.

Before invoking them, *SplittedDataQuery* checks necessity (by whereclause 
inside query and `splitting_time` - moment when the data splitted between 
databases) of getting data only from Pg, or only from vertica, or from both 
with data union. 

Data from vertica returns by *_vertica_query*, data from postgres - by 
*_pg_query*
*_vertica_query *binds with* SessionVertica.*
all other queries (*_pg_query and _base_query*) binds with *Session*

Function, creates *SessionVertica*, has nothing unusual.

Session for pg creates like this:
def _create_session(self, conn_string, splitted_query_cls=True):
engine = create_engine(conn_string)

from core.splitted_data import SplittedDataQuery
session = ScopedSession(sessionmaker(bind=engine, query_cls=
SplittedDataQuery if splitted_query_cls else Query))  # query_cls choosing 
only for SplittedDataQuery._pg_query

return session


There is overrided *all():*
def all(self):
return self._get_result()

Implementation of *_get_result() *looks like this*:*
def _get_result(self):
self._analyze_base_query()
self._split_query()
self._initialize_db_queries()
result = self._vertica_query.all()
if self._needs_to_get_from_pg:
   result.extend(self._pg_query.all())

*_db_queries* initialization inside *SplittedDataQuery* class:
def __init_pg_query(self):
original_session = _create_session(config['sqlalchemy'][
'conn_string'], splitted_query_cls=False)
self._pg_query = self._query.filter() \
.with_session(original_session)


def __init_vertica_query(self):
from hasoffers.core.model.meta import SessionVertica
self._vertica_query = self._query.filter() \
.with_session(SessionVertica)

If I don't create *_pg_query* using *splitted_query_cls=**False* (with std 
Query class)*, *then _pg_query invokation of methods (*all(), one() *and 
other) will always apply overrided methods (because it uses 
SplittedDataQuery).
But, if I create* _pg_query *by defined way, it creates new session and, 
so, new connection to Postgres. 

As you see, I don't want nor the first, neither the second option :)
вторник, 12 июля 2016 г., 13:35:48 UTC+3 пользователь Simon King написал:

> Could you describe what you are trying to achieve? There's nothing 
> about Mike's suggestion that means you need to create a new session - 
> you can reuse any existing session. 
>
> What does your CustomQueryCls do? Perhaps there's another way of doing 
> what you want? 
>
> Simon 
>
> On Tue, Jul 12, 2016 at 11:09 AM, Антонио Антуан <a.ch...@gmail.com 
> > wrote: 
> > But it means that I should create one more session with one more 
> connection 
> > to DB. This is not good for me :( 
> > 
> > понедельник, 11 июля 2016 г., 22:23:11 UTC+3 пользователь Антонио Антуан 
> > написал: 
> >> 
> >> Can I specify query_cls only for a one query? I try to change 
> >> query.session._query_cls and, of course, it doesn't  work... 
> >> 
> >> I want to perform something like this: 
> >> 
> >> query = Session.query(MyModel)... 
> >> default_querycls_result = query.all() 
> >> custom_querycls_result = query.change_query_cls(CustomQueryCls).all() 
> > 
> > -- 
> > You received this message because you are subscribed to the Google 
> Groups 
> > "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an 
> > email to sqlalchemy+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
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: Specify query_cls for the one query

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

But it means that I should create one more session with one more connection 
to DB. This is not good for me :(

понедельник, 11 июля 2016 г., 22:23:11 UTC+3 пользователь Антонио Антуан 
написал:
>
> Can I specify query_cls only for a one query? I try to change 
> query.session._query_cls and, of course, it doesn't  work...
>
> I want to perform something like this:
>
> query = Session.query(MyModel)...
> default_querycls_result = query.all()
> custom_querycls_result = query.change_query_cls(CustomQueryCls).all()
>
>

-- 
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] Specify query_cls for the one query

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

Can I specify query_cls only for a one query? I try to change 
query.session._query_cls and, of course, it doesn't  work...

I want to perform something like this:

query = Session.query(MyModel)...
default_querycls_result = query.all()
custom_querycls_result = query.change_query_cls(CustomQueryCls).all()

-- 
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] Changing columns in query

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

Hm,  now it is ok, but previously, during another task, I checked the 
method and it returned the result with subquery, like this:
SELECT a.c FROM (SELECT t.c FROM t) as a

Why did I get the result with subquery? Does it depend on something 
specific?


суббота, 30 апреля 2016 г., 16:05:12 UTC+3 пользователь Mike Bayer написал:
>
> yes please use query.with_entities 
>
>
> http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=query.with_entities#sqlalchemy.orm.query.Query.with_entities
>  
>
>
> On 04/30/2016 02:33 AM, Антонио Антуан wrote: 
> > 
> > I have the query: 
> > | 
> > q =Session.query(func.array_agg(Order.col)) 
> > | 
> > 
> > The compiled query will be: 
> > | 
> > SELECT array_agg(order.col)FROM orders 
> > | 
> > 
> > I want dynamically replace the existing column. After replacing query 
> > have to be: 
> > | 
> > SELECT group_concat(orders.col)FROM orders 
> > | 
> > 
> > I have to use Session and model. I don't have to use SQLAlchemy core. I 
> > don't have to use subqueries. And, of course, there can be some other 
> > columns, but I need to replace only one. I tried to replace objects in 
> > `column_descriptions` property, I tried to use `q.selectable.replace`(or 
> > something like this, sorry, but I don't remember right names) and I 
> > didn't get right result. 
> > 
> > Can I do what I want? 
> > 
> > -- 
> > 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  
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > <mailto:sqlal...@googlegroups.com >. 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

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

[sqlalchemy] Changing columns in query

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

I have the query:
q = Session.query(func.array_agg(Order.col)) 

The compiled query will be:
SELECT array_agg(order.col) FROM orders

I want dynamically replace the existing column. After replacing query have 
to be:
SELECT group_concat(orders.col) FROM orders

I have to use Session and model. I don't have to use SQLAlchemy core. I 
don't have to use subqueries. And, of course, there can be some other 
columns, but I need to replace only one. I tried to replace objects in 
`column_descriptions` property, I tried to use `q.selectable.replace`(or 
something like this, sorry, but I don't remember right names) and I didn't 
get right result.

Can I do what I want?

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