From d533b8e9223b9c938655e5b666fc928e2d996cd3 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 10 Dec 2015 17:09:47 -0500 Subject: [PATCH] - The ORM and Core tutorials, which have always been in doctest format, are now exercised within the normal unit test suite in both Python 2 and Python 3. - remove the old testdocs.py runner and replace with test/base/test_tutorials.py - use pytest's unicode fixer so that we can test for unicode strings in both py2k/3k - use py3k format overall for prints, exceptions - add other fixers to guarantee deterministic results - add skips and ellipses to outputs that aren't worth matching --- doc/build/changelog/changelog_10.rst | 8 + doc/build/core/tutorial.rst | 192 ++++++++++++------------ doc/build/orm/tutorial.rst | 209 +++++++++++++-------------- doc/build/testdocs.py | 69 --------- test/base/test_tutorials.py | 144 ++++++++++++++++++ 5 files changed, 347 insertions(+), 275 deletions(-) delete mode 100644 doc/build/testdocs.py create mode 100644 test/base/test_tutorials.py diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst index a07f2db213..f271832774 100644 --- a/doc/build/changelog/changelog_10.rst +++ b/doc/build/changelog/changelog_10.rst @@ -18,6 +18,14 @@ .. changelog:: :version: 1.0.10 + .. change:: + :tags: change, tests + :versions: 1.1.0b1 + + The ORM and Core tutorials, which have always been in doctest format, + are now exercised within the normal unit test suite in both Python + 2 and Python 3. + .. change:: :tags: bug, sql :tickets: 3603 diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst index a8c68babf2..5773cab402 100644 --- a/doc/build/core/tutorial.rst +++ b/doc/build/core/tutorial.rst @@ -55,7 +55,7 @@ A quick check to verify that we are on at least **version 1.1** of SQLAlchemy: .. sourcecode:: pycon+sql >>> import sqlalchemy - >>> sqlalchemy.__version__ # doctest:+SKIP + >>> sqlalchemy.__version__ # doctest: +SKIP 1.1.0 Connecting @@ -149,11 +149,8 @@ each table first before creating, so it's safe to call multiple times: .. sourcecode:: pycon+sql - {sql}>>> metadata.create_all(engine) #doctest: +NORMALIZE_WHITESPACE - PRAGMA table_info("users") - () - PRAGMA table_info("addresses") - () + {sql}>>> metadata.create_all(engine) + SE... CREATE TABLE users ( id INTEGER NOT NULL, name VARCHAR, @@ -243,7 +240,7 @@ data consists of literal values, SQLAlchemy automatically generates bind parameters for them. We can peek at this data for now by looking at the compiled form of the statement:: - >>> ins.compile().params #doctest: +NORMALIZE_WHITESPACE + >>> ins.compile().params # doctest: +SKIP {'fullname': 'Jack Jones', 'name': 'jack'} Executing @@ -257,7 +254,7 @@ connections capable of issuing SQL to the database. To acquire a connection, we use the ``connect()`` method:: >>> conn = engine.connect() - >>> conn #doctest: +ELLIPSIS + >>> conn The :class:`~sqlalchemy.engine.Connection` object represents an actively @@ -328,7 +325,7 @@ and use it in the "normal" way: .. sourcecode:: pycon+sql >>> ins = users.insert() - >>> conn.execute(ins, id=2, name='wendy', fullname='Wendy Williams') # doctest: +ELLIPSIS + >>> conn.execute(ins, id=2, name='wendy', fullname='Wendy Williams') {opensql}INSERT INTO users (id, name, fullname) VALUES (?, ?, ?) (2, 'wendy', 'Wendy Williams') COMMIT @@ -347,7 +344,7 @@ inserted, as we do here to add some email addresses: .. sourcecode:: pycon+sql - >>> conn.execute(addresses.insert(), [ # doctest: +ELLIPSIS + >>> conn.execute(addresses.insert(), [ ... {'user_id': 1, 'email_address' : 'jack@yahoo.com'}, ... {'user_id': 1, 'email_address' : 'jack@msn.com'}, ... {'user_id': 2, 'email_address' : 'www@www.org'}, @@ -386,7 +383,7 @@ statements is the :func:`.select` function: >>> from sqlalchemy.sql import select >>> s = select([users]) - >>> result = conn.execute(s) # doctest: +NORMALIZE_WHITESPACE + >>> result = conn.execute(s) {opensql}SELECT users.id, users.name, users.fullname FROM users () @@ -404,7 +401,7 @@ rows from it is to just iterate: .. sourcecode:: pycon+sql >>> for row in result: - ... print row + ... print(row) (1, u'jack', u'Jack Jones') (2, u'wendy', u'Wendy Williams') @@ -414,13 +411,13 @@ through dictionary access, using the string names of columns: .. sourcecode:: pycon+sql - {sql}>>> result = conn.execute(s) # doctest: +NORMALIZE_WHITESPACE + {sql}>>> result = conn.execute(s) SELECT users.id, users.name, users.fullname FROM users () {stop}>>> row = result.fetchone() - >>> print "name:", row['name'], "; fullname:", row['fullname'] + >>> print("name:", row['name'], "; fullname:", row['fullname']) name: jack ; fullname: Jack Jones Integer indexes work as well: @@ -428,7 +425,7 @@ Integer indexes work as well: .. sourcecode:: pycon+sql >>> row = result.fetchone() - >>> print "name:", row[1], "; fullname:", row[2] + >>> print("name:", row[1], "; fullname:", row[2]) name: wendy ; fullname: Wendy Williams But another way, whose usefulness will become apparent later on, is to use the @@ -436,8 +433,8 @@ But another way, whose usefulness will become apparent later on, is to use the .. sourcecode:: pycon+sql - {sql}>>> for row in conn.execute(s): # doctest: +NORMALIZE_WHITESPACE - ... print "name:", row[users.c.name], "; fullname:", row[users.c.fullname] + {sql}>>> for row in conn.execute(s): + ... print("name:", row[users.c.name], "; fullname:", row[users.c.fullname]) SELECT users.id, users.name, users.fullname FROM users () @@ -464,12 +461,12 @@ the ``c`` attribute of the :class:`~sqlalchemy.schema.Table` object: .. sourcecode:: pycon+sql >>> s = select([users.c.name, users.c.fullname]) - {sql}>>> result = conn.execute(s) # doctest: +NORMALIZE_WHITESPACE + {sql}>>> result = conn.execute(s) SELECT users.name, users.fullname FROM users () - {stop}>>> for row in result: #doctest: +NORMALIZE_WHITESPACE - ... print row + {stop}>>> for row in result: + ... print(row) (u'jack', u'Jack Jones') (u'wendy', u'Wendy Williams') @@ -482,7 +479,7 @@ our :func:`.select` statement: .. sourcecode:: pycon+sql {sql}>>> for row in conn.execute(select([users, addresses])): - ... print row # doctest: +NORMALIZE_WHITESPACE + ... print(row) SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address FROM users, addresses () @@ -505,7 +502,7 @@ WHERE clause. We do that using :meth:`.Select.where`: >>> s = select([users, addresses]).where(users.c.id == addresses.c.user_id) {sql}>>> for row in conn.execute(s): - ... print row # doctest: +NORMALIZE_WHITESPACE + ... print(row) SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address FROM users, addresses @@ -527,8 +524,8 @@ a WHERE clause. So lets see exactly what that expression is doing: .. sourcecode:: pycon+sql - >>> users.c.id == addresses.c.user_id #doctest: +ELLIPSIS - + >>> users.c.id == addresses.c.user_id + Wow, surprise ! This is neither a ``True`` nor a ``False``. Well what is it ? @@ -552,7 +549,7 @@ some of its capabilities. We've seen how to equate two columns to each other: .. sourcecode:: pycon+sql - >>> print users.c.id == addresses.c.user_id + >>> print(users.c.id == addresses.c.user_id) users.id = addresses.user_id If we use a literal value (a literal meaning, not a SQLAlchemy clause object), @@ -560,7 +557,7 @@ we get a bind parameter: .. sourcecode:: pycon+sql - >>> print users.c.id == 7 + >>> print(users.c.id == 7) users.id = :id_1 The ``7`` literal is embedded the resulting @@ -577,22 +574,22 @@ equals, not equals, etc.: .. sourcecode:: pycon+sql - >>> print users.c.id != 7 + >>> print(users.c.id != 7) users.id != :id_1 >>> # None converts to IS NULL - >>> print users.c.name == None + >>> print(users.c.name == None) users.name IS NULL >>> # reverse works too - >>> print 'fred' > users.c.name + >>> print('fred' > users.c.name) users.name < :name_1 If we add two integer columns together, we get an addition expression: .. sourcecode:: pycon+sql - >>> print users.c.id + addresses.c.id + >>> print(users.c.id + addresses.c.id) users.id + addresses.id Interestingly, the type of the :class:`~sqlalchemy.schema.Column` is important! @@ -603,7 +600,7 @@ something different: .. sourcecode:: pycon+sql - >>> print users.c.name + users.c.fullname + >>> print(users.c.name + users.c.fullname) users.name || users.fullname Where ``||`` is the string concatenation operator used on most databases. But @@ -611,8 +608,8 @@ not all of them. MySQL users, fear not: .. sourcecode:: pycon+sql - >>> print (users.c.name + users.c.fullname).\ - ... compile(bind=create_engine('mysql://')) + >>> print((users.c.name + users.c.fullname). + ... compile(bind=create_engine('mysql://'))) # doctest: +SKIP concat(users.name, users.fullname) The above illustrates the SQL that's generated for an @@ -624,7 +621,7 @@ always use the :meth:`.ColumnOperators.op` method; this generates whatever opera .. sourcecode:: pycon+sql - >>> print users.c.name.op('tiddlywinks')('foo') + >>> print(users.c.name.op('tiddlywinks')('foo')) users.name tiddlywinks :name_1 This function can also be used to make bitwise operators explicit. For example:: @@ -660,15 +657,16 @@ a :meth:`~.ColumnOperators.like`): .. sourcecode:: pycon+sql >>> from sqlalchemy.sql import and_, or_, not_ - >>> print and_( + >>> print(and_( ... users.c.name.like('j%'), - ... users.c.id == addresses.c.user_id, #doctest: +NORMALIZE_WHITESPACE + ... users.c.id == addresses.c.user_id, ... or_( ... addresses.c.email_address == 'wendy@aol.com', ... addresses.c.email_address == 'jack@yahoo.com' ... ), ... not_(users.c.id > 5) ... ) + ... ) users.name LIKE :name_1 AND users.id = addresses.user_id AND (addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2) @@ -680,12 +678,13 @@ parenthesis: .. sourcecode:: pycon+sql - >>> print users.c.name.like('j%') & (users.c.id == addresses.c.user_id) & \ + >>> print(users.c.name.like('j%') & (users.c.id == addresses.c.user_id) & ... ( ... (addresses.c.email_address == 'wendy@aol.com') | \ ... (addresses.c.email_address == 'jack@yahoo.com') ... ) \ - ... & ~(users.c.id>5) # doctest: +NORMALIZE_WHITESPACE + ... & ~(users.c.id>5) + ... ) users.name LIKE :name_1 AND users.id = addresses.user_id AND (addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2) @@ -716,7 +715,7 @@ not have a name: ... ) ... ) ... ) - >>> conn.execute(s).fetchall() #doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(s).fetchall() SELECT users.fullname || ? || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND @@ -745,7 +744,7 @@ A shortcut to using :func:`.and_` is to chain together multiple ... addresses.c.email_address.like('%@msn.com') ... ) ... ) - >>> conn.execute(s).fetchall() #doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(s).fetchall() SELECT users.fullname || ? || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND @@ -780,7 +779,7 @@ unchanged. Below, we create a :func:`~.expression.text` object and execute it: ... "AND users.name BETWEEN :x AND :y " ... "AND (addresses.email_address LIKE :e1 " ... "OR addresses.email_address LIKE :e2)") - {sql}>>> conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall() # doctest:+NORMALIZE_WHITESPACE + {sql}>>> conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall() SELECT users.fullname || ', ' || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND @@ -811,7 +810,7 @@ in order to specify column return types and names: ... "OR addresses.email_address LIKE :e2)") >>> s = s.columns(title=String) >>> s = s.bindparams(x='m', y='z', e1='%@aol.com', e2='%@msn.com') - >>> conn.execute(s).fetchall() # doctest:+NORMALIZE_WHITESPACE + >>> conn.execute(s).fetchall() SELECT users.fullname || ', ' || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND @@ -843,7 +842,7 @@ need to refer to any pre-established :class:`.Table` metadata: ... "OR addresses.email_address LIKE :y)") ... ) ... ).select_from(text('users, addresses')) - {sql}>>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall() SELECT users.fullname || ', ' || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' @@ -870,7 +869,7 @@ need to refer to any pre-established :class:`.Table` metadata: :ref:`orm_tutorial_literal_sql` - integrating ORM-level queries with :func:`.text` -.. versionchanged:: 1.0.0 +.. fchanged:: 1.0.0 The :func:`.select` construct emits warnings when string SQL fragments are coerced to :func:`.text`, and :func:`.text` should be used explicitly. See :ref:`migration_2992` for background. @@ -901,7 +900,7 @@ be quoted: >>> from sqlalchemy.sql import table, literal_column >>> s = select([ ... literal_column("users.fullname", String) + - ... ' , ' + + ... ', ' + ... literal_column("addresses.email_address").label("title") ... ]).\ ... where( @@ -914,13 +913,13 @@ be quoted: ... ) ... ).select_from(table('users')).select_from(table('addresses')) - {sql}>>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall() #doctest: +NORMALIZE_WHITESPACE - SELECT "users.fullname" || ? || "addresses.email_address" AS anon_1 + {sql}>>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall() + SELECT users.fullname || ? || addresses.email_address AS anon_1 FROM users, addresses - WHERE "users.id" = "addresses.user_id" + WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?) - (' , ', '%@aol.com', '%@msn.com') + (', ', '%@aol.com', '%@msn.com') {stop}[(u'Wendy Williams, wendy@aol.com',)] Ordering or Grouping by a Label @@ -943,7 +942,7 @@ expression from being rendered twice: ... func.count(addresses.c.id).label('num_addresses')]).\ ... order_by("num_addresses") - {sql}>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(stmt).fetchall() SELECT addresses.user_id, count(addresses.id) AS num_addresses FROM addresses ORDER BY num_addresses () @@ -960,7 +959,7 @@ name: ... func.count(addresses.c.id).label('num_addresses')]).\ ... order_by(desc("num_addresses")) - {sql}>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(stmt).fetchall() SELECT addresses.user_id, count(addresses.id) AS num_addresses FROM addresses ORDER BY num_addresses DESC () @@ -981,7 +980,7 @@ by a column name that appears more than once: ... where(u1a.c.name > u1b.c.name).\ ... order_by(u1a.c.name) # using "name" here would be ambiguous - {sql}>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(stmt).fetchall() SELECT users_1.id, users_1.name, users_1.fullname, users_2.id, users_2.name, users_2.fullname FROM users AS users_1, users AS users_2 @@ -1023,7 +1022,7 @@ once for each address. We create two :class:`.Alias` constructs against ... a1.c.email_address == 'jack@msn.com', ... a2.c.email_address == 'jack@yahoo.com' ... )) - {sql}>>> conn.execute(s).fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(s).fetchall() SELECT users.id, users.name, users.fullname FROM users, addresses AS addresses_1, addresses AS addresses_2 WHERE users.id = addresses_1.user_id @@ -1058,7 +1057,7 @@ to "correlate" the inner ``users`` table with the outer one: >>> a1 = s.correlate(None).alias() >>> s = select([users.c.name]).where(users.c.id == a1.c.id) - {sql}>>> conn.execute(s).fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(s).fetchall() SELECT users.name FROM users, (SELECT users.id AS id, users.name AS name, users.fullname AS fullname @@ -1083,7 +1082,7 @@ join: .. sourcecode:: pycon+sql - >>> print users.join(addresses) + >>> print(users.join(addresses)) users JOIN addresses ON users.id = addresses.user_id The alert reader will see more surprises; SQLAlchemy figured out how to JOIN @@ -1099,9 +1098,10 @@ username: .. sourcecode:: pycon+sql - >>> print users.join(addresses, + >>> print(users.join(addresses, ... addresses.c.email_address.like(users.c.name + '%') ... ) + ... ) users JOIN addresses ON addresses.email_address LIKE (users.name || :name_1) When we create a :func:`.select` construct, SQLAlchemy looks around at the @@ -1115,7 +1115,7 @@ here we make use of the :meth:`~.Select.select_from` method: ... users.join(addresses, ... addresses.c.email_address.like(users.c.name + '%')) ... ) - {sql}>>> conn.execute(s).fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(s).fetchall() SELECT users.fullname FROM users JOIN addresses ON addresses.email_address LIKE (users.name || ?) ('%',) @@ -1127,7 +1127,7 @@ and is used in the same way as :meth:`~.FromClause.join`: .. sourcecode:: pycon+sql >>> s = select([users.c.fullname]).select_from(users.outerjoin(addresses)) - >>> print s # doctest: +NORMALIZE_WHITESPACE + >>> print(s) SELECT users.fullname FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id @@ -1139,7 +1139,7 @@ would be using ``OracleDialect``) to use Oracle-specific SQL: .. sourcecode:: pycon+sql >>> from sqlalchemy.dialects.oracle import dialect as OracleDialect - >>> print s.compile(dialect=OracleDialect(use_ansi=False)) # doctest: +NORMALIZE_WHITESPACE + >>> print(s.compile(dialect=OracleDialect(use_ansi=False))) SELECT users.fullname FROM users, addresses WHERE users.id = addresses.user_id(+) @@ -1180,7 +1180,7 @@ at execution time, as here where it converts to positional for SQLite: >>> from sqlalchemy.sql import bindparam >>> s = users.select(users.c.name == bindparam('username')) - {sql}>>> conn.execute(s, username='wendy').fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(s, username='wendy').fetchall() SELECT users.id, users.name, users.fullname FROM users WHERE users.name = ? @@ -1195,7 +1195,7 @@ off to the database: .. sourcecode:: pycon+sql >>> s = users.select(users.c.name.like(bindparam('username', type_=String) + text("'%'"))) - {sql}>>> conn.execute(s, username='wendy').fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(s, username='wendy').fetchall() SELECT users.id, users.name, users.fullname FROM users WHERE users.name LIKE (? || '%') @@ -1219,7 +1219,7 @@ single named value is needed in the execute parameters: ... ).\ ... select_from(users.outerjoin(addresses)).\ ... order_by(addresses.c.id) - {sql}>>> conn.execute(s, name='jack').fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(s, name='jack').fetchall() SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id @@ -1241,16 +1241,16 @@ generates functions using attribute access: .. sourcecode:: pycon+sql >>> from sqlalchemy.sql import func - >>> print func.now() + >>> print(func.now()) now() - >>> print func.concat('x', 'y') - concat(:param_1, :param_2) + >>> print(func.concat('x', 'y')) + concat(:concat_1, :concat_2) By "generates", we mean that **any** SQL function is created based on the word you choose:: - >>> print func.xyz_my_goofy_function() # doctest: +NORMALIZE_WHITESPACE + >>> print(func.xyz_my_goofy_function()) xyz_my_goofy_function() Certain function names are known by SQLAlchemy, allowing special behavioral @@ -1259,7 +1259,7 @@ don't get the parenthesis added after them, such as CURRENT_TIMESTAMP: .. sourcecode:: pycon+sql - >>> print func.current_timestamp() + >>> print(func.current_timestamp()) CURRENT_TIMESTAMP Functions are most typically used in the columns clause of a select statement, @@ -1278,7 +1278,7 @@ not important in this case: ... func.max(addresses.c.email_address, type_=String). ... label('maxemail') ... ]) - ... ).scalar() # doctest: +NORMALIZE_WHITESPACE + ... ).scalar() {opensql}SELECT max(addresses.email_address) AS maxemail FROM addresses () @@ -1302,7 +1302,7 @@ well as bind parameters: ... ) ... ) >>> calc = calculate.alias() - >>> print select([users]).where(users.c.id > calc.c.z) # doctest: +NORMALIZE_WHITESPACE + >>> print(select([users]).where(users.c.id > calc.c.z)) SELECT users.id, users.name, users.fullname FROM users, (SELECT q, z, r FROM calculate(:x, :y)) AS anon_1 @@ -1320,14 +1320,14 @@ of our selectable: >>> calc2 = calculate.alias('c2').unique_params(x=5, y=12) >>> s = select([users]).\ ... where(users.c.id.between(calc1.c.z, calc2.c.z)) - >>> print s # doctest: +NORMALIZE_WHITESPACE + >>> print(s) SELECT users.id, users.name, users.fullname FROM users, (SELECT q, z, r FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r FROM calculate(:x_2, :y_2)) AS c2 WHERE users.id BETWEEN c1.z AND c2.z - >>> s.compile().params + >>> s.compile().params # doctest: +SKIP {u'x_2': 5, u'y_2': 12, u'y_1': 45, u'x_1': 17} .. seealso:: @@ -1347,7 +1347,7 @@ OVER clause, using the :meth:`.FunctionElement.over` method: ... users.c.id, ... func.row_number().over(order_by=users.c.name) ... ]) - >>> print s # doctest: +NORMALIZE_WHITESPACE + >>> print(s) SELECT users.id, row_number() OVER (ORDER BY users.name) AS anon_1 FROM users @@ -1374,7 +1374,7 @@ module level functions :func:`~.expression.union` and ... where(addresses.c.email_address.like('%@yahoo.com')), ... ).order_by(addresses.c.email_address) - {sql}>>> conn.execute(u).fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(u).fetchall() SELECT addresses.id, addresses.user_id, addresses.email_address FROM addresses WHERE addresses.email_address = ? @@ -1400,7 +1400,7 @@ Also available, though not supported on all databases, are ... where(addresses.c.email_address.like('%@msn.com')) ... ) - {sql}>>> conn.execute(u).fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(u).fetchall() SELECT addresses.id, addresses.user_id, addresses.email_address FROM addresses WHERE addresses.email_address LIKE ? @@ -1430,7 +1430,7 @@ want the "union" to be stated as a subquery: ... ).alias().select(), # apply subquery here ... addresses.select(addresses.c.email_address.like('%@msn.com')) ... ) - {sql}>>> conn.execute(u).fetchall() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> conn.execute(u).fetchall() SELECT anon_1.id, anon_1.user_id, anon_1.email_address FROM (SELECT addresses.id AS id, addresses.user_id AS user_id, addresses.email_address AS email_address @@ -1491,7 +1491,7 @@ other column within another :func:`.select`: .. sourcecode:: pycon+sql - >>> conn.execute(select([users.c.name, stmt])).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(select([users.c.name, stmt])).fetchall() {opensql}SELECT users.name, (SELECT count(addresses.id) AS count_1 FROM addresses WHERE users.id = addresses.user_id) AS anon_1 @@ -1507,7 +1507,7 @@ it using :meth:`.SelectBase.label` instead: >>> stmt = select([func.count(addresses.c.id)]).\ ... where(users.c.id == addresses.c.user_id).\ ... label("address_count") - >>> conn.execute(select([users.c.name, stmt])).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(select([users.c.name, stmt])).fetchall() {opensql}SELECT users.name, (SELECT count(addresses.id) AS count_1 FROM addresses WHERE users.id = addresses.user_id) AS address_count @@ -1538,7 +1538,7 @@ still have at least one FROM clause of its own. For example: ... where(addresses.c.user_id == users.c.id).\ ... where(addresses.c.email_address == 'jack@yahoo.com') >>> enclosing_stmt = select([users.c.name]).where(users.c.id == stmt) - >>> conn.execute(enclosing_stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(enclosing_stmt).fetchall() {opensql}SELECT users.name FROM users WHERE users.id = (SELECT addresses.user_id @@ -1564,7 +1564,7 @@ may be correlated: ... [users.c.name, addresses.c.email_address]).\ ... select_from(users.join(addresses)).\ ... where(users.c.id == stmt) - >>> conn.execute(enclosing_stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(enclosing_stmt).fetchall() {opensql}SELECT users.name, addresses.email_address FROM users JOIN addresses ON users.id = addresses.user_id WHERE users.id = (SELECT users.id @@ -1583,7 +1583,7 @@ as the argument: ... correlate(None) >>> enclosing_stmt = select([users.c.name]).\ ... where(users.c.id == stmt) - >>> conn.execute(enclosing_stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(enclosing_stmt).fetchall() {opensql}SELECT users.name FROM users WHERE users.id = (SELECT users.id @@ -1606,7 +1606,7 @@ by telling it to correlate all FROM clauses except for ``users``: ... [users.c.name, addresses.c.email_address]).\ ... select_from(users.join(addresses)).\ ... where(users.c.id == stmt) - >>> conn.execute(enclosing_stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(enclosing_stmt).fetchall() {opensql}SELECT users.name, addresses.email_address FROM users JOIN addresses ON users.id = addresses.user_id WHERE users.id = (SELECT users.id @@ -1624,7 +1624,7 @@ Ordering is done by passing column expressions to the .. sourcecode:: pycon+sql >>> stmt = select([users.c.name]).order_by(users.c.name) - >>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(stmt).fetchall() {opensql}SELECT users.name FROM users ORDER BY users.name () @@ -1636,7 +1636,7 @@ and :meth:`~.ColumnElement.desc` modifiers: .. sourcecode:: pycon+sql >>> stmt = select([users.c.name]).order_by(users.c.name.desc()) - >>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(stmt).fetchall() {opensql}SELECT users.name FROM users ORDER BY users.name DESC () @@ -1651,7 +1651,7 @@ This is provided via the :meth:`~.SelectBase.group_by` method: >>> stmt = select([users.c.name, func.count(addresses.c.id)]).\ ... select_from(users.join(addresses)).\ ... group_by(users.c.name) - >>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(stmt).fetchall() {opensql}SELECT users.name, count(addresses.id) AS count_1 FROM users JOIN addresses ON users.id = addresses.user_id @@ -1669,7 +1669,7 @@ method: ... select_from(users.join(addresses)).\ ... group_by(users.c.name).\ ... having(func.length(users.c.name) > 4) - >>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(stmt).fetchall() {opensql}SELECT users.name, count(addresses.id) AS count_1 FROM users JOIN addresses ON users.id = addresses.user_id @@ -1688,10 +1688,10 @@ is the DISTINCT modifier. A simple DISTINCT clause can be added using the ... where(addresses.c.email_address. ... contains(users.c.name)).\ ... distinct() - >>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(stmt).fetchall() {opensql}SELECT DISTINCT users.name FROM users, addresses - WHERE addresses.email_address LIKE '%%' || users.name || '%%' + WHERE (addresses.email_address LIKE '%%' || users.name || '%%') () {stop}[(u'jack',), (u'wendy',)] @@ -1709,7 +1709,7 @@ into the current backend's methodology: >>> stmt = select([users.c.name, addresses.c.email_address]).\ ... select_from(users.join(addresses)).\ ... limit(1).offset(1) - >>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE + >>> conn.execute(stmt).fetchall() {opensql}SELECT users.name, addresses.email_address FROM users JOIN addresses ON users.id = addresses.user_id LIMIT ? OFFSET ? @@ -1736,7 +1736,7 @@ as a value: >>> stmt = users.update().\ ... values(fullname="Fullname: " + users.c.name) - >>> conn.execute(stmt) #doctest: +ELLIPSIS + >>> conn.execute(stmt) {opensql}UPDATE users SET fullname=(? || users.name) ('Fullname: ',) COMMIT @@ -1761,7 +1761,7 @@ as in the example below: >>> stmt = users.insert().\ ... values(name=bindparam('_name') + " .. name") - >>> conn.execute(stmt, [ # doctest: +ELLIPSIS + >>> conn.execute(stmt, [ ... {'id':4, '_name':'name1'}, ... {'id':5, '_name':'name2'}, ... {'id':6, '_name':'name3'}, @@ -1781,7 +1781,7 @@ that can be specified: ... where(users.c.name == 'jack').\ ... values(name='ed') - >>> conn.execute(stmt) #doctest: +ELLIPSIS + >>> conn.execute(stmt) {opensql}UPDATE users SET name=? WHERE users.name = ? ('ed', 'jack') COMMIT @@ -1801,7 +1801,7 @@ used to achieve this: ... {'oldname':'jack', 'newname':'ed'}, ... {'oldname':'wendy', 'newname':'mary'}, ... {'oldname':'jim', 'newname':'jake'}, - ... ]) #doctest: +ELLIPSIS + ... ]) {opensql}UPDATE users SET name=? WHERE users.name = ? (('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim')) COMMIT @@ -1819,7 +1819,7 @@ table, or the same table: >>> stmt = select([addresses.c.email_address]).\ ... where(addresses.c.user_id == users.c.id).\ ... limit(1) - >>> conn.execute(users.update().values(fullname=stmt)) #doctest: +ELLIPSIS,+NORMALIZE_WHITESPACE + >>> conn.execute(users.update().values(fullname=stmt)) {opensql}UPDATE users SET fullname=(SELECT addresses.email_address FROM addresses WHERE addresses.user_id = users.id @@ -1934,13 +1934,13 @@ Finally, a delete. This is accomplished easily enough using the .. sourcecode:: pycon+sql - >>> conn.execute(addresses.delete()) #doctest: +ELLIPSIS + >>> conn.execute(addresses.delete()) {opensql}DELETE FROM addresses () COMMIT {stop} - >>> conn.execute(users.delete().where(users.c.name > 'm')) #doctest: +ELLIPSIS + >>> conn.execute(users.delete().where(users.c.name > 'm')) {opensql}DELETE FROM users WHERE users.name > ? ('m',) COMMIT @@ -1957,7 +1957,7 @@ The value is available as :attr:`~.ResultProxy.rowcount`: .. sourcecode:: pycon+sql - >>> result = conn.execute(users.delete()) #doctest: +ELLIPSIS + >>> result = conn.execute(users.delete()) {opensql}DELETE FROM users () COMMIT diff --git a/doc/build/orm/tutorial.rst b/doc/build/orm/tutorial.rst index dff181f6b9..607d3a8924 100644 --- a/doc/build/orm/tutorial.rst +++ b/doc/build/orm/tutorial.rst @@ -208,12 +208,12 @@ the actual ``CREATE TABLE`` statement: .. sourcecode:: python+sql - >>> Base.metadata.create_all(engine) # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE - {opensql}PRAGMA table_info("users") + >>> Base.metadata.create_all(engine) + SELECT ... + PRAGMA table_info("users") () CREATE TABLE users ( - id INTEGER NOT NULL, - name VARCHAR, + id INTEGER NOT NULL, name VARCHAR, fullname VARCHAR, password VARCHAR, PRIMARY KEY (id) @@ -369,7 +369,7 @@ added: .. sourcecode:: python+sql - {sql}>>> our_user = session.query(User).filter_by(name='ed').first() # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE + {sql}>>> our_user = session.query(User).filter_by(name='ed').first() # doctest:+NORMALIZE_WHITESPACE BEGIN (implicit) INSERT INTO users (name, fullname, password) VALUES (?, ?, ?) ('ed', 'Ed Jones', 'edspassword') @@ -513,7 +513,7 @@ Querying the session, we can see that they're flushed into the current transacti .. sourcecode:: python+sql - {sql}>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all() UPDATE users SET name=? WHERE users.id = ? ('Edwardo', 1) INSERT INTO users (name, fullname, password) VALUES (?, ?, ?) @@ -525,7 +525,7 @@ Querying the session, we can see that they're flushed into the current transacti FROM users WHERE users.name IN (?, ?) ('Edwardo', 'fakeuser') - {stop}[, ] + {stop}[, ] Rolling back, we can see that ``ed_user``'s name is back to ``ed``, and ``fake_user`` has been kicked out of the session: @@ -536,7 +536,7 @@ Rolling back, we can see that ``ed_user``'s name is back to ``ed``, and ROLLBACK {stop} - {sql}>>> ed_user.name #doctest: +NORMALIZE_WHITESPACE + {sql}>>> ed_user.name BEGIN (implicit) SELECT users.id AS users_id, users.name AS users_name, @@ -553,7 +553,7 @@ issuing a SELECT illustrates the changes made to the database: .. sourcecode:: python+sql - {sql}>>> session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all() SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -579,8 +579,8 @@ returned: .. sourcecode:: python+sql - {sql}>>> for instance in session.query(User).order_by(User.id): # doctest: +NORMALIZE_WHITESPACE - ... print instance.name, instance.fullname + {sql}>>> for instance in session.query(User).order_by(User.id): + ... print(instance.name, instance.fullname) SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -600,8 +600,8 @@ is expressed as tuples: .. sourcecode:: python+sql - {sql}>>> for name, fullname in session.query(User.name, User.fullname): # doctest: +NORMALIZE_WHITESPACE - ... print name, fullname + {sql}>>> for name, fullname in session.query(User.name, User.fullname): + ... print(name, fullname) SELECT users.name AS users_name, users.fullname AS users_fullname FROM users @@ -619,8 +619,8 @@ class: .. sourcecode:: python+sql - {sql}>>> for row in session.query(User, User.name).all(): #doctest: +NORMALIZE_WHITESPACE - ... print row.User, row.name + {sql}>>> for row in session.query(User, User.name).all(): + ... print(row.User, row.name) SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -639,7 +639,7 @@ is mapped to one (such as ``User.name``): .. sourcecode:: python+sql - {sql}>>> for row in session.query(User.name.label('name_label')).all(): #doctest: +NORMALIZE_WHITESPACE + {sql}>>> for row in session.query(User.name.label('name_label')).all(): ... print(row.name_label) SELECT users.name AS name_label FROM users @@ -658,8 +658,8 @@ entities are present in the call to :meth:`~.Session.query`, can be controlled u >>> from sqlalchemy.orm import aliased >>> user_alias = aliased(User, name='user_alias') - {sql}>>> for row in session.query(user_alias, user_alias.name).all(): #doctest: +NORMALIZE_WHITESPACE - ... print row.user_alias + {sql}>>> for row in session.query(user_alias, user_alias.name).all(): + ... print(row.user_alias) SELECT user_alias.id AS user_alias_id, user_alias.name AS user_alias_name, user_alias.fullname AS user_alias_fullname, @@ -677,8 +677,8 @@ conjunction with ORDER BY: .. sourcecode:: python+sql - {sql}>>> for u in session.query(User).order_by(User.id)[1:3]: #doctest: +NORMALIZE_WHITESPACE - ... print u + {sql}>>> for u in session.query(User).order_by(User.id)[1:3]: + ... print(u) SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -695,8 +695,8 @@ and filtering results, which is accomplished either with .. sourcecode:: python+sql {sql}>>> for name, in session.query(User.name).\ - ... filter_by(fullname='Ed Jones'): # doctest: +NORMALIZE_WHITESPACE - ... print name + ... filter_by(fullname='Ed Jones'): + ... print(name) SELECT users.name AS users_name FROM users WHERE users.fullname = ? ('Ed Jones',) @@ -709,8 +709,8 @@ operators with the class-level attributes on your mapped class: .. sourcecode:: python+sql {sql}>>> for name, in session.query(User.name).\ - ... filter(User.fullname=='Ed Jones'): # doctest: +NORMALIZE_WHITESPACE - ... print name + ... filter(User.fullname=='Ed Jones'): + ... print(name) SELECT users.name AS users_name FROM users WHERE users.fullname = ? ('Ed Jones',) @@ -727,8 +727,8 @@ users named "ed" with a full name of "Ed Jones", you can call {sql}>>> for user in session.query(User).\ ... filter(User.name=='ed').\ - ... filter(User.fullname=='Ed Jones'): # doctest: +NORMALIZE_WHITESPACE - ... print user + ... filter(User.fullname=='Ed Jones'): + ... print(user) SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -828,7 +828,7 @@ database results. Here's a brief tour: .. sourcecode:: python+sql >>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id) - {sql}>>> query.all() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> query.all() SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -844,7 +844,7 @@ database results. Here's a brief tour: .. sourcecode:: python+sql - {sql}>>> query.first() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> query.first() SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -862,10 +862,10 @@ database results. Here's a brief tour: .. sourcecode:: python+sql {sql}>>> from sqlalchemy.orm.exc import MultipleResultsFound - >>> try: #doctest: +NORMALIZE_WHITESPACE + >>> try: ... user = query.one() - ... except MultipleResultsFound, e: - ... print e + ... except MultipleResultsFound as e: + ... print(e) SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -880,10 +880,10 @@ database results. Here's a brief tour: .. sourcecode:: python+sql {sql}>>> from sqlalchemy.orm.exc import NoResultFound - >>> try: #doctest: +NORMALIZE_WHITESPACE + >>> try: ... user = query.filter(User.id == 99).one() - ... except NoResultFound, e: - ... print e + ... except NoResultFound as e: + ... print(e) SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -910,7 +910,7 @@ database results. Here's a brief tour: >>> query = session.query(User.id).filter(User.name == 'ed').\ ... order_by(User.id) - {sql}>>> query.scalar() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> query.scalar() SELECT users.id AS users_id FROM users WHERE users.name = ? ORDER BY users.id @@ -934,8 +934,8 @@ by most applicable methods. For example, >>> from sqlalchemy import text {sql}>>> for user in session.query(User).\ ... filter(text("id<224")).\ - ... order_by(text("id")).all(): #doctest: +NORMALIZE_WHITESPACE - ... print user.name + ... order_by(text("id")).all(): + ... print(user.name) SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -955,7 +955,7 @@ method: .. sourcecode:: python+sql {sql}>>> session.query(User).filter(text("id<:value and name=:name")).\ - ... params(value=224, name='fred').order_by(User.id).one() # doctest: +NORMALIZE_WHITESPACE + ... params(value=224, name='fred').order_by(User.id).one() SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -1021,7 +1021,7 @@ counting called :meth:`~sqlalchemy.orm.query.Query.count()`: .. sourcecode:: python+sql - {sql}>>> session.query(User).filter(User.name.like('%ed')).count() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> session.query(User).filter(User.name.like('%ed')).count() SELECT count(*) AS count_1 FROM (SELECT users.id AS users_id, users.name AS users_name, @@ -1059,7 +1059,7 @@ use it to return the count of each distinct user name: .. sourcecode:: python+sql >>> from sqlalchemy import func - {sql}>>> session.query(func.count(User.name), User.name).group_by(User.name).all() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> session.query(func.count(User.name), User.name).group_by(User.name).all() SELECT count(users.name) AS count_1, users.name AS users_name FROM users GROUP BY users.name () @@ -1069,7 +1069,7 @@ To achieve our simple ``SELECT count(*) FROM table``, we can apply it as: .. sourcecode:: python+sql - {sql}>>> session.query(func.count('*')).select_from(User).scalar() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> session.query(func.count('*')).select_from(User).scalar() SELECT count(?) AS count_1 FROM users ('*',) @@ -1080,7 +1080,7 @@ of the ``User`` primary key directly: .. sourcecode:: python+sql - {sql}>>> session.query(func.count(User.id)).scalar() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> session.query(func.count(User.id)).scalar() SELECT count(users.id) AS count_1 FROM users () @@ -1178,11 +1178,8 @@ already been created: .. sourcecode:: python+sql - {sql}>>> Base.metadata.create_all(engine) # doctest: +NORMALIZE_WHITESPACE - PRAGMA table_info("users") - () - PRAGMA table_info("addresses") - () + {sql}>>> Base.metadata.create_all(engine) + PRAGMA... CREATE TABLE addresses ( id INTEGER NOT NULL, email_address VARCHAR NOT NULL, @@ -1251,7 +1248,7 @@ Querying for Jack, we get just Jack back. No SQL is yet issued for Jack's addre .. sourcecode:: python+sql {sql}>>> jack = session.query(User).\ - ... filter_by(name='jack').one() #doctest: +NORMALIZE_WHITESPACE + ... filter_by(name='jack').one() BEGIN (implicit) SELECT users.id AS users_id, users.name AS users_name, @@ -1268,7 +1265,7 @@ Let's look at the ``addresses`` collection. Watch the SQL: .. sourcecode:: python+sql - {sql}>>> jack.addresses #doctest: +NORMALIZE_WHITESPACE + {sql}>>> jack.addresses SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, @@ -1303,9 +1300,9 @@ Below we load the ``User`` and ``Address`` entities at once using this method: {sql}>>> for u, a in session.query(User, Address).\ ... filter(User.id==Address.user_id).\ ... filter(Address.email_address=='jack@google.com').\ - ... all(): # doctest: +NORMALIZE_WHITESPACE - ... print u - ... print a + ... all(): + ... print(u) + ... print(a) SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -1327,7 +1324,7 @@ using the :meth:`.Query.join` method: {sql}>>> session.query(User).join(Address).\ ... filter(Address.email_address=='jack@google.com').\ - ... all() #doctest: +NORMALIZE_WHITESPACE + ... all() SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -1390,7 +1387,7 @@ same time: ... join(adalias2, User.addresses).\ ... filter(adalias1.email_address=='jack@google.com').\ ... filter(adalias2.email_address=='j25@yahoo.com'): - ... print username, email1, email2 # doctest: +NORMALIZE_WHITESPACE + ... print(username, email1, email2) SELECT users.name AS users_name, addresses_1.email_address AS addresses_1_email_address, addresses_2.email_address AS addresses_2_email_address @@ -1442,8 +1439,8 @@ accessible through an attribute called ``c``: .. sourcecode:: python+sql {sql}>>> for u, count in session.query(User, stmt.c.address_count).\ - ... outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id): # doctest: +NORMALIZE_WHITESPACE - ... print u, count + ... outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id): + ... print(u, count) SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -1475,9 +1472,9 @@ to associate an "alias" of a mapped class to a subquery: ... subquery() >>> adalias = aliased(Address, stmt) >>> for user, address in session.query(User, adalias).\ - ... join(adalias, User.addresses): # doctest: +NORMALIZE_WHITESPACE - ... print user - ... print address + ... join(adalias, User.addresses): + ... print(user) + ... print(address) SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -1510,8 +1507,8 @@ There is an explicit EXISTS construct, which looks like this: >>> from sqlalchemy.sql import exists >>> stmt = exists().where(Address.user_id==User.id) - {sql}>>> for name, in session.query(User.name).filter(stmt): # doctest: +NORMALIZE_WHITESPACE - ... print name + {sql}>>> for name, in session.query(User.name).filter(stmt): + ... print(name) SELECT users.name AS users_name FROM users WHERE EXISTS (SELECT * @@ -1527,8 +1524,8 @@ usage of EXISTS automatically. Above, the statement can be expressed along the .. sourcecode:: python+sql {sql}>>> for name, in session.query(User.name).\ - ... filter(User.addresses.any()): # doctest: +NORMALIZE_WHITESPACE - ... print name + ... filter(User.addresses.any()): + ... print(name) SELECT users.name AS users_name FROM users WHERE EXISTS (SELECT 1 @@ -1542,8 +1539,8 @@ usage of EXISTS automatically. Above, the statement can be expressed along the .. sourcecode:: python+sql {sql}>>> for name, in session.query(User.name).\ - ... filter(User.addresses.any(Address.email_address.like('%google%'))): # doctest: +NORMALIZE_WHITESPACE - ... print name + ... filter(User.addresses.any(Address.email_address.like('%google%'))): + ... print(name) SELECT users.name AS users_name FROM users WHERE EXISTS (SELECT 1 @@ -1559,7 +1556,7 @@ usage of EXISTS automatically. Above, the statement can be expressed along the .. sourcecode:: python+sql {sql}>>> session.query(Address).\ - ... filter(~Address.user.has(User.name=='jack')).all() # doctest: +NORMALIZE_WHITESPACE + ... filter(~Address.user.has(User.name=='jack')).all() SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id @@ -1637,7 +1634,7 @@ very easy to use: >>> from sqlalchemy.orm import subqueryload {sql}>>> jack = session.query(User).\ ... options(subqueryload(User.addresses)).\ - ... filter_by(name='jack').one() #doctest: +NORMALIZE_WHITESPACE + ... filter_by(name='jack').one() SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -1684,7 +1681,7 @@ will emit the extra join regardless: {sql}>>> jack = session.query(User).\ ... options(joinedload(User.addresses)).\ - ... filter_by(name='jack').one() #doctest: +NORMALIZE_WHITESPACE + ... filter_by(name='jack').one() SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -1746,7 +1743,7 @@ attribute: ... join(Address.user).\ ... filter(User.name=='jack').\ ... options(contains_eager(Address.user)).\ - ... all() #doctest: +NORMALIZE_WHITESPACE + ... all() SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -1776,11 +1773,9 @@ the session, then we'll issue a ``count`` query to see that no rows remain: .. sourcecode:: python+sql >>> session.delete(jack) - {sql}>>> session.query(User).filter_by(name='jack').count() # doctest: +NORMALIZE_WHITESPACE - UPDATE addresses SET user_id=? WHERE addresses.id = ? - (None, 1) + {sql}>>> session.query(User).filter_by(name='jack').count() UPDATE addresses SET user_id=? WHERE addresses.id = ? - (None, 2) + ((None, 1), (None, 2)) DELETE FROM users WHERE users.id = ? (5,) SELECT count(*) AS count_1 @@ -1799,7 +1794,7 @@ So far, so good. How about Jack's ``Address`` objects ? {sql}>>> session.query(Address).filter( ... Address.email_address.in_(['jack@google.com', 'j25@yahoo.com']) - ... ).count() # doctest: +NORMALIZE_WHITESPACE + ... ).count() SELECT count(*) AS count_1 FROM (SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, @@ -1826,6 +1821,8 @@ relationship needs to be removed, so we need to tear down the mappings completely and start again - we'll close the :class:`.Session`:: >>> session.close() + ROLLBACK + and use a new :func:`.declarative_base`:: @@ -1846,7 +1843,7 @@ including the cascade configuration (we'll leave the constructor out too):: ... cascade="all, delete, delete-orphan") ... ... def __repr__(self): - ... return "" % ( + ... return "" % ( ... self.name, self.fullname, self.password) Then we recreate ``Address``, noting that in this case we've created @@ -1869,7 +1866,7 @@ being deleted: .. sourcecode:: python+sql # load Jack by primary key - {sql}>>> jack = session.query(User).get(5) #doctest: +NORMALIZE_WHITESPACE + {sql}>>> jack = session.query(User).get(5) BEGIN (implicit) SELECT users.id AS users_id, users.name AS users_name, @@ -1881,7 +1878,7 @@ being deleted: {stop} # remove one Address (lazy load fires off) - {sql}>>> del jack.addresses[1] #doctest: +NORMALIZE_WHITESPACE + {sql}>>> del jack.addresses[1] SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id @@ -1893,7 +1890,7 @@ being deleted: # only one address remains {sql}>>> session.query(Address).filter( ... Address.email_address.in_(['jack@google.com', 'j25@yahoo.com']) - ... ).count() # doctest: +NORMALIZE_WHITESPACE + ... ).count() DELETE FROM addresses WHERE addresses.id = ? (2,) SELECT count(*) AS count_1 @@ -1912,7 +1909,7 @@ with the user: >>> session.delete(jack) - {sql}>>> session.query(User).filter_by(name='jack').count() # doctest: +NORMALIZE_WHITESPACE + {sql}>>> session.query(User).filter_by(name='jack').count() DELETE FROM addresses WHERE addresses.id = ? (1,) DELETE FROM users WHERE users.id = ? @@ -1929,7 +1926,7 @@ with the user: {sql}>>> session.query(Address).filter( ... Address.email_address.in_(['jack@google.com', 'j25@yahoo.com']) - ... ).count() # doctest: +NORMALIZE_WHITESPACE + ... ).count() SELECT count(*) AS count_1 FROM (SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, @@ -1962,8 +1959,8 @@ to serve as the association table. This looks like the following:: >>> from sqlalchemy import Table, Text >>> # association table >>> post_keywords = Table('post_keywords', Base.metadata, - ... Column('post_id', Integer, ForeignKey('posts.id')), - ... Column('keyword_id', Integer, ForeignKey('keywords.id')) + ... Column('post_id', ForeignKey('posts.id'), primary_key=True), + ... Column('keyword_id', ForeignKey('keywords.id'), primary_key=True) ... ) Above, we can see declaring a :class:`.Table` directly is a little different @@ -2038,40 +2035,32 @@ Create new tables: .. sourcecode:: python+sql - {sql}>>> Base.metadata.create_all(engine) # doctest: +NORMALIZE_WHITESPACE - PRAGMA table_info("users") - () - PRAGMA table_info("addresses") - () - PRAGMA table_info("posts") - () - PRAGMA table_info("keywords") - () - PRAGMA table_info("post_keywords") - () - CREATE TABLE posts ( + {sql}>>> Base.metadata.create_all(engine) + PRAGMA... + CREATE TABLE keywords ( id INTEGER NOT NULL, - user_id INTEGER, - headline VARCHAR(255) NOT NULL, - body TEXT, + keyword VARCHAR(50) NOT NULL, PRIMARY KEY (id), - FOREIGN KEY(user_id) REFERENCES users (id) + UNIQUE (keyword) ) () COMMIT - CREATE TABLE keywords ( + CREATE TABLE posts ( id INTEGER NOT NULL, - keyword VARCHAR(50) NOT NULL, + user_id INTEGER, + headline VARCHAR(255) NOT NULL, + body TEXT, PRIMARY KEY (id), - UNIQUE (keyword) + FOREIGN KEY(user_id) REFERENCES users (id) ) () COMMIT CREATE TABLE post_keywords ( - post_id INTEGER, - keyword_id INTEGER, - FOREIGN KEY(post_id) REFERENCES posts (id), - FOREIGN KEY(keyword_id) REFERENCES keywords (id) + post_id INTEGER NOT NULL, + keyword_id INTEGER NOT NULL, + PRIMARY KEY (post_id, keyword_id), + FOREIGN KEY(post_id) REFERENCES posts (id), + FOREIGN KEY(keyword_id) REFERENCES keywords (id) ) () COMMIT @@ -2082,7 +2071,7 @@ Usage is not too different from what we've been doing. Let's give Wendy some bl {sql}>>> wendy = session.query(User).\ ... filter_by(name='wendy').\ - ... one() #doctest: +NORMALIZE_WHITESPACE + ... one() SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -2110,7 +2099,7 @@ keyword string 'firstpost'": {sql}>>> session.query(BlogPost).\ ... filter(BlogPost.keywords.any(keyword='firstpost')).\ - ... all() #doctest: +NORMALIZE_WHITESPACE + ... all() INSERT INTO keywords (keyword) VALUES (?) ('wendy',) INSERT INTO keywords (keyword) VALUES (?) @@ -2118,7 +2107,7 @@ keyword string 'firstpost'": INSERT INTO posts (user_id, headline, body) VALUES (?, ?, ?) (2, "Wendy's Blog Post", 'This is a test') INSERT INTO post_keywords (post_id, keyword_id) VALUES (?, ?) - ((1, 1), (1, 2)) + (...) SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, @@ -2140,7 +2129,7 @@ the query to narrow down to that ``User`` object as a parent: {sql}>>> session.query(BlogPost).\ ... filter(BlogPost.author==wendy).\ ... filter(BlogPost.keywords.any(keyword='firstpost')).\ - ... all() #doctest: +NORMALIZE_WHITESPACE + ... all() SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, @@ -2161,7 +2150,7 @@ relationship, to query straight from there: {sql}>>> wendy.posts.\ ... filter(BlogPost.keywords.any(keyword='firstpost')).\ - ... all() #doctest: +NORMALIZE_WHITESPACE + ... all() SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, diff --git a/doc/build/testdocs.py b/doc/build/testdocs.py deleted file mode 100644 index 815aa86694..0000000000 --- a/doc/build/testdocs.py +++ /dev/null @@ -1,69 +0,0 @@ -import sys -sys.path = ['../../lib', './lib/'] + sys.path - -import os -import re -import doctest -import sqlalchemy.util as util -import sqlalchemy.log as salog -import logging - -rootlogger = logging.getLogger('sqlalchemy.engine.base.Engine') -class MyStream(object): - def write(self, string): - sys.stdout.write(string) - sys.stdout.flush() - def flush(self): - pass -handler = logging.StreamHandler(MyStream()) -handler.setFormatter(logging.Formatter('%(message)s')) -rootlogger.addHandler(handler) - - -def teststring(s, name, globs=None, verbose=None, report=True, - optionflags=0, extraglobs=None, raise_on_error=False, - parser=doctest.DocTestParser()): - - from doctest import DebugRunner, DocTestRunner, master - - # Assemble the globals. - if globs is None: - globs = {} - else: - globs = globs.copy() - if extraglobs is not None: - globs.update(extraglobs) - - if raise_on_error: - runner = DebugRunner(verbose=verbose, optionflags=optionflags) - else: - runner = DocTestRunner(verbose=verbose, optionflags=optionflags) - - test = parser.get_doctest(s, globs, name, name, 0) - runner.run(test) - - if report: - runner.summarize() - - if master is None: - master = runner - else: - master.merge(runner) - - return runner.failures, runner.tries - -def replace_file(s, newfile): - engine = r"'(sqlite|postgresql|mysql):///.*'" - engine = re.compile(engine, re.MULTILINE) - s, n = re.subn(engine, "'sqlite:///" + newfile + "'", s) - if not n: - raise ValueError("Couldn't find suitable create_engine call to replace '%s' in it" % oldfile) - return s - -for filename in 'orm/tutorial','core/tutorial',: - filename = '%s.rst' % filename - s = open(filename).read() - #s = replace_file(s, ':memory:') - s = re.sub(r'{(?:stop|sql|opensql)}', '', s) - teststring(s, filename) - diff --git a/test/base/test_tutorials.py b/test/base/test_tutorials.py new file mode 100644 index 0000000000..73dcbb524f --- /dev/null +++ b/test/base/test_tutorials.py @@ -0,0 +1,144 @@ +from __future__ import print_function +from sqlalchemy.testing import fixtures +from sqlalchemy.testing import config +import doctest +import logging +import sys +import re +import os + + +class DocTest(fixtures.TestBase): + def _setup_logger(self): + rootlogger = logging.getLogger('sqlalchemy.engine.base.Engine') + + class MyStream(object): + def write(self, string): + sys.stdout.write(string) + sys.stdout.flush() + + def flush(self): + pass + + self._handler = handler = logging.StreamHandler(MyStream()) + handler.setFormatter(logging.Formatter('%(message)s')) + rootlogger.addHandler(handler) + + def _teardown_logger(self): + rootlogger = logging.getLogger('sqlalchemy.engine.base.Engine') + rootlogger.removeHandler(self._handler) + + def _setup_create_table_patcher(self): + from sqlalchemy.sql import ddl + self.orig_sort = ddl.sort_tables_and_constraints + + def our_sort(tables, **kw): + return self.orig_sort( + sorted(tables, key=lambda t: t.key), **kw + ) + ddl.sort_tables_and_constraints = our_sort + + def _teardown_create_table_patcher(self): + from sqlalchemy.sql import ddl + ddl.sort_tables_and_constraints = self.orig_sort + + def setup(self): + self._setup_logger() + self._setup_create_table_patcher() + + def teardown(self): + self._teardown_create_table_patcher() + self._teardown_logger() + + + def _run_doctest_for_content(self, name, content): + optionflags = ( + doctest.ELLIPSIS | doctest.NORMALIZE_WHITESPACE | + _get_allow_unicode_flag() + ) + runner = doctest.DocTestRunner( + verbose=None, optionflags=optionflags, + checker=_get_unicode_checker()) + globs = { + 'print_function': print_function} + parser = doctest.DocTestParser() + test = parser.get_doctest(content, globs, name, name, 0) + runner.run(test) + runner.summarize() + assert not runner.failures + + def _run_doctest(self, fname): + here = os.path.dirname(__file__) + sqla_base = os.path.normpath(os.path.join(here, "..", "..")) + path = os.path.join(sqla_base, "doc/build", fname) + if not os.path.exists(path): + config.skip_test("Can't find documentation file %r" % path) + with open(path) as file_: + content = file_.read() + content = re.sub(r'{(?:stop|sql|opensql)}', '', content) + self._run_doctest_for_content(fname, content) + + def test_orm(self): + self._run_doctest("orm/tutorial.rst") + + def test_core(self): + self._run_doctest("core/tutorial.rst") + + +# unicode checker courtesy py.test + + +def _get_unicode_checker(): + """ + Returns a doctest.OutputChecker subclass that takes in account the + ALLOW_UNICODE option to ignore u'' prefixes in strings. Useful + when the same doctest should run in Python 2 and Python 3. + + An inner class is used to avoid importing "doctest" at the module + level. + """ + if hasattr(_get_unicode_checker, 'UnicodeOutputChecker'): + return _get_unicode_checker.UnicodeOutputChecker() + + import doctest + import re + + class UnicodeOutputChecker(doctest.OutputChecker): + """ + Copied from doctest_nose_plugin.py from the nltk project: + https://github.com/nltk/nltk + """ + + _literal_re = re.compile(r"(\W|^)[uU]([rR]?[\'\"])", re.UNICODE) + + def check_output(self, want, got, optionflags): + res = doctest.OutputChecker.check_output(self, want, got, + optionflags) + if res: + return True + + if not (optionflags & _get_allow_unicode_flag()): + return False + + else: # pragma: no cover + # the code below will end up executed only in Python 2 in + # our tests, and our coverage check runs in Python 3 only + def remove_u_prefixes(txt): + return re.sub(self._literal_re, r'\1\2', txt) + + want = remove_u_prefixes(want) + got = remove_u_prefixes(got) + res = doctest.OutputChecker.check_output(self, want, got, + optionflags) + return res + + _get_unicode_checker.UnicodeOutputChecker = UnicodeOutputChecker + return _get_unicode_checker.UnicodeOutputChecker() + + +def _get_allow_unicode_flag(): + """ + Registers and returns the ALLOW_UNICODE flag. + """ + import doctest + return doctest.register_optionflag('ALLOW_UNICODE') -- 2.47.2