Re: [sqlalchemy] filter like using a string in a column of timestamps without time zones - postgres

[Simon King]

On Tue, Sep 11, 2018 at 9:39 AM George Brande  wrote:
>
> Hello.
>
> My angular is using a datepicker to send a date in string format(ex: 
> 2018-09-11) to my flask app to postgres via sqlalchemy.
> In my postgres all rows have a column ef_time of timestamps type.(ex: 
> 2018-09-07 13:24:30.138)
>
>
> @app.route('/orders/')
> def get_orders(ide):
> session = Session()
> orders_objects = 
> session.query(Orders).filter(Orders.ef_time.like(ide+"%")).all()
> schema = OrdersSchema(many=True)
>orders = schema.dump(orders_objects)
>
> session.close()
> return json.dumps(orders.data)
>
> When sending a test, /orders/2018-09-11 my flask app gives me an error: 
> operator does not exist: timestamp without time zone ~~~unknown.
> Line3: Where ord7.ef_time LIKE '2018-09-11%' because, obviously i am seding a 
> string and my ef_time column is of type datestamp without timezone in 
> postgres.
>
> Please give some support, i don't know how to get out this situation. king 
> regards.

I think you need to convert your string to a python datetime object
(eg. using datetime.strptime) and calculate the end date (eg. by using
timedelta), rather than using "LIKE".

Something like this:

  import datetime as dt
  import sqlalchemy as sa

  starttime = dt.datetime.strptime(ide, "%Y-%m-%d")
  endtime = starttime + dt.timedelta(days=1)
  condition = sa.and_(Orders.ef_time >= starttime, Orders.ef_time < endtime)
  orders_objects = session.query(Orders).filter(condition).all()

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] How to customize base declarative class to add naming conventions

[Simon King]

On Tue, Sep 4, 2018 at 3:48 PM  wrote:
>
> I'd like to create a mixin to specify naming conventions.
>
> I tried both:
>
> class Base:
> metadata = MetaData(naming_convention={
> "ix": "ix_%(column_0_label)s",
> "uq": "uq_%(table_name)s_%(column_0_name)s",
> "ck": "ck_%(table_name)s_%(constraint_name)s",
> "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
> "pk": "pk_%(table_name)",
> })
>
>
> and
>
> class Base:
> @declared_attr
> def metadata(cls):
> return MetaData(naming_convention={
> "ix": "ix_%(column_0_label)s",
> "uq": "uq_%(table_name)s_%(column_0_name)s",
> "ck": "ck_%(table_name)s_%(constraint_name)s",
> "fk": 
> "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
> "pk": "pk_%(table_name)",
> })
>
> But if I inspect a model created using this base I always got:
>
> >>> Test.metadata.naming_convention
> immutabledict({'ix': 'ix_%(column_0_label)s'})
>
> while I correctly have:
>
> >>> Base.metadata.naming_convention
> {'ix': 'ix_%(column_0_label)s',
>  'uq': 'uq_%(table_name)s_%(column_0_name)s',
>  'ck': 'ck_%(table_name)s_%(constraint_name)s',
>  'fk': 'fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s',
>  'pk': 'pk_%(table_name)'}
>
> What is the correct way to do it? what am i doing wrong? Should I do this in 
> my migration tool (alembic) ?
> Also would it works for unique constraint on multiple column or do we have to 
> name them explicitly.
>

Is it important to you to do this via a mixin? declarative_base
accepts a "metadata" parameter, so something like this should work:

metadata = MetaData(naming_convention={...})
Base = declarative_base(metadata=metadata)

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: Relationship to child with 2 foreginKey from same Parent column

[Simon King]

Here's a version which I *think* does what you want. There are a
couple of things that you might want to note.

First, in your paste, Conversation.user1 and Conversation.user2 are
integer columns, but you are assigning User objects to those
properties. That's not the way SQLAlchemy works - you can't assign a
related object directly to the column. In my version below I've
renamed the columns to "userid1" and "userid2" and then created
relationships for "user1" and "user2".

Second, the User.conversations property needs to have "viewonly=True".
It wouldn't make sense for it to be a writable property, because if
you appended a new Conversation to it, SQLAlchemy wouldn't know which
of the userid1 or userid2 columns to update.




import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Conversation(Base):
__tablename__ = 'conversations'
id = sa.Column(sa.Integer(), primary_key=True)
userid1 = sa.Column(
sa.Integer(),
sa.ForeignKey('users.id'),
nullable=False,
)
userid2 = sa.Column(
sa.Integer(),
sa.ForeignKey('users.id'),
nullable=False,
)

user1 = saorm.relationship(
"User",
primaryjoin="Conversation.userid1 == User.id",
)
user2 = saorm.relationship(
"User",
primaryjoin="Conversation.userid2 == User.id",
)


class User(Base):
__tablename__ = 'users'
id = sa.Column(sa.Integer(), primary_key=True)
userName = sa.Column(sa.String(32), unique=True, nullable=False)

conversations = saorm.relationship(
Conversation,
primaryjoin=sa.or_(
id == Conversation.userid1,
id == Conversation.userid2,
),
viewonly=True,
)


if __name__ == '__main__':
engine = sa.create_engine("sqlite:///:memory:", echo="debug")
Session = saorm.sessionmaker(bind=engine)
Base.metadata.create_all(engine)

session = Session()
u1 = User(userName="Alireza")
u2 = User(userName="Amir")
session.add_all([u1, u2])
session.commit()

con = Conversation(user1=u1, user2=u2)
session.add(con)
session.commit()

print u1.conversations



On Sun, Sep 2, 2018 at 3:27 PM Alireza Ayin Mehr
 wrote:
>
> Well, it seems weird this way, I only have one userId in my parent class 
> which is the source class
>
>
> On Sunday, September 2, 2018 at 6:08:33 AM UTC+4:30, Seth P wrote:
>>
>> In relationship(), foreign_keys refers to the field in the source table, not 
>> the destination.
>
> --
> 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 session in same transaction as existing psycopg2 connection

[Simon King]

On Tue, Aug 28, 2018 at 5:53 PM Simon King  wrote:
>
> On Tue, Aug 28, 2018 at 4:32 PM 'Brian DeRocher' via sqlalchemy
>  wrote:
> >
> > Hey all,
> >
> > I'm writing some automated tests for some legacy python code using a 
> > psycopg2 connection.  I'd like to check data in the database by using 
> > SQLAlchemy.  I rollback the database transaction after each test in 
> > tearDown().
> >
> > The problem is my SQLAlchemy connection doesn't see the database updates.  
> > At first I thought they weren't using the same connection, but they are.  
> > I'm using create_engine(..., creator=get_conn).
> >
> > The problem appears to be that DefaultDialect is rolling back the 
> > transaction.  See sqlalchemy/engine/default.py line 167.  I had to review 
> > PG logs to spot this as it's not logged.
> >
> > self.do_rollback(connection.connection)
> >
> > Is this line really needed?  What would it be rolling back?  Can it be 
> > avoided?  When I disable this line of code, the transaction continues and 
> > sqlalchemy can see the updates from psyopg2.
> >
> > I've attached a demo file.
> >
>
> I haven't tried to run your code, but the usual way to connect a
> session to an existing connection is to bind the session directly to
> the connection:
>
> http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites
>
> Would that work for your situation?
>
> Hope that helps,
>
> Simon

Sorry, I realised just after I pressed Send that you have a
DBAPI-level connection rather than an SQLAlchemy connection, so my
suggestion doesn't really help...

For what it's worth, the "do_rollback" was apparently added here:

https://bitbucket.org/zzzeek/sqlalchemy/commits/39fd3442e306f9c2981c347ab2487921f3948a61#chg-lib/sqlalchemy/engine/default.py

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] sqlalchemy session in same transaction as existing psycopg2 connection

[Simon King]

On Tue, Aug 28, 2018 at 4:32 PM 'Brian DeRocher' via sqlalchemy
 wrote:
>
> Hey all,
>
> I'm writing some automated tests for some legacy python code using a psycopg2 
> connection.  I'd like to check data in the database by using SQLAlchemy.  I 
> rollback the database transaction after each test in tearDown().
>
> The problem is my SQLAlchemy connection doesn't see the database updates.  At 
> first I thought they weren't using the same connection, but they are.  I'm 
> using create_engine(..., creator=get_conn).
>
> The problem appears to be that DefaultDialect is rolling back the 
> transaction.  See sqlalchemy/engine/default.py line 167.  I had to review PG 
> logs to spot this as it's not logged.
>
> self.do_rollback(connection.connection)
>
> Is this line really needed?  What would it be rolling back?  Can it be 
> avoided?  When I disable this line of code, the transaction continues and 
> sqlalchemy can see the updates from psyopg2.
>
> I've attached a demo file.
>

I haven't tried to run your code, but the usual way to connect a
session to an existing connection is to bind the session directly to
the connection:

http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites

Would that work for your situation?

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] Encoding troubles with pythong, mysql and utf8mb4

[Simon King]

According to https://dev.mysql.com/doc/refman/5.7/en/variables-table.html,
VARIABLE_VALUE is a column in the INFORMATION_SCHEMA GLOBAL_VARIABLES and
SESSION_VARIABLES tables.

You could use the python warnings filter to turn these warnings into
exceptions:

import warnings
warnings.simplefilter("error")

...which will give you an exception and a stack trace, so you can see where
these warnings are coming from.

Simon

On Wed, Aug 22, 2018 at 10:37 AM  wrote:

