[sqlalchemy] how to prepare "get by primary key" query?
Hello.
I am just starting with SQLAlchemy and have a small problem: my "get" query
is compiled every time I use it.
According to cProfile:
ncalls tottime percall cumtime percall filename:lineno(function)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py:1864(compile)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py:1908(_compiler)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py:231(__init__)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/engine/interfaces.py:764(__init__)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/engine/interfaces.py:805(process)
90/100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/visitors.py:73(_compiler_dispatch)
100.0000.0000.0020.000
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.8.1dev-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py:1082(visit_select)
In case of native API, I would use prepare "command/statement", but it
looks I am missing something obvious and cannot find similar functionality
in the package.
Below, you can find a complete source code, which reproduce the issue.
Thank you!
import sqlalchemy
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
name = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
def run_query(session):
employee_by_id = session.query(Employee)
for i in range(10):
x = employee_by_id.get(i)
print x.name
session.rollback()
if __name__ == '__main__':
engine = sqlalchemy.create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
for i in range(10):
session.add(Employee(id=i, name='name' + str(i)))
session.commit()
import cProfile
cProfile.run('run_query(Session())', 'get.stats')
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] discriminator_on_association example and association proxies
the Customer.comments attribute proxies through "comment_association" which is configured as a scalar.this should probably be improved in this particular recipe, but for starting assignment you need to assign a collection first: c1 = Customer() c1.comments = [] I don't ever use "discriminator_on_association", it's the most awkward and least efficient recipe; it's only there to show an improvement over what Django and Rails do. In practice, I typically advise table_per_assocation; the use case of being able to trace back everything that's "tagged" back to the parents polymorphically is not needed much in practice. On Apr 2, 2013, at 3:45 PM, bruk.habtu wrote: > I was attempting to implement the example shown in > https://github.com/ContextLogic/sqlalchemy/blob/master/examples/generic_associations/discriminator_on_association.py > > trace: https://gist.github.com/brukhabtu/2294f0873830243845db > my code: https://gist.github.com/brukhabtu/1423c9825252ddcf85a5 > > Customer model inherits CommentMixin. > > Error occurs when appending to the customer's comments > > customer.comments.append(comment) > > > The trace shows that the error happens in associationproxy.py > > return self._scalar_get(getattr(obj, self.target_collection)) > > obj is a Customer model object and self.target_collection is a string with > the value 'comment_association'. > > The attribute exists but is set to None. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] python: sqlalchemy - how do I ensure connection not stale using new event system
if you're doing a query that is causing the database connection to simply fail,
then you'd need to address that issue primarily. you'd probably want to
perform multiple insert statements, chunking about 5-10K records at at time.
On Apr 2, 2013, at 1:26 PM, algotr8...@gmail.com wrote:
> To clarify my environment. I have a VM (Linux Redhat) which has MySQL server
> running on it. My script runs locally on the same machine. It is a simple
> script that is doing a insert many after processing/parsing a csv file. I
> don't have any web apps or anything of that nature.
>
> On Monday, April 1, 2013 7:04:48 PM UTC-7, algot...@gmail.com wrote:
> I am using the sqlalchemy package in python. I have an operation that takes
> some time to execute after I perform an autoload on an existing table. This
> causes the following error when I attempt to use the connection:
>
> sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has
> gone away')
>
> I have a simple utility function that performs an insert many:
>
> def insert_data(data_2_insert, table_name):
> engine = create_engine('mysql://blah:blah123@localhost/dbname')
> # Metadata is a Table catalog.
> metadata = MetaData()
> table = Table(table_name, metadata, autoload=True, autoload_with=engine)
> for c in mytable.c:
> print c
> column_names = tuple(c.name for c in mytable.c)
> final_data = [dict(zip(column_names, x)) for x in data_2_insert]
> ins = mytable.insert()
> conn = engine.connect()
> conn.execute(ins, final_data)
> conn.close()
>
> It is the following line that times long time to execute since
> 'data_2_insert' has 677,161 rows.
>
> final_data = [dict(zip(column_names, x)) for x in data_2_insert]
>
> I came across the following post below which refers to a similar problem,
> however I am not sure how to implement the connection management suggested as
> I am quite a newbie.
>
> http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy
>
> Note for SQLAlchemy 0.7 - PoolListener is deprecated, but The same solution
> can be implemented using the new event system. – robots.jpg
>
> Here is the link to the new event system described by one of the users:
>
> http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
>
> If someone can please show me a couple of pointers on how I could go about
> integrating the suggestions into the way I use sqlalchemy I would be very
> appreciative. Thank you.
>
>
>
>
>
>
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] discriminator_on_association example and association proxies
I was attempting to implement the example shown in https://github.com/ContextLogic/sqlalchemy/blob/master/examples/generic_associations/discriminator_on_association.py trace: https://gist.github.com/brukhabtu/2294f0873830243845db my code: https://gist.github.com/brukhabtu/1423c9825252ddcf85a5 Customer model inherits CommentMixin. Error occurs when appending to the customer's comments customer.comments.append(comment) The trace shows that the error happens in associationproxy.py return self._scalar_get(getattr(obj, self.target_collection)) obj is a Customer model object and self.target_collection is a string with the value 'comment_association'. The attribute exists but is set to None. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: python: sqlalchemy - how do I ensure connection not stale using new event system
To clarify my environment. I have a VM (Linux Redhat) which has MySQL
server running on it. My script runs locally on the same machine. It is a
simple script that is doing a insert many after processing/parsing a csv
file. I don't have any web apps or anything of that nature.
On Monday, April 1, 2013 7:04:48 PM UTC-7, algot...@gmail.com wrote:
>
> I am using the sqlalchemy package in python. I have an operation that
> takes some time to execute after I perform an autoload on an existing
> table. This causes the following error when I attempt to use the connection:
>
> sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has
> gone away')
>
> I have a simple utility function that performs an insert many:
>
> def insert_data(data_2_insert, table_name):
> engine = create_engine('mysql://blah:blah123@localhost/dbname')
> # Metadata is a Table catalog.
> metadata = MetaData()
> table = Table(table_name, metadata, autoload=True, autoload_with=engine)
> for c in mytable.c:
> print c
> column_names = tuple(c.name for c in mytable.c)
> final_data = [dict(zip(column_names, x)) for x in data_2_insert]
> ins = mytable.insert()
> conn = engine.connect()
> conn.execute(ins, final_data)
> conn.close()
>
>
> It is the following line that times long time to execute since
> 'data_2_insert' has 677,161 rows.
>
> final_data = [dict(zip(column_names, x)) for x in data_2_insert]
>
> I came across the following post below which refers to a similar problem,
> however I am not sure how to implement the connection management suggested
> as I am quite a newbie.
>
> http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy
>
> Note for SQLAlchemy 0.7 - PoolListener is deprecated, but The same solution
> can be implemented using the new event system. – robots.jpg
>
> Here is the link to the new event system described by one of the users:
>
> http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
>
> If someone can please show me a couple of pointers on how I could go about
> integrating the suggestions into the way I use sqlalchemy I would be very
> appreciative. Thank you.
>
>
>
>
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: python: sqlalchemy - how do I ensure connection not stale using new event system
I tried to include pool_recycle = 10 (seconds) in my create_engine call but
that doesn't fix the problem. I still get the same error. Hmm
On Monday, April 1, 2013 7:04:48 PM UTC-7, algot...@gmail.com wrote:
>
> I am using the sqlalchemy package in python. I have an operation that
> takes some time to execute after I perform an autoload on an existing
> table. This causes the following error when I attempt to use the connection:
>
> sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has
> gone away')
>
> I have a simple utility function that performs an insert many:
>
> def insert_data(data_2_insert, table_name):
> engine = create_engine('mysql://blah:blah123@localhost/dbname')
> # Metadata is a Table catalog.
> metadata = MetaData()
> table = Table(table_name, metadata, autoload=True, autoload_with=engine)
> for c in mytable.c:
> print c
> column_names = tuple(c.name for c in mytable.c)
> final_data = [dict(zip(column_names, x)) for x in data_2_insert]
> ins = mytable.insert()
> conn = engine.connect()
> conn.execute(ins, final_data)
> conn.close()
>
>
> It is the following line that times long time to execute since
> 'data_2_insert' has 677,161 rows.
>
> final_data = [dict(zip(column_names, x)) for x in data_2_insert]
>
> I came across the following post below which refers to a similar problem,
> however I am not sure how to implement the connection management suggested
> as I am quite a newbie.
>
> http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy
>
> Note for SQLAlchemy 0.7 - PoolListener is deprecated, but The same solution
> can be implemented using the new event system. – robots.jpg
>
> Here is the link to the new event system described by one of the users:
>
> http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
>
> If someone can please show me a couple of pointers on how I could go about
> integrating the suggestions into the way I use sqlalchemy I would be very
> appreciative. Thank you.
>
>
>
>
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: python: sqlalchemy - how do I ensure connection not stale using new event system
Below is the traceback. When it attempts to perform the conn.execute(),
which is the db insert is where it seems to realize the connection is
stale.
Traceback (most recent call last):
File "myscript.py", line 126, in
main()
File "myscript.py", line 33, in main
insert_data(final_data, table_name)
File "myscript.py", line 122, in insert_data
conn.execute(ins, final_data)
File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line
824, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line
874, in _execute_clauseelement
return self.__execute_context(context)
File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line
894, in __execute_context
self._cursor_executemany(context.cursor, context.statement,
context.parameters, context=context)
File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line
960, in _cursor_executemany
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server
has gone away') u'INSERT INTO...
On Monday, April 1, 2013 7:04:48 PM UTC-7, algot...@gmail.com wrote:
>
> I am using the sqlalchemy package in python. I have an operation that
> takes some time to execute after I perform an autoload on an existing
> table. This causes the following error when I attempt to use the connection:
>
> sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has
> gone away')
>
> I have a simple utility function that performs an insert many:
>
> def insert_data(data_2_insert, table_name):
> engine = create_engine('mysql://blah:blah123@localhost/dbname')
> # Metadata is a Table catalog.
> metadata = MetaData()
> table = Table(table_name, metadata, autoload=True, autoload_with=engine)
> for c in mytable.c:
> print c
> column_names = tuple(c.name for c in mytable.c)
> final_data = [dict(zip(column_names, x)) for x in data_2_insert]
> ins = mytable.insert()
> conn = engine.connect()
> conn.execute(ins, final_data)
> conn.close()
>
>
> It is the following line that times long time to execute since
> 'data_2_insert' has 677,161 rows.
>
> final_data = [dict(zip(column_names, x)) for x in data_2_insert]
>
> I came across the following post below which refers to a similar problem,
> however I am not sure how to implement the connection management suggested
> as I am quite a newbie.
>
> http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy
>
> Note for SQLAlchemy 0.7 - PoolListener is deprecated, but The same solution
> can be implemented using the new event system. – robots.jpg
>
> Here is the link to the new event system described by one of the users:
>
> http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
>
> If someone can please show me a couple of pointers on how I could go about
> integrating the suggestions into the way I use sqlalchemy I would be very
> appreciative. Thank you.
>
>
>
>
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] unexpected behavior with the orm
Mike, thanks for the clarification. I never noticed that particular implication of the default cascade! On Mon, Apr 1, 2013 at 1:23 PM, Michael Bayer wrote: > > On Apr 1, 2013, at 2:10 PM, Michael Merickel wrote: > > > I ran into a situation the other day where I would create a new object > but copy over some properties from an earlier version of the object. To do > this, I first created the new object, then I queried the database for the > latest copy of the object, copied properties to the new object, then added > the new object to the session and committed. I was very surprised to > discover that the new object (which was not added to the session) was > returned from the database query as the "latest" object. > > > > bar = session.query(Bar).first() > > > > new_foo = Foo() > > new_foo.bar = bar # apparently causes new_obj to be added to the session > > this behavior is known as "cascade", and is described in detail here: > http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#cascades . It > can be fully customized, but it seems like you are at least expecting basic > forwards-direction "save-update" cascade to occur. > > Cascade also takes place during backref events. The specific behavior > regarding bi-directional cascades due to backrefs, as well as controlling > their behavior using the "cascade_backrefs" flag, is here: > > > http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#controlling-cascade-on-backrefs > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Update object with results of a server-side processing [pgsql]
Wonderful! Thanks a lot. 2013/4/2 Michael Bayer > The insert() construct supports a call called returning() to emit whatever > RETURNING you want, but if you're using the ORM, then the insert() > construct is generated by your mappings. In this case, the ORM right now > favors being able to batch the INSERT statements together into an > executemany(), which doesn't support RETURNING, so as far as it using > RETURNING specifically for non primary-key defaults, the ORM isn't quite > set up for that yet. > > But, that doesn't mean you can't get those values automatically. Any > column that's not passed in and is marked as having a "server default" will > be queued up to fetch automatically as soon as you access it. The > FetchedValue construct is used as this marker when you're dealing with > something like a trigger: > > from sqlalchemy import FetchedValue > > class MyClass(Base): ># ... > >some_col = Column(Integer, server_default=FetchedValue()) > > > > http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#triggered-columns > > On Apr 1, 2013, at 5:26 PM, Alexey Vihorev wrote: > > Hi! > I've got a server-side trigger function (before insert) - it changes some > fields of the inserted record, and I need this info back in my SA entity > object (akin to what SA does with ID's). SA uses RETURNING whenever it is > supported, maybe I can use it as well? Or am I limited to refreshing the > object manually via session.refresh()? > Thanks! > > -- > 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 tosqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- Алексей Вихорев -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
