5
============================
6
Frequently Asked Questions
7
============================
18
How do I configure logging?
19
---------------------------
21
See :ref:`dbengine_logging`.
23
How do I pool database connections? Are my connections pooled?
24
----------------------------------------------------------------
26
SQLAlchemy performs application-level connection pooling automatically
27
in most cases. With the exception of SQLite, a :class:`.Engine` object
28
refers to a :class:`.QueuePool` as a source of connectivity.
30
For more detail, see :ref:`engines_toplevel` and :ref:`pooling_toplevel`.
32
How do I pass custom connect arguments to my database API?
33
-----------------------------------------------------------
35
The :func:`.create_engine` call accepts additional arguments either
36
directly via the ``connect_args`` keyword argument::
38
e = create_engine("mysql://scott:tiger@localhost/test",
39
connect_args={"encoding": "utf8"})
41
Or for basic string and integer arguments, they can usually be specified
42
in the query string of the URL::
44
e = create_engine("mysql://scott:tiger@localhost/test?encoding=utf8")
48
:ref:`custom_dbapi_args`
50
"MySQL Server has gone away"
51
----------------------------
53
There are two major causes for this error:
55
1. The MySQL client closes connections which have been idle for a set period
56
of time, defaulting to eight hours. This can be avoided by using the ``pool_recycle``
57
setting with :func:`.create_engine`, described at :ref:`mysql_connection_timeouts`.
59
2. Usage of the MySQLdb :term:`DBAPI`, or a similar DBAPI, in a non-threadsafe manner, or in an otherwise
60
inappropriate way. The MySQLdb connection object is not threadsafe - this expands
61
out to any SQLAlchemy system that links to a single connection, which includes the ORM
62
:class:`.Session`. For background
63
on how :class:`.Session` should be used in a multithreaded environment,
64
see :ref:`session_faq_threadsafe`.
66
Why does SQLAlchemy issue so many ROLLBACKs?
67
---------------------------------------------
69
SQLAlchemy currently assumes DBAPI connections are in "non-autocommit" mode -
70
this is the default behavior of the Python database API, meaning it
71
must be assumed that a transaction is always in progress. The
72
connection pool issues ``connection.rollback()`` when a connection is returned.
73
This is so that any transactional resources remaining on the connection are
74
released. On a database like Postgresql or MSSQL where table resources are
75
aggressively locked, this is critical so that rows and tables don't remain
76
locked within connections that are no longer in use. An application can
77
otherwise hang. It's not just for locks, however, and is equally critical on
78
any database that has any kind of transaction isolation, including MySQL with
79
InnoDB. Any connection that is still inside an old transaction will return
80
stale data, if that data was already queried on that connection within
81
isolation. For background on why you might see stale data even on MySQL, see
82
http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html
84
I'm on MyISAM - how do I turn it off?
85
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
87
The behavior of the connection pool's connection return behavior can be
88
configured using ``reset_on_return``::
90
from sqlalchemy import create_engine
91
from sqlalchemy.pool import QueuePool
93
engine = create_engine('mysql://scott:tiger@localhost/myisam_database', pool=QueuePool(reset_on_return=False))
95
I'm on SQL Server - how do I turn those ROLLBACKs into COMMITs?
96
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
98
``reset_on_return`` accepts the values ``commit``, ``rollback`` in addition
99
to ``True``, ``False``, and ``None``. Setting to ``commit`` will cause
100
a COMMIT as any connection is returned to the pool::
102
engine = create_engine('mssql://scott:tiger@mydsn', pool=QueuePool(reset_on_return='commit'))
105
I am using multiple connections with a SQLite database (typically to test transaction operation), and my test program is not working!
106
----------------------------------------------------------------------------------------------------------------------------------------------------------
108
If using a SQLite ``:memory:`` database, or a version of SQLAlchemy prior
109
to version 0.7, the default connection pool is the :class:`.SingletonThreadPool`,
110
which maintains exactly one SQLite connection per thread. So two
111
connections in use in the same thread will actually be the same SQLite
112
connection. Make sure you're not using a :memory: database and
113
use :class:`.NullPool`, which is the default for non-memory databases in
114
current SQLAlchemy versions.
118
:ref:`pysqlite_threading_pooling` - info on PySQLite's behavior.
120
How do I get at the raw DBAPI connection when using an Engine?
121
--------------------------------------------------------------
123
With a regular SA engine-level Connection, you can get at a pool-proxied
124
version of the DBAPI connection via the :attr:`.Connection.connection` attribute on
125
:class:`.Connection`, and for the really-real DBAPI connection you can call the
126
:attr:`.ConnectionFairy.connection` attribute on that - but there should never be any need to access
127
the non-pool-proxied DBAPI connection, as all methods are proxied through::
129
engine = create_engine(...)
130
conn = engine.connect()
131
conn.connection.<do DBAPI things>
132
cursor = conn.connection.cursor(<DBAPI specific arguments..>)
134
You must ensure that you revert any isolation level settings or other
135
operation-specific settings on the connection back to normal before returning
138
As an alternative to reverting settings, you can call the :meth:`.Connection.detach` method on
139
either :class:`.Connection` or the proxied connection, which will de-associate
140
the connection from the pool such that it will be closed and discarded
141
when :meth:`.Connection.close` is called::
143
conn = engine.connect()
144
conn.detach() # detaches the DBAPI connection from the connection pool
145
conn.connection.<go nuts>
146
conn.close() # connection is closed for real, the pool replaces it with a new connection
151
My program is hanging when I say ``table.drop()`` / ``metadata.drop_all()``
152
----------------------------------------------------------------------------
154
This usually corresponds to two conditions: 1. using PostgreSQL, which is really
155
strict about table locks, and 2. you have a connection still open which
156
contains locks on the table and is distinct from the connection being used for
157
the DROP statement. Heres the most minimal version of the pattern::
159
connection = engine.connect()
160
result = connection.execute(mytable.select())
164
Above, a connection pool connection is still checked out; furthermore, the
165
result object above also maintains a link to this connection. If
166
"implicit execution" is used, the result will hold this connection opened until
167
the result object is closed or all rows are exhausted.
169
The call to ``mytable.drop(engine)`` attempts to emit DROP TABLE on a second
170
connection procured from the :class:`.Engine` which will lock.
172
The solution is to close out all connections before emitting DROP TABLE::
174
connection = engine.connect()
175
result = connection.execute(mytable.select())
177
# fully read result sets
183
# now locks are removed
186
Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?
187
-----------------------------------------------------------------------------------------------
189
General ALTER support isn't present in SQLAlchemy directly. For special DDL
190
on an ad-hoc basis, the :class:`.DDL` and related constructs can be used.
191
See :doc:`core/ddl` for a discussion on this subject.
193
A more comprehensive option is to use schema migration tools, such as Alembic
194
or SQLAlchemy-Migrate; see :ref:`schema_migrations` for discussion on this.
196
How can I sort Table objects in order of their dependency?
197
-----------------------------------------------------------
199
This is available via the :attr:`.MetaData.sorted_tables` function::
201
metadata = MetaData()
202
# ... add Table objects to metadata
203
ti = metadata.sorted_tables:
207
How can I get the CREATE TABLE/ DROP TABLE output as a string?
208
---------------------------------------------------------------
210
Modern SQLAlchemy has clause constructs which represent DDL operations. These
211
can be rendered to strings like any other SQL expression::
213
from sqlalchemy.schema import CreateTable
215
print CreateTable(mytable)
217
To get the string specific to a certain engine::
219
print CreateTable(mytable).compile(engine)
221
There's also a special form of :class:`.Engine` that can let you dump an entire
222
metadata creation sequence, using this recipe::
224
def dump(sql, *multiparams, **params):
225
print sql.compile(dialect=engine.dialect)
226
engine = create_engine('postgresql://', strategy='mock', executor=dump)
227
metadata.create_all(engine, checkfirst=False)
229
The `Alembic <https://bitbucket.org/zzzeek/alembic>`_ tool also supports
230
an "offline" SQL generation mode that renders database migrations as SQL scripts.
232
How can I subclass Table/Column to provide certain behaviors/configurations?
233
------------------------------------------------------------------------------
235
:class:`.Table` and :class:`.Column` are not good targets for direct subclassing.
236
However, there are simple ways to get on-construction behaviors using creation
237
functions, and behaviors related to the linkages between schema objects such as
238
constraint conventions or naming conventions using attachment events.
239
An example of many of these
240
techniques can be seen at `Naming Conventions <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions>`_.
246
Why does ``.col.in_([])`` Produce ``col != col``? Why not ``1=0``?
247
-------------------------------------------------------------------
249
A little introduction to the issue. The IN operator in SQL, given a list of
250
elements to compare against a column, generally does not accept an empty list,
251
that is while it is valid to say::
255
it's not valid to say::
259
SQLAlchemy's :meth:`.Operators.in_` operator, when given an empty list, produces this
264
As of version 0.6, it also produces a warning stating that a less efficient
265
comparison operation will be rendered. This expression is the only one that is
266
both database agnostic and produces correct results.
268
For example, the naive approach of "just evaluate to false, by comparing 1=0
269
or 1!=1", does not handle nulls properly. An expression like::
273
will not return a row when "column" is null, but an expression which does not
274
take the column into account::
280
Closer to the mark is the following CASE expression::
282
CASE WHEN column IS NOT NULL THEN 1=0 ELSE NULL END
284
We don't use this expression due to its verbosity, and its also not
285
typically accepted by Oracle within a WHERE clause - depending
286
on how you phrase it, you'll either get "ORA-00905: missing keyword" or
287
"ORA-00920: invalid relational operator". It's also still less efficient than
288
just rendering SQL without the clause altogether (or not issuing the SQL at
289
all, if the statement is just a simple search).
291
The best approach therefore is to avoid the usage of IN given an argument list
292
of zero length. Instead, don't emit the Query in the first place, if no rows
293
should be returned. The warning is best promoted to a full error condition
294
using the Python warnings filter (see http://docs.python.org/library/warnings.html).
299
.. _faq_mapper_primary_key:
301
How do I map a table that has no primary key?
302
---------------------------------------------
304
The SQLAlchemy ORM, in order to map to a particular table, needs there to be
305
at least one column denoted as a primary key column; multiple-column,
306
i.e. composite, primary keys are of course entirely feasible as well. These
307
columns do **not** need to be actually known to the database as primary key
308
columns, though it's a good idea that they are. It's only necessary that the columns
309
*behave* as a primary key does, e.g. as a unique and not nullable identifier
312
Most ORMs require that objects have some kind of primary key defined
313
because the object in memory must correspond to a uniquely identifiable
314
row in the database table; at the very least, this allows the
315
object can be targeted for UPDATE and DELETE statements which will affect only
316
that object's row and no other. However, the importance of the primary key
317
goes far beyond that. In SQLAlchemy, all ORM-mapped objects are at all times
318
linked uniquely within a :class:`.Session`
319
to their specific database row using a pattern called the :term:`identity map`,
320
a pattern that's central to the unit of work system employed by SQLAlchemy,
321
and is also key to the most common (and not-so-common) patterns of ORM usage.
326
It's important to note that we're only talking about the SQLAlchemy ORM; an
327
application which builds on Core and deals only with :class:`.Table` objects,
328
:func:`.select` constructs and the like, **does not** need any primary key
329
to be present on or associated with a table in any way (though again, in SQL, all tables
330
should really have some kind of primary key, lest you need to actually
331
update or delete specific rows).
333
In almost all cases, a table does have a so-called :term:`candidate key`, which is a column or series
334
of columns that uniquely identify a row. If a table truly doesn't have this, and has actual
335
fully duplicate rows, the table is not corresponding to `first normal form <http://en.wikipedia.org/wiki/First_normal_form>`_ and cannot be mapped. Otherwise, whatever columns comprise the best candidate key can be
336
applied directly to the mapper::
338
class SomeClass(Base):
339
__table__ = some_table_with_no_pk
341
'primary_key':[some_table_with_no_pk.c.uid, some_table_with_no_pk.c.bar]
344
Better yet is when using fully declared table metadata, use the ``primary_key=True``
345
flag on those columns::
347
class SomeClass(Base):
348
__tablename__ = "some_table_with_no_pk"
350
uid = Column(Integer, primary_key=True)
351
bar = Column(String, primary_key=True)
353
All tables in a relational database should have primary keys. Even a many-to-many
354
association table - the primary key would be the composite of the two association
357
CREATE TABLE my_association (
358
user_id INTEGER REFERENCES user(id),
359
account_id INTEGER REFERENCES account(id),
360
PRIMARY KEY (user_id, account_id)
364
How do I configure a Column that is a Python reserved word or similar?
365
----------------------------------------------------------------------------
367
Column-based attributes can be given any name desired in the mapping. See
368
:ref:`mapper_column_distinct_names`.
370
How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?
371
-------------------------------------------------------------------------------------------------
373
This information is all available from the :class:`.Mapper` object.
375
To get at the :class:`.Mapper` for a particular mapped class, call the
376
:func:`.inspect` function on it::
378
from sqlalchemy import inspect
380
mapper = inspect(MyClass)
382
From there, all information about the class can be acquired using such methods as:
384
* :attr:`.Mapper.attrs` - a namespace of all mapped attributes. The attributes
385
themselves are instances of :class:`.MapperProperty`, which contain additional
386
attributes that can lead to the mapped SQL expression or column, if applicable.
388
* :attr:`.Mapper.column_attrs` - the mapped attribute namespace
389
limited to column and SQL expression attributes. You might want to use
390
:attr:`.Mapper.columns` to get at the :class:`.Column` objects directly.
392
* :attr:`.Mapper.relationships` - namespace of all :class:`.RelationshipProperty` attributes.
394
* :attr:`.Mapper.all_orm_descriptors` - namespace of all mapped attributes, plus user-defined
395
attributes defined using systems such as :class:`.hybrid_property`, :class:`.AssociationProxy` and others.
397
* :attr:`.Mapper.columns` - A namespace of :class:`.Column` objects and other named
398
SQL expressions associated with the mapping.
400
* :attr:`.Mapper.mapped_table` - The :class:`.Table` or other selectable to which
401
this mapper is mapped.
403
* :attr:`.Mapper.local_table` - The :class:`.Table` that is "local" to this mapper;
404
this differs from :attr:`.Mapper.mapped_table` in the case of a mapper mapped
405
using inheritance to a composed selectable.
407
I'm using Declarative and setting primaryjoin/secondaryjoin using an ``and_()`` or ``or_()``, and I am getting an error message about foreign keys.
408
------------------------------------------------------------------------------------------------------------------------------------------------------------------
410
Are you doing this?::
415
foo = relationship("Dest", primaryjoin=and_("MyClass.id==Dest.foo_id", "MyClass.foo==Dest.bar"))
417
That's an ``and_()`` of two string expressions, which SQLAlchemy cannot apply any mapping towards. Declarative allows :func:`.relationship` arguments to be specified as strings, which are converted into expression objects using ``eval()``. But this doesn't occur inside of an ``and_()`` expression - it's a special operation declarative applies only to the *entirety* of what's passed to primaryjoin or other arguments as a string::
422
foo = relationship("Dest", primaryjoin="and_(MyClass.id==Dest.foo_id, MyClass.foo==Dest.bar)")
424
Or if the objects you need are already available, skip the strings::
429
foo = relationship(Dest, primaryjoin=and_(MyClass.id==Dest.foo_id, MyClass.foo==Dest.bar))
431
The same idea applies to all the other arguments, such as ``foreign_keys``::
434
foo = relationship(Dest, foreign_keys=["Dest.foo_id", "Dest.bar_id"])
437
foo = relationship(Dest, foreign_keys="[Dest.foo_id, Dest.bar_id]")
440
foo = relationship(Dest, foreign_keys=[Dest.foo_id, Dest.bar_id])
442
# if you're using columns from the class that you're inside of, just use the column objects !
448
foo = relationship(Dest, foreign_keys=[foo_id, bar_id])
454
"This Session's transaction has been rolled back due to a previous exception during flush." (or similar)
455
---------------------------------------------------------------------------------------------------------
457
This is an error that occurs when a :meth:`.Session.flush` raises an exception, rolls back
458
the transaction, but further commands upon the `Session` are called without an
459
explicit call to :meth:`.Session.rollback` or :meth:`.Session.close`.
461
It usually corresponds to an application that catches an exception
462
upon :meth:`.Session.flush` or :meth:`.Session.commit` and
463
does not properly handle the exception. For example::
465
from sqlalchemy import create_engine, Column, Integer
466
from sqlalchemy.orm import sessionmaker
467
from sqlalchemy.ext.declarative import declarative_base
469
Base = declarative_base(create_engine('sqlite://'))
472
__tablename__ = 'foo'
473
id = Column(Integer, primary_key=True)
475
Base.metadata.create_all()
477
session = sessionmaker()()
479
# constraint violation
480
session.add_all([Foo(id=1), Foo(id=1)])
488
# continue using session without rolling back
492
The usage of the :class:`.Session` should fit within a structure similar to this::
501
session.close() # optional, depends on use case
503
Many things can cause a failure within the try/except besides flushes. You
504
should always have some kind of "framing" of your session operations so that
505
connection and transaction resources have a definitive boundary, otherwise
506
your application doesn't really have its usage of resources under control.
507
This is not to say that you need to put try/except blocks all throughout your
508
application - on the contrary, this would be a terrible idea. You should
509
architect your application such that there is one (or few) point(s) of
510
"framing" around session operations.
512
For a detailed discussion on how to organize usage of the :class:`.Session`,
513
please see :ref:`session_faq_whentocreate`.
515
But why does flush() insist on issuing a ROLLBACK?
516
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
518
It would be great if :meth:`.Session.flush` could partially complete and then not roll
519
back, however this is beyond its current capabilities since its internal
520
bookkeeping would have to be modified such that it can be halted at any time
521
and be exactly consistent with what's been flushed to the database. While this
522
is theoretically possible, the usefulness of the enhancement is greatly
523
decreased by the fact that many database operations require a ROLLBACK in any
524
case. Postgres in particular has operations which, once failed, the
525
transaction is not allowed to continue::
527
test=> create table foo(id integer primary key);
528
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
532
test=> insert into foo values(1);
538
test=> insert into foo values(1);
539
ERROR: duplicate key value violates unique constraint "foo_pkey"
540
test=> insert into foo values(2);
541
ERROR: current transaction is aborted, commands ignored until end of transaction block
543
What SQLAlchemy offers that solves both issues is support of SAVEPOINT, via
544
:meth:`.Session.begin_nested`. Using :meth:`.Session.begin_nested`, you can frame an operation that may
545
potentially fail within a transaction, and then "roll back" to the point
546
before its failure while maintaining the enclosing transaction.
548
But why isn't the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?
549
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
551
This is again a matter of the :class:`.Session` providing a consistent interface and
552
refusing to guess about what context its being used. For example, the
553
:class:`.Session` supports "framing" above within multiple levels. Such as, suppose
554
you had a decorator ``@with_session()``, which did this::
556
def with_session(fn):
558
session.begin(subtransactions=True)
560
ret = fn(*args, **kw)
568
The above decorator begins a transaction if one does not exist already, and
569
then commits it, if it were the creator. The "subtransactions" flag means that
570
if :meth:`.Session.begin` were already called by an enclosing function, nothing happens
571
except a counter is incremented - this counter is decremented when :meth:`.Session.commit`
572
is called and only when it goes back to zero does the actual COMMIT happen. It
573
allows this usage pattern::
589
``one()`` can call ``two()``, or ``two()`` can be called by itself, and the
590
``@with_session`` decorator ensures the appropriate "framing" - the transaction
591
boundaries stay on the outermost call level. As you can see, if ``two()`` calls
592
``flush()`` which throws an exception and then issues a ``rollback()``, there will
593
*always* be a second ``rollback()`` performed by the decorator, and possibly a
594
third corresponding to two levels of decorator. If the ``flush()`` pushed the
595
``rollback()`` all the way out to the top of the stack, and then we said that
596
all remaining ``rollback()`` calls are moot, there is some silent behavior going
597
on there. A poorly written enclosing method might suppress the exception, and
598
then call ``commit()`` assuming nothing is wrong, and then you have a silent
599
failure condition. The main reason people get this error in fact is because
600
they didn't write clean "framing" code and they would have had other problems
603
If you think the above use case is a little exotic, the same kind of thing
604
comes into play if you want to SAVEPOINT- you might call ``begin_nested()``
605
several times, and the ``commit()``/``rollback()`` calls each resolve the most
606
recent ``begin_nested()``. The meaning of ``rollback()`` or ``commit()`` is
607
dependent upon which enclosing block it is called, and you might have any
608
sequence of ``rollback()``/``commit()`` in any order, and its the level of nesting
609
that determines their behavior.
611
In both of the above cases, if ``flush()`` broke the nesting of transaction
612
blocks, the behavior is, depending on scenario, anywhere from "magic" to
613
silent failure to blatant interruption of code flow.
615
``flush()`` makes its own "subtransaction", so that a transaction is started up
616
regardless of the external transactional state, and when complete it calls
617
``commit()``, or ``rollback()`` upon failure - but that ``rollback()`` corresponds
618
to its own subtransaction - it doesn't want to guess how you'd like to handle
619
the external "framing" of the transaction, which could be nested many levels
620
with any combination of subtransactions and real SAVEPOINTs. The job of
621
starting/ending the "frame" is kept consistently with the code external to the
622
``flush()``, and we made a decision that this was the most consistent approach.
624
I'm inserting 400,000 rows with the ORM and it's really slow!
625
--------------------------------------------------------------
627
The SQLAlchemy ORM uses the :term:`unit of work` pattern when synchronizing
628
changes to the database. This pattern goes far beyond simple "inserts"
629
of data. It includes that attributes which are assigned on objects are
630
received using an attribute instrumentation system which tracks
631
changes on objects as they are made, includes that all rows inserted
632
are tracked in an identity map which has the effect that for each row
633
SQLAlchemy must retrieve its "last inserted id" if not already given,
634
and also involves that rows to be inserted are scanned and sorted for
635
dependencies as needed. Objects are also subject to a fair degree of
636
bookkeeping in order to keep all of this running, which for a very
637
large number of rows at once can create an inordinate amount of time
638
spent with large data structures, hence it's best to chunk these.
640
Basically, unit of work is a large degree of automation in order to
641
automate the task of persisting a complex object graph into a
642
relational database with no explicit persistence code, and this
643
automation has a price.
645
ORMs are basically not intended for high-performance bulk inserts -
646
this is the whole reason SQLAlchemy offers the Core in addition to the
647
ORM as a first-class component.
649
For the use case of fast bulk inserts, the
650
SQL generation and execution system that the ORM builds on top of
651
is part of the Core. Using this system directly, we can produce an INSERT that
652
is competitive with using the raw database API directly.
654
The example below illustrates time-based tests for four different
655
methods of inserting rows, going from the most automated to the least.
656
With cPython 2.7, runtimes observed::
658
classics-MacBook-Pro:sqlalchemy classic$ python test.py
659
SQLAlchemy ORM: Total time for 100000 records 14.3528850079 secs
660
SQLAlchemy ORM pk given: Total time for 100000 records 10.0164160728 secs
661
SQLAlchemy Core: Total time for 100000 records 0.775382995605 secs
662
sqlite3: Total time for 100000 records 0.676795005798 sec
664
We can reduce the time by a factor of three using recent versions of `Pypy <http://pypy.org/>`_::
666
classics-MacBook-Pro:sqlalchemy classic$ /usr/local/src/pypy-2.1-beta2-osx64/bin/pypy test.py
667
SQLAlchemy ORM: Total time for 100000 records 5.88369488716 secs
668
SQLAlchemy ORM pk given: Total time for 100000 records 3.52294301987 secs
669
SQLAlchemy Core: Total time for 100000 records 0.613556146622 secs
670
sqlite3: Total time for 100000 records 0.442467927933 sec
677
from sqlalchemy.ext.declarative import declarative_base
678
from sqlalchemy import Column, Integer, String, create_engine
679
from sqlalchemy.orm import scoped_session, sessionmaker
681
Base = declarative_base()
682
DBSession = scoped_session(sessionmaker())
685
class Customer(Base):
686
__tablename__ = "customer"
687
id = Column(Integer, primary_key=True)
688
name = Column(String(255))
690
def init_sqlalchemy(dbname='sqlite:///sqlalchemy.db'):
692
engine = create_engine(dbname, echo=False)
694
DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)
695
Base.metadata.drop_all(engine)
696
Base.metadata.create_all(engine)
698
def test_sqlalchemy_orm(n=100000):
702
customer = Customer()
703
customer.name = 'NAME ' + str(i)
704
DBSession.add(customer)
708
print("SQLAlchemy ORM: Total time for " + str(n) +
709
" records " + str(time.time() - t0) + " secs")
711
def test_sqlalchemy_orm_pk_given(n=100000):
715
customer = Customer(id=i+1, name="NAME " + str(i))
716
DBSession.add(customer)
720
print("SQLAlchemy ORM pk given: Total time for " + str(n) +
721
" records " + str(time.time() - t0) + " secs")
723
def test_sqlalchemy_core(n=100000):
727
Customer.__table__.insert(),
728
[{"name": 'NAME ' + str(i)} for i in range(n)]
730
print("SQLAlchemy Core: Total time for " + str(n) +
731
" records " + str(time.time() - t0) + " secs")
733
def init_sqlite3(dbname):
734
conn = sqlite3.connect(dbname)
736
c.execute("DROP TABLE IF EXISTS customer")
737
c.execute("CREATE TABLE customer (id INTEGER NOT NULL, "
738
"name VARCHAR(255), PRIMARY KEY(id))")
742
def test_sqlite3(n=100000, dbname='sqlite3.db'):
743
conn = init_sqlite3(dbname)
747
row = ('NAME ' + str(i),)
748
c.execute("INSERT INTO customer (name) VALUES (?)", row)
750
print("sqlite3: Total time for " + str(n) +
751
" records " + str(time.time() - t0) + " sec")
753
if __name__ == '__main__':
754
test_sqlalchemy_orm(100000)
755
test_sqlalchemy_orm_pk_given(100000)
756
test_sqlalchemy_core(100000)
761
How do I make a Query that always adds a certain filter to every query?
762
------------------------------------------------------------------------------------------------
764
See the recipe at `PreFilteredQuery <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery>`_.
766
I've created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?
767
------------------------------------------------------------------------------------------------------------------
769
Rows returned by an outer join may contain NULL for part of the primary key,
770
as the primary key is the composite of both tables. The :class:`.Query` object ignores incoming rows
771
that don't have an acceptable primary key. Based on the setting of the ``allow_partial_pks``
772
flag on :func:`.mapper`, a primary key is accepted if the value has at least one non-NULL
773
value, or alternatively if the value has no NULL values. See ``allow_partial_pks``
777
I'm using ``joinedload()`` or ``lazy=False`` to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)
778
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
780
The joins generated by joined eager loading are only used to fully load related
781
collections, and are designed to have no impact on the primary results of the query.
782
Since they are anonymously aliased, they cannot be referenced directly.
784
For detail on this beahvior, see :doc:`orm/loading`.
786
Query has no ``__len__()``, why not?
787
------------------------------------
789
The Python ``__len__()`` magic method applied to an object allows the ``len()``
790
builtin to be used to determine the length of the collection. It's intuitive
791
that a SQL query object would link ``__len__()`` to the :meth:`.Query.count`
792
method, which emits a `SELECT COUNT`. The reason this is not possible is
793
because evaluating the query as a list would incur two SQL calls instead of
796
class Iterates(object):
803
return iter([1, 2, 3, 4, 5])
812
How Do I use Textual SQL with ORM Queries?
813
-------------------------------------------
817
* :ref:`orm_tutorial_literal_sql` - Ad-hoc textual blocks with :class:`.Query`
819
* :ref:`session_sql_expressions` - Using :class:`.Session` with textual SQL directly.
821
I'm calling ``Session.delete(myobject)`` and it isn't removed from the parent collection!
822
------------------------------------------------------------------------------------------
824
See :ref:`session_deleting_from_collections` for a description of this behavior.
826
why isnt my ``__init__()`` called when I load objects?
827
------------------------------------------------------
829
See :ref:`mapping_constructors` for a description of this behavior.
831
how do I use ON DELETE CASCADE with SA's ORM?
832
----------------------------------------------
834
SQLAlchemy will always issue UPDATE or DELETE statements for dependent
835
rows which are currently loaded in the :class:`.Session`. For rows which
836
are not loaded, it will by default issue SELECT statements to load
837
those rows and udpate/delete those as well; in other words it assumes
838
there is no ON DELETE CASCADE configured.
839
To configure SQLAlchemy to cooperate with ON DELETE CASCADE, see
840
:ref:`passive_deletes`.
842
I set the "foo_id" attribute on my instance to "7", but the "foo" attribute is still ``None`` - shouldn't it have loaded Foo with id #7?
843
----------------------------------------------------------------------------------------------------------------------------------------------------
845
The ORM is not constructed in such a way as to support
846
immediate population of relationships driven from foreign
847
key attribute changes - instead, it is designed to work the
848
other way around - foreign key attributes are handled by the
849
ORM behind the scenes, the end user sets up object
850
relationships naturally. Therefore, the recommended way to
851
set ``o.foo`` is to do just that - set it!::
853
foo = Session.query(Foo).get(7)
857
Manipulation of foreign key attributes is of course entirely legal. However,
858
setting a foreign-key attribute to a new value currently does not trigger
859
an "expire" event of the :func:`.relationship` in which it's involved. This means
860
that for the following sequence::
862
o = Session.query(SomeClass).first()
863
assert o.foo is None # accessing an un-set attribute sets it to None
866
``o.foo`` is initialized to ``None`` when we first accessed it. Setting
867
``o.foo_id = 7`` will have the value of "7" as pending, but no flush
868
has occurred - so ``o.foo`` is still ``None``::
870
# attribute is already set to None, has not been
871
# reconciled with o.foo_id = 7 yet
874
For ``o.foo`` to load based on the foreign key mutation is usually achieved
875
naturally after the commit, which both flushes the new foreign key value
876
and expires all state::
878
Session.commit() # expires all attributes
880
foo_7 = Session.query(Foo).get(7)
882
assert o.foo is foo_7 # o.foo lazyloads on access
884
A more minimal operation is to expire the attribute individually - this can
885
be performed for any :term:`persistent` object using :meth:`.Session.expire`::
887
o = Session.query(SomeClass).first()
889
Session.expire(o, ['foo']) # object must be persistent for this
891
foo_7 = Session.query(Foo).get(7)
893
assert o.foo is foo_7 # o.foo lazyloads on access
895
Note that if the object is not persistent but present in the :class:`.Session`,
896
it's known as :term:`pending`. This means the row for the object has not been
897
INSERTed into the database yet. For such an object, setting ``foo_id`` does not
898
have meaning until the row is inserted; otherwise there is no row yet::
900
new_obj = SomeClass()
905
# accessing an un-set attribute sets it to None
906
assert new_obj.foo is None
908
Session.flush() # emits INSERT
910
# expire this because we already set .foo to None
911
Session.expire(o, ['foo'])
913
assert new_obj.foo is foo_7 # now it loads
916
.. topic:: Attribute loading for non-persistent objects
918
One variant on the "pending" behavior above is if we use the flag
919
``load_on_pending`` on :func:`.relationship`. When this flag is set, the
920
lazy loader will emit for ``new_obj.foo`` before the INSERT proceeds; another
921
variant of this is to use the :meth:`.Session.enable_relationship_loading`
922
method, which can "attach" an object to a :class:`.Session` in such a way that
923
many-to-one relationships load as according to foreign key attributes
924
regardless of the object being in any particular state.
925
Both techniques are **not recommended for general use**; they were added to suit
926
specfic programming scenarios encountered by users which involve the repurposing
927
of the ORM's usual object states.
929
The recipe `ExpireRelationshipOnFKChange <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ExpireRelationshipOnFKChange>`_ features an example using SQLAlchemy events
930
in order to coordinate the setting of foreign key attributes with many-to-one
933
Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?
934
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
936
When people read the many-to-many example in the docs, they get hit with the
937
fact that if you create the same ``Keyword`` twice, it gets put in the DB twice.
938
Which is somewhat inconvenient.
940
This `UniqueObject <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject>`_ recipe was created to address this issue.
added
removed
doc/_sources/faq.txt
