Re: [sqlalchemy] SQLAlchemy does not properly create temporary table for subqueries in MariaDB

[Mike Bayer]

On Mon, Jan 15, 2018 at 6:09 PM, Mike Bayer  wrote:
> On Mon, Jan 15, 2018 at 5:32 PM, Mike Bayer  wrote:
>
> that's not quite right, because I'm not selecting from Test.  Adding
> the JOIN back in, I've tried aliasing Test everywhere and it still
> produces the error.  MySQL is really bad at subqueries.  give me a
> minute to find a query that works here.

OK try this:

latest_items = session.query(
Test.value, func.max(Test.timestamp).label('latest_timestamp')
).group_by(Test.value).subquery(name='subquery1')


deleted_rows = (
session.query(Test)
.filter((Test.timestamp < 5))
.filter(
~exists().
where(Test.value == latest_items.c.value).
where(Test.timestamp == latest_items.c.latest_timestamp)
).delete(synchronize_session=False)
)


output:

DELETE FROM test WHERE test.timestamp < %s AND NOT (EXISTS (SELECT *
FROM (SELECT test.value AS value, max(test.timestamp) AS latest_timestamp
FROM test GROUP BY test.value) AS subquery1
WHERE test.value = subquery1.value AND test.timestamp =
subquery1.latest_timestamp))



>
>
>
>
>
>>
>>
>>
>>
>>>
>>> … I guess that's a bug? At least I would assume that this should not result
>>> in an OperationalError, especially since it works with sqlite?
>>>
>>> Thanks for any help,
>>>
>>> Lukas
>>>
>>> [0] https://tinloaf.de/~tinloaf/sqlalchemy/mariadb.html
>>> [1] https://tinloaf.de/~tinloaf/sqlalchemy/sqlite.html
>>> [2]
>>> https://stackoverflow.com/questions/5816840/delete-i-cant-specify-target-table
>>>
>>> --
>>> 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] SQLAlchemy does not properly create temporary table for subqueries in MariaDB

[Mike Bayer]