> Sorry for my last messy message, here's a better version.
>
> That's a good question actually. If you follow my Stackoverflow Post
> you'll see I've updated it with the following information :
>
>
> The dataframe that is persisted in the database (See attached image):
>
>
> [image: DataFrame.JPG]
>
> So I actually have no idea wherethe column "VARIABLE_VALUE" is coming from.
>
>
> On Wednesday, August 22, 2018 at 10:37:30 AM UTC+2, Simon King wrote:
>>
>> I've never used Pandas, so this may not make any sense, but where does
>> the column "VARIABLE_VALUE" come from? Is it a column in your
>> dataframe?
>>
>> Simon
>>
>> On Wed, Aug 22, 2018 at 8:52 AM  wrote:
>> >
>> > I get the following warnings, when trying to save a simple dataframe to
>> mysql.:
>> >
>> > > C:\...\anaconda3\lib\site-packages\pymysql\cursors.py:170: Warning:
>> (1366, "Incorrect string value: '\\x92\\xE9t\\xE9)' for column
>> 'VARIABLE_VALUE' at row 518")
>> >   result = self._query(query)
>> >
>> > And
>> > > C:\...anaconda3\lib\site-packages\pymysql\cursors.py:170: Warning:
>> > > (3719, "'utf8' is currently an alias for the character set UTF8MB3,
>> > > but will be an alias for UTF8MB4 in a future release. Please consider
>> > > using UTF8MB4 in order to be unambiguous.")   result =
>> > > self._query(query)
>> >
>> > Environment info : I use Mysql8, python3.6 (pymysql 0.9.2, sqlalchemy
>> 1.2.1)
>> >
>> > I visited posts like the one linked bellow, none of which seem to give
>> a solution as to how to avoid this warning.
>> >
>> >  - [MySQL “incorrect string value” error when save unicode string in
>> Django][1] -> Indication is to use UTF8
>> >
>> >
>> > N.B : The Collation in the table within mysql doesn't seem to be set to
>> the one I specified in the `create_db` function within the `Connection`
>> class.
>> >
>> > The executable code:
>> >
>> > import DataEngine.db.Connection as connection
>> > import random
>> > import pandas as pd
>> >
>> > if __name__ == "__main__":
>> > conn = connection.Connection(host="host_name", port="3306",
>> user="username", password="password")
>> > conn.create_db("raw_data")
>> > conn.establish("raw_data")
>> > l1 = []
>> > for i in range(100):
>> > l_nested = []
>> > for j in range(10):
>> > l_nested.append(random.randint(0, 100))
>> > l1.append(l_nested)
>> > df = pd.DataFrame(l1)
>> >
>> > conn.save(df, "random_df")
>> > # df2 = conn.retrieve("random_df")
>> >
>> > My `Connection class`
>> >
>> > import sqlalchemy
>> > import pymysql
>> > import pandas as pd
>> >
>> >
>> > class Connection:
>> > def __init__(self: object, host: str, port: str, user: str,
>> password: str):
>> > self.host = host
>> > self.port = port
>> > self.user = user
>> > self.password = password
>> > self.conn = None
>> >
>> > def create_db(self: object, db_name: str, charset: str =
>> "utf8mb4", collate:str ="utf8mb4_unicode_ci",drop_if_exists: bool = True):
>> > c = pymysql.connect(host=self.host, user=self.user,
>> password=self.password)
>> > if drop_if_exists:
>> > c.cursor().execute("DROP DATABASE IF EXISTS " +
>> db_name)
>> > c.cursor().execute("CREATE DATABASE " + db_name + "
>> CHARACTER SET=" + charset + " COLLATE=" + collate)
>> > c.close()
>> > print("Database %s created with a %s chars

Re: [sqlalchemy] Encoding troubles with pythong, mysql and utf8mb4

[Simon King]

I've never used Pandas, so this may not make any sense, but where does
the column "VARIABLE_VALUE" come from? Is it a column in your
dataframe?

Simon

On Wed, Aug 22, 2018 at 8:52 AM  wrote:
>
> I get the following warnings, when trying to save a simple dataframe to 
> mysql.:
>
> > C:\...\anaconda3\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, 
> > "Incorrect string value: '\\x92\\xE9t\\xE9)' for column 'VARIABLE_VALUE' at 
> > row 518")
>   result = self._query(query)
>
> And
> > C:\...anaconda3\lib\site-packages\pymysql\cursors.py:170: Warning:
> > (3719, "'utf8' is currently an alias for the character set UTF8MB3,
> > but will be an alias for UTF8MB4 in a future release. Please consider
> > using UTF8MB4 in order to be unambiguous.")   result =
> > self._query(query)
>
> Environment info : I use Mysql8, python3.6 (pymysql 0.9.2, sqlalchemy 1.2.1)
>
> I visited posts like the one linked bellow, none of which seem to give a 
> solution as to how to avoid this warning.
>
>  - [MySQL “incorrect string value” error when save unicode string in 
> Django][1] -> Indication is to use UTF8
>
>
> N.B : The Collation in the table within mysql doesn't seem to be set to the 
> one I specified in the `create_db` function within the `Connection` class.
>
> The executable code:
>
> import DataEngine.db.Connection as connection
> import random
> import pandas as pd
>
> if __name__ == "__main__":
> conn = connection.Connection(host="host_name", port="3306", 
> user="username", password="password")
> conn.create_db("raw_data")
> conn.establish("raw_data")
> l1 = []
> for i in range(100):
> l_nested = []
> for j in range(10):
> l_nested.append(random.randint(0, 100))
> l1.append(l_nested)
> df = pd.DataFrame(l1)
>
> conn.save(df, "random_df")
> # df2 = conn.retrieve("random_df")
>
> My `Connection class`
>
> import sqlalchemy
> import pymysql
> import pandas as pd
>
>
> class Connection:
> def __init__(self: object, host: str, port: str, user: str, password: 
> str):
> self.host = host
> self.port = port
> self.user = user
> self.password = password
> self.conn = None
>
> def create_db(self: object, db_name: str, charset: str = "utf8mb4", 
> collate:str ="utf8mb4_unicode_ci",drop_if_exists: bool = True):
> c = pymysql.connect(host=self.host, user=self.user, 
> password=self.password)
> if drop_if_exists:
> c.cursor().execute("DROP DATABASE IF EXISTS " + db_name)
> c.cursor().execute("CREATE DATABASE " + db_name + " CHARACTER 
> SET=" + charset + " COLLATE=" + collate)
> c.close()
> print("Database %s created with a %s charset" % (db_name, 
> charset))
>
> def establish(self: object, db_name: str, charset: str = "utf8mb4"):
> self.conn = sqlalchemy.create_engine(
> "mysql+pymysql://" + self.user + ":" + self.password + "@" + 
> self.host + ":" + self.port + "/" + db_name +
> "?charset=" + charset)
> print("Connection with database : %s has been established as %s 
> at %s." % (db_name, self.user, self.host))
> print("Charset : %s" % charset)
>
> def retrieve(self, table):
> df = pd.read_sql_table(table, self.conn)
> return df
>
> def save(self: object, df: "Pandas.DataFrame", table: str, if_exists: 
> str = "replace", chunksize: int = 1):
> df.to_sql(name=table, con=self.conn, if_exists=if_exists, 
> chunksize=chunksize)
>
> Some elements that might help:
> [![Database is of charset utf8mb4][2]][2]
> [![Table columns don't seem to have the correct collation even though I 
> specified it when creating the db][3]][3]
>
>
>   [1]: 
> https://stackoverflow.com/questions/2108824/mysql-incorrect-string-value-error-when-save-unicode-string-in-django
>   [2]: https://i.stack.imgur.com/9iAPF.png
>   [3]: https://i.stack.imgur.com/qBq6U.png
>
> --
> 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 

Re: [sqlalchemy] How to ignore primary key errors on insert

[Simon King]

If you are using SQLAlchemy core, there's this:

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#insert-on-conflict-upsert

Does that meet your needs?

Simon

On Tue, Aug 21, 2018 at 3:36 AM Hardik Sanghavi  wrote:
>
> Did this get resolved or are we to still ignore it
>
>
> On Wednesday, May 7, 2008 at 12:40:23 AM UTC+10, Michael Bayer wrote:
>>
>>
>> just use try/except
>>
>> from sqlalchemy import exceptions
>>
>> try:
>>  engine.execute(mytable.insert(), **kwargs)
>> except exceptions.IntegrityError, e:
>> print "Error !", e, "well, I guess we'll ignore it."
>>
>> engine.execute(some_other_insert_statement ... )
>>
>> On May 6, 2008, at 10:06 AM, Alexis B wrote:
>>
>> >
>> > Hi to all,
>> >
>> > This may be a newbie question but I just can't find the answer. I have
>> > to make multiple submissions to a postgresql table and I want to use
>> > python. I found everything to execute my insert commands, the problem
>> > is that I have to repeat it regularly, and I expected not to check
>> > which record have already inserted thanks to the primary key ( which
>> > is a couple if integer I set ).
>> > So when it tries to insert again some records, it doesn't insert it,
>> > as expected, but it raises an error which interrupt the script :
>> >
>> > **
>> > Traceback (most recent call last):
>> >  File "./script.py", line 44, in 
>> >connection.execute(line)
>> >  File " . . ./python-2.5.1/lib/python2.5/site-packages/
>> > SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py", line 844, in
>> > execute
>> >return Connection.executors[c](self, object, multiparams, params)
>> >  File ". . ./python-2.5.1/lib/python2.5/site-packages/
>> > SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py", line 854, in
>> > _execute_text
>> >self.__execute_raw(context)
>> >  File ". . ./python-2.5.1/lib/python2.5/site-packages/
>> > SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py", line 916, in
>> > __execute_raw
>> >self._cursor_execute(context.cursor, context.statement,
>> > context.parameters[0], context=context)
>> >  File ". . ./python-2.5.1/lib/python2.5/site-packages/
>> > SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py", line 960, in
>> > _cursor_execute
>> >self._handle_dbapi_exception(e, statement, parameters, cursor)
>> >  File ". . ./python-2.5.1/lib/python2.5/site-packages/
>> > SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py", line 942, in
>> > _handle_dbapi_exception
>> >raise exceptions.DBAPIError.instance(statement, parameters, e,
>> > connection_invalidated=is_disconnect)
>> > sqlalchemy.exceptions.IntegrityError: (IntegrityError) duplicate key
>> > violates unique constraint "my_primarykey"
>> > "INSERT INTO . . . )" {}
>> > **
>> >
>> > So I just wanted to know if there was an option to ignore the error ,
>> > which possibly raise it, but don't interrupt the script.
>> >
>> > Thanks
>> >
>> > >
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> ---
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
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: Generic Associations - table_per_association: parent attribute

[Simon King]

You could build this, but it's going to be messy. At the SQL level, if
you look at an Address row, there's not enough information to know
which association table to look in. You'd have to query all of the
association tables (perhaps using a UNION) to find the one that
contains the parent, and then query the appropriate parent table.

Simon

On Sat, Jul 28, 2018 at 7:39 AM Sven  wrote:
>
> Hi Mike,
>
> Ok, let's forget everything I said before, it is too confusing.
>
> I propose to start from the table_per_association example:
>
> http://docs.sqlalchemy.org/en/latest/_modules/examples/generic_associations/table_per_association.html
>
> Would it be possible to have an attribute address.parent linked to 
> Customer/Supplier? Exactly like in the table_per_related example?
>
> for customer in session.query(Customer):
> for address in customer.addresses:
> print(address)
> print(address.parent) # this attribute is what I need
>
> 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.

-- 
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] So, Sqlalchemy migration tool is not support chang column type ?

[Simon King]

I've never done this, but I see an alternative syntax is:

ALTER TABLE invite_code ALTER COLUMN created_at TYPE TIMESTAMP WITH
TIME ZONE USING created_at::timestamp

...so maybe you could use the "postgres_using" flag?

http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations.alter_column.params.postgresql_using

Simon

On Thu, Jul 26, 2018 at 11:47 AM Yingchen Zhang  wrote:
>
> use PostgreSQL change column type need `cast` like `ALTER TABLE invite_code 
> ALTER COLUMN created_at::timestamp TYPE TIMESTAMP WITH TIME ZONE `,
> but sqlalchemy auto generated is not use, how specify it.
>
> auto generated sql : ALTER TABLE invite_code ALTER COLUMN created_at TYPE 
> TIMESTAMP WITH TIME ZONE
>
> 在 2018年7月26日星期四 UTC+8下午6:16:10，Simon King写道：
>>
>> On Thu, Jul 26, 2018 at 11:03 AM Yingchen Zhang  wrote:
>> >
>> > old model
>> >
>> > class User(db.Model):
>> > __tablename__ = 'users'
>> > id = db.Column(db.BigInteger, primary_key=True)
>> > name = db.Column(db.VARCHAR(50), nullable=False)
>> >
>> > email = db.Column(db.VARCHAR(200), nullable=False)
>> >
>> > time = db.Column(db.INTGER, nullable=False)
>> >
>> >
>> > new model
>> >
>> > class User(db.Model):
>> > __tablename__ = 'users'
>> > id = db.Column(db.BigInteger, primary_key=True)
>> > name = db.Column(db.VARCHAR(50), nullable=False)
>> >
>> > email = db.Column(db.VARCHAR(200), nullable=False)
>> >
>> > time = db.Column(db.TIMESTAMP(timezone=True), nullable=False)
>> >
>> >
>> > new database table and is still empty, so do not ask why... e
>> >
>> > I use flask-sqlalchemy migration tool. When I modified User mode and run 
>> > `python manager.py db migrate`.got :
>> >
>> > `INFO  [alembic.env] No changes in schema detected.`
>> >
>> > So, Sqlalchemy migrate tool only support create and drop ?
>>
>> Alembic (the library that flask-sqlalchemy is using for migrations)
>> supports changing column types. However, it doesn't *auto-detect*
>> changes of column types by default:
>>
>> http://alembic.zzzcomputing.com/en/latest/autogenerate.html#what-does-autogenerate-detect-and-what-does-it-not-detect
>>
>> """
>> Autogenerate can optionally detect:
>>
>> Change of column type. This will occur if you set the
>> EnvironmentContext.configure.compare_type parameter to True, or to a
>> custom callable function. The feature works well in most cases, but is
>> off by default so that it can be tested on the target schema first. It
>> can also be customized by passing a callable here; see the section
>> Comparing Types for details.
>> """
>>
>> 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.

-- 
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] So, Sqlalchemy migration tool is not support chang column type ?

[Simon King]

On Thu, Jul 26, 2018 at 11:03 AM Yingchen Zhang  wrote:
>
> old model
>
> class User(db.Model):
> __tablename__ = 'users'
> id = db.Column(db.BigInteger, primary_key=True)
> name = db.Column(db.VARCHAR(50), nullable=False)
>
> email = db.Column(db.VARCHAR(200), nullable=False)
>
> time = db.Column(db.INTGER, nullable=False)
>
>
> new model
>
> class User(db.Model):
> __tablename__ = 'users'
> id = db.Column(db.BigInteger, primary_key=True)
> name = db.Column(db.VARCHAR(50), nullable=False)
>
> email = db.Column(db.VARCHAR(200), nullable=False)
>
> time = db.Column(db.TIMESTAMP(timezone=True), nullable=False)
>
>
> new database table and is still empty, so do not ask why... e
>
> I use flask-sqlalchemy migration tool. When I modified User mode and run 
> `python manager.py db migrate`.got :
>
> `INFO  [alembic.env] No changes in schema detected.`
>
> So, Sqlalchemy migrate tool only support create and drop ?

Alembic (the library that flask-sqlalchemy is using for migrations)
supports changing column types. However, it doesn't *auto-detect*
changes of column types by default:

http://alembic.zzzcomputing.com/en/latest/autogenerate.html#what-does-autogenerate-detect-and-what-does-it-not-detect

"""
Autogenerate can optionally detect:

Change of column type. This will occur if you set the
EnvironmentContext.configure.compare_type parameter to True, or to a
custom callable function. The feature works well in most cases, but is
off by default so that it can be tested on the target schema first. It
can also be customized by passing a callable here; see the section
Comparing Types for details.
"""

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] M2M relationship back_populates KeyError

[Simon King]

On Mon, Jul 2, 2018 at 4:39 AM Juan  wrote:
>
> Hi,
>
> I do not know if this is that I am missing something in how SQLAlchemy
> works or an issue; I have read the doc and searched for an explanation
> but could not find an answer.
>
> When appending via an intermediate table,  a  M2M relationship behaves
> differently wether I use the back_populates argument or not ( or use
> backref)
>
> * Without using back_populates or backref is works fine
> ```
> from sqlalchemy import Column, Integer, String, ForeignKey, Table
> from sqlalchemy.orm import relationship
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
> class User(Base):
>  __tablename__ = 'user'
>  id = Column(Integer, primary_key=True)
>  name = Column(String(64))
>  keywords = relationship("UsersKeywords")
>
>  def __init__(self, name):
>  self.name = name
>
> class Keyword(Base):
>  __tablename__ = 'keyword'
>  id = Column(Integer, primary_key=True)
>  keyword = Column('keyword', String(64))
>
>  def __init__(self, keyword):
>  self.keyword = keyword
>
> class UsersKeywords(Base):
>  __tablename__ = 'userskeywords'
>  user_id = Column( Integer, ForeignKey("user.id"), primary_key=True)
>  keyword_id = Column(Integer, ForeignKey("keyword.id"),
> primary_key=True)
>  user = relationship(User, back_populates="keywords")
>  keyword = relationship(Keyword)
>
>  >>>u = User(‘John’)
>  >>>u.keywords.append(Keyword(‘Python’)
>  >>>u.keywords
> []
> ```
>
> * With back_populates or backref in one of the ‘M’ sides of the
> relation, ‘User’ in this case, I get a KeyError exception:
> ```
> class User(Base):
>  __tablename__ = 'user'
>  id = Column(Integer, primary_key=True)
>  name = Column(String(64))
>  keywords = relationship('UsersKeywords', back_populates='user') #
> added back_populates argument
>
>  def __init__(self, name):
>  self.name = name
>
>  >>>u = User(‘John’)
>  >>>u.keywords.append(Keyword(‘Python’)
>
> Traceback (most recent call last):
>File "", line 1, in 
>File
> "/Users/xxx/Development/python_ws/flask_env/lib/python3.6/site-packages/sqlalchemy/orm/collections.py",
> line 1044, in append
>  item = __set(self, item, _sa_initiator)
>File
> "/Users/xxx/Development/python_ws/flask_env/lib/python3.6/site-packages/sqlalchemy/orm/collections.py",
> line 1016, in __set
>  item = executor.fire_append_event(item, _sa_initiator)
>File
> "/Users/xxx/Development/python_ws/flask_env/lib/python3.6/site-packages/sqlalchemy/orm/collections.py",
> line 680, in fire_append_event
>  item, initiator)
>File
> "/Users/xxx/Development/python_ws/flask_env/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py",
> line 943, in fire_append_event
>  state, value, initiator or self._append_token)
>File
> "/Users/xxx/Development/python_ws/flask_env/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py",
> line 1210, in emit_backref_from_collection_append_event
>  child_impl = child_state.manager[key].impl
> KeyError: 'user'
> ```
>
> Why is the behavior different?

Your "User.keywords" property is supposed to contain "UsersKeyword"
instances (which have a "user" property), You've appended a "Keyword"
instance to it, which *doesn't* have a "user" property, hence the
error.

If your userskeywords table only has foreign keys to the user and
keyword tables, and doesn't have any other behaviour, you probably
don't want to define it as a mapped class at all. Instead, use the
Table constructor, as in the first example at
http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many.

If you really do want to treat UsersKeywords as a mapped class in its
own right, you need to ensure that those objects are constructed
whenever you make a relationship between a user and a keyword:

>>> u.keywords.append(UsersKeywords(keyword=keyword))

The association proxy
(http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html)
can help with this.

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] three table join with confusing results

[Simon King]

What does the SQL look like for each of your queries?

Can you produce a standalone script that demonstrates the problem? You
could use my script from
https://groups.google.com/forum/#!topic/sqlalchemy/GNIBQMvMRg8 as a
template.

Thanks,

Simon

On Fri, Jun 1, 2018 at 2:41 PM  wrote:
>
> I'm trying to join three tables in SQLAlchemy and while it works on joining 
> and filtering with two of the tables the third one it only returns an 
> arbitrary row (always the same one for some reason) and I'm completely 
> confused about this behavior.
>
> Table classes:
>
> class Users(db.Model):
> id = db.Column(db.Integer, primary_key=True)
> username = db.Column(db.String(64), index=True, unique=True, 
> nullable=False)
>
> class Groups(db.Model):
> id = db.Column(db.Integer, unique=True)
> group_number = db.Column(db.String(30),index=True, unique=True, 
> nullable=False, primary_key=True)
>
> class GroupMembers(db.Model):
> id = db.Column(db.Integer, primary_key=True)
> group_id = db.Column(db.String(64), db.ForeignKey('groups.group_number'), 
> nullable=False)
> user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
>
> I'm trying to retrieve the results where a certain group_id is filtered by, 
> and all the rows that match that in the relationship table (GroupMembers) and 
> all the Users rows that correspond to the matching IDs.
>
> I've tried three things:
>
> q = db.session.query(GroupMemers, Groups, Users)
> q = q.join(Groups).join(Users)
> q = q.filter(GroupMembers.group_id == 5).all()
>
> This returns the expected tuples, except that it only returns the same row 
> from Users over and over with each tuple:
>
> for row in q:
> print(row)
> (, , )
> (, , )
> (, , )
> (, , )
> (, , )
>
> which is correct, in that there are 5 rows which should match in 
> GroupMembers, but each one of those rows lists a different User.id in the 
> foreign key GroupMembers.user_id, and none of them match id 3.
>
> The second thing I tried was switching the orders of the tables, so Users was 
> the first table in the list:
>
> q = db.session.query(Users, Groups, GroupMembers)
> q = q.join(Groups).join(GroupMemebrs)
> q = q.filter(GroupMembers.group_id == 5).all()
>
> but the results were exactly the same, just in the order indicated in the 
> query tuple.
>
> The third thing I tried doing was using a filter() to match 
> GroupMembers.user_id to Users.id, but this returned 0 matching rows, even 
> though there should be 5.
>
> q = db.session.query(GroupMemers, Groups, Users)
> q = q.join(Groups).join(Users)
> q = q.filter(Users.id == FacilityStaff.user_id)
> q = q.filter(GroupMembers.group_id == 5).all()
>
> I'm a bit perplexed and confused at this behavior. Without using backrefs or 
> anything like that can anyone help me with what I might be doing wrong here 
> on the joins and filtering?
>
> Thanks!
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> ---
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
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: Compare two schemas of two different DB's using Diff

[Simon King]

(Note that sqlalchemy-diff is not part of sqlalchemy, it's a tool
built on top of it, and there may not be anyone on this list who has
ever used it)

Something like this might do what you want:


from pprint import pprint
from sqlalchemydiff import compare

DBURI1 = 'oracle://scott:tiger@127.0.0.1:1521/dbname1'
DBURI2 = 'oracle://scott:tiger@127.0.0.1:1521/dbname2'

result = compare(DBURI1, DBURI2)
if result.is_match:
print('Databases are identical')
else:
print('Databases are different')
pprint(result.errors)


Simon

On Tue, May 22, 2018 at 12:26 PM, Neethu Abhinav
 wrote:
> And can you also help me the imports, that is required to use this tool?
>
>
> On Tuesday, May 22, 2018 at 4:54:03 PM UTC+5:30, Neethu Abhinav wrote:
>>
>> Yes, that's the tool i am talking about
>>
>> oh okay, so ideally if i pass my two connections, it will get me a
>> difference at the whole DB level and from the result, i can segregate what i
>> need? Sorry if i sound ignorant, i am totally new to SQLAlchemy.
>>
>> Thanks,
>> Neethu
>>
>> On Tuesday, May 22, 2018 at 4:33:34 PM UTC+5:30, Neethu Abhinav wrote:
>>>
>>> Hi,
>>>
>>> I have installed sqlalchemy 1.2.7, and went through the docs for
>>> sqlAlchemy Diff, but i am still confused on how to use it effectively for my
>>> requirement.
>>>
>>> We use Oracle 12C
>>>
>>> 1. We have two databases, which have the schema in common, DB1 Schema A,
>>> DB2 Schema A
>>> 2. I want to compare these two schemas across db's to get the difference
>>> in the metadata.(eg: difference in Table columns, missing index and so on).
>>> 3. Are these possible?
>>> 4. And in the documentation, it is mentioned to use compare(uri_left,
>>> uri_right), what are these uri's and how to use them or rather create them.
>>> Please guide me through this.
>>>
>>> Thanks,
>>> Neethu
>
> --
> 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] Compare two schemas of two different DB's using Diff

[Simon King]

On Tue, May 22, 2018 at 12:03 PM, Neethu Abhinav
 wrote:
> Hi,
>
> I have installed sqlalchemy 1.2.7, and went through the docs for sqlAlchemy
> Diff, but i am still confused on how to use it effectively for my
> requirement.
>
> We use Oracle 12C
>
> 1. We have two databases, which have the schema in common, DB1 Schema A, DB2
> Schema A
> 2. I want to compare these two schemas across db's to get the difference in
> the metadata.(eg: difference in Table columns, missing index and so on).
> 3. Are these possible?
> 4. And in the documentation, it is mentioned to use compare(uri_left,
> uri_right), what are these uri's and how to use them or rather create them.
> Please guide me through this.
>

I assume this is the tool you are talking about:

  http://sqlalchemy-diff.readthedocs.io/en/latest/

The URIs it expects will be the same ones accepted by
sqlalchemy.create_engine():

  http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls

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] Issue with Sqlalchemy in exe file(created using pyinstaller)

[Simon King]

You'll probably need to take this up with the PyInstaller folks
(http://www.pyinstaller.org/support.html). SQLAlchemy uses
pkg_resources entry points to load database dialects, and I don't know
how well PyInstaller supports those.

Sorry,

Simon

On Mon, May 21, 2018 at 8:02 PM,  <bin...@gmail.com> wrote:
> I tried --hidden-import sqlalchemy_teradata. It didnt help. Any way thanks
> for the suggestion.
>
> On Wednesday, May 16, 2018 at 3:26:48 AM UTC-5, Simon King wrote:
>>
>> On Tue, May 15, 2018 at 8:59 PM,  <bin...@gmail.com> wrote:
>> > I have been struggling for a few days due to an issue with SQLAlchemy.
>> >
>> > The line of code which is giving problem is
>> >
>> > engine =
>> > sqlalchemy.create_engine('teradata://pwd@DBinstance?driver=Teradata')
>> >
>> > This code works perfectly fine while running from Spyder while running
>> > from
>> > .py file.
>> > But while converting to an exe file and run, it gives the following
>> > error:
>> >
>> > sqlalchemy.exc.NoSuchModuleError: Can't load plugin:
>> > sqlalchemy.dialects:teradata
>> >
>> >
>> > Any help would be appreciated.
>> >
>>
>> It's possible that pyinstaller hasn't included the sqlalchemy_teradata
>> package in its output. Perhaps adding "--hidden-import
>> sqlalchemy_teradata" to your command line when building the exe might
>> help?
>>
>> 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 - 
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] polymorphic_on / polymorphic_identity randomly 'ignored'

[Simon King]

On Fri, May 18, 2018 at 12:19 PM, Benjamin B.  wrote:
> Hello everybody,
>
> TL;DR We are experiencing a bug where, with perfectly valid data, the
> polymorphic_identity is randomly (0,05%) not respected, and the object
> ends-up being the parent class instead of the "requested" child class.
>
> First, some code ! We have here a three table schema :
>
> Comm, the table that contains means of communication (emails, phones,
> ...)
> Link, which is a table responsible for linking two or more entities (of
> any kind) (legacy architecture... we're slowly getting rid of it)
> Contact, which represents a user.
>
>
> class CommTypes(enum.Enum):
> email = 1
> phone = 2
>
>
> class Comm(db.Model):
> __tablename__ = 'comm'
>
> id = db.Column('id', db.Integer, primary_key=True)
> type_id = db.Column('type', db.Integer)
>
> __mapper_args__ = {
> 'polymorphic_on': type_id
> }
>
>
> class Email(Comm):
> __mapper_args__ = {
> 'polymorphic_identity': CommTypes.email.value
> }
>
>
> class Phone(Comm):
> __mapper_args__ = {
> 'polymorphic_identity': CommTypes.phone.value
> }
>
>
> class Contact(db.Model):
> id = db.Column('id', db.Integer, primary_key=True)
>
> _comms = db.relationship(
> 'Comm',
> secondary='link',
> primaryjoin="and_(Contact.id == link.c.id1, link.c.type == 1)",
> secondaryjoin="link.c.id2 == Comm.id",
> foreign_keys='[link.c.id2, link.c.id1]'
> )
>
> @hybrid_property
> def emails(self):
> return [comm for comm in self._comms if isinstance(comm, Email)]
>
> def email():
> if not self.emails:
> return None
>
> return self.emails[0]
>
> The problem we're having is that: in production, with a perfectly valid
> contact, its email comm and the appropriate 'link' committed ; right after
> being committed, it happens that the email comm which should be an instance
> of Email, is actually an instance of Comm. Having a PDB open at the time the
> bug happens show us that we have indeed a Comm instead of an Email, but
> doing a db.session.refresh(contact) in the repl seems to solve the problem.
>
> Inspecting the content of the object (meant to be an Email) show us that the
> type_id is indeed the right one (=CommTypes.email.value), but for a reason
> we do not fully understand, it is a Comm instead. We tried to put a debug
> helper in production and we found out that immediately after, trying to get
> a new contact with Contact.query.get(contact.id) and getting the email of
> this new contact, does lead to the exact same problem.
>
> Having a random bug, we tried to reproduce by 'brute-forcing' with the code
> below, which worked. It takes a random amount of time until the bug is
> triggered.
>
> import requests
> import random
> import string
>
> def rand_str(length=10):
> return ''.join(random.choice(string.ascii_lowercase) for _ in
> range(length))
>
> def rand_email():
> return 'debug_' + rand_str() + '@' + rand_str() + '.' +
> rand_str(length=3)
>
> def gen_user_payload():
> return {
> "action": "signup",
> "email": rand_email(),
> }
>
> def register_all_users():
> session = requests.Session()
> for _ in range(1):
> user = gen_user_payload()
> session.post('https://xxx:5000/auth/register', json=user,
> verify=False)
>
> register_all_users()
>
> Notes :
>
> We are using flask / flask_sqlalchemy
> Our sqlalchemy version is 1.1.16. We looked at the changelogs to see if
> there was any update linked to our issue, but haven't seen anything in
> particular.
>
>
> Do you have any idea about the source of this problem ? What could cause
> this sort of bug ?
> If you need more data, more code .. do not hesitate to ask !
>
> Anyway, thanks for building such an awesome tool !
>

In your server code, do you ever construct Comm() instances directly,
rather than explicitly constructing Email() or Phone() instances? If
you do that, the Comm instance will be stored in the session's
identity map. As long as that instance exists in the session,
SQLAlchemy will not construct an instance of the appropriate subclass
(otherwise the session would contain 2 objects representing the same
row in the database, which SQLAlchemy tries to avoid).

For example, the script below creates a Comm object and tries to load
it back from the database without expunging the original. The result
is always a Comm object. Only after we expunged the session and reload
it do we get an Email object back.

Hope that helps,

Simon

##
import enum

import sqlalchemy as sa
import sqlalchemy.orm as saorm

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class CommTypes(enum.Enum):
email = 1
phone = 2


class Comm(Base):
__tablename__ = 'comm'
id = sa.Column('id', sa.Integer, primary_key=True)

Re: [sqlalchemy] Issue with Sqlalchemy in exe file(created using pyinstaller)

[Simon King]

On Tue, May 15, 2018 at 8:59 PM,   wrote:
> I have been struggling for a few days due to an issue with SQLAlchemy.
>
> The line of code which is giving problem is
>
> engine =
> sqlalchemy.create_engine('teradata://pwd@DBinstance?driver=Teradata')
>
> This code works perfectly fine while running from Spyder while running from
> .py file.
> But while converting to an exe file and run, it gives the following error:
>
> sqlalchemy.exc.NoSuchModuleError: Can't load plugin:
> sqlalchemy.dialects:teradata
>
>
> Any help would be appreciated.
>

It's possible that pyinstaller hasn't included the sqlalchemy_teradata
package in its output. Perhaps adding "--hidden-import
sqlalchemy_teradata" to your command line when building the exe might
help?

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] trying to retrieve data from multiple tables with .join()

[Simon King]

On Mon, May 14, 2018 at 9:37 AM,   wrote:
> Hi, I'm still relatively a newby to SQLAlchemy and not sure if I'm using the
> correct query to achieve what I'm trying to and hoping someone can point me
> in the correct direction or help me understand what I need to use to run the
> query I'm attempting.
>
> I'm trying to retrieve data from multiple tables with SQLAlchemy using the
> .join() method.
>
> When I run the query I was expecting to get a single object back which had
> all the data from the different tables joined so that I could use
> a.area_name and so on where area_name is on one of the joined tables. Below
> is the query I am running and the table layout, if anyone could offer
> insight into how to achieve the behavior I'm aiming for I would greatly
> appreciate it! I've been able to use the .join() method with this same
> syntax to match results and return them, I figured it would return the extra
> data from the rows as well since it joins the tables (perhaps I'm
> misunderstanding how the method works or how to retrieve the information via
> the query object?).
>
> If it helps with the troubleshooting I'm using MySQL as the database
>
> Thanks for any help!
>
>
> query:
>
> a = User.query.filter(User.user_id==1).join(UserGroup,
> User.usergroup==UserGroup.group_id).join(Areas,
> User.area==Areas.area_id).first()
>
>
> tables:
>
> class User(db.Model):
> user_id = db.Column(db.Integer, primary_key=True)
> name = db.Column(db.String(20), unique=True)
> usergroup = db.Column(db.Integer, db.ForeignKey('user_group.group_id'),
> nullable=False)
> area = db.Column(db.Integer, db.ForeignKey('areas.area_id'),
> nullable=False)
>
>
>
> class UserGroups(db.Model):
> id = db.Column(db.Integer, primary_key=True)
> group_id = db.Column(db.Integer, nullable=False, unique=True)
> group_name = db.Column(db.String(64), nullable=False, unique=True)
>
>
>
> class Areas(db.Model):
> id = db.Column(db.Integer, primary_key=True)
> area_id = db.Column(db.Integer, nullable=False, unique=True)
> area_name = db.Column(db.String(64), nullable=False, unique=True)
>

The normal way to handle this is to create a relationship between your
User and Areas classes:


http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-one

...which might look something like this:


import sqlalchemy.orm as saorm

class User(db.Model):
user_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), unique=True)
area_id = db.Column(db.Integer, db.ForeignKey('areas.area_id'),
nullable=False)
area = saorm.relationship("Areas")

class Areas(db.Model):
id = db.Column(db.Integer, primary_key=True)
area_id = db.Column(db.Integer, nullable=False, unique=True)
area_name = db.Column(db.String(64), nullable=False, unique=True)



Note that I renamed your "area" column to "area_id". This isn't
strictly necessary, but it makes the code a bit clearer. If "user" is
an instance of your User class, "user.area_id" would be the integer
value of the foreign key, and "user.area" would be an instance of the
Areas class. You would access the area_name value as
"user.area.area_name".

To load the areas at the same time as loading the user, you would use
one of the eager loading options:


http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#joined-eager-loading

For example:

import sqlalchemy.orm as saorm

user = User.query.options(saorm.joinedload('area')).get(1)

This would automatically add the join between the user and areas tables.

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] Confused about Many-to-Many outerjoin

[Simon King]

On Tue, May 1, 2018 at 2:35 PM,   wrote:
> I'm using the PrettyPrinted tutorial on Many to Many relationships with
> Flask-SQLAlchemy however what I'm trying to figure out he doesn't go into in
> the tutorial and I haven't had much luck in _Essential SQLAlchemy_ or
> StackOverflow finding a solution.
>
> So for his example he has two main tables, Channel and User, and then a
> relationship table to create the many to many relationship.
>
> What I'm trying to do is to use an outerjoin (or whatever would work for
> this solution in SQLAlchemy's ORM) to retrieve a list of all of the entries
> in Channel but filter it so if a certain user id is 'subscribed' (has a
> relationship in the relationship table) then it will return that ID as well,
> or a calculated column/alias (however is best in SQLAlchemy's ORM and most
> Pythonic).
>
> At the moment the only way I'm able to accomplish this requires two queries,
> one for all channels, and one for all the channels that the particular user
> is subscribed to.  Then combining them with a comprehension.  While the
> comprehension is Pythonic the double querying seems like a bad practice, and
> also not very Pythonic.
>
> I'm using MySQL for the database if that helps, but I'm guessing whatever
> the solution is it will be agnostic to the database due to the ORM?
>
> Here is the video: https://youtu.be/OvhoYbjtiKc
>
> So the DB has a Channel table, with an ID and Name, a User table with ID and
> Name, and a 'subs' table (subscriptions) to manage the relationships that
> has a foreign key for the ID in each of the other two tables.
>
> I've tried several methods using the outerjoin() from SQLAlchemy but it
> doesn't seem to limit it in the way I'm trying.
>
> Here is the join that I've had the most luck with (but again, doesn't limit
> it, it just returns all the channels, then in each channel if you look at
> the User relationships you just see ALL the users subsribed to that channel,
> not just the one I try to search for).  Below that is the code for the
> tables:
>
> chan_query = Channel.query.outerjoin(User, User.user_id==1).all()
>
> which returns all the rows in Channel (desired) but nothing to indicate if
> the selected user is subscribed to the particular row/Channel, just a list
> of ALL the users identified with that row, same for each of them.
>
> Code for the tables:
> ---
>
> subs = db.Table('subs',
> db.Column('user_id', db.Integer, db.ForeignKey('user.user_id')),
> db.Column('channel_id', db.Integer, db.ForeignKey('channel.channel_id'))
> )
>
>
> class User(db.Model):
> user_id = db.Column(db.Integer, primary_key=True)
> name = db.Column(db.String(20))
> subscriptions = db.relationship('Channel', secondary=subs,
> backref=db.backref('subscribers', lazy='dynamic'))
>
>
> class Channel(db.Model):
> channel_id = db.Column(db.Integer, primary_key=True)
> channel_name = db.Column(db.String(20))
> ---
>
> Any help is greatly appreciated, feel like I'm just stuck on this and don't
> even know how to search to find the solution :(
>

You want to return 2 pieces of information in each row: the channel,
and a flag that indicates whether the user subscribes. The best way to
do that is to use the "session.query()" method. This is the more
general version of your "Channel.query" function from
Flask-SQLAlchemy.

There are probably many different ways of expressing the query you
want in SQL. I think of it as needing an inner join between "user" and
"subs", with an extra condition restricting it to the user you are
interested in. This is then outer-joined to the "channel" table, so
that you still get channels that the user hasn't subscribed to. That
would look something like this:

userjoin = saorm.join(
User, subs,
sa.and_(subs.c.user_id == User.user_id,
User.name == 'joe'),
)
q = (session.query(Channel, User.user_id)
 .outerjoin(userjoin))

...which produces this SQL (reformatted):

SELECT channel.channel_id AS channel_channel_id,
   channel.channel_name AS channel_channel_name,
   user.user_id AS user_user_id
FROM channel
LEFT OUTER JOIN (
user JOIN subs ON subs.user_id = user.user_id AND user.name = ?
) ON channel.channel_id = subs.channel_id


Here's a full working example:


import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

subs = sa.Table(
'subs', Base.metadata,
sa.Column('user_id', sa.Integer, sa.ForeignKey('user.user_id')),
sa.Column('channel_id', sa.Integer, sa.ForeignKey('channel.channel_id')),
)


class User(Base):
__tablename__ = 'user'
user_id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(20))
subscriptions = saorm.relationship(
'Channel',
secondary=subs,
backref=saorm.backref('subscribers', lazy='dynamic'),
)