On Mon, Jan 15, 2018 at 5:32 PM, Mike Bayer  wrote:
> On Mon, Jan 15, 2018 at 4:47 PM, Lukas Barth  wrote:
>> Hi,
>>
>> first things first: I put a complete notebook showing the problem at [0],
>> I'll only post the excerpts I deem useful below. The exact same code, this
>> time working, with an sqlite in-memory database is at [1]. I'm using
>> SQLAlchemy version 1.2.1, python 3.5, mysqlclient version 1.3.12 and MariaDB
>> version 10.1.29.
>>
>> I have this persistent class:
>> class Test(Base):
>> __tablename__ = "test"
>>
>> id = Column(Integer, primary_key=True)
>> timestamp = Column(Integer)
>> value = Column(Integer)
>>
>> Resulting in this schema:
>>
>>
>> CREATE TABLE test (
>>id INTEGER NOT NULL,
>>timestamp INTEGER,
>>value INTEGER,
>>PRIMARY KEY (id)
>> )
>>
>> What I want to achieve is to delete all objects from Test, which have a
>> timestamp less than some value (five in my example code), but which are
>> *not* the most recent entries for their respective value. For example: If
>> there is a Test object (call it A) with timestamp = 4 and value = 1234, and
>> all other (if there exist any at all…) Test objects with value = 1234 have a
>> timestamp of less than 4, then I want A to *not* be deleted, even though its
>> timestamp is less than 5.
>>
>>
>> I figured I go with two subqueries: The first subquery uses a group_by on
>> value, and max() on timestamp. These are the objects to be protected from
>> deletion. The second subquery retrieves the ids of the objects in subquery
>> 1. Then I can issue a delete statement, filtering so that the ids of the
>> objects to be deleted are not in subquery 2.
>>
>> Here's the first subquery:
>> protected_items = session.query(Test.id, Test.value,
>> func.max(Test.timestamp))\
>>  .group_by(Test.value).subquery(name='subquery1')
>>
>> And the second one:
>> protected_items_ids = session.query(Test.id).join(
>> protected_items, Test.id == protected_items.c.id)\
>> .subquery(name='subquery2')
>>
>> And finally, the deletion:
>>
>> deleted_rows = session.query(Test) \
>>   .filter((Test.timestamp < 5)) \
>>   .filter(~Test.id.in_(
>>   protected_items_ids)) \
>>   .delete(synchronize_session=False)
>>
>>
>> This works great when using it with a sqlite database. However, it gives an
>> OperationalError when using it with MariaDB. See the bottom of [0] for the
>> full stack trace. This is the error message I get from the MariaDB server:
>>
>> OperationalError: (_mysql_exceptions.OperationalError)
>>
>> (1093, "Table 'test' is specified twice, both as a target for 'DELETE' and
>> as a separate source for data")
>>
>> [SQL: 'DELETE FROM test WHERE test.timestamp < %s AND test.id NOT IN (SELECT
>> test.id \nFROM test INNER JOIN (SELECT test.id AS id, test.value AS value,
>> max(test.timestamp) AS max_1 \nFROM test GROUP BY test.value) AS subquery1
>> ON test.id = subquery1.id)'] [parameters: (5,)] (Background on this error
>> at: http://sqlalche.me/e/e3q8)
>>
>>
>> Doing dome digging, I think one needs to specify a name for the innermost
>> subquery (subquery 1), to make MariaDB create a temporary table for this. At
>> least that's what [2] suggests. I would have assumed the inner subquery to
>> be named ("… as subquery1"), since I specified "name = subquery1", but that
>> doesn't seem to be
>>
> print(protected_items)
>> SELECT test.id, test.value, max(test.timestamp) AS max_1
>> FROM test GROUP BY test.value
>
> doing a print() on the subquery object itself does not show it in
> context, as the subquery / alias name only comes out if you select
> FROM the object.  If you look at the SQL shown in the error message,
> it is rendering"AS subquery1", so that is not the problem here.
>
> the query as written doesn't actually seem to do anything with the
> subqueries, because you are putting this max(test.timestamp) in the
> columns clause of an embedded subquery, but you aren't filtering on it
> or doing anything at all with it, it's being thrown away.The query
> I think you need is:
>
> latest_items = session.query(
> Test.value, func.max(Test.timestamp).label('latest_timestamp')
> ).group_by(Test.value).subquery(name='subquery1')
>
> latest_item_ids = session.query(Test.id).filter(
> Test.timestamp == latest_items.c.latest_timestamp).\
> filter(Test.value == latest_items.c.value)
>
> deleted_rows = session.query(Test) \
>   .filter((Test.timestamp < 5)) \
>   .filter(~Test.id.in_(latest_item_ids)) \
>   .delete(synchronize_session=False)
>
>
>
> generating:
>
> DELETE FROM test WHERE test.timestamp < %s AND test.id NOT IN (SELECT
> test.id AS test_id
> FROM (SELECT test.value AS value, max(test.timestamp) AS latest_timestamp
> FROM test GROUP BY test.value) 

[sqlalchemy] First application: visual linting requested

[Rich Shepard]

  Several years ago I started a project with SQLAlchemy but dropped
development to go in a different direction. Recently returning to the
project I recognized my error and have returned to SQLAlchemy for this
application (using version 1.2.1).

  I've read the ORM tutorial and followed links on foreign keys and
constraints. I _think_ that my syntax is correct but imports and other
details might be out of sequence so I would like someone with experience to
look at the file for me as a visual lint, checking for syntax or other
errors. I'll send the file off the mail list if you are willing to do this
for me.

  Getting this feedback will increase my confidence in correctly applying
SQLAlchemy as I continue building this application.

Rich

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


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] SQLAlchemy does not properly create temporary table for subqueries in MariaDB

[Mike Bayer]

On Mon, Jan 15, 2018 at 4:47 PM, Lukas Barth  wrote:
> Hi,
>
> first things first: I put a complete notebook showing the problem at [0],
> I'll only post the excerpts I deem useful below. The exact same code, this
> time working, with an sqlite in-memory database is at [1]. I'm using
> SQLAlchemy version 1.2.1, python 3.5, mysqlclient version 1.3.12 and MariaDB
> version 10.1.29.
>
> I have this persistent class:
> class Test(Base):
> __tablename__ = "test"
>
> id = Column(Integer, primary_key=True)
> timestamp = Column(Integer)
> value = Column(Integer)
>
> Resulting in this schema:
>
>
> CREATE TABLE test (
>id INTEGER NOT NULL,
>timestamp INTEGER,
>value INTEGER,
>PRIMARY KEY (id)
> )
>
> What I want to achieve is to delete all objects from Test, which have a
> timestamp less than some value (five in my example code), but which are
> *not* the most recent entries for their respective value. For example: If
> there is a Test object (call it A) with timestamp = 4 and value = 1234, and
> all other (if there exist any at all…) Test objects with value = 1234 have a
> timestamp of less than 4, then I want A to *not* be deleted, even though its
> timestamp is less than 5.
>
>
> I figured I go with two subqueries: The first subquery uses a group_by on
> value, and max() on timestamp. These are the objects to be protected from
> deletion. The second subquery retrieves the ids of the objects in subquery
> 1. Then I can issue a delete statement, filtering so that the ids of the
> objects to be deleted are not in subquery 2.
>
> Here's the first subquery:
> protected_items = session.query(Test.id, Test.value,
> func.max(Test.timestamp))\
>  .group_by(Test.value).subquery(name='subquery1')
>
> And the second one:
> protected_items_ids = session.query(Test.id).join(
> protected_items, Test.id == protected_items.c.id)\
> .subquery(name='subquery2')
>
> And finally, the deletion:
>
> deleted_rows = session.query(Test) \
>   .filter((Test.timestamp < 5)) \
>   .filter(~Test.id.in_(
>   protected_items_ids)) \
>   .delete(synchronize_session=False)
>
>
> This works great when using it with a sqlite database. However, it gives an
> OperationalError when using it with MariaDB. See the bottom of [0] for the
> full stack trace. This is the error message I get from the MariaDB server:
>
> OperationalError: (_mysql_exceptions.OperationalError)
>
> (1093, "Table 'test' is specified twice, both as a target for 'DELETE' and
> as a separate source for data")
>
> [SQL: 'DELETE FROM test WHERE test.timestamp < %s AND test.id NOT IN (SELECT
> test.id \nFROM test INNER JOIN (SELECT test.id AS id, test.value AS value,
> max(test.timestamp) AS max_1 \nFROM test GROUP BY test.value) AS subquery1
> ON test.id = subquery1.id)'] [parameters: (5,)] (Background on this error
> at: http://sqlalche.me/e/e3q8)
>
>
> Doing dome digging, I think one needs to specify a name for the innermost
> subquery (subquery 1), to make MariaDB create a temporary table for this. At
> least that's what [2] suggests. I would have assumed the inner subquery to
> be named ("… as subquery1"), since I specified "name = subquery1", but that
> doesn't seem to be
>
 print(protected_items)
> SELECT test.id, test.value, max(test.timestamp) AS max_1
> FROM test GROUP BY test.value

doing a print() on the subquery object itself does not show it in
context, as the subquery / alias name only comes out if you select
FROM the object.  If you look at the SQL shown in the error message,
it is rendering"AS subquery1", so that is not the problem here.

the query as written doesn't actually seem to do anything with the
subqueries, because you are putting this max(test.timestamp) in the
columns clause of an embedded subquery, but you aren't filtering on it
or doing anything at all with it, it's being thrown away.The query
I think you need is:

latest_items = session.query(
Test.value, func.max(Test.timestamp).label('latest_timestamp')
).group_by(Test.value).subquery(name='subquery1')

latest_item_ids = session.query(Test.id).filter(
Test.timestamp == latest_items.c.latest_timestamp).\
filter(Test.value == latest_items.c.value)

deleted_rows = session.query(Test) \
  .filter((Test.timestamp < 5)) \
  .filter(~Test.id.in_(latest_item_ids)) \
  .delete(synchronize_session=False)



generating:

DELETE FROM test WHERE test.timestamp < %s AND test.id NOT IN (SELECT
test.id AS test_id
FROM (SELECT test.value AS value, max(test.timestamp) AS latest_timestamp
FROM test GROUP BY test.value) AS subquery1
WHERE test.timestamp = subquery1.latest_timestamp AND test.value =
subquery1.value)


which is accepted by the database.




>
> … I guess that's a bug? At least I would assume that this should not result
> in 

[sqlalchemy] Re: SQLAlchemy does not properly create temporary table for subqueries in MariaDB

[Jonathan Vanasco]

I can't speak to the internals of this being a bug or not, or how this 
should be done... but I think you could do a short-term (and cross 
platform) fix using an alias via `sqlalchemy.orm.aliased` for one (or more) 
of the inner subqueries.  That should result in a unique discriminator 
being generated for the table.

It's usually used for joining a table against itself, but I've used it to 
get more control over the generated sql like this a few times.  

http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=alias#sqlalchemy.orm.aliased
http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.alias

something like this...

Test1 = sqlalchemy.orm.aliased(Test, name='test1')
protected_items_ids = session.query(Test1.id).join(
protected_items, Test1.id == protected_items.c.id)\
.subquery(name='subquery2')




-- 
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] Inserting Chinese characters in Oracle database

[Mike Bayer]

On Mon, Jan 15, 2018 at 3:18 PM, Stefan Schwarzer
 wrote:
> On 2018-01-12 16:33, Mike Bayer wrote:> On Fri, Jan 12, 2018 at 7:14 AM, 
> Stefan Schwarzer
>>  wrote:
>> In SQLAlchemy 1.1 series and earlier, you can specify
>> exclude_setinputsizes=() to have STRING be part of the automatic
>> setinputsizes call.  In SQLAlchemy 1.2 these features were all removed
>> as there was never any reason to pass most datatypes to setinputsizes.
>>   in 1.2 you can still say
>> engine.dialect._include_setinputsizes.add(cx_Oracle.STRING) to re-add
>> it, but this should not be necessary (my test works with or without
>> it).
>>
>> Also which version of Python you're using matters, however I get a
>> round trip under both pythons.
>>
>> To round trip it, do this - note I had to expand the size of the
>> VARCHAR to fit your string, it was giving me a "data too large" error
>> before, so that might be a clue:
>> [...]
>
> I tried your example and got the same mismatch between
> original and actual value as before. Also your workaround
> that I tried for SQLAlchemy 1.2 didn't work for me. I
> haven't tried the workaround for SQLAlchemy 1.1.15. As
> described below, I (partially) found out why the workaround
> for version 1.2 didn't work.
>
> Since your code worked for you, but not for me, I concluded
> that probably the setup of your database is different from
> "mine" (I don't control the server). I searched for
> information on encodings in Oracle and found this document:
>
> https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/supporting-multilingual-databases-with-unicode.html#GUID-AA09A60E-123E-457C-ACE1-89E4634E492C
>
> So far I had assumed that if I use `NVARCHAR2`, the column
> data would be "unicode" and the Oracle driver would do all
> the necessary conversions.
>
> However, with `SELECT * FROM V$NLS_PARAMETERS` I got
>
> NLS_CHARACTERSETWE8MSWIN1252
> NLS_NCHAR_CHARACTERSET  AL16UTF16
>
> What are the values for your database?
>
> As far as I understand the above Oracle document, the first
> encoding, `NLS_CHARACTERSET`, is used for the names of SQL
> entity names like table and column names, and also for
> columns defined with `VARCHAR2`, `CLOB` etc. (character data
> types without the `N` prefix). The second encoding,
> `NLS_NCHAR_CHARACTERSET` is used for `NVARCHAR2`, `NCLOB`
> etc. (character data types with the `N` prefix).
>
> According to the document, Oracle nowadays recommends
> `AL32UTF8` for the database encoding and not using
> `NVARCHAR2` etc. for columns because `AL32UTF8` for
> `VARCHAR2` can generally encode unicode.
>
> I assume that I won't be able to have the above database
> encodings changed. In this case, what do you recommend when
> using the database with SQLAlchemy? As far as I understand,
> if I use `VARCHAR` or `VARCHAR2` (as other tables in the
> database so far), I won't be able to store anything that
> doesn't belong in CP1252. If I use `NVARCHAR2`, I _should_
> be able to use Chinese characters.
>
> Why not? I changed your suggested code for SQLAlchemy 1.2
> to include
>
> e.dialect._include_setinputsizes.add(cx_Oracle.STRING)
> e.dialect._include_setinputsizes.add(cx_Oracle.NCHAR)
> e.dialect._include_setinputsizes.add(cx_Oracle.NCLOB)
>
> but still inserting the Chinese characters failed.
>
> With some debugging, I noticed that in
> `engine.default.DefaultExecutionContext.set_input_sizes`
> before the call to `cursor.setinputsizes`, the `text` column
> has a corresponding `cx_Oracle.STRING` in the `inputsizes`
> dictionary. However, the type of `text` (an `NVARCHAR2`
> column) should be `cx_Oracle.NCHAR`. This is also what I get
> in the cursor description after the `SELECT` invocation
> before the `cursor.setinputsizes` in my working pure-cx_Oracle
> example in my original posting. If I change `cx_Oracle.STRING`
> to `cx_Oracle.NCHAR` in
> `DefaultExecutionContext.set_input_sizes` in the debugger,
> the Chinese characters appear in the database as expected.
>
> See also
> https://github.com/oracle/python-cx_Oracle/issues/119
>
> If you don't have a suspicion why the wrong type for
> the `NVARCHAR2` column is used, I could try to do more
> debugging and hopefully find out why.

the issue you refer to with Anthony refers to a new use case for the
cx_Oracle DBAPI, where we use setinputsizes() again and use a
different datatype for strings.   So that would need to be added to
SQLAlchemy's cx_Oracle dialect as an option, which can either be on a
per-datatype basis or engine-wide (engine-wide is easier to implement
and use however I'm not sure which is more appropriate).

You can probably get this to work right now doing this:

from sqlalchemy.dialects.oracle import _OracleString
class NCHAR(_OracleNVarChar):
def get_dbapi_type(self, dbapi):
return cx_Oracle.NCHAR

and then add cx_Oracle.NCHAR to the dialect._include_setinputsizes list.

I've proposed a few ways we might be able to add this API 

Re: [sqlalchemy] Define more events with sqlalchemy.event.Events

[Mike Bayer]

On Sat, Jan 13, 2018 at 2:38 PM, Gleb Getmanenko  wrote:
> I would like to define my event "after_create" for class Engine which I
> would invoke with
>
> eng = create_engine('sqlite:///:memory:')
> eng.dispatch.after_create(eng)
>
> I tried this to do this by
>
> class MoreEngineEvents(event.Events):
>
> _target_class_doc = "SomeEngine"
> _dispatch_target = Engine
>
> def after_create(self, eng):
> pass
>
> But it seems to override existing events for Engine. For example
> "before_execute".
> If I add method _set_dispatch to MoreEngineEvents as
>
> @staticmethod
> def _set_dispatch(cls, dispatch_cls):
>   new_dispatch = dispatch_cls(None)
> dispatch_cls._events = cls
> if cls.dispatch is None:
> cls.dispatch = new_dispatch
> else:
> cls.dispatch = new_dispatch._join(cls.dispatch)
> return cls.dispatch
>
> it does not seem to help.
>
> What can I do?

I wouldn't go that route because the event API at that level is
essentially private and doesn't support simple subclassing in that
way.

In this case the simplest approach, since you are looking to do
something that occurs after a public API function is used and you
don't need your feature to be "injected" into SQLAlchemy, is to create
a wrapper function:


from sqlalchemy import create_engine as _create_engine

def create_engine(*arg, **kw):
engine = _create_engine(*arg, **kw)
do_things(engine)
return engine

There's also a special plugin point you can use called CreateEnginePlugin:

http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=createengineplugin#sqlalchemy.engine.CreateEnginePlugin

The docstring there discusses the intent of this system.I created
it to help me develop a SQLAlchemy addon called "HAAlchemy" that
unfortunately I never got to work on.

that system is designed to work with a setuptools entry point.
However you can manually inject your plugin like this also:

from sqlalchemy.dialects import plugins
plugins.register("my_plugin", "myapplication.sqla_plugin", "MyPluginClass")

I think though making a wrapper for create_engine() function is much
simpler.  SQLAlchemy events are there to provide a hook to do
something within the SQLAlchemy internals that would otherwise be
impossible.   So there are not generally events for doing things
outside of SQLAlchemy user-facing functions as they are not needed and
would be a burden on complexity and performance.








>
> --
> 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] SQLAlchemy does not properly create temporary table for subqueries in MariaDB

[Lukas Barth]

Hi,

first things first: I put a complete notebook showing the problem at [0], 
I'll only post the excerpts I deem useful below. The exact same code, this 
time working, with an sqlite in-memory database is at [1]. I'm using 
SQLAlchemy version 1.2.1, python 3.5, mysqlclient version 1.3.12 and 
MariaDB version 10.1.29.

I have this persistent class:
class Test(Base):
__tablename__ = "test"

id = Column(Integer, primary_key=True)
timestamp = Column(Integer)
value = Column(Integer)

Resulting in this schema:
 

CREATE TABLE test ( 
   id INTEGER NOT NULL, 
   timestamp INTEGER, 
   value INTEGER, 
   PRIMARY KEY (id) 
)

What I want to achieve is to delete all objects from Test, which have a 
timestamp less than some value (five in my example code), but which are *not* 
the most recent entries for their respective value. For example: If there is a 
Test object (call it A) with timestamp = 4 and value = 1234, and all other (if 
there exist any at all…) Test objects with value = 1234 have a timestamp of 
less than 4, then I want A to *not* be deleted, even though its timestamp is 
less than 5.


I figured I go with two subqueries: The first subquery uses a group_by on 
value, and max() on timestamp. These are the objects to be protected from 
deletion. The second subquery retrieves the ids of the objects in subquery 
1. Then I can issue a delete statement, filtering so that the ids of the 
objects to be deleted are not in subquery 2.

Here's the first subquery:
protected_items = session.query(Test.id, Test.value,
func.max(Test.timestamp))\
 .group_by(Test.value).subquery(name='subquery1')

And the second one:
protected_items_ids = session.query(Test.id).join(
protected_items, Test.id == protected_items.c.id)\
.subquery(name='subquery2')

And finally, the deletion:

deleted_rows = session.query(Test) \
  .filter((Test.timestamp < 5)) \
  .filter(~Test.id.in_(
  protected_items_ids)) \
  .delete(synchronize_session=False)


This works great when using it with a sqlite database. However, it gives an 
OperationalError when using it with MariaDB. See the bottom of [0] for the 
full stack trace. This is the error message I get from the MariaDB server:

OperationalError: (_mysql_exceptions.OperationalError) 

(1093, "Table 'test' is specified twice, both as a target for 'DELETE' and as a 
separate source for data") 

[SQL: 'DELETE FROM test WHERE test.timestamp < %s AND test.id NOT IN (SELECT 
test.id \nFROM test INNER JOIN (SELECT test.id AS id, test.value AS value, 
max(test.timestamp) AS max_1 \nFROM test GROUP BY test.value) AS subquery1 ON 
test.id = subquery1.id)'] [parameters: (5,)] (Background on this error at: 
http://sqlalche.me/e/e3q8)


Doing dome digging, I think one needs to specify a name for the innermost 
subquery (subquery 1), to make MariaDB create a temporary table for this. 
At least that's what [2] suggests. I would have assumed the inner subquery 
to be named ("… as subquery1"), since I specified "name = subquery1", but 
that doesn't seem to be 

>>> print(protected_items)
SELECT test.id, test.value, max(test.timestamp) AS max_1 
FROM test GROUP BY test.value

… I guess that's a bug? At least I would assume that this should not result 
in an OperationalError, especially since it works with sqlite?

Thanks for any help,

Lukas

[0] https://tinloaf.de/~tinloaf/sqlalchemy/mariadb.html 
[1] https://tinloaf.de/~tinloaf/sqlalchemy/sqlite.html
[2] 
https://stackoverflow.com/questions/5816840/delete-i-cant-specify-target-table

-- 
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: SQLAlcemy relationships that work with MySQL generate error when used with Sql Server

[michael lovett]

I think I figure this out:



down voteunaccept

In addition to mentioning the SQL Server schema names as part of the table 
args, as in:

class UserGroup(Base):
__tablename__ = 'user_group'
__table_args__ = (
{'schema': 'admn'})

The schema is named "admn".

You also have to mention the schema name in various strings in the ORM in 
which you are naming tables. Two examples I've found so far:

Foreign keys:

user_id = Column(Integer, ForeignKey('admn.user.user_id', 
ondelete="cascade", onupdate="cascade"), primary_key = True)

In relationships when you mention a table, such as a secondary table:

  users = relationship(
"User",
secondary="admn.user_group",
back_populates="groups",
cascade="all",
passive_deletes=True) 

It was this last place that was causing my mapper errors. IE as soon as I 
mentioned the schema name in secondary=... the mapper errors went away.

-- 
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] Inserting Chinese characters in Oracle database

[Stefan Schwarzer]

On 2018-01-12 16:33, Mike Bayer wrote:> On Fri, Jan 12, 2018 at 7:14 AM, Stefan 
Schwarzer
>  wrote:
> In SQLAlchemy 1.1 series and earlier, you can specify
> exclude_setinputsizes=() to have STRING be part of the automatic
> setinputsizes call.  In SQLAlchemy 1.2 these features were all removed
> as there was never any reason to pass most datatypes to setinputsizes.
>   in 1.2 you can still say
> engine.dialect._include_setinputsizes.add(cx_Oracle.STRING) to re-add
> it, but this should not be necessary (my test works with or without
> it).
>
> Also which version of Python you're using matters, however I get a
> round trip under both pythons.
>
> To round trip it, do this - note I had to expand the size of the
> VARCHAR to fit your string, it was giving me a "data too large" error
> before, so that might be a clue:
> [...]

I tried your example and got the same mismatch between
original and actual value as before. Also your workaround
that I tried for SQLAlchemy 1.2 didn't work for me. I
haven't tried the workaround for SQLAlchemy 1.1.15. As
described below, I (partially) found out why the workaround
for version 1.2 didn't work.

Since your code worked for you, but not for me, I concluded
that probably the setup of your database is different from
"mine" (I don't control the server). I searched for
information on encodings in Oracle and found this document:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/supporting-multilingual-databases-with-unicode.html#GUID-AA09A60E-123E-457C-ACE1-89E4634E492C

So far I had assumed that if I use `NVARCHAR2`, the column
data would be "unicode" and the Oracle driver would do all
the necessary conversions.

However, with `SELECT * FROM V$NLS_PARAMETERS` I got

NLS_CHARACTERSETWE8MSWIN1252
NLS_NCHAR_CHARACTERSET  AL16UTF16

What are the values for your database?

As far as I understand the above Oracle document, the first
encoding, `NLS_CHARACTERSET`, is used for the names of SQL
entity names like table and column names, and also for
columns defined with `VARCHAR2`, `CLOB` etc. (character data
types without the `N` prefix). The second encoding,
`NLS_NCHAR_CHARACTERSET` is used for `NVARCHAR2`, `NCLOB`
etc. (character data types with the `N` prefix).

According to the document, Oracle nowadays recommends
`AL32UTF8` for the database encoding and not using
`NVARCHAR2` etc. for columns because `AL32UTF8` for
`VARCHAR2` can generally encode unicode.

I assume that I won't be able to have the above database
encodings changed. In this case, what do you recommend when
using the database with SQLAlchemy? As far as I understand,
if I use `VARCHAR` or `VARCHAR2` (as other tables in the
database so far), I won't be able to store anything that
doesn't belong in CP1252. If I use `NVARCHAR2`, I _should_
be able to use Chinese characters.

Why not? I changed your suggested code for SQLAlchemy 1.2
to include

e.dialect._include_setinputsizes.add(cx_Oracle.STRING)
e.dialect._include_setinputsizes.add(cx_Oracle.NCHAR)
e.dialect._include_setinputsizes.add(cx_Oracle.NCLOB)

but still inserting the Chinese characters failed.

With some debugging, I noticed that in
`engine.default.DefaultExecutionContext.set_input_sizes`
before the call to `cursor.setinputsizes`, the `text` column
has a corresponding `cx_Oracle.STRING` in the `inputsizes`
dictionary. However, the type of `text` (an `NVARCHAR2`
column) should be `cx_Oracle.NCHAR`. This is also what I get
in the cursor description after the `SELECT` invocation
before the `cursor.setinputsizes` in my working pure-cx_Oracle
example in my original posting. If I change `cx_Oracle.STRING`
to `cx_Oracle.NCHAR` in
`DefaultExecutionContext.set_input_sizes` in the debugger,
the Chinese characters appear in the database as expected.

See also
https://github.com/oracle/python-cx_Oracle/issues/119

If you don't have a suspicion why the wrong type for
the `NVARCHAR2` column is used, I could try to do more
debugging and hopefully find out why.

What do you think?

Best regards,
Stefan

-- 
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 1.2.1 released

[Mike Bayer]

SQLAlchemy release 1.2.1 is now available.

Release 1.2.1 fixes some minor issues for the newly released 1.2
series, including a few small regressions as well as some issues
reported with the new "selectin" relationship and polymorphic loader
features.

Changelog for 1.2.1 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_2_1

SQLAlchemy 1.2.1 is available on the Download Page at:
http://www.sqlalchemy.org/download.html

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

[sqlalchemy] SQLAlchemy 1.2.1 released

[Mike Bayer]

SQLAlchemy release 1.2.1 is now available.

Release 1.2.1 fixes some minor issues for the newly released 1.2
series, including a few small regressions as well as some issues
reported with the new "selectin" relationship and polymorphic loader
features.

Changelog for 1.2.1 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_2_1

SQLAlchemy 1.2.1 is available on the Download Page at:
http://www.sqlalchemy.org/download.html

-- 
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: Temporarily disable DB persistence for optimization routine

[Simon King]

Yes, if you can't find where you are creating new Satellite instances,
I'd probably stick an assert statement in Satellite.__init__ and see
where it gets triggered.

Simon

On Mon, Jan 15, 2018 at 10:34 AM, Ruben Di Battista
 wrote:
> Dear Simon,
>
> thanks again for your kind help.
>
> Actually the creation of new instances is not intended. But I'm not getting
> where they are created...
>
> I give you more insight:
>
> This is the scheduler object with the associated propagate() method
>
> class Scheduler(six.with_metaclass(abc.ABCMeta)):
> """ This class gets a list of GroundStation objects and a list of
> Satellites
> objects and compute all the passages of the Satellites over the
> GroundStations
>
> Args:
> sat_list(list): List of Satellites objects
> gs_list(list): List of GroundStation objects
> start_day(datetime): The datetime object representing the day from
> which to start the propagation
> time_of_propagation(int): Number of hours to propagate
> [default:24]
> deltaT(float): Time step to use for angles retrieval, in seconds
> """
>
> def __init__(self, sat_list, gs_list, start_day, time_of_propagation=24,
>  deltaT=0.05):
> # Monkey patch the Satellite class with the cost_function specific
> # of the scheduler algorithm.
> sat_class = type(sat_list[0])
> sat_class.cost_function = self.cost_function
>
> self.sat_list = sat_list
> self.gs_list = gs_list
> self.start_day = start_day
> self.time_of_propagation = time_of_propagation
> self.deltaT = deltaT
>
> def propagate(self):
> """ This method computes all the passages of the Satellites over the
> GroundStations
>
> Args:
>
> Returns:
> all_passages(PassageList): A list ordered from the earliest
> passage\
> of all passages
>
> Raises:
> ModelNotAvailable: When a satellite is too far from Earth and
> the
> models available in Orbital are not good, a
> ModelNotAvailable is
> raised
> """
>
> all_passages = PassageList()
>
> # Loop Over the gs list
> for gs in self.gs_list:
> # Loop over the satellites list
> for sat in self.sat_list:
> # Compute all the passages in the specified period
> passages = \
> sat.get_next_passes(gs, self.start_day,
> self.time_of_propagation,
> deltaT=self.deltaT)
>
> # Unfolding the list of passages in a flat list
> all_passages = all_passages + passages
>
> return all_passages
>
>
> It just basically loops over all the ground station and satellites and
> generates all the passages. Maybe the fact that I monkey patch the satellite
> class induces the creation of a new instance of `Satellite`?
>
> The details of the `get_next_passes` method of the `Satellite` class for
> what concerns the `Passage` instance creation, skipping the algorithmic
> part, are:
>
> def _generate_passage(self, next_pass, ground_station, deltaT):
> """ This method returns a Passage Object from the data returned from
> the original Orbital.get_next_passes method.
>
> """
>
> aos, los, tca = next_pass
>
> return Passage(satellite=self,
>ground_station=ground_station,
>aos=aos, los=los, tca=tca,
>deltaT=deltaT)
>
>
> `self` should be a reference to the instance of `Satellite` already loaded
> from DB. I will try to dive more into the code...
>
>
> Thanks a lot for the kind help of all of you,
>
> On Monday, January 15, 2018 at 10:06:24 AM UTC+1, Simon King wrote:
>>
>> On Sat, Jan 13, 2018 at 3:31 PM, Ruben Di Battista
>>  wrote:
>> >
>> >
>> > On Friday, January 12, 2018 at 10:54:49 AM UTC+1, Simon King wrote:
>> >>
>> >> If I understand your code correctly, scheduler.propagate() creates a
>> >> large number of Passage instances, and you only want a small subset of
>> >> them to be added to the database. Is that correct?
>> >
>> >
>> > Correct!
>> >
>> >>
>> >> I would guess that the passages are getting added to the session
>> >> because you are setting their 'satellite' property to point to a
>> >> Satellite which is already in the database. This then causes the
>> >> passages to be added to the session due to the default cascade rules
>> >> on the relationship
>> >> (http://docs.sqlalchemy.org/en/latest/orm/cascades.html).
>> >>
>> >> If that really is the case, you can change the cascade rules for that
>> >> relationship, and then you'll probably need to explicitly add the
>> >> passages you want to *keep* to the session instead.
>> >>
>> >> Hope that helps,
>> >>
>> >> 

Re: [sqlalchemy] About multithread session (scoped session)

[Simon King]

That's a bit of a complicated topic. Database consistency is generally
ensured by using transactions with an isolation level that is
appropriate for your usage. Each thread/connection will be operating
in a transaction. The transaction isolation level determines when one
thread will see the results that another thread has written:

https://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_levels

https://sqlite.org/isolation.html

https://www.postgresql.org/docs/9.1/static/transaction-iso.html

Simon

On Mon, Jan 15, 2018 at 9:39 AM,   wrote:
> Simon,
>
> Thanks for your kind answer.
> So when I have several threads trying to insert/update the same database
> (pgsql or sqlite) at the same time, how can I ensure consistency? By using
> scoped session? This will rely on the DB implementation behind, right?
>
> 在 2018年1月15日星期一 UTC+8下午5:21:42，Simon King写道：
>>
>> On Mon, Jan 15, 2018 at 8:34 AM,   wrote:
>> > Hello,
>> >
>> > Sessions are not thread safe.
>> > But for the scoped sessions, each thread will have its own session, my
>> > question is, even the scoped session itself rely on the thread safety
>> > feature of  the DB behind?
>> > for example, PGSQL server may handle multiple connections and resolving
>> > the
>> > update/insert requests, but for SQLITE3, normally it's NOT thread safe,
>> > how
>> > scoped session will behave towards such DB system?
>> >
>> > Not sure if I'm stating clearly here, Thanks!
>>
>> A session (scoped or otherwise) normally operates on a single
>> connection, either because it was explicitly bound to a connection, or
>> because it was bound to an engine and it checked a connection out of
>> the engine's connection pool. This usually ensures that a connection
>> is never used simultaneously by multiple threads.
>>
>> SQLAlchemy does have some special behaviour for SQLite, described here:
>>
>>
>> http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#threading-pooling-behavior
>>
>> It says that connections will never be shared between threads, and for
>> file-based SQLite databases, the default is for there to be no
>> connection pooling (it uses a NullPool). Whenever a connection is
>> requested from the pool, it will always be freshly created, and
>> discarded when it is returned to the pool.
>>
>> I assume that sqlite itself will be operating in "Multi-thread" mode
>> as described at https://sqlite.org/threadsafe.html
>>
>> Hope that helps,
>>
>> Simon

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

http://www.sqlalchemy.org/

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

Re: [sqlalchemy] Re: Temporarily disable DB persistence for optimization routine

[Ruben Di Battista]

Dear Simon,

thanks again for your kind help. 

Actually the creation of new instances is not intended. But I'm not getting 
where they are created...

I give you more insight:

This is the scheduler object with the associated propagate() method

class Scheduler(six.with_metaclass(abc.ABCMeta)):
""" This class gets a list of GroundStation objects and a list of 
Satellites
objects and compute all the passages of the Satellites over the
GroundStations

Args:
sat_list(list): List of Satellites objects
gs_list(list): List of GroundStation objects
start_day(datetime): The datetime object representing the day from
which to start the propagation
time_of_propagation(int): Number of hours to propagate
[default:24]
deltaT(float): Time step to use for angles retrieval, in seconds
"""

def __init__(self, sat_list, gs_list, start_day, time_of_propagation=24,
 deltaT=0.05):
# Monkey patch the Satellite class with the cost_function specific
# of the scheduler algorithm.
sat_class = type(sat_list[0])
sat_class.cost_function = self.cost_function

self.sat_list = sat_list
self.gs_list = gs_list
self.start_day = start_day
self.time_of_propagation = time_of_propagation
self.deltaT = deltaT

def propagate(self):
""" This method computes all the passages of the Satellites over the
GroundStations

Args:

Returns:
all_passages(PassageList): A list ordered from the earliest 
passage\
of all passages

Raises:
ModelNotAvailable: When a satellite is too far from Earth and 
the
models available in Orbital are not good, a 
ModelNotAvailable is
raised
"""

all_passages = PassageList()

# Loop Over the gs list
for gs in self.gs_list:
# Loop over the satellites list
for sat in self.sat_list:
# Compute all the passages in the specified period
passages = \
sat.get_next_passes(gs, self.start_day,
self.time_of_propagation,
deltaT=self.deltaT)

# Unfolding the list of passages in a flat list
all_passages = all_passages + passages

return all_passages


It just basically loops over all the ground station and satellites and 
generates all the passages. Maybe the fact that I monkey patch the 
satellite class induces the creation of a new instance of `Satellite`?

The details of the `get_next_passes` method of the `Satellite` class for 
what concerns the `Passage` instance creation, skipping the algorithmic 
part, are:

def _generate_passage(self, next_pass, ground_station, deltaT):
""" This method returns a Passage Object from the data returned from
the original Orbital.get_next_passes method.

"""

aos, los, tca = next_pass

return Passage(satellite=self,
   ground_station=ground_station,
   aos=aos, los=los, tca=tca,
   deltaT=deltaT)


`self` should be a reference to the instance of `Satellite` already loaded 
from DB. I will try to dive more into the code...


Thanks a lot for the kind help of all of you, 

On Monday, January 15, 2018 at 10:06:24 AM UTC+1, Simon King wrote:
>
> On Sat, Jan 13, 2018 at 3:31 PM, Ruben Di Battista 
>  wrote: 
> > 
> > 
> > On Friday, January 12, 2018 at 10:54:49 AM UTC+1, Simon King wrote: 
> >> 
> >> If I understand your code correctly, scheduler.propagate() creates a 
> >> large number of Passage instances, and you only want a small subset of 
> >> them to be added to the database. Is that correct? 
> > 
> > 
> > Correct! 
> > 
> >> 
> >> I would guess that the passages are getting added to the session 
> >> because you are setting their 'satellite' property to point to a 
> >> Satellite which is already in the database. This then causes the 
> >> passages to be added to the session due to the default cascade rules 
> >> on the relationship 
> >> (http://docs.sqlalchemy.org/en/latest/orm/cascades.html). 
> >> 
> >> If that really is the case, you can change the cascade rules for that 
> >> relationship, and then you'll probably need to explicitly add the 
> >> passages you want to *keep* to the session instead. 
> >> 
> >> Hope that helps, 
> >> 
> >> Simon 
> > 
> > 
> > Dear Simon, 
> > thank you. That was the case. Modifying the cascade disabling the 
> backref 
> > cascade does not load in the DB the passages at propagation time. 
> > 
> > But now, when I manually add the subset of passages after the 
> optimization, 
> > I get a: 
> > 
> > InvalidRequestError: Can't attach instance ; 
> > another instance with key [...] is already present in this section. 
> > 
> > 

Re: [sqlalchemy] About multithread session (scoped session)

[gingerluo]

Simon,

Thanks for your kind answer.
So when I have several threads trying to insert/update the same database 
(pgsql or sqlite) at the same time, how can I ensure consistency? By using 
scoped session? This will rely on the DB implementation behind, right?

在 2018年1月15日星期一 UTC+8下午5:21:42，Simon King写道：
>
> On Mon, Jan 15, 2018 at 8:34 AM,   
> wrote: 
> > Hello, 
> > 
> > Sessions are not thread safe. 
> > But for the scoped sessions, each thread will have its own session, my 
> > question is, even the scoped session itself rely on the thread safety 
> > feature of  the DB behind? 
> > for example, PGSQL server may handle multiple connections and resolving 
> the 
> > update/insert requests, but for SQLITE3, normally it's NOT thread safe, 
> how 
> > scoped session will behave towards such DB system? 
> > 
> > Not sure if I'm stating clearly here, Thanks! 
>
> A session (scoped or otherwise) normally operates on a single 
> connection, either because it was explicitly bound to a connection, or 
> because it was bound to an engine and it checked a connection out of 
> the engine's connection pool. This usually ensures that a connection 
> is never used simultaneously by multiple threads. 
>
> SQLAlchemy does have some special behaviour for SQLite, described here: 
>
>
> http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#threading-pooling-behavior
>  
>
> It says that connections will never be shared between threads, and for 
> file-based SQLite databases, the default is for there to be no 
> connection pooling (it uses a NullPool). Whenever a connection is 
> requested from the pool, it will always be freshly created, and 
> discarded when it is returned to the pool. 
>
> I assume that sqlite itself will be operating in "Multi-thread" mode 
> as described at https://sqlite.org/threadsafe.html 
>
> Hope that helps, 
>
> Simon 
>

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

http://www.sqlalchemy.org/

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

Re: [sqlalchemy] Re: Temporarily disable DB persistence for optimization routine

[Simon King]

On Sat, Jan 13, 2018 at 3:31 PM, Ruben Di Battista
 wrote:
>
>
> On Friday, January 12, 2018 at 10:54:49 AM UTC+1, Simon King wrote:
>>
>> If I understand your code correctly, scheduler.propagate() creates a
>> large number of Passage instances, and you only want a small subset of
>> them to be added to the database. Is that correct?
>
>
> Correct!
>
>>
>> I would guess that the passages are getting added to the session
>> because you are setting their 'satellite' property to point to a
>> Satellite which is already in the database. This then causes the
>> passages to be added to the session due to the default cascade rules
>> on the relationship
>> (http://docs.sqlalchemy.org/en/latest/orm/cascades.html).
>>
>> If that really is the case, you can change the cascade rules for that
>> relationship, and then you'll probably need to explicitly add the
>> passages you want to *keep* to the session instead.
>>
>> Hope that helps,
>>
>> Simon
>
>
> Dear Simon,
> thank you. That was the case. Modifying the cascade disabling the backref
> cascade does not load in the DB the passages at propagation time.
>
> But now, when I manually add the subset of passages after the optimization,
> I get a:
>
> InvalidRequestError: Can't attach instance ;
> another instance with key [...] is already present in this section.
>
> So, I suppose that disabling the backref cascade now SQLAlchemy is not
> capable anymore to recognize the already loaded Satellite objects...
>
> Should I maybe merge somewhere?
>

It sounds like your propagate() function is creating new Satellite
instances with the same primary key as instances that have been loaded
from the database. Merging is one way to avoid that, and the
UniqueObject pattern is another. It's difficult to say which is more
appropriate for your usage without seeing more code. But if
propagate() is only supposed to be calculating passages, I don't
understand why new Satellites are being created at all - shouldn't all
the Satellites already be loaded?

Simon

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

http://www.sqlalchemy.org/

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

[sqlalchemy] About multithread session (scoped session)

[gingerluo]

Hello,

Sessions are not thread safe.
But for the scoped sessions, each thread will have its own session, my 
question is, even the scoped session itself rely on the thread safety 
feature of  the DB behind?
for example, PGSQL server may handle multiple connections and resolving the 
update/insert requests, but for SQLITE3, normally it's NOT thread safe, how 
scoped session will behave towards such DB system?

Not sure if I'm stating clearly here, 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.