class 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Simon King]

No, you'll need to convert that to the equivalent SQL.

On Fri, Apr 27, 2018 at 3:14 PM, Jeremy Flowers
<jeremy.g.flow...@gmail.com> wrote:
> But can you still do things like slice(0,5)? Struggling to get that to work
> atm...
>
> On Fri, Apr 27, 2018 at 2:48 PM, Simon King <si...@simonking.org.uk> wrote:
>>
>> On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
>> <jeremy.g.flow...@gmail.com> wrote:
>> > Right...
>> > Been trawling back thru this chain of exchanges..
>> > Looking for this:
>> >>
>> >> At this point I would echo Mike's question: why can't you just use
>> >> "text()"?
>> >
>> > Just spotted another comment from Mike, that I've just fixed too...
>> >>
>> >>  the literal() has to be against the regular value 1 and not the
>> >> string...
>> >
>> >
>> >> as a practical matter, this query is Oracle-specific in any
>> >> case, is there a reason you can't just use text() ?   The reason
>> >> text() exists is for when one has the exact SQL they want already and
>> >> there is no need to work it into the expression language.
>> >
>> >
>> > Can I just put that into some sort of session execute?
>> > If there's a relevant example someone can point me to, it would help.
>> > I'll
>> > have a dig around.
>> >
>>
>> There are a few examples of using text() with the ORM here:
>>
>>
>> http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-textual-sql
>>
>> session.execute() behaves more like the Core-level
>> connection.execute(), which is described here:
>>
>>
>> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#sqlexpression-text
>>
>> 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 a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/sqlalchemy/aWJT1VdY3LU/unsubscribe.
>> To unsubscribe from this group and all its topics, 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.

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Simon King]

On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
 wrote:
> Right...
> Been trawling back thru this chain of exchanges..
> Looking for this:
>>
>> At this point I would echo Mike's question: why can't you just use
>> "text()"?
>
> Just spotted another comment from Mike, that I've just fixed too...
>>
>>  the literal() has to be against the regular value 1 and not the string...
>
>
>> as a practical matter, this query is Oracle-specific in any
>> case, is there a reason you can't just use text() ?   The reason
>> text() exists is for when one has the exact SQL they want already and
>> there is no need to work it into the expression language.
>
>
> Can I just put that into some sort of session execute?
> If there's a relevant example someone can point me to, it would help. I'll
> have a dig around.
>

There are a few examples of using text() with the ORM here:

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-textual-sql

session.execute() behaves more like the Core-level
connection.execute(), which is described here:

http://docs.sqlalchemy.org/en/latest/core/tutorial.html#sqlexpression-text

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: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Simon King]

The ".first()" method applies the DB-specific equivalent of "LIMIT 1"
to the query. Oracle apparently doesn't support the LIMIT keyword, so
SQLAlchemy wraps the entire query in "SELECT ... FROM (subquery) WHERE
ROWNUM <= limit":

http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#limit-offset-support

The code to do this is a bit hairy:

https://bitbucket.org/zzzeek/sqlalchemy/src/30b02003a70f37aa83e20de6229afe2a3600b648/lib/sqlalchemy/dialects/oracle/base.py#base.py-813

I suspect that it's not handling your CTE properly. I don't even know
what the right SQL syntax should be.

At this point I would echo Mike's question: why can't you just use "text()"?

Simon

On Fri, Apr 27, 2018 at 10:07 AM, Jeremy Flowers
 wrote:
> Well the printed oracle sql dialect now works correctly - when I hack it an
> replace the substitution variables for the literals like so in my SQL
> Editor:
> ==
> WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS
> (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name,
> jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, 1
> AS lvl
> FROM jobmst
> WHERE jobmst.jobmst_prntid IS NULL
> UNION ALL
> SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name,
> j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type,
> j1.lvl + 1
> FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
> WHERE j2.jobmst_prntid IS NOT NULL)
>  SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
> SELECT j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type,
> j1.lvl
> FROM j1 ORDER BY DISP_SEQ
>
> ===
>
> But for some reason, the for row in stmt.first() when I run my code listed
> below yields:  "ORA-00907: missing right parenthesis":
>
> #!/usr/bin/env python
> import sqlalchemy
> from tidal import Jobmst
> from sqlalchemy import create_engine, literal, text
> from sqlalchemy.dialects import oracle
> from sqlalchemy.orm import Session, aliased, sessionmaker
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.sql.expression import Select
> import re
>
> @compiles(Select, "oracle")
> def _add_search_hints(element, compiler, **kw):
> text = compiler.visit_select(element, **kw)
> for prefix, _ in element._prefixes:
> prefix = prefix.text
> text = re.sub(r'SELECT %s' % prefix, "%s SELECT" % prefix, text)
> return text
>
> engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
> echo=True)
> Session = sessionmaker(bind=engine)
> ses = Session()
> j2 = aliased(Jobmst, name='j2')
> j1 = ses.query(
>   Jobmst.jobmst_id, Jobmst.jobmst_name,
>   Jobmst.jobmst_prntid, Jobmst.jobmst_type, literal('1').label('lvl')
> ).filter(Jobmst.jobmst_prntid == None).cte(recursive=True,name='j1')
> j1 = j1.union_all(
>ses.query(j2.jobmst_id, j2.jobmst_name,
>  j2.jobmst_prntid, j2.jobmst_type,
>  j1.c.lvl + 1
> ).join(j1, j2.jobmst_prntid == j1.c.jobmst_id
>   ).filter(j2.jobmst_prntid != None)
> )
> stmt = ses.query(j1).prefix_with('SEARCH DEPTH FIRST BY JOBMST_NAME SET
> DISP_SEQ').order_by(text('DISP_SEQ'))
> oraclesql = stmt.statement.compile(dialect=oracle.dialect())
> print('oraclesql: ', oraclesql)
> for row in stmt.first():
>   print(row)
>
> 
>
> Here is the output:
>
> ===
> C:\opt\tidalconversion>jobwalk.py
> oraclesql:  WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl)
> AS
> (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name,
> jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type,
> :param_1 AS lvl
> FROM jobmst
> WHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS
> j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS
> j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS
> anon_1
> FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
> WHERE j2.jobmst_prntid IS NOT NULL)
>  SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT j1.jobmst_id,
> j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl
> FROM j1 ORDER BY DISP_SEQ
> 2018-04-27 09:53:25,847 INFO sqlalchemy.engine.base.Engine SELECT USER FROM
> DUAL
> 2018-04-27 09:53:25,847 INFO sqlalchemy.engine.base.Engine {}
> 2018-04-27 09:53:25,850 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
> plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
> 2018-04-27 09:53:25,850 INFO sqlalchemy.engine.base.Engine {}
> 2018-04-27 09:53:25,851 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
> unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
> 2018-04-27 09:53:25,851 INFO sqlalchemy.engine.base.Engine {}
> 2018-04-27 09:53:25,852 INFO sqlalchemy.engine.base.Engine select value from
> nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
> 2018-04-27 09:53:25,852 INFO sqlalchemy.engine.base.Engine {}
> 

Re: [sqlalchemy] Python Sqlalchemy filter by date range

[Simon King]

On Tue, Apr 24, 2018 at 4:49 AM, sum abiut  wrote:
> I have two date picker fields that i want the users to select from date to
> end date. and i want to display data between the two dates that was
> selected. From example form date: 2/14/2018 to date:3/15/2018. when the
> function is called i want to extract and display data between this two
> dates.
>
> i just need some help with the query to accomplish that.
>
> I am using sqlalchemy to pull data from an mssql db
>

Are you asking what an SQL query that filters rows between 2 dates
might look like, or are you asking how to use SQLAlchemy to generate
that query?

The answer to the first question depends on your table structure, but
might look something like:

SELECT *
FROM 
WHERE  >= '2018-02-14'
AND  <= '2018-03-15'

For the second question, you should probably go through the tutorial
at http://docs.sqlalchemy.org/en/latest/orm/tutorial.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] simple: get max id in table

[Simon King]

On Tue, Apr 24, 2018 at 4:43 PM, Steve Murphy  wrote:
> I'm just not getting it:
>
> Want:   select max(id) from table;
>
> attempt (latest):
>
> from sqlalchemy import *
> from sqlalchemy.engine import reflection
> from sqlalchemy import schema
> from sqlalchemy import exc
> from psycopg2 import *
> import re
> import time
> import os
> targethost = "192.168.181.204"
> targetdb = "postgresql://user:pword@"+ targethost +"/whatever"
> eng2 = create_engine(targetdb)
> con2 = eng2.connect()
> meta2 = MetaData()
> meta2.reflect(bind=eng2)
> insp2 = inspect(eng2)
> dst_tab = meta2.tables["tab1"]
> q3 = dst_tab.select([func.max(dst_tab.c.id)])
> maxphoneid = con2.execute(q3).scalar()
>
>
> I get: sqlalchemy.exc.ArgumentError: SQL expression object or string
> expected, got object of type  instead
>
> without the brackets in the select call, I get:
>
> sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) argument of
> WHERE must be type boolean, not type integer
> LINE 3: WHERE max(phone.id)
>
> There's gotta be a way! Any ideas?
>

You are calling the "select" method of a Table object. The first
parameter is expected to be the WHERE clause:


http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Table.select

You probably just want to call the bare "select" function instead:

q3 = select([func.max(dst_tab.c.id)])


http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.select

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: unable to do nested select while using query object

[Simon King]

Trying to diagnose a problem with a query when we don't have any way
to run it for ourselves or sample data to run it against, is very
difficult. If you want more help, I think you're going to need to
produce a self-contained script with your table definitions and some
sample data, that runs against a sqlite database.

Good luck!

Simon

On Wed, Apr 18, 2018 at 10:21 PM, su-sa  wrote:
> @Mike, Jonathan and Simon - Thank you so much for all your efforts. I hope I
> can fix the problem soon in the dialect :-)
>
> P.s. I have finally been able to generate the correct query, but now
> currently the problem is that I get no result, although the query is
> absolutely correct and should give back results.
> Thats how I built the query now:
>
> subquery = select([func.min(Partsupp.ps_supplycost)]).where(Part.p_partkey
> == Partsupp.ps_partkey).where(
>Supplier.s_suppkey ==
> Partsupp.ps_suppkey).where(Supplier.s_nationkey ==
> Nation.n_nationkey).where(
>Nation.n_regionkey ==
> Region.r_regionkey).where(Region.r_name == 'EUROPE').correlate(Part)
> q2 = session.query(Supplier.s_acctbal, Supplier.s_name, Nation.n_name,
> Part.p_partkey, Part.p_mfgr,
>Supplier.s_address, Supplier.s_phone,
> Supplier.s_comment).filter(Part.p_partkey == Partsupp.ps_partkey,
>Supplier.s_suppkey== Partsupp.ps_suppkey, Part.p_size ==
> 15, Part.p_type.like('%BRASS'),
>Supplier.s_nationkey == Nation.n_nationkey,
> Nation.n_regionkey == Region.r_regionkey,
>Region.r_name == 'Europe', Partsupp.ps_supplycost ==
> subquery).order_by(Supplier.s_acctbal.desc(),
>Nation.n_name, Supplier.s_name,
> Part.p_partkey).limit(100)
>
>
>
>
> --
> 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: unable to do nested select while using query object

[Simon King]

Ah, I didn't notice this part of your query:

.filter(..., Partsupp.ps_supplycost ==
session.query(func.min(Partsupp.ps_supplycost)))

I'm not sure exactly what SQL you are aiming for there, but I think
you need to add .correlate (or maybe .correlate_except) to that inner
query.

Simon


On Wed, Apr 18, 2018 at 5:24 PM, su-sa <sachdeva.sugandh...@gmail.com> wrote:
>
> Hi Simon,
>
> I am executing q2 on its own, may be I shouldnt have mentioned correlate,
> but if I dont mention it I get another error:
>
> sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT
> min(partsupp.ps_supplycost) AS min_1
> FROM partsupp, part, supplier, nation, region
> WHERE part.p_partkey = partsupp.ps_partkey AND supplier.s_suppkey =
> partsupp.ps_suppkey AND supplier.s_nationkey = nation.n_nationkey AND
> nation.n_regionkey = region.r_regionkey AND region.r_name = ?' returned no
> FROM clauses due to auto-correlation; specify correlate() to control
> correlation manually.
>
> I am not sure how I should go about it.
>
> Thanks for your help,
>
> Greetings,
> Sugandha
>
> On Wednesday, 18 April 2018 17:27:36 UTC+2, Simon King wrote:
>>
>> Are you executing q2 on it's own, or nested in a larger query? If so,
>> can you show the code for the larger query.
>>
>> I ask because you are calling ".correlate(Partsupp, Supplier, Nation,
>> Region)", which I think has the effect of removing those tables from
>> the FROM clause, in the assumption that they are part of the outer
>> query.
>>
>> 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 - 
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: unable to do nested select while using query object

[Simon King]

Are you executing q2 on it's own, or nested in a larger query? If so,
can you show the code for the larger query.

I ask because you are calling ".correlate(Partsupp, Supplier, Nation,
Region)", which I think has the effect of removing those tables from
the FROM clause, in the assumption that they are part of the outer
query.

Simon

On Wed, Apr 18, 2018 at 4:20 PM, su-sa  wrote:
>
> But if I am not mistaken, the from clause of the query is generated by
> SQLAlchemy and the database driver or the dialect has no influence on this
> from clause generation of SQLAlchemy.
>
> And as you can see, the wrong from clause in generated in the subquery:
>
> AS min_1 \nFROM part \nWHERE part.p_partkey = partsupp.ps_partkey AND
> supplier.s_suppkey = partsupp.ps_suppkey AND supplier.s_nationkey =
> nation.n_nationkey
>
>
> What should actually be generated is:
> FROM supplier, nation, part, partsupp, region
>
> --
> 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] How to get ForeignKey from Relationship property

[Simon King]

On Wed, Apr 11, 2018 at 7:40 AM, Tolstov Sergey  wrote:
> I use dynamic constructor for class.
> It works fine for instance who have not foreign keys. But when FK on another
> instance it cannot load them
>
> Example:
> class left (Base):
>   id = sqlalchemy.Column(UUID, primary_key = True)
>   def __getattr__(self, attribute):
> if attribute == 'rights':
>   right().left
>   return getattr(self, attribute)
>
> class right(Base):
>   id = sqlalchemy.Column(UUID, primary_key = True)
>   def __getattr__(self, attribute):
> if attribute == 'left':
>   rel_key = sqlalchemy.Column(UUID, sqlalchemy.ForeignKey(left.mRID),
> nullable = True)
>   setattr(self.__class__, 'left_mRID', rel_key)
>   setattr(self.__class__, 'left', sqlalchemy.orm.relationship(left,\
>foreign_keys = rel_key, uselist = False,\
>backref = sqlalchemy.orm.backref('rights', uselist = True)))
>   return getattr(self,attribute)
>
> Works:
> left_1 = session.query(left).first()
> print (str(left_1.rights))
>
> Not works:
> right_1 = session.query(right).first()
> print (str(right_1.left))
> I think that it may be fix them, but i need universal for all
> relationshipproperties
> class right(Base):
>   ...
>   def __getattr__(self, attribute):
> ...
> session.refresh(self, attribute_names = ['left_mRID'])
>

I don't know what's going on here, but this isn't really the way
SQLAlchemy is expected to work. Columns and relationships are normally
set up statically, and SQLAlchemy has a "compile mappers" phase which
figures out the connections between all the classes, which you might
be bypassing by defining them dynamically.

Can you explain why you are trying to create columns and relationships
dynamically? There might be a different way to approach the problem.

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] is this intentional or a bug with subquery join

[Simon King]

On Wed, Apr 4, 2018 at 4:08 AM, Jonathan Vanasco  wrote:
>
>
> On Tuesday, April 3, 2018 at 10:34:03 PM UTC-4, Mike Bayer wrote:
>>
>> "does not work"  ?
>
>
> wow, i am an awful person. sorry. that is the least helpful description of
> what happens i can think of.
>
> second try:
>
> if the subquery is the 1st element, the select compiles correctly and gives
> me the correct data.
>
> if the subquery is 2nd element, sqlalchemy raises an exception that
> event_timestamp is not a column in subquery.c
>

Where is the traceback raised? On the evaluation of the
"subquery.c.event_timestamp" expression? That wouldn't really make any
sense. Or is it when the query itself is evaluated? We need to see at
least the traceback, or better a script to reproduce.

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] rollback() practice

[Simon King]

On Thu, Mar 29, 2018 at 8:57 AM,   wrote:
> Hello,
>
> I had some database problems (unfinished transactions) so I have wrapped
> sqlalchemy database handling functions in following way:
>
> def add(entry, session):
> """Wrapper for db_session.add"""
> try:
> return session.add(entry)
> except Exception as e:
> session.rollback()
> raise e
>
> def query(ModelClass, session):
> """Wrapper for session.query(Class)"""
> try:
> return session.query(ModelClass)
> except Exception as e:
> session.rollback()
> raise e
>
> def commit(session):
> """Wrapper for session.commit"""
> try:
> return session.commit()
> except Exception as e:
> session.rollback()
> raise e
>
> def delete(entry, session):
> """Wrapper for session.delete"""
> try:
> return session.delete(entry)
> except Exception as e:
> session.rollback()
> raise e
>
> Is this a good practice?
>

In general, you should do what makes the most sense for your
application, and if this style works for you, that's fine. It's
difficult to say more without seeing how you use these functions. A
more common pattern is to put the session management "around" the rest
of your application code, and handle exceptions in just one place.
There's a section in the docs with more details:

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-faq-whentocreate

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] Session's dialect's server_version_info may be None? (Execution order dependency)

[Simon King]

On Wed, Feb 21, 2018 at 6:14 AM,   wrote:
> Hello,
> Suppose I create a db session the normal way:
>
 engine = engine_from_config({"sqlalchemy.url":"mysql+pymysql://…"})
 Session = sessionmaker(bind=engine)
 session = Session()
>
> I noticed that there is now an order dependency between:
>
 session.bind.dialect.server_version_info  # None
 session.query(Alembic.version_num).scalar()
> 'cb13f97d30c6'
 session.bind.dialect.server_version_info  # Now contains a tuple
> (5, 6, 34)
>
> I can't quite make sense of this behavior, can somebody please shed some
> light on it?
>

SQLAlchemy doesn't connect to the database until it needs to. Creating
a Session by itself does not cause it to connect. This is mentioned in
the docs:

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html

"""
The Session begins in an essentially stateless form. Once queries are
issued or other objects are persisted with it, it requests a
connection resource from an Engine that is associated either with the
Session itself or with the mapped Table objects being operated upon.
This connection represents an ongoing transaction, which remains in
effect until the Session is instructed to commit or roll back its
pending state.
"""

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] How right use session/scoped_session in web app?

[Simon King]

OK, so what's not working?

On Fri, Feb 16, 2018 at 10:55 AM,  <eugene.de...@gmail.com> wrote:
> Yes session.is_active is True.
> I really sure, because i log this operation.
>
> пятница, 16 февраля 2018 г., 13:50:51 UTC+3 пользователь Simon King написал:
>>
>> You haven't explained in what way it's not working with your latest
>> iteration.
>>
>> The last code you posted only called session.close() if
>> session.is_active was true. Are you sure you really are closing the
>> session?
>>
>> Simon
>>
>> On Fri, Feb 16, 2018 at 10:02 AM,  <eugene...@gmail.com> wrote:
>> > 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 Fal

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

[Simon King]

You haven't explained in what way it's not working with your latest iteration.

The last code you posted only called session.close() if
session.is_active was true. Are you sure you really are closing the
session?

Simon

On Fri, Feb 16, 2018 at 10:02 AM,  <eugene.de...@gmail.com> wrote:
> 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/api/#signals) with request_started
>>>

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

[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,  <eugene.de...@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/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,  <eugene...@gmail.com> 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 ==

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

[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,  <eugene.de...@gmail.com> 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:
> return True
> elif fetch == 'count':
> return result
> return result.fetchone()
>
> and use it in all execute statements, but that not help.
> Still receive error
> This result object does not return rows. It has been closed automatically.
> But how it closed if that another session?
> Can you correct my code?
>
>
>
>
> среда, 14 февраля 2018 г., 17:45:59 UTC+3 пользователь Simon King написал:
>>
>> I think there are a couple of problems with this.
>>
>> 1. You are calling scoped_session and sessionmaker every time the
>> decorated function is called, which is unnecessary. sessionmaker
>> returns a factory function for creating sessions, so you typically
>> only have one sessionmaker() call in your application. You could
>> either make it a module-global, or do it during application
>> configuration.
>>
>> 2. You are never calling session.close(). This means that the
>> transaction started by a web request will stay open after the request
>> ends. Since you are using scoped_session as well, the session is
>> maintained as a thread-local object, so when the thread handles
>> another web request, it will use the same session and the same
>> transaction. Your long-running transactions are probably the reason
>> why you are getting blocked requests.
>>
>> You might like to read
>>
>> http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-frequently-asked-questions.
>>
>> I was going to say that you need to add a "finally" section to your
>> session_decorator that calls session.close(), but if you ever have one
>> decorated function calling another decorated function, they are going
>> to interfere with each other. If you are *absolutely certain* that
>> will never happen, adding the "finally" section will probably improve
>> things, but I would recommend that you try to structure your app a
>> little differently, so that the session creation and teardown happen
>> at the beginning and end of the web request.
>>
>> Hope that helps,
>>
>> Simon
>>
>>
>> On Wed, Feb 14, 2018 at 2:14 PM,  <eugene...@gmail.com> wrote:
>> > Decorator like this
>> >
>> > engine = create_engine(
>> >
>> >
>> > 'mssql+pymssql://{LOGIN}:{PASSWORD}@{HOST}/{DATABASE}?charset={CHARSET}={TIMEOUT}'.format(**DATABASE),
>> > isolation_level='READ COMMITTED'
>> > )
>> >
>> >
>> > def decorator_with_args(decorator_to_enhance):
>> > """
>> > https://habrahabr.ru/post/141501

Re: [sqlalchemy] Define one to one and many-to-many relationship using SQLAlchemy Core

[Simon King]

On Thu, Feb 15, 2018 at 5:09 AM, George  wrote:
> I have searched the documentation but didn't find anything useful. Please
> guide me in the right direction.
>

I'm not sure I understand the question. Relationships are part of the
ORM layer of SQLAlchemy, not Core. Can you explain what you're looking
for in more detail?

Thanks,

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] How right use session/scoped_session in web app?

[Simon King]

I think there are a couple of problems with this.

1. You are calling scoped_session and sessionmaker every time the
decorated function is called, which is unnecessary. sessionmaker
returns a factory function for creating sessions, so you typically
only have one sessionmaker() call in your application. You could
either make it a module-global, or do it during application
configuration.

2. You are never calling session.close(). This means that the
transaction started by a web request will stay open after the request
ends. Since you are using scoped_session as well, the session is
maintained as a thread-local object, so when the thread handles
another web request, it will use the same session and the same
transaction. Your long-running transactions are probably the reason
why you are getting blocked requests.

You might like to read
http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-frequently-asked-questions.

I was going to say that you need to add a "finally" section to your
session_decorator that calls session.close(), but if you ever have one
decorated function calling another decorated function, they are going
to interfere with each other. If you are *absolutely certain* that
will never happen, adding the "finally" section will probably improve
things, but I would recommend that you try to structure your app a
little differently, so that the session creation and teardown happen
at the beginning and end of the web request.

Hope that helps,

Simon


On Wed, Feb 14, 2018 at 2:14 PM,  <eugene.de...@gmail.com> wrote:
> Decorator like this
>
> engine = create_engine(
>
> 'mssql+pymssql://{LOGIN}:{PASSWORD}@{HOST}/{DATABASE}?charset={CHARSET}={TIMEOUT}'.format(**DATABASE),
> isolation_level='READ COMMITTED'
> )
>
>
> def decorator_with_args(decorator_to_enhance):
> """
> https://habrahabr.ru/post/141501/
> """
> def decorator_maker(*args, **kwargs):
> def decorator_wrapper(func):
> return decorator_to_enhance(func, *args, **kwargs)
> return decorator_wrapper
> return decorator_maker
>
>
> def scope_func():
> return scoped_session(sessionmaker(bind=engine, autocommit=True))()
>
>
> @decorator_with_args
> def session_decorator(func, default=None, **kwargs):
> def wrapper(*a, **kw):
> session = scope_func()
> if 'session' not in kw:
> kw['session'] = session
> try:
> return func(*a, **kw)
> except Exception as e:
> session.rollback()
>
> print('#FUNCTION NAME: {}'.format(func.__name__))
>
>     print(e)
> logging.error(e)
> return default
> return wrapper
>
>
>
>
> среда, 14 февраля 2018 г., 17:06:54 UTC+3 пользователь Simon King написал:
>>
>> The pattern you should be aiming for is one in which a fresh
>> transaction is started for every web request that touches the
>> database, and that the transaction is closed at the end of the
>> request. How are you ensuring that at the moment?
>>
>> Simon
>>
>> On Wed, Feb 14, 2018 at 12:51 PM,  <eugene...@gmail.com> wrote:
>> > If I run tests where all functions run one-by-one - all tests passed.
>> > But when i run web app and functions can call almost in parallel then i
>> > have
>> > a problem, then there are problems - transactions can block each other.
>> > I tried to set the isolation level of SNAPSHOT and READ COMMITTED, but
>> > it
>> > did not help.
>> >
>> > среда, 14 февраля 2018 г., 14:58:37 UTC+3 пользователь
>> > eugene...@gmail.com
>> > написал:
>> >>
>> >> Hello, Mike!
>> >> In my web app i have many selects like
>> >> session.execute(select([table1]).where(condition))
>> >> and not so much updates, inserts and deletes like
>> >> session.execute(update(table1).where(condition).values(**values))
>> >> session.execute(insert(table1).values(**values))
>> >> session.execute(delete(table1).where(condition))
>> >>
>> >> What better way to create session for web application?
>> >> Without additional components like flask-sqlalchemy.
>> >>
>> >> суббота, 27 января 2018 г., 20:23:05 UTC+3 пользователь Mike Bayer
>> >> написал:
>> >>>
>> >>> On Sat, Jan 27, 2018 at 5:49 AM, Евгений Рымарев
>> >>> <rymarev...@gmail.com> wrote:
>> >>> > I receive this error:
>> >>> > This result object does not return rows. It has been closed
>> >>> > automatically.
>&

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

[Simon King]

The pattern you should be aiming for is one in which a fresh
transaction is started for every web request that touches the
database, and that the transaction is closed at the end of the
request. How are you ensuring that at the moment?

Simon

On Wed, Feb 14, 2018 at 12:51 PM,   wrote:
> If I run tests where all functions run one-by-one - all tests passed.
> But when i run web app and functions can call almost in parallel then i have
> a problem, then there are problems - transactions can block each other.
> I tried to set the isolation level of SNAPSHOT and READ COMMITTED, but it
> did not help.
>
> среда, 14 февраля 2018 г., 14:58:37 UTC+3 пользователь eugene...@gmail.com
> написал:
>>
>> Hello, Mike!
>> In my web app i have many selects like
>> session.execute(select([table1]).where(condition))
>> and not so much updates, inserts and deletes like
>> session.execute(update(table1).where(condition).values(**values))
>> session.execute(insert(table1).values(**values))
>> session.execute(delete(table1).where(condition))
>>
>> What better way to create session for web application?
>> Without additional components like flask-sqlalchemy.
>>
>> суббота, 27 января 2018 г., 20:23:05 UTC+3 пользователь Mike Bayer
>> написал:
>>>
>>> On Sat, Jan 27, 2018 at 5:49 AM, Евгений Рымарев
>>>  wrote:
>>> > I receive this error:
>>> > This result object does not return rows. It has been closed
>>> > automatically.
>>>
>>> there's a lot of weird situations which can cause that error, usually
>>> when using a connection that has had some failure condition occur upon
>>> it which renders the connection unusable until either a transaction is
>>> rolled back or sometimes the connection needs to be discarded.  We
>>> can't diagnose it without a full example that reproduces it as well as
>>> the complete stack trace.
>>>
>>>
>>> >
>>> >
>>> >
>>> > суббота, 27 января 2018 г., 1:09:53 UTC+3 пользователь Mike Bayer
>>> > написал:
>>> >>
>>> >> On Fri, Jan 26, 2018 at 4:21 PM, Евгений Рымарев
>>> >>  wrote:
>>> >> > Hello, everyone!
>>> >> > Engine:
>>> >> > engine = create_engine(
>>> >> >
>>> >> >
>>> >> >
>>> >> > 'mssql+pymssql://{LOGIN}:{PASSWORD}@{HOST}/{DATABASE}?charset={CHARSET}'.format(**DATABASE),
>>> >> > isolation_level='READ COMMITTED'
>>> >> > )
>>> >> >
>>> >> >
>>> >> > My first decorator for session:
>>> >> > @decorator_with_args
>>> >> > def session_decorator(func, default=None):
>>> >> >def wrapper(*a, **kw):
>>> >> >s = Session(engine)
>>> >> >kw['session'] = s
>>> >> >try:
>>> >> >return func(*a, **kw)
>>> >> >except Exception as e:
>>> >> >func(*a, **kw)
>>> >> >s.rollback()
>>> >> >s.close()
>>> >> >logging.error(e)
>>> >> >return default
>>> >> >finally:
>>> >> >s.commit()
>>> >> >s.close()
>>> >> >return wrapper
>>> >> >
>>> >> > My second decorator for session:
>>> >> > session = scoped_session(sessionmaker(bind=engine))
>>> >> >
>>> >> >
>>> >> > @decorator_with_args
>>> >> > def session_decorator(func, default=None):
>>> >> >def wrapper(*a, **kw):
>>> >> >kw['session'] = session
>>> >> >try:
>>> >> >return func(*a, **kw)
>>> >> >except Exception as e:
>>> >> >session.remove()
>>> >> >logging.error(e)
>>> >> >return default
>>> >> >finally:
>>> >> >session.remove()
>>> >> >return wrapper
>>> >> >
>>> >> > In both attempts, I came to the conclusion that sessions can block
>>> >> > other
>>> >> > sessions.
>>> >> > How correctly to create a session once, so that it can be used
>>> >> > throughout
>>> >> > the web application and no locks were created?
>>> >>
>>> >> The Session doesn't create locks, transactions and your database does.
>>> >>
>>> >> What is the nature of these "blocks", are they deadlocks between
>>> >> tables or rows?  What operations are prevented from proceeding?  Are
>>> >> the locks timing out?   SQL Server has graphical consoles that can
>>> >> show you this.
>>> >>
>>> >> What version of SQL Server is this?   Do you have
>>> >> ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT turned on?
>>> >>
>>> >>
>>> >> >
>>> >> > --
>>> >> > 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 

Re: [sqlalchemy] running py.test

[Simon King]

The error is coming from here:

https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/testing/mock.py

So I guess you could start by adding some print statements in that
file to see where the exception is being raised. Perhaps you have a
different "mock.py" somewhere on the PYTHONPATH that is being imported
instead of the real library?

Simon


On Tue, Feb 13, 2018 at 3:58 PM, su-sa <sachdeva.sugandh...@gmail.com> wrote:
> Hi Simon,
>
> I installed both pytest and mock using pip install py.test mock in the
> virtualenv. I am using anaconda python. Both the libraries are surely
> installed.
>
>
> On Friday, 9 February 2018 12:36:26 UTC+1, Simon King wrote:
>>
>> On Wed, Feb 7, 2018 at 2:23 PM, su-sa <sachdeva@gmail.com> wrote:
>> > Hallo everyone,
>> >
>> > I am trying to run all the pytest of sqlalchemy. But I always get the
>> > error
>> > - SQLAlchemy requires Mock as of version 0.8.2. I already have the mock
>> > library installed. Could somebody please help me to solve the problem.
>> >
>>
>> How have you installed these libraries? Are you using virtualenv? If
>> so, are pytest and mock both installed in the virtualenv?
>>
>> 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 - 
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: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Simon King]

On Fri, Feb 9, 2018 at 7:38 PM, Jeremy Flowers
 wrote:
> And I do want to use this functionality repeatedly on many columns, hence
> the idea of registering a function.
>

For what it's worth, I would do it something like this:

def removechars(col, chars):
for c in chars:
col = sa.func.REPLACE(col, c, '')
return col

def removewhitespace(col):
return removechars(col, '\t\r\n')

print removewhitespace(Jobdtl.jobdtl_cmd)

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: Why does join() method not have join type as an argument?/Is left-join the default..

[Simon King]

As you say, .join() produces an inner join by default. You can specify
isouter=True to get a left outer join (or call the .outerjoin method
instead), and full=True to get a full outer join. I think you'd get a
cross join if you just didn't call the .join() method at all.

Simon

On Fri, Feb 9, 2018 at 4:52 PM, Jeremy Flowers
 wrote:
> I'm wondering if this is part of the secret sauce:
> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.join
> Also I guess cross-join may be another join type.
> JOIN is INNER by default..
> https://stackoverflow.com/a/19646594/495157
>
>
> On Friday, 9 February 2018 16:03:03 UTC, Jeremy Flowers wrote:
>>
>> I've got a domain model out of sqlacodegen.
>> I need to do a SQL a join between two tables, with no inferred
>> foreign-keys in the database I'm working on.
>> Consequently there isn't a relationship defined for the specific columns I
>> need to join
>>
>> 1) I need a INNER JOIN for the first.
>> 2) But later I need multiple LEFT JOINs on other tables, I've not yet
>> introduced to the code..
>>
>> (1) How would I go about doing the INNER JOIN first?: (What is the
>> default? left, inner etc?)
>>
>> print(session.query(Jobmst.jobmst_type,
>> Jobmst.jobmst_name,
>> Jobmst.jobmst_prntname,
>> Jobmst.jobmst_prntid,
>> Jobmst.jobmst_evntoffset,
>> Jobmst.evntmst_id,
>> Jobdtl.jobdtl_proxy,
>> Jobdtl.jobdtl_proxy2,
>> Jobdtl.jobdtl_inhagent,
>> Jobdtl.jobdtl_inhevent,
>> Jobdtl.jobdtl_cmd,
>> Jobdtl.jobdtl_params,
>> Jobdtl.jobdtl_fromtm,
>> Jobdtl.jobdtl_untiltm,
>> Jobdtl.jobdtl_timewin,
>> Jobdtl.jobdtl_interval,
>> Jobdtl.jobdtl_intervalcnt
>>).join(Jobdtl, Jobmst.jobmst_id==Jobdtl.jobdtl_id,)
>> .first())
>>
>>
>> Why doesn't join have another parameter where I can say, 'inner', 'left',
>> etc?
>> Per the join types listed here:
>> https://www.w3schools.com/sql/sql_join.asp
>>
>> I've seen stuff here
>>
>> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join.params.isouter
>> And here...
>>
>> http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.joinedload
>> But if I understand it correctly I'd need a relationship defined for
>> joinedload.
>>
>> Can someone advise. Thanks
>>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
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: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Simon King]

Out of interest, why would you not write:

print(
session.query(Jobmst.jobmst_type, Jobmst.jobmst_name)
.first()
)

The call to with_entities seems unnecessary.

Simon

On Fri, Feb 9, 2018 at 12:27 PM, Jeremy Flowers
<jeremy.g.flow...@gmail.com> wrote:
> And that can be simplified to:
> print(session.query()
>  .with_entities(Jobmst.jobmst_type, Jobmst.jobmst_name)
>  .first()
>   )
>
> On Friday, 9 February 2018 12:21:37 UTC, Jeremy Flowers wrote:
>>
>> Hi Simon.
>> Instead of using values(), I did this.
>> print(session.query(Jobmst)
>>  .with_entities(Jobmst.jobmst_type, Jobmst.jobmst_name)
>>  .first()
>>   )
>>
>> and that worked a treat too.
>> Thanks.
>>
>>
>> On Friday, 9 February 2018 11:58:18 UTC, Simon King wrote:
>>>
>>> The chaining-friendly method you are looking for is probably
>>> with_entities():
>>>
>>>
>>> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_entities
>>>
>>> Simon
>>>
>>> On Fri, Feb 9, 2018 at 11:52 AM, Jeremy Flowers
>>> <jeremy.g...@gmail.com> wrote:
>>> > From watching your videos I always thought some sort of query object
>>> > would
>>> > be returned from the query() operation, so method chaining (aka fluent
>>> > interface) could always be performed.. in a way that is analogous to
>>> > JQuery.
>>> > But what I'm doing seems to be breaking that paradigm. What am I doing
>>> > wrong
>>> > or not getting?
>>> >
>>> > On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote:
>>> >>
>>> >> I've seen you can do things like this:
>>> >> fields = ['jobmst_type', 'jobmst_name']
>>> >>  print(session.query(Jobmst).options(load_only(*fields)).first())
>>> >>
>>> >> But according to the documentation, you should be able to do something
>>> >> with Query values() too.
>>> >> But once again I seem to be dumbfounded by the syntatic sugar.
>>> >>
>>> >> What data type does the list of values need?
>>> >> Is there a way to introspect that ahead of time?
>>> >>
>>> >> Thought I was onto something with .base_columns, but that didn't work
>>> >> either...
>>> >>
>>> >> I ended up with something like an instrumentalAttributes mismatch.
>>> >> print(session.query(Jobmst)
>>> >>  .values([Jobmst.jobmst_type.base_columns,
>>> >>   Jobmst.jobmst_name.base_columns
>>> >>   ])
>>> >>  .first()
>>> >>   )
>>> >>
>>> >> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
>>> >> entity expected - got '[{Column('jobmst_type', Numeric(scale=0,
>>> >> asdecimal=False), table=, nullable=False)},
>>> >> {Column('jobmst_name',
>>> >> String(length=256), table=, nullable=False)}]'
>>> >>
>>> >> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type,
>>> >> Jobmst.jobmst_name) too - but looking to understand what values
>>> >> expects.
>>> >> Mike, Can documentation not specify 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+...@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
> Ve

Re: [sqlalchemy] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Simon King]

The chaining-friendly method you are looking for is probably with_entities():

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_entities

Simon

On Fri, Feb 9, 2018 at 11:52 AM, Jeremy Flowers
 wrote:
> From watching your videos I always thought some sort of query object would
> be returned from the query() operation, so method chaining (aka fluent
> interface) could always be performed.. in a way that is analogous to JQuery.
> But what I'm doing seems to be breaking that paradigm. What am I doing wrong
> or not getting?
>
> On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote:
>>
>> I've seen you can do things like this:
>> fields = ['jobmst_type', 'jobmst_name']
>>  print(session.query(Jobmst).options(load_only(*fields)).first())
>>
>> But according to the documentation, you should be able to do something
>> with Query values() too.
>> But once again I seem to be dumbfounded by the syntatic sugar.
>>
>> What data type does the list of values need?
>> Is there a way to introspect that ahead of time?
>>
>> Thought I was onto something with .base_columns, but that didn't work
>> either...
>>
>> I ended up with something like an instrumentalAttributes mismatch.
>> print(session.query(Jobmst)
>>  .values([Jobmst.jobmst_type.base_columns,
>>   Jobmst.jobmst_name.base_columns
>>   ])
>>  .first()
>>   )
>>
>> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
>> entity expected - got '[{Column('jobmst_type', Numeric(scale=0,
>> asdecimal=False), table=, nullable=False)}, {Column('jobmst_name',
>> String(length=256), table=, nullable=False)}]'
>>
>> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type,
>> Jobmst.jobmst_name) too - but looking to understand what values expects.
>> Mike, Can documentation not specify 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] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Simon King]

The main point you should take from Mike's original reply is:

.values() is a weird method and it's pretty old, usually people
just set the columns up front

You probably shouldn't use it.

On Fri, Feb 9, 2018 at 11:45 AM, Jeremy Flowers
 wrote:
> Also this didn't work for me:
>
> print(session.query(Jobmst)
>  .values(Jobmst.jobmst_type,
>  Jobmst.jobmst_name)
>  .first()
>   )
>
> yields:
> AttributeError: 'generator' object has no attribute 'first'

This is because .first() is a method of Query, but .values() doesn't
return a Query, it returns an iterator (in this case, a generator).

>
> Swapping first to earlier in the chain like so:
>
> print(session.query(Jobmst).first()
>  .values(Jobmst.jobmst_type,
>  Jobmst.jobmst_name)
>   )
>
> yields:
>
>
> AttributeError: 'Jobmst' object has no attribute 'values'
>

This is because Query.first() returns an instance of the thing that
you are querying for (in this case Jobmst).

> I get confused as well, thinking that *columns (from here) infers a list of
> some kind.
>
> So if I don't have a columns variable assigned to a list, I should be able
> to embed one directly with this sort of syntax values([c1,c2])
> But you indicate something like values(c1,c2).
> So that confuses me as a newbie to Python (my background being predominantly
> Java, Groovy, Typescript, Javascript, Angular 2-4, Ionic 2-3).

If your parameters are in a list, you can call a function like this:

params = [1, 2, 3, 4]
function(*params)
# is equivalent to function(1, 2, 3, 4)

I think a similar idea in Javascript is

params = [1, 2, 3, 4]
function.call(null, params)

(my JS is rusty though, so that might be wrong)

> The * prefix immediately clicked for my from somewhere else. Maybe
> academically from reading about C++.. Something like a pointer to the whole
> array.
>
> I've been brushing up on things like *args *kwargs too - so understand their
> purpose.
> Duck typing, passing parameters as a dictionary is known to me from Groovy.
> It still confounds me though knowing what type to pass in the values().
> Have been reading here about it:
>
>
>
>
> On Friday, 9 February 2018 09:48:51 UTC, Jeremy Flowers wrote:
>>
>> I was thinking about this overnight..
>> Do values relates specifically to inserts and updates, not selects/deletes
>> perhaps, which would correlate to SQL syntax.
>> If, it would make sense to indicate that in the documentation
>>
>> On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote:
>>>
>>> I've seen you can do things like this:
>>> fields = ['jobmst_type', 'jobmst_name']
>>>  print(session.query(Jobmst).options(load_only(*fields)).first())
>>>
>>> But according to the documentation, you should be able to do something
>>> with Query values() too.
>>> But once again I seem to be dumbfounded by the syntatic sugar.
>>>
>>> What data type does the list of values need?
>>> Is there a way to introspect that ahead of time?
>>>
>>> Thought I was onto something with .base_columns, but that didn't work
>>> either...
>>>
>>> I ended up with something like an instrumentalAttributes mismatch.
>>> print(session.query(Jobmst)
>>>  .values([Jobmst.jobmst_type.base_columns,
>>>   Jobmst.jobmst_name.base_columns
>>>   ])
>>>  .first()
>>>   )
>>>
>>> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
>>> entity expected - got '[{Column('jobmst_type', Numeric(scale=0,
>>> asdecimal=False), table=, nullable=False)}, {Column('jobmst_name',
>>> String(length=256), table=, nullable=False)}]'
>>>
>>> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type,
>>> Jobmst.jobmst_name) too - but looking to understand what values expects.
>>> Mike, Can documentation not specify 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 

Re: [sqlalchemy] Query a query in SQLAlchemy

[Simon King]

On Fri, Feb 9, 2018 at 6:06 AM, Charles Heizer  wrote:
> Hello, does anyone know if it's possible to query a query result in
> SQLAlchemy?
>
> Thanks!
>

I'm not quite sure what you mean. If you've got a Query object with
some filter conditions already defined, you can add further
restrictions to it simply by calling its ".filter()" method.
Alternatively you can convert the query to a subquery and then build
another query around that.

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-subqueries

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] running py.test

[Simon King]

On Wed, Feb 7, 2018 at 2:23 PM, su-sa  wrote:
> Hallo everyone,
>
> I am trying to run all the pytest of sqlalchemy. But I always get the error
> - SQLAlchemy requires Mock as of version 0.8.2. I already have the mock
> library installed. Could somebody please help me to solve the problem.
>

How have you installed these libraries? Are you using virtualenv? If
so, are pytest and mock both installed in the virtualenv?

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] MySQL's sql_mode (ORM)

[Simon King]

On Tue, Feb 6, 2018 at 7:08 PM,   wrote:
> If I were to go into my MySQL DB and
>
> mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES';
>
> would that have the same effect? I find the MySQL documentation somewhat
> lacking on that topic. What are the scope and lifetime of the above vs.
> using a listener as suggested by Michael?
>

I'm pretty sure variables set using "SET GLOBAL" will not survive a
database restart. You'd need to specify it on the server command line
or config file if you wanted it to be persistent.

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] Implementing historical tables

[Simon King]

On Wed, Jan 31, 2018 at 5:27 PM, Stanislav Lobanov  wrote:
> Hello, i need to create a system, that can store historical versions of
> objects.
>
> Example model looks like:
>
> class User(Base):
>id=Column(Integer)  # ForeignKey
>start=Column(DateTime)
>end=Column(DateTime)
>name=Column(String)
>
> I have implemented .add_new() and .save() methods on base class, which
> performs custom logic on inserting rows into database.
>
> .add_new() method just assigns next id, default start and end date values
> (start=datetime.now() and end=None).
>
> Rows where start has value and end is null are considered "live" rows, all
> other rows are considered as "historical"
>
> .save() method changes current record dates (end date becomes current
> moment, so this version is considered as "historical", because it has start
> and end date lifetime moments set). Also, it performs some custom logic on
> determining if new version of record should be created — it should not be
> created, if name is not chaged.
>
> Example:
>
> user = User(name='User1')
> user.add_new()
> # user has values: id=1, start=, end=None, name='User1'
>
> user.name='test2'
> user.save()
> # user has values: id=1, start=, end=,
> name='test2'
>
> user.name='test2'
> user.save()
> # user has values: id=1, start=, end=,
> name='test2' and no insert performed, because name is not changed.
>
>
>
> This methods look for me inconsistent and error prone, so i want to be able
> to use session.add() method.
>
> Are there any possibility to add support for custom process of saving and
> modifying objects by using default sqlalchemy's apis?
>
> P.S.: in current example, table has composite pk (id, start, end), so id is
> not auto incremented when inserting new row. Also, in such scenario we can
> not have ForeignKey constraint.
>

The SQLAlchemy documentation has some examples of a couple of
different ways of doing this:

http://docs.sqlalchemy.org/en/latest/orm/examples.html#versioning-objects

The history_meta.py example creates separate "history" tables for each
table that you want to be versioned. Whenever you UPDATE a versioned
object, a new row gets INSERTed into the history table.

The versioned_rows.py example is more like what you described,
converting what would have been an UPDATE into an INSERT on the same
table.

Both examples use the "before_flush" session event to implement the
versioning, so other parts of your code don't need to be aware of it.

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]

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
<rubendibatti...@gmail.com> 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
>> <rubendi...@gmail.com> 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 ge

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,  <ginger...@gmail.com> 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,  <ging...@gmail.com> 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
<rubendibatti...@gmail.com> 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.

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

[Simon King]

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?

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

On Fri, Jan 12, 2018 at 2:10 AM, Mike Bayer  wrote:
> I can't give you much detail except to say the unique object recipe is
> doing an .add() when it finds an identity that isn't taken, if you
> don't want those persisted then take out the part of the recipe doing
> add().  However, you'd need to alter the recipe further such that if
> the program asks for that same identity again which you didn't want to
> flush to the DB, and you'd like to use the same object, you need to
> pull that from some kind of local dictionary of "pending" objects with
> those identities, if that makes sense.
>
> the second email with the after_attach thing implies you are already
> adding an object to the Session.
>
> Neither of these code examples show example of use, where you are
> doing things that make objects and you'd like them to not be
> persisted.   If you need to create unique objects in memory without
> persisting, you just need to store them in some dictionary that sets
> up the in-memory uniqueness you are looking for.
>
>
>
> On Thu, Jan 11, 2018 at 11:37 AM, Ruben Di Battista
>  wrote:
>> Last copy paste went wrong.
>>
>> The uniqueness is ensured by:
>>
>> @event.listens_for(orm.session.Session, "after_attach")
>> def after_attach(session, instance):
>> # when ConstrainedSatellite objects are attached to a Session,
>> # figure out if in the database there's already the Constraint,
>> # requested, if yes return that object, if not create a new one.
>> # This is an adaptation of the UniqueObject pattern
>> # suggested by SQLAlchemy documentation
>> # https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject
>> if isinstance(instance, UniqueAssociationProxy):
>> instance = instance.ensure_unicity(session)
>>
>>
>>
>>
>> On Thursday, January 4, 2018 at 6:05:38 PM UTC+1, Ruben Di Battista wrote:
>>>
>>> Hello,
>>> I'm writing a satellite passage scheduler that has a database persistence
>>> layer to store the scheduled passages.
>>>
>>> The DB schema is organized as follows:
>>> - A table storing the satellites (using NORAD No as Primary Key)
>>> - A table storing the ground stations where to compute the passages of the
>>> satellites
>>> - A table storing the passages of these satellites, with two foreign keys
>>> linking each passage to a Ground Station and a Satellite
>>> - A table storing all the types of constraints a satellite can have
>>> - A table storing all the types of weights (used to perform the
>>> scheduling) that can be assigned to each satellite
>>>
>>> Than I configured some association proxies (with the related `Unique
>>> Object` pattern) in order to assign the weights and the satellites as a
>>> dictionary
>>>
>>> sat.constraints['min_elevation']= 10
>>>
>>> The details of the relationships here below:
>>>
>>> # Relationship definitions
>>> orm.mapper(Satellite, satellite, properties={
>>> 'passages': orm.relationship(Passage,
>>>  backref='satellite',
>>>  order_by=passage.c.aos,
>>>  cascade='all, delete-orphan'),
>>>
>>>
>>> '_constraints': orm.relationship(
>>> ConstrainedSatellite, backref='satellite',
>>>
>>> collection_class=orm.collections.attribute_mapped_collection('name'),
>>> cascade='all, delete-orphan'),
>>>
>>>
>>> '_weights': orm.relationship(
>>> WeightedSatellite, backref='satellite',
>>>
>>> collection_class=orm.collections.attribute_mapped_collection('name'),
>>> lazy='joined',
>>> cascade='all, delete-orphan'),
>>>
>>>
>>> '_tle': satellite.c.tle
>>>
>>>
>>> })
>>>
>>>
>>> orm.mapper(Constraint, constraint, properties={
>>> 'satellites': orm.relationship(ConstrainedSatellite,
>>> backref='constraint')
>>>
>>>
>>> })
>>>
>>>
>>> orm.mapper(Weight, weight, properties={
>>> 'satellites': orm.relationship(WeightedSatellite, backref='weight')
>>> })
>>>
>>>
>>>
>>>
>>> orm.mapper(ConstrainedSatellite, constraint_satellite)
>>>
>>>
>>> orm.mapper(WeightedSatellite, weight_satellite)
>>>
>>>
>>> orm.mapper(PassageData, passage_data)
>>>
>>>
>>> orm.mapper(Passage, passage, properties={
>>> 

Re: [sqlalchemy] Polymorphic discriminator not being added to update queries

[Simon King]

On Thu, Jan 11, 2018 at 4:51 PM, Mischa S  wrote:
> In [6]: ClassificationTask.query.update(values={'completed': True})
> INFO [sqlalchemy.engine.base.Engine] base.py:679 BEGIN (implicit)
> INFO [sqlalchemy.engine.base.Engine] base.py:1140 UPDATE schwartz_task
> SET completed=%(completed)s
> INFO [sqlalchemy.engine.base.Engine] base.py:1143 {'completed': True}
> Out[6]: 71
>
>
> In [7]: ClassificationTask.query.count()
> INFO [sqlalchemy.engine.base.Engine] base.py:1140 SELECT count(*) AS
> count_1
> FROM (SELECT [...]
> FROM schwartz_task
> WHERE schwartz_task.task_type_id IN (%(task_type_id_1)s)) AS anon_1
> INFO [sqlalchemy.engine.base.Engine] base.py:1143 {'task_type_id_1': 10}
> Out[7]: 14
>
> If I have a polymorphic discriminator, it's automatically added as a
> constraint when doing a SELECT query. When I do an update query, it's not
> automatically added. This is surprising and dangerous. Is this a bug?
>
>
> (semi-related thread:
> https://groups.google.com/forum/#!searchin/sqlalchemy/polymorphic_on$20join$20mischa%7Csort:date/sqlalchemy/U7RkHnM3R2U/332NjxnLAAAJ)
>

This is mentioned in the docs, in the "Additional Caveats for bulk
query updates:

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.update

* The polymorphic identity WHERE criteria is not included for single-
or joined- table updates - this must be added manually, even for
single table inheritance.

So I guess it's not strictly a bug, and there's probably a good reason
why it's not being done. It might be simply that it's a bit
complicated to implement so it hasn't been done yet.

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] Building relatively complex dynamic queries in SQLAlchemy

[Simon King]

On Tue, Jan 2, 2018 at 11:59 PM, Nikola Jankovic  wrote:
> I have an API endpoint that handles searches from the frontend. A search can
> have a dynamic amount of filters applied to it, including (1) sizes, (2)
> colors, (3) price, and (4) category that are passed through query
> parameters. Sizes and colors are passed as comma separated values (i.e.
> colors=Red,Blue,Orange) and are converted to lists on the backend. Price and
> Category are single values (i.e. price=20-300=Shirt).
>
> I'm having trouble constructing a query based on these filters. If multiple
> values could not be passed for any parameters I wouldn't have much of an
> issue - I could construct a dictionary based on the parameter name and
> unpack it in a .filter() call. That's not the case though - I need to
> implement an 'or' operator to accomodate the multiple possible colors and
> sizes.
>
> Additionally, each of these filters is optional, meaning some queries will
> comprise only some of them, which again complicates the query.
>
> And if that wasn't complicated enough - properties like sizes and colors are
> represented as a one to many relationship not simple categories. I have
> hybrid properties that return a list of possible sizes but using those in
> queries containing filter_by() raises issues.
>
> Does anyone know how I could even begin to structure a dynamic query like
> this?

Just build it up one step at a time, something like this:

q = session.query(YourObject)

if price is not None:
q = q.filter(YourObject.price == price)

if category is not None:
q = q.filter(YourObject.category == category)

if sizes:
q = q.join(Size).filter(Size.size.in_(sizes))

if colors:
q = q.join(Color).filter(Color.name.in_(colors))

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] Help writing query - "where most recent is true, but any other is false"

[Simon King]

On Wed, Dec 13, 2017 at 10:24 PM, Leo Simmons  wrote:
> I have a table `special_product_list` and a table
> `special_product_historical_details_list` that holds information of products
> over time, where there's a record in the history table every day for every
> product.
>
> I want to write a query to select all products from `special_product_list`
> that meet the two following conditions:
> - the product's most recent `special_product_historical_details_list`
> entry has `available=true`
> - at least one other `special_product_historical_details_list` entry for
> that product except the most recent one has `available=false`
>
> if the following were the most recent
> `special_product_historical_details_list` entries for a product, only in the
> first situation would I want that product to be returned from the query
>
> p_id | date   | is_available
> 
> 1   | 2017-12-13 | true
> 1   | 2017-12-12 | false
> 1   | 2017-12-11 | true
> 1   | 2017-12-10 | true
>
> p_id | date  | is_available
>  ---
>  1   |2017-12-13 | false
>  1   |2017-12-12 | true
>  1   |2017-12-11 | false
>  1   |2017-12-10 | false
>
> p_id | date  | is_available
>  ---
>  1   |2017-12-13 | true
>  1   |2017-12-12 | true
>  1   |2017-12-11 | true
>  1   |2017-12-10 | true
>

I would approach this by figuring out the SQL first, and then
translate to SQLAlchemy afterwards.

To match your first condition (most recent row has 'available=true'),
you need a way to find the most recent row for each p_id. Here's one
way (all untested):

SELECT p_id, MAX(date) AS max_date
FROM special_product_historical_details_list
GROUP BY p_id

To find rows where the most recent entry has 'available=true', you
would combine the above subquery with a join back to the
special_product_historical_details_list table again, something like:

SELECT *
FROM special_product_historical_details_list sphdl
INNER JOIN (
SELECT p_id, MAX(date) AS max_date
FROM special_product_historical_details_list
GROUP BY p_id
) lastrows ON (lastrows.p_id = sphdl.p_id AND lastrows.max_date = sphdl.date)
WHERE sphdl.is_available = 'true'

(This assumes that each product has unique date values - you'll get
confusing results if a product ever has more than one row with the
same date)

For your second condition, you want to know if a product has any
"false" entries. (There's no need to include the "except for the most
recent entry" part, because you're going to combine this condition
with the previous one, which already asserts that the most recent
entry is "true"). There are a few ways of writing this (eg. using
EXISTS or ANY with a correlated subquery) but I'm not sure of the
performance characteristics. Here's one way:

SELECT DISTINCT p_id
FROM special_product_historical_details_list
WHERE available = 'false'

The combined query would look something like:

SELECT *
FROM special_product_historical_details_list sphdl
INNER JOIN (
SELECT p_id, MAX(date) AS max_date
FROM special_product_historical_details_list
GROUP BY p_id
) lastrows ON (lastrows.p_id = sphdl.p_id AND lastrows.max_date = sphdl.date)
INNER JOIN (
SELECT DISTINCT p_id
FROM special_product_historical_details_list
WHERE available = 'false'
) havefalse ON sphdl.p_id = havefalse.p_id
WHERE sphdl.is_available = 'true'

> Is there a way I could write this in SqlAlchemy?

You ought to be able to build up the above SQL incrementally using the
Query.subquery() method. See the example at:

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-subqueries

If you wanted to use the EXISTS version of the second condition, you
would need something like:

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-exists

Hope that helps,

Simon

>
> Here are my models:
>
> class SpecialProduct(Product):
> __tablename__ =  'special_product_list'
> special_id = db.Column(String(64), nullable=False, index=True,
> primary_key=True)
> history = relationship("SpecialProductHistoricalDetails",
> back_populates='special_product',
>
> foreign_keys='SpecialProductHistoricalDetails.special_id')
> most_recent_historical_details_id = db.Column(Integer,
>
> ForeignKey('special_product_historical_details_list.id'))
> most_recent_historical_details_entry =
> relationship("SpecialProductHistoricalDetails",
> uselist=False,
> foreign_keys=[most_recent_historical_details_id])
>
> class SpecialProductReviewsHistoricalDetails(db.Model):
> __tablename__ = 'special_product_historical_details_list'
> id = db.Column(Integer, primary_key=True)
> special_id = db.Column(String(64),
> ForeignKey('special_product_list.special_id'), nullable=False)
> special_product = relationship("SpecialProduct", uselist=False,
> foreign_keys=[special_id])
> time_updated = db.Column(TIMESTAMP, default=func.now(),
> 

Re: [sqlalchemy] How to make polymorphic identity condition involving a join not be a subselect

[Simon King]

On Sun, Nov 26, 2017 at 11:19 AM, Mischa S  wrote:
> I want to have a polymorphic table that gets its identity from a column in a
> "type" table. I have a "task" table and a "task_type" table. I want to be
> able to create task subclasses that say something like:
>
> __mapper_args__ = {
> 'polymorphic_identity': 'do_stuff_task',
> }
>
> (Assuming that a row with do_stuff_task is created in the task_type table)
>
>
> Currently in my base Task class I do:
>
> task_type_name =
> column_property(select([TaskType.task_type]).where(TaskType.id ==
> task_type_id).as_scalar())
>
> __mapper_args__ = {
> 'polymorphic_on': task_type_name,
> }
>
>
> If I try to get say, a user.do_stuff_tasks relationship it emits the
> following SQL:
>
> SELECT
>   (SELECT task_type.task_type
>FROM task_type
>WHERE task_type.id = task.task_type_id)
> FROM task
> WHERE 123 = task.user_id
>   AND
> (SELECT task_type.task_type
>  FROM task_type
>  WHERE task_type.id = task.task_type_id) IN ('do_stuff_task');
>
>
> This is technically correct, however this query is highly problematic for
> us. It does a scan on task in the subquery, which is extremely unpleasant
> and basically takes forever.
> What I want is something more like:
>
> SELECT *
> FROM task st
> JOIN task_type stt ON st.task_type_id=stt.id
> WHERE stt.task_type='do_stuff_task'
>   AND st.user_id=123;
>
> Is there some way to rewrite the column_property or polymorphic identity
> condition to combine the WHERE conditions in the JOIN instead of doing two
> subselects?
>
>
> Thanks!

Is it strictly necessary for your Task class to be polymorphic? Could
you make TaskType polymorphic instead, and then delegate from Task to
TaskType? Something like this:

class Task(Base):
def execute(self):
self.task_type.execute(self)

class TaskType(Base):
name = sa.Column(sa.String(16))
__mapper_args__ = {
'polymorphic_on': name,
}
def execute(self, task):
raise NotImplemented

class DoStuffTaskType(TaskType):
__mapper_args__ = {
'polymorphic_on': 'do_stuff_task',
}
def execute(self, task):
# do stuff

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] Disable `session_identity_map` for `_instance_processor`

[Simon King]

On Thu, Nov 23, 2017 at 6:55 PM, Антонио Антуан  wrote:
>
>
> чт, 23 нояб. 2017 г. в 20:27, Mike Bayer :
>>
>> On Thu, Nov 23, 2017 at 8:44 AM, Антонио Антуан 
>> 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.

A couple of things here. First, you are using ScopedSession, which is
essentially a wrapper around an actual Session. The commit(),
rollback() and expunge_all() methods are proxies that pass directly
through to the underlying Session. I believe commit() and rollback()
*expire* instances (so attributes will be reloaded on the next
access), but don't actually remove them from the identity map (but I
could be wrong about this).

remove() is not a Session method though - it tells the ScopedSession
to discard the current Session. A new Session will be created the next
time you call one of the proxied methods.

The default behaviour for ScopedSession is to use thread-locals, so
each thread gets its own Session. However, you can provide your own
scoping function that does whatever you want:

http://docs.sqlalchemy.org/en/latest/orm/contextual.html#using-custom-created-scopes

It sounds like you could pass a "get_current_tenant" function to the
scoped session, along with a custom session_factory, to get the
behaviour you want. Something like this (untested, would definitely
require more care to make it thread-safe and so on):

class SessionManager(object):
def __init__(self, tenant_uris):
self.engines = {}
self.current_tenant = None
for name, dburi in tenant_uris.items():
self.engines[name] = sa.create_engine(name)
self.sessionmaker = saorm.sessionmaker()

def get_current_tenant(self):
return self.current_tenant

def set_current_tenant(self, name):
self.current_tenant = name

def create_session(self):
engine = self.engines[self.current_tenant]
return self.sessionmaker(bind=engine)

tenant_uris = {
'one': 'mysql://...',
'two': 'mysql://...',
}
manager = SessionManager(tenant_uris)

Session = saorm.scoped_session(manager.create_session,
scopefunc=manager.get_current_tenant)

Base.query = Session.query_property


As long as you call manager.set_current_tenant whenever you switch to
querying a different tenant, this ought to work. But note that all of
this confusion and complexity stems from using scoped sessions and
Base.query. If you used explicit sessions everywhere, you would
probably find your code less magical and easier to understand.

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] Disable `session_identity_map` for `_instance_processor`

[Simon King]

On Thu, Nov 23, 2017 at 5:27 PM, Mike Bayer  wrote:
> On Thu, Nov 23, 2017 at 8:44 AM, Антонио Антуан  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.
>

OP, can you describe in more detail why these applications need to
talk to multiple tenant databases in a single session? Perhaps there
might be an alternative way to approach that.

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: Required properties of first arg to bulk_insert_mappings

[Simon King]

I'm pretty sure that bulk_insert_mappings ends up just calling the
same code that I suggested.

What database are you using? If it's Postgres, you might be interested
in 
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#psycopg2-batch-mode
(linked from 
http://docs.sqlalchemy.org/en/latest/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow)

If that still isn't fast enough, I guess you'll need to prepare a data
file and then use the appropriate DB-specific mechanism to load it. I
don't think SQLAlchemy has any specific tools for that.

Simon

On Tue, Nov 21, 2017 at 12:15 PM, Skip Montanaro
 wrote:
> Thanks. I guess I'm still a bit confused. The problem I've been trying
> to solve happens to involve inserting records into a table. In my real
> application, the list of records can contain millions of dicts. The
> name, "bulk_insert_mappings" sort of sounds like it's going to use
> BULK INSERT types of statements under the covers (though I realize
> there's certainly no guarantee of that, and I may well be reading more
> into the name than I should).
>
> Like most database applications, this is got some updating, but most
> database operations involve working with data already in the database.
> Is it reasonable to adopt an ORM stance w.r.t. most of the application
> code, then throw it over for more straightforward Core constructs when
> data needs to be (in this case, bulk) updated? Or is it expected that
> any given application should live at one level or the other?
>
> Skip
>
> --
> 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: Required properties of first arg to bulk_insert_mappings

[Simon King]

(TLDR: I think bulk_insert_mappings is the wrong function for you to use)

SQLAlchemy consists of 2 main layers. The Core layer deals with SQL
construction, database dialects, connection pooling and so on. The ORM
is built on top of Core, and is intended for people who want to work
with "mapped classes" (such as your User class). The ORM takes
(typically) a Table instance (like your "mytable" object) and connects
a more traditional-looking Python class to it, so that rather than
explicitly inserting, updating and deleting rows in a table, you
create instances of the mapped class and modify its attributes. Older
versions of SQLAlchemy used to require you to declare the tables and
mapped classes separately
(http://docs.sqlalchemy.org/en/latest/orm/mapping_styles.html#classical-mappings),
but the declarative_base style is much more convenient for people who
are mostly going to be using the ORM.

bulk_insert_mappings is part of the ORM layer, so it assumes you are
working with mapped classes. If you just want to insert dictionaries
into a table, you don't need the ORM at all. Something like this ought
to work (using the definitions from your first message):

mytable = metadata.tables['mytable']
session.execute(mytable.insert(), records)

http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements

(Note that the Core docs tend to use engines and connections rather
than sessions, because Session is part of the ORM, but
Session.execute() accepts any Core construct)

Hope that helps,

Simon


On Mon, Nov 20, 2017 at 9:16 PM, Skip Montanaro
 wrote:
> I've narrowed down my problem space a bit. Consider this simple code:
>
> from sqlalchemy import (Integer, String, Column, MetaData, create_engine)
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
>
> METADATA = MetaData()
> BASE = declarative_base(metadata=METADATA)
> SESSION = sessionmaker()
>
> class User(BASE):
> __tablename__ = "user"
> first_name = Column(String(32))
> last_name = Column(String(32))
> id = Column(Integer, primary_key=True)
>
> print(type(User))
> print(type(METADATA.tables['user']))
>
> When run, I get this output:
>
> 
> 
>
> The User class is suitable to use as the first arg to
> session.bulk_insert_mapping(), but the object plucked from the METADATA
> tables dict is not. Will I have to always carry around my own references to
> the various subclasses of BASE which I defined to describe my schema? If I
> have metadata and session objects, is there a way to get back that usable
> (or a usable) class?
>
> --
> 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] Confusion over session.dirty, query, and flush

[Simon King]

Can you explain why you actually want to do this? There might be
better options than before_flush, but we'd need to know exactly what
you're trying to do first.

Simon

On Thu, Nov 16, 2017 at 12:55 PM,  <jens.troe...@gmail.com> wrote:
> That makes sense, thank you, Simon!
>
> Regarding the events: you suggest to use a before_flush() to examine
> session.dirty whenever a session.query() executes?
>
> Also, is there a way to get the list of objects that have been flushed, or
> should I track them myself whenever a before_flush() event occurs?
>
> Jens
>
>
> On Thursday, November 16, 2017 at 7:49:54 PM UTC+10, Simon King wrote:
>>
>> On Thu, Nov 16, 2017 at 7:45 AM,  <jens.t...@gmail.com> wrote:
>> > Hello,
>> >
>> > I've been exploring some of the session functionality that handles
>> > object
>> > states, and I'm quite confused. Here is what I see:
>> >
>> >>>> engine = engine_from_config({'sqlalchemy.url': 'mysql+pymysql://…'})
>> >>>> session_factory = sessionmaker(bind=engine) # No autoflush
>> >>>> session = session_factory()
>> >>>> # Now query a table to get an object.
>> >>>> p = session.query(Table).filter(Table.id == '0f4…ed6').one_or_none()
>> >>>> p.name
>> > "Some Name"
>> >>>> p.name = "Other Name"
>> >>>> session.dirty
>> > IdentitySet([])
>> >>>> session.is_modified(p)
>> > True
>> >>>> session._is_clean()
>> > False
>> >
>> > This all makes sense. If I now create a new query, then the above change
>> > seems to be gone, but isn't?
>> >
>> >>>> p2 = session.query(Table).filter(Table.id ==
>> >>>> '384…a05c').one_or_none()
>> >>>> session.dirty
>> > IdentitySet([])
>> >>>> session.is_modified(p)
>> > False # p is not modified according to this.
>> >>>> session._is_clean()
>> > True
>> >>>> p.name
>> > "Other Name" # p still has the modified name.
>> >
>> > The new query seems to set the session to "clean", but the object p
>> > still
>> > contains its change. I can't quite find documentation for the behavior.
>> > What
>> > am I missing?
>> >
>> > What I would like to do is: in one session, select a few objects
>> > (multiple
>> > queries), inspect, perhaps modify them. Then I'd like to query if there
>> > were
>> > any modifications/deletes and if so choose to commit or rollback.
>> > Initially
>> > I thought to use the Session.dirty/deleted/new properties, but then the
>> > above showed.
>> >
>> > If I was to set autoflush, how could I inspect the flushed code buffer?
>> >
>> > Thanks!
>> > Jens
>>
>> The results you are seeing are due to autoflush, which is on by
>> default. When you run your second query, the session flushes any
>> pending changes to the database before running the query. After the
>> flush, the session is considered clean. The methods you were using
>> (dirty, is_modified etc.) indicate whether the session contains
>> changes that haven't been flushed to the database. They don't tell you
>> if the database has received changes that haven't yet been committed.
>>
>> There are various ways to do what you want, depending on how your code
>> is structured. One possibility is to use a Session event such as
>> before_flush to set a flag saying that there are uncomitted changes in
>> the database.
>>
>> http://docs.sqlalchemy.org/en/latest/orm/session_events.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.

-- 
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] Confusion over session.dirty, query, and flush

[Simon King]

On Thu, Nov 16, 2017 at 7:45 AM,   wrote:
> Hello,
>
> I've been exploring some of the session functionality that handles object
> states, and I'm quite confused. Here is what I see:
>
 engine = engine_from_config({'sqlalchemy.url': 'mysql+pymysql://…'})
 session_factory = sessionmaker(bind=engine) # No autoflush
 session = session_factory()
 # Now query a table to get an object.
 p = session.query(Table).filter(Table.id == '0f4…ed6').one_or_none()
 p.name
> "Some Name"
 p.name = "Other Name"
 session.dirty
> IdentitySet([])
 session.is_modified(p)
> True
 session._is_clean()
> False
>
> This all makes sense. If I now create a new query, then the above change
> seems to be gone, but isn't?
>
 p2 = session.query(Table).filter(Table.id == '384…a05c').one_or_none()
 session.dirty
> IdentitySet([])
 session.is_modified(p)
> False # p is not modified according to this.
 session._is_clean()
> True
 p.name
> "Other Name" # p still has the modified name.
>
> The new query seems to set the session to "clean", but the object p still
> contains its change. I can't quite find documentation for the behavior. What
> am I missing?
>
> What I would like to do is: in one session, select a few objects (multiple
> queries), inspect, perhaps modify them. Then I'd like to query if there were
> any modifications/deletes and if so choose to commit or rollback. Initially
> I thought to use the Session.dirty/deleted/new properties, but then the
> above showed.
>
> If I was to set autoflush, how could I inspect the flushed code buffer?
>
> Thanks!
> Jens

The results you are seeing are due to autoflush, which is on by
default. When you run your second query, the session flushes any
pending changes to the database before running the query. After the
flush, the session is considered clean. The methods you were using
(dirty, is_modified etc.) indicate whether the session contains
changes that haven't been flushed to the database. They don't tell you
if the database has received changes that haven't yet been committed.

There are various ways to do what you want, depending on how your code
is structured. One possibility is to use a Session event such as
before_flush to set a flag saying that there are uncomitted changes in
the database.

http://docs.sqlalchemy.org/en/latest/orm/session_events.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] Two classes linked to the same backref attribute in a third classe

[Simon King]

On Wed, Nov 15, 2017 at 12:10 AM, Mike Bayer  wrote:
> On Tue, Nov 14, 2017 at 2:58 PM, Olaf  wrote:
>> Hello everybody.
>>
>> Is it possible to have the following relationships between three classes
>> (Animal, Car, Description) ?
>>
>> * An animal has an attribute description
>> * A car has an attribute description
>> * Each description contains his parent as an attribut. So parent can be a
>> car or an animal
>>
>> Here is my code :
>>
>> from sqlalchemy import create_engine
>> from sqlalchemy.orm import sessionmaker
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy import Column, Integer, String, ForeignKey
>> from sqlalchemy.orm import relationship
>>
>>
>> Base = declarative_base()
>>
>>
>> class Description(Base):
>> __tablename__ = "descriptions"
>> id = Column(Integer, primary_key=True)
>> name = Column(String)
>>
>>
>> def __init__(self, name):
>> self.name = name
>> self.parent = None
>>
>>
>> def __repr__(self):
>> return self.name
>>
>>
>> class Animal(Base):
>> __tablename__ = "animals"
>> id = Column(Integer, primary_key=True)
>> name = Column(String)
>> id_description = Column(Integer, ForeignKey("descriptions.id"), nullable
>> = False)
>> description = relationship("Description", backref = "parent")
>>
>>
>> def __init__(self, name):
>>  self.name = name
>>
>>
>> def __repr__(self):
>> return self.name
>>
>>
>> class Car(Base):
>> __tablename__ = "cars"
>> id = Column(Integer, primary_key=True)
>> name = Column(String)
>> id_description = Column(Integer, ForeignKey("descriptions.id"), nullable
>> = False)
>> description = relationship("Description", backref = "parent")
>>
>>
>> def __init__(self, name):
>> self.name = name
>>
>>
>> def __repr__(self):
>> return self.name
>>
>>
>>
>>
>> engine = create_engine('YOUR DATABASE', echo = True)
>>
>>
>> Base.metadata.create_all(engine)
>>
>> Session = sessionmaker(bind=engine)
>> session = Session()
>>
>> rex = Animal("Rex")
>> swift = Car("Suzuki Swift")
>>
>> d1 = Description("Rex is a good dog.")
>> d2 = Description("What a beautiful car !")
>>
>> rex.description = d1
>> d1.parent = rex
>> swift.description = d2
>> d2.parent = swift
>>
>> session.add(rex)
>> session.add(swift)
>> session.add(d1)
>> session.add(d2)
>>
>> session.commit()
>>
>>
>>
>> Error :
>>
>> Traceback (most recent call last):
>>   File "C:\Users\user\Desktop\test.py", line 56, in 
>> rex = Animal("Rex")
>>   File "", line 2, in __init__
>>   File "C:\Python34\lib\site-packages\sqlalchemy\orm\instrumentation.py",
>> line 347, in _new_state_if_none
>> state = self._state_constructor(instance, self)
>>   File "C:\Python34\lib\site-packages\sqlalchemy\util\langhelpers.py", line
>> 767, in __get__
>> obj.__dict__[self.__name__] = result = self.fget(obj)
>>   File "C:\Python34\lib\site-packages\sqlalchemy\orm\instrumentation.py",
>> line 177, in _state_constructor
>> self.dispatch.first_init(self, self.class_)
>>   File "C:\Python34\lib\site-packages\sqlalchemy\event\attr.py", line 256,
>> in __call__
>> fn(*args, **kw)
>>   File "C:\Python34\lib\site-packages\sqlalchemy\orm\mapper.py", line 3129,
>> in _event_on_first_init
>> configure_mappers()
>>   File "C:\Python34\lib\site-packages\sqlalchemy\orm\mapper.py", line 3019,
>> in configure_mappers
>> mapper._post_configure_properties()
>>   File "C:\Python34\lib\site-packages\sqlalchemy\orm\mapper.py", line 1810,
>> in _post_configure_properties
>> prop.init()
>>   File "C:\Python34\lib\site-packages\sqlalchemy\orm\interfaces.py", line
>> 184, in init
>> self.do_init()
>>   File "C:\Python34\lib\site-packages\sqlalchemy\orm\relationships.py", line
>> 1661, in do_init
>> self._generate_backref()
>>   File "C:\Python34\lib\site-packages\sqlalchemy\orm\relationships.py", line
>> 1851, in _generate_backref
>> (backref_key, self, m))
>> sqlalchemy.exc.ArgumentError: Error creating backref 'parent' on
>> relationship 'Animal.description': property of that name exists on mapper
>> 'Mapper|Description|descriptions'
>>
>>
>> It seems impossible to link the classes to the same attribute "parent" in
>> the class "Description". Is there a solution ?
>
>
> you're attempting a pattern we call "generic association" (I've also
> called it "polymorphic association") - a single destination item that
> can be referred to by many types of objects.   I've documented three
> ways of doing these, noting that one of them is a common pattern from
> the Django / Rails world, though I don't recommend that one.   Unless
> you need to query for descriptions across Dogs/Cars at the same time,
> I'd go with table_per_related.   The mental exercise here is being OK
> with the fact that there are more tables than you thought there would
> be.It "feels" wrong to have multiple tables with the same columns
> but 

Re: [sqlalchemy] SQLAlchemy leaves MySQL open connections

[Simon King]

On Mon, Nov 13, 2017 at 4:35 PM, Ahmad Javed  wrote:
> Thank you Simon. Appreciate your response. Just using session.remove() at
> the end of each request is not closing the connection and leave open
> connections in MySQL database. One more thing I want to tell that we have
> deployed Django applications with uwsgi and nginx.

Well, this is deliberate - closing the session returns the connection
to a pool, so that it can be used by a subsequent request. If it
didn't do this, it would have to create a new database connection for
every web request, which would be inefficient for a busy application.
You can read about SQLAlchemy's options for configuring the behaviour
of the connection pool at:

http://docs.sqlalchemy.org/en/latest/core/pooling.html

You should ensure that your pool_size and max_overflow options are set
appropriately. The pool should ensure that there are never more than
(pool_size + max_overflow) connections open to the database at once
per process.

If you are seeing more open connections than this, then something is
going wrong, and you'd need to start digging to find out why
connections aren't being returned to the pool.

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] SQLAlchemy leaves MySQL open connections

[Simon King]

On Mon, Nov 13, 2017 at 3:58 AM, Ahmad Javed  wrote:
> Hi,
>
> We have implemented web applications using Django and SQLAlchemy framework.
> We have distributed system in the company and some API used frequently.
>
> We are facing a problem where MySQL connections reached a maximum limit.
>
> In Django applications, we are using SQLAlchemy with pool engine and
> scoped_session. Also using pool recycle option as well.
>
> There is custom Django middleware placed in application to remove the
> session at the end and dispose the engine as well.
>
> But still, it leaves MySQL open connections.
>
> Today I have reproduced and investigated problem locally and dig into
> library code. I found that instead of disposing engine if I dispose engine's
> pool then it actually remove connections.
>
> I was expecting that disposing engine only will take care of connections.
>
> I need some guidance from experts. Is this approach good? Should I go with
> this solution? Or is there better solution that can help me?
>
> P.S, we are using 0.7.8 version of SQLAlchemy. Unfortunately due to some
> issues I can not update the version.
>
> Thanks in advance,
> Ahmad Javed
>

It sounds like you are recreating the engine for each request, which
is not the way it is supposed to be used. You should only have a
single engine instance, typically created or configured when your
application starts up. Disposing the engine (or the pool) should never
be necessary.

If all your per-request DB access goes through the scoped session, and
you are calling session.remove() at the end of each request,
connections should be returned to the pool properly.

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] Excluding attributes from polymorphic hierarchy

[Simon King]

On Fri, Oct 27, 2017 at 4:57 PM,   wrote:
> My SQLAlchemy version is 1.1.14.
>
> I'm having difficulty understanding behaviours around a polymorphic
> hierarchy.
>
> I have an AbstractConcreteBase and two subclasses.
>
> The ACB declares some attributes shared by the two subclasses, and the
> subclasses have attributes of their own that are not shared(i.e. they are
> not part of the shared identity represented by the ACB).
>
> class MyBase(AbstractConcreteBase, Base, Mixin):
> shared_id = Column(Integer, default=Sequence("database_shared_id_seq",
> start=0, increment=1), primary_key=True)
>
> @declared_attr
> def name(self):
> return Column(String(100), nullable=False)
>
> @declared_attr
> def foreign_key(self):
> return Column(Integer, ForeignKey("MyOtherTable.id"))
>
> ...
>
>
> class Concrete1(MyBase):
> start = Column(Date)
> end = Column(Date)
>
>
> class Concrete2(MyBase):
>
> @property
> def start(self):
> return self.details.start
>
> @property
> def end(self):
> return self.details.end
>
> details = relationship("Concrete2Detail", uselist=False, viewonly=True)
>
>
> As you can see, "start" and "end" are not declared in the ACB and are
> implemented differently between the table. I don't want them to be shared by
> the subclasses.
>
> When I query one of the Concretes, the ACB mapper is updated with the
> "start" and "end" attributes, and then the Concretes are expected to
> implement the attributes on the mapper level(which is not the case for
> Concrete2).
>
> How can I prevent this from happening?
>

Can you provide a full script that demonstrates the problem?

Thanks,

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

[Simon King]

This is complete guesswork - I haven't examined this code in detail so
I could be wrong. Also, I don't know what version of SQLAlchemy you
are using, so this may not apply.

Session.connection takes separate "mapper" and "clause" parameters:

https://bitbucket.org/zzzeek/sqlalchemy/src/f34b180ca9059a74c3bf1db1b79e187c3f4b81c9/lib/sqlalchemy/orm/session.py#session.py-958

Query._execute_and_instances passes both of those parameters:

https://bitbucket.org/zzzeek/sqlalchemy/src/f34b180ca9059a74c3bf1db1b79e187c3f4b81c9/lib/sqlalchemy/orm/query.py#query.py-2893

(via the _get_bind_args helper function)

Your overridden version of _execute_and_instances is passing a clause
in the "mapper" parameter, which seems wrong. Somehow you need to fix
that, perhaps by using the _get_bind_args helper.

Simon



On Wed, Oct 25, 2017 at 1:02 PM, Антонио Антуан <a.ch@gmail.com> wrote:
> 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.

Re: [sqlalchemy] mapper existance checks possibly wrong

[Simon King]

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 октября 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`:
>>>> >>> >
>>&g

Re: [sqlalchemy] mapper existance checks possibly wrong

[Simon King]

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.

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 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/pytho

Re: [sqlalchemy] mapper existance checks possibly wrong

[Simon King]

On Fri, Oct 20, 2017 at 2:15 PM, Антонио Антуан  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/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?

Are you able to catch this error in a debugger?

I'm going to make a wild guess that "mapper" at this point is some
sort of SQL construct (such as a column object or mapped property),
rather than an actual mapper.

You don't say which version of SQLAlchemy you are using, but since
you've overridden _execute_and_instances, I wonder if you need to use
something like query._bind_mapper() as used by _get_bind_args:



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 

Re: [sqlalchemy] SQLAlchemy enum in external file

[Simon King]

On Thu, Oct 19, 2017 at 9:10 PM, TazMainiac  wrote:
>
>
> On Thu, Oct 19, 2017 at 2:25 PM, Jonathan Vanasco 
> wrote:
>>
>> On Thursday, October 19, 2017 at 11:55:49 AM UTC-4, Taz Mainiac wrote:
>>>
>>> So - a morning spent googling does not turn up any information about
>>> Python classes having a different id depending on context (script vs
>>> module).  I'm probably not googling for the right thing?  Can anyone point
>>> me to information?
>>>
>>
>> The Python classes have a different id on each run not because of the
>> context (script vs module), but because they are run in different processes.
>> The "id" , like the object, is only local to the process.
>>
>> The attributes of your custom enum class are instances of enum objects.
>> which have attributes like name and value.
>
>
> I understand this.  The id is the address of the object under CPython (so it
> says in the docs).
>
>> If you ran two scripts or two modules, it would also almost always fail
>> (IIRC, it is remotely possible but highly unlikely, to generate the same id
>> in another process).
>
>
> I am not doing that.
>
>>
>> You should be storing/comparing the enum's 'name' or 'value' attributes --
>> not the enum object itself.  The enum object will always be different.
>
>
> I'm following the established documentation on how to use an enum with
> SQLAlchemy (with Postgres DB backend) - almost exactly as described in the
> doc under the Enum type:
>
>   http://docs.sqlalchemy.org/en/rel_1_1/core/type_basics.html
>
> (except I'm using declarative style).
>
> The problem is apparently that the enumerated type gets a different id
> depending on the context in which it is used (from a script vs from a module
> import).  This is very surprising to me, and I have not seen anything like
> it before (hence my question above asking for any references to this
> behavior).
>
> In any case, this causes SQL Alchemy to not recognize that the enumerated
> value assigned to a field (in another file) is not the same type as what is
> expected.  Clearly the ' is ' relationship is failing (since the id's are
> different).
>
> So - need to reorganize code a bit to avoid this...
>
> Taz

This is a subtle python gotcha based on how the import system works.
It doesn't have anything to do with SQLAlchemy.



When you import a module, it gets cached in sys.modules under its
"fully qualified module name", so it doesn't matter how many times you
run "import foo", the module is only loaded once.

However, the script that you initially *execute* gets the special name
"__main__". If it gets subsequently imported via an "import"
statement, it is not found in the cache, so it is executed again,
meaning you get duplicate definitions of everything in the module.

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] Postgres database: open and close connection

[Simon King]

On Fri, Oct 6, 2017 at 9:59 AM, Martin Cigorraga
 wrote:
> Hi,
>
> I'm trying to create a simple function to check the reachability of a
> Postgres database using the http method GET; if the connection can't be
> established, it should respond with a custom http error number.
>
> This is what I got so far: https://pastebin.com/rh3t6nYN
>
> Being fairly new to Python does not help, for sure, but after trying
> everything I can think of for the last hours I'm stagnant.
> Can anybody give me a hint on which direction I should go? I feel this has
> to be a basic stuff but at the moment I just can't wrap my head over it.
>

See this note on http://docs.sqlalchemy.org/en/latest/core/engines.html:

"""
Note that the Engine and its underlying Pool do not establish the
first actual DBAPI connection until the Engine.connect() method is
called, or an operation which is dependent on this method such as
Engine.execute() is invoked.
"""

Your code probably needs to do something like this:

engine = sqlalchemy.create_engine(url, client_encoding='utf8')
connection = engine.connect()

"engine.connect()" will raise an exception if it fails to connect.

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] sqlalchemy foreignkey relation with inheritance

[Simon King]

On Fri, Sep 22, 2017 at 9:42 AM, Simon King <si...@simonking.org.uk> wrote:
> On Fri, Sep 22, 2017 at 8:18 AM, John Smith <johnsmith31...@gmail.com> wrote:
>> I have the following database schema:
>>
>> Table "Parent":
>> 1. id - primary key identifier.
>> 2. type - polymorphic_identity.
>> 3. name - string data column.
>>
>> Table "Child"  - inherits Parent:
>> 1. id - primary key identifier.
>> 2. parent_id - foreignkey to Parent.
>> 3. category - string data column.
>>
>> Summing up I have two tables. Table Child inherits from Parent and also have
>> a foreignkey to it.
>> I really need both inheritance and foreignkey. This example is only a short
>> demo which reproduces the problem.
>> My real database has 1000+ tables with complex inheritance.
>>
>> I used declarative_base to declare the schema:
>>
>>
>>> # -*- coding: utf-8 -*-
>>>
>>> from sqlalchemy import Column, String, Integer, ForeignKey
>>> from sqlalchemy.orm import relationship
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy import create_engine
>>> from sqlalchemy.orm import sessionmaker
>>>
>>> Base = declarative_base()
>>>
>>> class Parent(Base):
>>>   __tablename__ = "Parent"
>>>   id = Column(Integer, primary_key=True)
>>>   type = Column(String(250))
>>>
>>>   name = Column(String(250))
>>>
>>>   __mapper_args__ = {
>>> 'polymorphic_identity':'Parent',
>>> 'polymorphic_on':type
>>>   }
>>>
>>> class Child(Parent):
>>>   __tablename__ = 'Child'
>>>   id = Column(Integer, ForeignKey('Parent.id'), primary_key=True)
>>>
>>>   parent_id = Column(ForeignKey("Parent.id"), nullable=True)
>>>   category = Column(String(250))
>>>
>>>   __mapper_args__ = {
>>> 'polymorphic_identity':'Child',
>>>   }
>>>
>>> engine = create_engine('postgresql+psycopg2://joe:joe@localhost/alch')
>>>
>>> session = sessionmaker()
>>> session.configure(bind=engine)
>>> Base.metadata.create_all(engine)
>>
>>
>> But when I run the code I get the following error:
>>
>>
>>> sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
>>> 'Parent' and 'Child'; tables have more than one foreign key constraint
>>> relationship between them. Please specify the 'onclause' of this join
>>> explicitly.
>>
>>
>> I have tried to set relationship attribute for Parent or for Child
>> separately and for both too. Tried to use primaryjoin and foreign_keys
>> parameters of relationship. But the error was the same.
>>
>> I'm totally confused about this error.
>> Please help. Thanks.
>>
>
> The docs at http://docs.sqlalchemy.org/en/latest/orm/inheritance.html say:
>
> It is most common that the foreign key constraint
> is established on the same column or columns
> as the primary key itself, however this is not
> required; a column distinct from the primary key
> may also be made to refer to the parent via foreign
> key. The way that a JOIN is constructed from the
> base table to subclasses is also directly
> customizable, however this is rarely necessary.
>
> The last sentence is the important one. Normally there is only a
> single foreign key from the child to the parent table, so SA can
> automatically figure out the join condition. In your case, you've got
> 2 foreign keys, so SA is asking you to be explicit. However, it's the
> *inheritance* join that is causing the problem, and the main docs
> don't describe how to customize that.
>
> However, if you look at the docs for the underlying mapper() function,
> you'll find an "inherit_condition" parameter:
>
> http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherit_condition
>
> Unfortunately it doesn't seem to accept strings, so you need to
> construct your code such that you have direct access to the classes
> themselves.
>
> Here's a working version of your script:
>
> # -*- coding: utf-8 -*-
>
> from sqlalchemy import Column, String, Integer, ForeignKey
> from sqlalchemy.orm import relationship
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import create_engine
> from sqlalchemy.orm import sessionmaker
>
> Base = declarative_base()
>
> class Parent(Base):
>   __tablename__ = "Parent"
>   id = Column(Int

Re: [sqlalchemy] sqlalchemy foreignkey relation with inheritance

[Simon King]

On Fri, Sep 22, 2017 at 8:18 AM, John Smith  wrote:
> I have the following database schema:
>
> Table "Parent":
> 1. id - primary key identifier.
> 2. type - polymorphic_identity.
> 3. name - string data column.
>
> Table "Child"  - inherits Parent:
> 1. id - primary key identifier.
> 2. parent_id - foreignkey to Parent.
> 3. category - string data column.
>
> Summing up I have two tables. Table Child inherits from Parent and also have
> a foreignkey to it.
> I really need both inheritance and foreignkey. This example is only a short
> demo which reproduces the problem.
> My real database has 1000+ tables with complex inheritance.
>
> I used declarative_base to declare the schema:
>
>
>> # -*- coding: utf-8 -*-
>>
>> from sqlalchemy import Column, String, Integer, ForeignKey
>> from sqlalchemy.orm import relationship
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy import create_engine
>> from sqlalchemy.orm import sessionmaker
>>
>> Base = declarative_base()
>>
>> class Parent(Base):
>>   __tablename__ = "Parent"
>>   id = Column(Integer, primary_key=True)
>>   type = Column(String(250))
>>
>>   name = Column(String(250))
>>
>>   __mapper_args__ = {
>> 'polymorphic_identity':'Parent',
>> 'polymorphic_on':type
>>   }
>>
>> class Child(Parent):
>>   __tablename__ = 'Child'
>>   id = Column(Integer, ForeignKey('Parent.id'), primary_key=True)
>>
>>   parent_id = Column(ForeignKey("Parent.id"), nullable=True)
>>   category = Column(String(250))
>>
>>   __mapper_args__ = {
>> 'polymorphic_identity':'Child',
>>   }
>>
>> engine = create_engine('postgresql+psycopg2://joe:joe@localhost/alch')
>>
>> session = sessionmaker()
>> session.configure(bind=engine)
>> Base.metadata.create_all(engine)
>
>
> But when I run the code I get the following error:
>
>
>> sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
>> 'Parent' and 'Child'; tables have more than one foreign key constraint
>> relationship between them. Please specify the 'onclause' of this join
>> explicitly.
>
>
> I have tried to set relationship attribute for Parent or for Child
> separately and for both too. Tried to use primaryjoin and foreign_keys
> parameters of relationship. But the error was the same.
>
> I'm totally confused about this error.
> Please help. Thanks.
>

The docs at http://docs.sqlalchemy.org/en/latest/orm/inheritance.html say:

It is most common that the foreign key constraint
is established on the same column or columns
as the primary key itself, however this is not
required; a column distinct from the primary key
may also be made to refer to the parent via foreign
key. The way that a JOIN is constructed from the
base table to subclasses is also directly
customizable, however this is rarely necessary.

The last sentence is the important one. Normally there is only a
single foreign key from the child to the parent table, so SA can
automatically figure out the join condition. In your case, you've got
2 foreign keys, so SA is asking you to be explicit. However, it's the
*inheritance* join that is causing the problem, and the main docs
don't describe how to customize that.

However, if you look at the docs for the underlying mapper() function,
you'll find an "inherit_condition" parameter:

http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherit_condition

Unfortunately it doesn't seem to accept strings, so you need to
construct your code such that you have direct access to the classes
themselves.

Here's a working version of your script:

# -*- coding: utf-8 -*-

from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Parent(Base):
  __tablename__ = "Parent"
  id = Column(Integer, primary_key=True)
  type = Column(String(250))

  name = Column(String(250))

  __mapper_args__ = {
'polymorphic_identity':'Parent',
'polymorphic_on':type
  }

class Child(Parent):
  __tablename__ = 'Child'
  id = Column(Integer, ForeignKey('Parent.id'), primary_key=True)

  parent_id = Column(ForeignKey("Parent.id"), nullable=True)
  category = Column(String(250))

  parent = relationship(Parent, foreign_keys=[parent_id])

  __mapper_args__ = {
'polymorphic_identity':'Child',
'inherit_condition': id == Parent.id,
  }

engine = create_engine('sqlite:///:memory:', echo=True)

Session = sessionmaker()
Session.configure(bind=engine)
Base.metadata.create_all(engine)

session = Session()
parent = Parent(name='Bob')
child = Child(name='Joe', category='cheese')
session.add_all([parent, child])
session.flush()


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, 

Re: [sqlalchemy] A "after flush before commit" event

[Simon King]

Could you pair a before_commit handler with an after_flush_postexec
handler? The before_commit one would set a flag to say that you are
committing, and the after_flush_postexec one would look for that flag
before proceeding.

Simon

On Fri, Sep 15, 2017 at 12:08 AM,   wrote:
> I'm writing a handler for the "before_commit" event that requires that all
> objects have primary key ids, and realizing that "before_commit" runs before
> the flush preceding a commit. Is there a way to have the handler run after
> the initial flush preceding a commit? Or, alternatively, have an
> "after_flush" listener run only on the flush preceding a commit?
>
> --
> 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] Multiple front end server with Single MySQL database

[Simon King]

On Thu, Sep 14, 2017 at 12:07 PM, Ankur Kumar
 wrote:
>> I'm reallly sorry.
>
>
> Please also update line number 13 in api.py and api1_py as
>
> old :
> output = session.query(model.Bug_Test.value).with_for_update('read').all()
>
> New:
> output = session.query(model.Bug_Test.value).all()
>

In model.py you've got this:

SessionMaker = sessionmaker(bind=engine)
Session = scoped_session(SessionMaker)

scoped_session is a tool for managing thread-local sessions
(http://docs.sqlalchemy.org/en/latest/orm/contextual.html). Each time
you call Session(), you'll get the same session instance back, as long
as you are in the same thread. Since you are never calling
session.close(), every call to select_data will actually use the same
transaction, which is probably why you are not seeing data that was
inserted after that transaction began.

Typically in a web application you want to ensure that each request
starts with a clean session, and closes the session again at the end,
so that transactions aren't shared between requests.

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-faq-whentocreate

I don't use Flask, so I don't know the best way to get it to do that,
but I know there is a library which will do it for you:

http://flask-sqlalchemy.pocoo.org/2.1/

If you don't want to use that library, you could at least see how it
closes out sessions:

https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/__init__.py#L851

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] Multiple front end server with Single MySQL database

[Simon King]

On Thu, Sep 14, 2017 at 10:59 AM, Ankur Kumar
 wrote:
>> Hello Simon,
>
>
> Thanks,
> I donot want to read uncommitted data.
>
> I want to read committed data by one API server from another API server.
>>
>>
>
> From another API server it keeps showing old data
>

Ah, sorry, I misunderstood your original message. I don't think you
attached your code - if you do that then we might be able to help
more.

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] Multiple front end server with Single MySQL database

[Simon King]

On Thu, Sep 14, 2017 at 10:03 AM, Ankur Kumar
 wrote:
> I'm using Flask, sqlalchemy with MySQL database for my API server.
>
> It's working fine with one front end API server.
> But now i want to increase a front end API server in my Load balancer.
>
> So, the scenario will be two (Flask + sqlalchemy) server and 1 MySQL server.
>
> The issue that the new user is not able to read data committed by another
> server until any session.commit() is called.
>
> I know i can use "autocommit=True" in sessionmaker but i don't want to use
> it.
>
> Is there any other solution?
>
> I'm also attaching sample  raw script  for demo of the issue
>

If you want to be able to read uncommitted data from another
transaction, you need to use a lower isolation level (presumably READ
UNCOMITTED):

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#setting-transaction-isolation-levels

(Obviously with lower transaction isolation comes the possibility of
reading inconsistent data and so on)

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] Is Textual SQL DB neutral

[Simon King]

I'm pretty sure the bad performance is due to pyodbc (or maybe SQL
Server) not handling *huge* numbers of bind parameters gracefully. You
are generating a query with (batch_size * n_primary_key_columns) bind
parameters, which even in your original version meant 2000 bind
parameters.

Try with a batch sizes of 10, 50 and 100. You'll run more queries but
I suspect each query will perform better.

Simon

On Wed, Aug 30, 2017 at 10:02 PM, Ken MacKenzie  wrote:
> With a batch size of 1 based on the original selection, no improvement.
>
> I am going to see what sql this is creating.
>
>  2328928 function calls (2085124 primitive calls) in 1084.925
> seconds
>
>Ordered by: internal time
>
>ncalls  tottime  percall  cumtime  percall filename:lineno(function)
>26 1074.733   41.336 1074.733   41.336 {method 'execute' of
> 'pyodbc.Cursor' objects}
> 20.8710.4360.8720.436 {built-in method connect}
> 349128/3485230.5560.0000.5730.000 {built-in method
> isinstance}
>260.5020.0190.5020.019 {method 'close' of
> 'pyodbc.Cursor' objects}
>200.4850.0240.4850.024 {method 'commit' of
> 'pyodbc.Connection' objects}
> 161062/1610600.3170.0000.3180.000 {built-in method getattr}
> 191230.2590.0000.7890.000 elements.py:861(__init__)
>  95670.2580.0000.2580.000 {method 'fetchone' of
> 'pyodbc.Cursor' objects}
> 76492/191230.2480.0002.8420.000 operators.py:297(__eq__)
>  67012/240.2360.0002.6010.108
> visitors.py:75(_compiler_dispatch)
> 191230.2340.0000.4240.000 elements.py:2927(__init__)
>  95820.2130.0001.1040.000 elements.py:1860(_construct)
> 191320.2050.0000.4350.000 compiler.py:676(visit_column)
> 191230.1890.0001.8060.000
> compiler.py:1040(_generate_generic_binary)
> 191230.1830.0001.2630.000
> default_comparator.py:290(_check_literal)
> 57389/191430.1730.0002.7320.000 {built-in method eq}
> 191230.1710.0001.9120.000
> default_comparator.py:22(_boolean_compare)
> 286840.1690.0000.3460.000
> operators.py:1007(is_precedent)
> 191260.1430.0000.3520.000 elements.py:3962(__new__)
> 191230.1290.0000.7950.000
> compiler.py:1164(visit_bindparam)
> 191230.1280.0000.9180.000 elements.py:3818(_bind_param)
> 191530.1260.0000.5810.000 base.py:1128(decorate)
> 10.1250.125 1084.925 1084.925 del_test.py:1()
>1168450.1210.0000.1210.000 {method 'get' of 'dict'
> objects}
> 38264/95810.1150.0002.5670.000 compiler.py:806()
> 191230.1130.0002.0480.000 compiler.py:1006(visit_binary)
> 191250.1130.0000.1740.000
> compiler.py:1265(_process_anon)
> 191250.1090.0000.4510.000
> compiler.py:1246(_truncated_identifier)
> 191230.1080.0002.4570.000 annotation.py:100(__eq__)
> 191230.1070.0002.1900.000 base.py:1306(visit_binary)
> 191230.1040.0002.0150.000 type_api.py:60(operate)
> 382830.1010.0000.1670.000 compiler.py:2901(quote)
> 191230.0950.0000.5770.000
> compiler.py:1233(_truncate_bindparam)
> 192060.0940.0000.1630.000 elements.py:3918(__new__)
> 191250.0930.0000.3230.000 elements.py:4073(apply_map)
> 382460.0910.0000.0910.000 elements.py:640(self_group)
> 884600.0880.0000.0880.000 {method 'append' of 'list'
> objects}
> 191230.0840.0002.6210.000 properties.py:269(operate)
> 38264/95810.0840.0002.5860.000 compiler.py:804()
> 191230.0820.0000.7320.000
> annotation.py:78(_compiler_dispatch)
> 12703/31420.0760.0002.6050.001 {method 'join' of 'str'
> objects}
> 191230.0750.0000.1290.000
> compiler.py:959(_get_operator_dispatch)
> 10.0710.071 1082.250 1082.250 db_base.py:172(dict_delete)
> 191230.0700.0000.0890.000
> compiler.py:1271(bindparam_string)
> 191230.0700.0002.0850.000 :1()
>  95810.0670.0000.5200.000 elements.py:1883()
>  95610.0640.0002.9050.000 db_base.py:208()
> 23611/233230.0620.0000.0670.000 {built-in method hasattr}
> 287050.0610.0000.1590.000
> elements.py:4216(_expression_literal_as_text)
> 287460.0580.0000.0980.000
> elements.py:4220(_literal_as_text)
> 191230.0580.0000.1300.000
> type_api.py:504(coerce_compared_value)
>  95620.0560.0000.4160.000 result.py:1156(fetchone)
> 191250.0560.0000.230

Re: [sqlalchemy] Is Textual SQL DB neutral

[Simon King]

The generic version would look something like this:

table = cls.__table__
pkconditions = []
for pk in cls.SQL_PK:
pkconditions.append(table.c[pk] == sa.bindparam(pk)
condition = sa.and_(*pkconditions)
statement = sa.delete(table, whereclause=condition)
batchparams = [dict(zip(cls.SQL_PK, v)) for v in id_batch]
session.execute(statement, batchparams)

As for the portability of the textual version: your original code
produced fragments like this:

  field == 'value'

a) Is the double-equals a typo, or is it syntax that SQL Server
expects? I've never used double-equals in MySQL.
b) Are the primary key fields always strings? You later said that they
were, but if you ever had an integer primary key column, comparing it
against a quoted value would be an error on postgres (I believe).
c) Can the values that you are comparing against ever contain single
quotes? That would break your query and potentially leave you open to
SQL injection.
d) Are your values unicode strings or byte strings? If unicode, does
the encoding match what the database expects?

If none of those are issues for you, the textual version is probably
pretty safe.

Simon

On Wed, Aug 30, 2017 at 5:30 PM, Ken MacKenzie <deviloc...@gmail.com> wrote:
> Not sure how I would iterate through a non predetermined number of primary
> keys.
>
> I guess part of me is wondering that although textual sql is not inherently
> db neutral how different between the db targets is the where field = 'value'
> syntax?
>
> On Wednesday, August 30, 2017 at 12:07:52 PM UTC-4, Simon King wrote:
>>
>> You could also try using executemany:
>>
>>
>> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements
>>
>> I think it would look something like this:
>>
>> table = cls.__table__
>> condition = sa.and_(
>> table.c.pk1 == sa.bindparam('pk1'),
>> table.c.pk2 == sa.bindparam('pk2'),
>> )
>> statement = sa.delete(table, whereclause=condition)
>> batchparams = [{'pk1': v[0], 'pk2': v[1]} for v in id_batch]
>> session.execute(statement, batchparams)
>>
>> Simon
>>
>> On Wed, Aug 30, 2017 at 4:28 PM, Ken MacKenzie <devil...@gmail.com> wrote:
>> > After the current sorted profile finishes I will revert to the textual
>> > version and run a profile on that.  I expect another 10-15 minutes for
>> > this
>> > to finish right now.
>> >
>> > At present the batch size is set to 1000, total record count is just
>> > over
>> > 9000 in these tests.
>> >
>> > The reason for 1000 was at first I was looking at doing this as a
>> > tuple_(fld, fld).in_((val, val),(val,val)) format.  The 1000 should keep
>> > me
>> > under most DB restrictions on the in statement.
>> >
>> > However since SQL Server does not seem to support the tuple_ usage I
>> > reverted to this method.
>> >
>> > I technically have one more method and that is a concat_ in_ where I
>> > concat
>> > the fields.
>> >
>> > Other specifics, the table in question has 2 fields for the PK, both are
>> > varchar, one length 3, the other length 10.  There are 5 non key fields,
>> > 3
>> > short varchars, one decimal at 14,2 precision and one varchar(800) which
>> > contains description text.
>> >
>> > Total record count of the table before any deletion is about 1.05
>> > million.
>> >
>> > Python version is 3.4.5, running on a modest CentOS desktop and to be
>> > fair
>> > the SQL Server instance is sub optimal for development.
>> >
>> > On Wednesday, August 30, 2017 at 11:18:13 AM UTC-4, Simon King wrote:
>> >>
>> >> It would be interesting to see the profile of the textual SQL version.
>> >> It looks like most of the time is being spent inside pyodbc, rather
>> >> than SQLAlchemy, so I guess it must be something to do with the
>> >> processing of bind parameters. How many parameters are being sent in
>> >> per query? ie. what is len(id_batch) * len(cls.SQL_PK)?
>> >>
>> >> You could try playing with your batch sizes to see what sort of effect
>> >> that has.
>> >>
>> >> 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 t

Re: [sqlalchemy] Is Textual SQL DB neutral

[Simon King]

You could also try using executemany:

http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements

I think it would look something like this:

table = cls.__table__
condition = sa.and_(
table.c.pk1 == sa.bindparam('pk1'),
table.c.pk2 == sa.bindparam('pk2'),
)
statement = sa.delete(table, whereclause=condition)
batchparams = [{'pk1': v[0], 'pk2': v[1]} for v in id_batch]
session.execute(statement, batchparams)

Simon

On Wed, Aug 30, 2017 at 4:28 PM, Ken MacKenzie <deviloc...@gmail.com> wrote:
> After the current sorted profile finishes I will revert to the textual
> version and run a profile on that.  I expect another 10-15 minutes for this
> to finish right now.
>
> At present the batch size is set to 1000, total record count is just over
> 9000 in these tests.
>
> The reason for 1000 was at first I was looking at doing this as a
> tuple_(fld, fld).in_((val, val),(val,val)) format.  The 1000 should keep me
> under most DB restrictions on the in statement.
>
> However since SQL Server does not seem to support the tuple_ usage I
> reverted to this method.
>
> I technically have one more method and that is a concat_ in_ where I concat
> the fields.
>
> Other specifics, the table in question has 2 fields for the PK, both are
> varchar, one length 3, the other length 10.  There are 5 non key fields, 3
> short varchars, one decimal at 14,2 precision and one varchar(800) which
> contains description text.
>
> Total record count of the table before any deletion is about 1.05 million.
>
> Python version is 3.4.5, running on a modest CentOS desktop and to be fair
> the SQL Server instance is sub optimal for development.
>
> On Wednesday, August 30, 2017 at 11:18:13 AM UTC-4, Simon King wrote:
>>
>> It would be interesting to see the profile of the textual SQL version.
>> It looks like most of the time is being spent inside pyodbc, rather
>> than SQLAlchemy, so I guess it must be something to do with the
>> processing of bind parameters. How many parameters are being sent in
>> per query? ie. what is len(id_batch) * len(cls.SQL_PK)?
>>
>> You could try playing with your batch sizes to see what sort of effect
>> that has.
>>
>> 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 - 
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] Is Textual SQL DB neutral

[Simon King]

0.0350.0001.5390.000 db_base.py:174()
> 167040.0330.0000.0550.000
> elements.py:4220(_literal_as_text)
> 111230.0330.0000.0730.000
> type_api.py:504(coerce_compared_value)
> 31584/314690.0310.0000.0320.000 {built-in method len}
> 111230.0300.0000.1650.000 elements.py:2986(self_group)
>  55620.0300.0000.1870.000 result.py:1156(fetchone)
> 111310.0300.0000.0770.000 compiler.py:494()
> 111230.0290.0000.0470.000
> type_api.py:452(_cached_bind_processor)
>
> Any guidance to how I can improve the performance of this solution and
> remain DB neutral?
>
> On Wednesday, August 30, 2017 at 4:43:01 AM UTC-4, Simon King wrote:
>>
>> On Tue, Aug 29, 2017 at 9:49 PM, Ken MacKenzie <devil...@gmail.com> wrote:
>> > I have a query I have constructed and I had to deal with a composite
>> > primary
>> > key to select items;
>> >
>> > q = s.query(cls)
>> > or_cond = []
>> > for x in id_batch:
>> >
>> > pkf = [text(f + "=='" + v + "'") for f,v in
>> > zip(cls.SQL_PK,
>> > x)]
>> > and_cond = (and_(*pkf))
>> > or_cond.append(and_cond)
>> >
>> >
>> > q =
>> > q.filter(or_(*or_cond)).delete(synchronize_session=False)
>> >
>> > cls.SQL_PK is a tuple of the primary key fields for the model described
>> > by
>> > class.  This is a class method that is part of a inherited class to the
>> > model
>> >
>> > The current target is SQL Server.  My concern is using text('field =
>> > 'value'), is that going to work for other DB targets like say postgres?
>> >
>> > The first round of doing this I tried using a
>> > tuple_(*cls.SQL_PK).in_(id_batch), but that did not work and the
>> > resulting
>> > SQL id not work in SSMS leading me to believe that SQL Server (or at
>> > least
>> > the version we are using) does not support tuples.
>> >
>>
>> Textual SQL is not DB-neutral in general. Luckily, in this case you
>> shouldn't even need it. Try something like this:
>>
>> for x in id_batch:
>> cols = [getattr(cls, colname) for colname in cls.SQL_PK]
>> pkf = [(col == v) for (col, v) in zip(cols, x)]
>> and_cond = and_(*pkf)
>> or_cond.append(and_cond)
>>
>> ie. use "getattr" to retrieve the actual column property from the class.
>>
>> 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.

-- 
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] Is Textual SQL DB neutral

[Simon King]

On Tue, Aug 29, 2017 at 9:49 PM, Ken MacKenzie  wrote:
> I have a query I have constructed and I had to deal with a composite primary
> key to select items;
>
> q = s.query(cls)
> or_cond = []
> for x in id_batch:
>
> pkf = [text(f + "=='" + v + "'") for f,v in zip(cls.SQL_PK,
> x)]
> and_cond = (and_(*pkf))
> or_cond.append(and_cond)
>
>
> q = q.filter(or_(*or_cond)).delete(synchronize_session=False)
>
> cls.SQL_PK is a tuple of the primary key fields for the model described by
> class.  This is a class method that is part of a inherited class to the
> model
>
> The current target is SQL Server.  My concern is using text('field =
> 'value'), is that going to work for other DB targets like say postgres?
>
> The first round of doing this I tried using a
> tuple_(*cls.SQL_PK).in_(id_batch), but that did not work and the resulting
> SQL id not work in SSMS leading me to believe that SQL Server (or at least
> the version we are using) does not support tuples.
>

Textual SQL is not DB-neutral in general. Luckily, in this case you
shouldn't even need it. Try something like this:

for x in id_batch:
cols = [getattr(cls, colname) for colname in cls.SQL_PK]
pkf = [(col == v) for (col, v) in zip(cols, x)]
and_cond = and_(*pkf)
or_cond.append(and_cond)

ie. use "getattr" to retrieve the actual column property from the class.

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] SQLAlchemy 1.1 cause core dump committing multiple dirty records outside of function where mutation happens

[Simon King]

On Tue, Aug 22, 2017 at 9:26 AM, Konstantin Kashin  wrote:
> # Setup
> Suppose I have a table with two fields: a string primary key and a boolean
> flag. I want to query multiple rows and then update the flag across all of
> them, then commit my changes. I have a MySQL DB with the following DBAPI
> (http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html).
>
> ## Model
> class TestSQLTable(Base):
> __tablename__ = 'test_table'
>
>
> some_string_id = Column(String(32), nullable=False, primary_key=True)
> flag = Column(Boolean, nullable=False, default=True)
>
>
> # SQLAlchemy 1.0.12
>
> In SQLAlchemy 1.0.12, the following worked:
>
>
> sm = sessionmaker(
> autocommit=False,
> autoflush=True,
> expire_on_commit=True,
> bind=engine,  # MySQL engine
> )
> session_factory = scoped_session(sm)
>
>
> def modify_records(session, flag=False):
> records = session.query(TestSQLTable).all()
> for r in records:
> r.flag = flag
>
>
> session = session_factory()
> modify_records(session)
> session.commit()
>
>
>
> # SQLAlchemy 1.1.13
> Now, this does *not* work and causes a core dump. I am unable to tell what
> change was made in 1.1 that causes this different behavior when reading the
> ["What's New in SQLAlchemy
> 1.1?"](http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html)
> guide.
>
> If I do this in a Jupyter notebook, for instance, the kernel just crashes on
> commit without any error.
>
> However, this **does** work for just a single row:
>
> def modify_record(session, flag=False):
> record = session.query(TestSQLTable).first()
> record.flag = flag
>
>
> ## Ways I have gotten this to work
>
> ### Add an explicit return
>
> def modify_records(session, flag=False):
> records = session.query(TestSQLTable).all()
> for r in records:
> r.flag = flag
> return records  # ADD THIS!
>
>
> ### Flush after each mutation
>
>
> def modify_records(session, flag=False):
> records = session.query(TestSQLTable).all()
> for r in records:
> r.flag = flag
> session.flush()  # ADD THIS!
>
>
> ### Restructure code so that the commit happens inside `modify_records`
>
> This works, but is not the way the application is currently built and would
> thus require a major refactor (b/c commit happens via a decorator).
>
> # Main question
>
> Why does this happen exactly for multiple records and why did this work in
> 1.0 but does not in 1.1?

What platform are you running this on, and how have you installed
MySQLdb and SQLAlchemy? If you ugraded SQLAlchemy in-place, is there
any chance that you are running a mix of old and new code? Have you
reproduced the error in a completely fresh installation?

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] How do I get an object and update multiple fields in sqlalchemy

[Simon King]

Are you flushing the same session that was used to load the record?
What session does get_instance use?

Simon

On Mon, Aug 21, 2017 at 7:47 AM, pravin battula
<pravin.batt...@gmail.com> wrote:
> I did as below. But didn't work. Am i missing anything else.
>
> def update_record(session, id):
> record_instance = get_instance(id)
> if record_instance:
> updateobj(record_instance, {'time_zone':'GMT','status':'done'})
> session.flush()
> session.commit()
>
> def updateobj(obj, data):
> for key, value in data.items():
> setattr(obj, key, value)
>
>
> On Friday, 18 August 2017 19:47:50 UTC+5:30, Simon King wrote:
>>
>> No, but you can trivially write your own function to do it:
>>
>> def updateobj(obj, data):
>> for key, value in data.items():
>> setattr(obj, key, value)
>>
>> Simon
>>
>> On Fri, Aug 18, 2017 at 3:14 PM, pravin battula
>> <pravin@gmail.com> wrote:
>> > The solution which you gave will work but I have a dict of keys to be
>> > updated with that get instance. Is there any specific way of updating
>> > something like product_live_time_instance.update(data_dict).
>> >
>> >
>> > On Friday, 18 August 2017 19:21:12 UTC+5:30, Simon King wrote:
>> >>
>> >> On Fri, Aug 18, 2017 at 2:41 PM, pravin battula
>> >> <pravin@gmail.com> wrote:
>> >> > Hi,
>> >> >
>> >> > I'm getting an instance of a model using a primary key like below.
>> >> > product_live_time = session.query(ProductLiveTime).get(product_id)
>> >> >
>> >> > Now, i want to update few columns using the same instance
>> >> > product_live_time,
>> >> > how can i do it without doing filter again like below i.e
>> >> > session.query(ProductLiveTime).filter(ProductLiveTime.product_id ==
>> >> > product_id).update(data).
>> >> > is there any other better way of doing it.
>> >> >
>> >>
>> >> Maybe I'm misunderstanding the question, but:
>> >>
>> >> product_live_item.attr1 = value1
>> >> product_live_item.attr2 = value2
>> >> session.flush()
>> >>
>> >> Is that what you meant?
>> >>
>> >> 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+...@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 - 
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 do I get an object and update multiple fields in sqlalchemy

[Simon King]

No, but you can trivially write your own function to do it:

def updateobj(obj, data):
for key, value in data.items():
setattr(obj, key, value)

Simon

On Fri, Aug 18, 2017 at 3:14 PM, pravin battula
<pravin.batt...@gmail.com> wrote:
> The solution which you gave will work but I have a dict of keys to be
> updated with that get instance. Is there any specific way of updating
> something like product_live_time_instance.update(data_dict).
>
>
> On Friday, 18 August 2017 19:21:12 UTC+5:30, Simon King wrote:
>>
>> On Fri, Aug 18, 2017 at 2:41 PM, pravin battula
>> <pravin@gmail.com> wrote:
>> > Hi,
>> >
>> > I'm getting an instance of a model using a primary key like below.
>> > product_live_time = session.query(ProductLiveTime).get(product_id)
>> >
>> > Now, i want to update few columns using the same instance
>> > product_live_time,
>> > how can i do it without doing filter again like below i.e
>> > session.query(ProductLiveTime).filter(ProductLiveTime.product_id ==
>> > product_id).update(data).
>> > is there any other better way of doing it.
>> >
>>
>> Maybe I'm misunderstanding the question, but:
>>
>> product_live_item.attr1 = value1
>> product_live_item.attr2 = value2
>> session.flush()
>>
>> Is that what you meant?
>>
>> 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 - 
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 do I get an object and update multiple fields in sqlalchemy

[Simon King]

On Fri, Aug 18, 2017 at 2:41 PM, pravin battula
 wrote:
> Hi,
>
> I'm getting an instance of a model using a primary key like below.
> product_live_time = session.query(ProductLiveTime).get(product_id)
>
> Now, i want to update few columns using the same instance product_live_time,
> how can i do it without doing filter again like below i.e
> session.query(ProductLiveTime).filter(ProductLiveTime.product_id ==
> product_id).update(data).
> is there any other better way of doing it.
>

Maybe I'm misunderstanding the question, but:

product_live_item.attr1 = value1
product_live_item.attr2 = value2
session.flush()

Is that what you meant?

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] Raising on usage of attribute in SQL queries

[Simon King]

On Thu, Aug 17, 2017 at 12:35 AM, Neena Parikh  wrote:
> Hi there!
>
> I'm looking to create a helper or decorator function that will enable us to
> "mark" a column or table as “unused”, and raise an error if that column or
> table is queried for in SQL.
>
>
> Context
> The motivation behind this is to help us catch accidental references to
> deleted columns in our code. We currently follow this process for database
> migrations that involve dropping columns or tables:
>
> Remove all usages of the column in the code, except the column definition.
> (We keep the column definition around since we have tests to ensure our
> column definitions match up with our migrated database schemas.)
> Make the column deferred
> Wait a few days, then remove the column definition and run the database
> migration that actually drops the column
>
> The main issue with this process is that if there’s any code left in that
> loads in the deleted column, we run into a number of issues upon running the
> migration. We’d like to introduce a helper or decorator for dropped
> columns/tables that will trigger an error when the column/table is
> referenced / loaded in any of the following ways:
>
> session.query(MyTable.my_column).all()
> session.query(MyTable).options(undefer('*')).all()
> my_table_instance.my_column
> session.query(MyDroppedTable).all()
>
> Ideally, this would be a lightweight wrapper or decorator that could be
> applied to any column or table — something like my_column =
> unused(Column(…)) or an @unused decorator on a class.
>
>
> Prior Work
> We’ve looked at using hybrid_property to achieve this:
>
>
> class MyTable():
> ...
> _my_column = deferred(Column('my_column', String(255), nullable=True))
>
> @hybrid_property
> def my_column(self):
> raise Exception('Unused column')
>
> @my_column.setter
> def my_column(self, value):
> raise Exception('Unused column')
>
>
> What this gets us:
>
> Exception on my_table_instance.my_column
> Exception on session.query(MyTable.my_column).all()
> Exception on session.query(MyTable).options(undefer('my_column')).all()
>
> Issues with this approach:
>
> No exception on session.query(MyTable).options(undefer('*')).all()
> Requires a fair amount of implementation overhead for each column
> Not applicable to unused tables
>
>
>
> Any tips on how we can best use SQLAlchemy to accomplish all of the above?
>

To catch the "undefer('*')" case, you could perhaps change the column
type to something based on a TypeDecorator that raises an exception in
process_bind_param and process_result_value:

http://docs.sqlalchemy.org/en/latest/core/custom_types.html#sqlalchemy.types.TypeDecorator

By "implementation overhead", do you mean the amount of typing you
have to do each time you disable a column (creating the hybrid
properties)? If so, you might be able to use the events system to
create those properties automatically. For example, you could try
listening for the "after_configured" mapper event, then inspect the
class's properties. If you find any disabled columns, you could then
attach corresponding hybrid properties that raise exceptions.

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] Guidance for mapping tables dynamically

[Simon King]

On Fri, Jul 28, 2017 at 12:44 AM, Saurabh Bhandiwad
 wrote:
> Hello experts,
>
> Apologies if this question has been asked  before, I couldn't find the right
> question on internet which reflects my problem.
>
> In my project i receive SQLITE file and I need to query information. Though
> the Database Schema is bit unorthodox.
>
> For example, Database will contain n tables in - Main, PropertyReference
> and n-2 Property%d tables. Main contains Key and value columns and
> PropertyReference contains Id and PropertyName (Property Table name may not
> always be Property%d). Hence I need to read the Property table name before I
> create any mapping for actual Property%d.
>
> I could create the automap_base class for Main and PropertyReference easily.
> I created Table object for each Property%d and mapped them using mapper (in
> get_property_tables). while creating table I created ForeignKey with
> Main.Key for one-to-one unidirectional mapping. Though I couldn't figure out
> how to create relationship while creating Table object. Hence i am using
> join API of Query to explicitly join these classes.
>
> Since i am fairly new to Sqlalchemy, my concern is, am i doing it right way?
> Is there any better way to solve this problem? I was hoping to create a
> relationship for my Property%d tables with Main so I don't have to use join
> explicitly.
>
> Thanks in advance!!!
>
> Sample Code:: I have not attached the dummy.dB I created. Let me know if
> that is needed.
>
> from sqlalchemy.ext.automap import automap_base
> from sqlalchemy.orm import Session, mapper
> from sqlalchemy import create_engine
> from sqlalchemy import Integer, Text
> from sqlalchemy import ForeignKey, Column, Table
>
> Base = automap_base()
>
> class Main(Base):
> __tablename__ = "Main"
>
> key = Column("Key", Text)
> value = Column("value", Integer)
>
> class PropertyReference(Base):
> __tablename__ = "PropertyReference"
>
> id = Column(Integer, primary_key=True)
> tablename = Column(Text)
>
> def get_property_tables(session):
> ref_tables_names = session.query(
> PropertyReference.tablename
> ).all()
> ref_table = {}
> for table in ref_tables_names:
> temp_table = Table(
> table[0],
> Base.metadata,
> Column('Key', Text, ForeignKey(Main.key)),
> Column('Value', Text),
> Column('Status', Text),
> extend_existing=True,
> )
> class TempTable(object):
> pass
> mapper(TempTable, temp_table, primary_key=temp_table.c.Key)
> ref_table[table[0]] = temp_table
> return ref_table
>
> engine = create_engine(r'sqlite:///C:\Temp\dummy.dB')
> Base.prepare(engine, reflect=True)
> session = Session(engine)
> property_tables = get_property_tables(session)
> failed_keys = []
> for name, table in property_tables.items():
> failed_keys.append(session.query(table,
> Main).filter(table.c.Status=="FAIL").join(Main).all())
>

I haven't run your code, so this answer may not be appropriate, but in
general, you can create a relationship when using classical mappings
by passing a "properties" parameter to the mapper() function,
something like this:

mapper(TempTable,
   temp_table,
   primary_key=temp_table.c.Key,
   properties={'main': relationship(Main, backref='property_%s' %
table[0])},
)

http://docs.sqlalchemy.org/en/latest/orm/mapping_styles.html#classical-mappings

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] Is there a way to configure session.commit() so that it automatically return the committed instance's class.__name__ + primary id?

[Simon King]

On Tue, Jul 25, 2017 at 2:35 AM, Jinghui Niu  wrote:
> I was wondering if there is a way to configure Session.commit() so on each
> successful commit, it will return the committed/updated/deleted instance's
> class.__name__ + row.id. Is this possible? Thanks.

No, there's no way to make the commit() method do that. In general the
request doesn't really make sense as a commit may cover multiple
instances, not just one. Also, objects are inserted/updated during a
flush(), which may happen separately from the commit().

If you wanted to build this yourself, you could use ORM events
(http://docs.sqlalchemy.org/en/latest/orm/events.html) to be notifed
when an instance is inserted/updated/deleted.

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] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Simon King]

The key part of the stack trace is:

File "c:\Program
Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py",
line 1773, in _get_default_schema_name default_schema_name =
connection.scalar(query)

...which is in this function:

https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11=file-view-default#base.py-1768

It's failing to fetch the single value that ought to come back from
the query "SELECT schema_name()".

I don't know anything about MSSQL or ODBC, but you could try poking
around with pdb in the scalar() function:

https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11=file-view-default#result.py-1212

Simon


On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel
 wrote:
> Mike,
>
> Sorry for the lack of information.  Please find the rest of what you wanted
> below.
>
> Full Stack Trace:
>
>>  Running Direct SQL Query
>> Traceback (most recent call last):
>>   File "test.py", line 45, in  result = con.execute('SELECT * FROM
>> EPO_MODELS')
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1139, in
>> execute bind, close_with_result=True).execute(clause, params or {})
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1003, in
>> _connection_for_bind engine, execution_options)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 403, in
>> _connection_for_bind conn = bind.contextual_connect()
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2112, in
>> contextual_connect self._wrap_pool_connect(self.pool.connect, None),
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2147, in
>> _wrap_pool_connect return fn()
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 387, in connect return _ConnectionFairy._checkout(self)
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 766, in _checkout fairy = _ConnectionRecord.checkout(pool)
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 516, in checkout rec = pool._do_get()
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 1138, in _do_get self._dec_overflow()
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66,
>> in __exit__ compat.reraise(exc_type, exc_value, exc_tb)
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 1135, in _do_get return self._create_connection()
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 333, in _create_connection return _ConnectionRecord(self)
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 461, in __init__ self.__connect(first_connect_check=True)
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 661, in __connect exec_once(self.connection, self)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 246, in
>> exec_once self(*args, **kw)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 256, in
>> __call__ fn(*args, **kw)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line
>> 1331, in go return once_fn(*arg, **kw)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\strategies.py", line
>> 181, in first_connect dialect.initialize(c)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\connectors\pyodb c.py", line
>> 165, in initialize super(PyODBCConnector, self).initialize(connection)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line
>> 1742, in initialize super(MSDialect, self).initialize(connection)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\default.py", line 250,
>> in initialize self._get_default_schema_name(connection)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line
>> 1773, in _get_default_schema_name default_schema_name =
>> connection.scalar(query)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 877, in
>> scalar return self.execute(object, *multiparams, **params).scalar()
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 1223,
>> in scalar return row[0]
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 563, in
>> _key_fallback expression._string_or_unprintable(key))
>>   sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for
>> column '0'"
>
>
> ODBC Driver Versions:
>>
>> 

Re: [sqlalchemy] Optimize SQLAlchemy query

[Simon King]

On Fri, Jun 30, 2017 at 12:44 PM, mvidalgarcia  wrote:
> Hi, I'm performing a query to some data models but the ORM response time
> looks much higher compared to the SQL one. I have a script to reproduce the
> problem here.
> Currently getting values like
>
>> sql time: 0.068972826004
>> orm time: 0.915776014328
>
>
> and I consider there's a notable overhead in the ORM time.
>
> Do you know how I can optimize this query to reduce the overhead?
>

Your "sql time" measurement only includes the time it takes to send
the query to the database. I don't think it includes the time that the
database has to evaluate the query and return the rows. To measure
that, you ought to take the SQL string that the query produces,
execute that string outside of the ORM, and fetch all the rows back.
You should do that first with a bare PG connection, then with
SQLAlchemy Core, and compare those 2 times to the time you get with
the ORM.

It's also worth noting that your query returns a lot of rows. I
haven't run your script, but it looks like you've got 254
Registrations, each with 88 RegistrationData items, so that'll be
22352 rows. For each of those rows, SA has to pick out the columns
representing the primary keys of the Registration, RegistrationData,
RegistrationFormFieldData and RegistrationFormItem classes, then check
them to see if they correspond to objects that are already in the
session. If they aren't, it has to construct those objects and link
them together. Processing 22352 rows in 0.91 seconds doesn't seem bad
to me.

If you can reply with the bare PG and SA core timings, we may have a
better idea of how to optimise your query.

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] execute Stored procedure.

[Simon King]

On Wed, Jun 28, 2017 at 6:39 AM, Vigneshrajan  wrote:
> Is it is possible to execute stored procedure in connection.execute()
> function?
>

I think it depends on the stored procedure. Do any of these search results help?

http://docs.sqlalchemy.org/en/latest/search.html?q=%22stored+procedure%22_keywords=yes=default

If not, how would you normally call the stored procedure if you
weren't using SQLAlchemy?

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: Wierdness with JSONB / Python dictionary

[Simon King]

On Wed, Jun 28, 2017 at 1:35 AM, Andrew M  wrote:
> I can also replicate it with JSON, with a table defined as follows:
>
> class Test(Base):
> __tablename__ = 'test'
> id = Column(INT, primary_key=True, index=True)
> json = Column(JSON, default={})
>
> And running the following code:
>
> p0 = Test()
> p1 = Test()
> session.add(p0)
> session.add(p1)
> session.commit()
> p0.json  # {}
> p1.json  # {}
>
> p0.json['US'] = 999
> p1.json = {'US': 999}
> p0.json == p1.json # True
>
> session.commit()
> p0.json  # {}
> p1.json  # {'US': 999}
> p0.json == p1.json # False
>
> I'm running the latest version of SQLAlchemy (1.1.11 on Python 3.6). Any
> thoughts, please?
>
> Thanks,
> Andrew

When SQLAlchemy creates a property that corresponds to a column in a
table, it sets up event listeners that are triggered whenever that
property is assigned to. The event listeners tell the SQLAlchemy
session that the object has been modified, so SA can send the changes
to the database on the next flush.

With JSON columns, the value is typically a mutable structure (a list
or a dict). They are the native Python types, so there is no mechanism
for SA to be notified when some entry in that structure changes. Since
SA doesn't see the change, it doesn't consider the object to be
modified, so the changes don't get flushed.

The fix is to not use a native Python dict for the property value, but
instead to use something that will notify SA when its contents change.
SA has an extension that provides MutableDict and MutableList classes,
as well as tools to build your own more specialized types:

http://docs.sqlalchemy.org/en/latest/orm/extensions/mutable.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: How to do scalar sub selects with the ORM correctly?

[Simon King]

You might be interested in this thread:

https://groups.google.com/forum/#!msg/sqlalchemy/8z0XGRMDgCk/tazeIUsdDgAJ

Simon

On Thu, Jun 15, 2017 at 1:53 PM, 'mike waites' via sqlalchemy
 wrote:
> Interestingly I've just discovered the concept of Bundles.
> http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#column-bundles
>
> Im guessing this could be used to have it populate the model instance with
> the extra columns ?
>
>
> On Thursday, 15 June 2017 13:25:00 UTC+1, mike waites wrote:
>>
>> Hi
>>
>> This is something i've been wrestling in different ways for some time so i
>> thought it was time that i sought a definitive answer on what the "proper"
>> way to do this is with the ORM.
>>
>> Often you'll want to have some kind of scalar sub select that computes a
>> column in your query.  This can be achieved easily by doing the following
>>
>> session.query(Model,scalar_sub_query).filter(...)
>>
>> When passing additional column properties to the Query constructor like
>> this results in SQLAlchemy giving you a named tuple back.  In some cases
>> this is fine but notice the first argument passed was Model, this is because
>> we also want the ORM to do its normal thing of populating an instance of
>> Model for us from the result of the query.
>>
>> In some cases this can be worked around by using declared_attr.  This will
>> allow us to define a field that is configured at import time from a function
>> or something like that.  This, in my experience, is suitable in rare
>> occasions.  The moment you need something that is not setup at import time
>> makes this unsuitable. IE suppose your column requires a user id to figure
>> out the result.
>>
>> The next thing that comes to mind is hybrid_property.  This is a lot
>> closer to what we want in most cases, the function is deferred until query
>> time however, to my knowledge there's no way to have SQLAlchemy
>> automatically populate a Model instance property from a hybrid_property
>> which result in use coming back to the original problem. Specifying columns
>> explicitly results in a named tuple when we simply want our model instance
>> populated.
>>
>> There's no doubt a way to do this but i've given up trying to find it.
>>
>> Hope someone can help!
>
> --
> 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] Adding filters to association (secondary) relationships

[Simon King]

Actually I think in this case you could probably leave the "secondary"
argument as the association table itself, but change the join
condition via the "primaryjoin" parameter. Something like this
(completely untested):

class User(Base):
id = Column(Integer, primary_key=True)
managed_teams = relationship(
'Team',
secondary=user_team_association_table,
primaryjoin=sa.and_(user_team_association_table.c.user_id == id,

user_team_association_table.c.is_manager==sa.true()),
)

Simon

On Thu, Jun 15, 2017 at 12:26 PM,  <jens.troe...@gmail.com> wrote:
> Thanks Simon. While this seems to have worked, I only run into the next
> error. Mind you, I’m somewhat new to Alchemy and my SQL is rather rusty at
> the moment. My current approach
>
> managed_teams = relationship("Team",
>  secondary="join(user_team_association,
> user_team_association.c.is_manager==true)",
>  backref="managers")
>
> seems to be an incomplete join. I’ll look into this tomorrow…
> Jens
>
>
> On Thursday, June 15, 2017 at 6:25:00 PM UTC+10, Simon King wrote:
>>
>> Table objects put columns under the ".c" attribute, so you probably
>> need "user_team_association.c.is_manager".
>>
>> 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 - 
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] Adding filters to association (secondary) relationships

[Simon King]

Table objects put columns under the ".c" attribute, so you probably
need "user_team_association.c.is_manager".

Simon

On Thu, Jun 15, 2017 at 12:11 AM,   wrote:
> Thank you Mike, the composite joins look like something close to what I
> need. However, it seems that they too require a mapped class for the
> association table?
>
> I tried
>
> managed_teams = relationship("Team",
>
> secondary="join(user_team_association.is_manager==true)",
>  backref="managers")
>
> using the table name directly, but this gives me an error:
>
> AttributeError: 'Table' object has no attribute 'is_manager'
>
> which I suspect indicates that the join expected a mapped class rather than
> a table name?
>
> Thanks!
>
>
>
> On Thursday, June 15, 2017 at 7:05:45 AM UTC+10, Mike Bayer wrote:
>>
>>
>> On 06/14/2017 04:54 PM, jens.t...@gmail.com wrote:
>> > […]
>> >
>> > # Filtered association that lists only managed teams. The tuple maps
>> > # the column to the value and would translate to a WHERE.
>> >  managed_teams =relationship("Team",
>> >
>> > secondary=user_team_association_table,
>> >   backref="managers",
>> >   filter=("is_manager",True))
>>
>>
>> you use a custom primaryjoin / secondaryjoin for this
>>
>> see  http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html
>>
>>
>> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#composite-secondary-joins
>>
>> etc
>>
> --
> 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] Purpose of assertion in table reflection test (1.1.9)

[Simon King]

On Thu, May 18, 2017 at 11:56 PM, jan.karstens via sqlalchemy <
sqlalchemy@googlegroups.com> wrote:

> Upgrading a specific dialect (EXASOL) to 1.1.9 made me stumble across this
> test (part of test_reflection.py):
>
> https://github.com/zzzeek/sqlalchemy/blob/rel_1_1_9/lib/
> sqlalchemy/testing/suite/test_reflection.py#L737
>
> def test_get_table_names(self):
> tablenames = [
> t for t in inspect(testing.db).get_table_names()
> if t.lower() in ("t1", "t2")]
> eq_(tablenames[0].upper(), tablenames[0].lower())
> eq_(tablenames[1].upper(), tablenames[1].lower())
>
> The test case fails and this does not look too unexpected to me. The
> tables T1 and T2 are added to the tablenames array, and u"t1".upper() is
> always different from u"t1".lower().
>
> Am I missing the purpose of the test case or is this always bound to fail?
>

This piqued my interest, so I did a bit of digging. The tables are defined
using the "quoted_name" class with quote=True:

https://github.com/zzzeek/sqlalchemy/blob/rel_1_1_9/lib/sqlalchemy/testing/suite/test_reflection.py#L717

https://github.com/zzzeek/sqlalchemy/blob/rel_1_1_9/lib/sqlalchemy/sql/elements.py#L3872

That class defines _memoized_method_lower() and _memoized_method_upper()
methods, and inherits from the MemoizedSlots class:

def _memoized_method_lower(self):
if self.quote:
return self
else:
return util.text_type(self).lower()

https://github.com/zzzeek/sqlalchemy/blob/rel_1_1_9/lib/sqlalchemy/util/langhelpers.py#L817

The result is that .upper() and .lower() call those memoized methods,
rather than the base string implementations. When self.quote is True, those
methods both return self unchanged. So "eq_(tablenames[0].upper(),
tablenames[0].lower())" should succeed.

I guess the intention of the test is to check that the inspection routines
return a quoted_name with quote=True.

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] Composite column with null property

[Simon King]

On Thu, May 11, 2017 at 6:18 PM,   wrote:
> Hello,
>
> I have a Money composite column, comprised of an `amount` (Decimal) and a
> `currency` (String). Sometimes the amount needs to be NULL, but then I get
> an instance of Money(None, 'GBP'). Is there any way to force the composite
> to return None in this case?

I'm not sure if that's possible. As an alternative, you could do
something like this:

class YourObject(Base):
_money = composite(Money, amount, currency)
@property
def money(self):
if self._money.amount is not None:
return self._money
return None

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.