2017年12月4日 星期一

[ Python 文章收集 ] SQLAlchemy - SQL Expression Language Tutorial - Part2

Source From Here 
Everything Else 
The concepts of creating SQL expressions have been introduced. What’s left are more variants of the same themes. So now we’ll catalog the rest of the important things we’ll need to know. 

Bind Parameter Objects 
Throughout all these examples, SQLAlchemy is busy creating bind parameters wherever literal expressions occur. You can also specify your own bind parameters with your own names, and use the same statement repeatedly. The bindparam() construct is used to produce a bound parameter with a given name. While SQLAlchemy always refers to bound parameters by name on the API side, the database dialect converts to the appropriate named or positional style 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'))
>>> conn.execute(s, username='wendy').fetchall()
[(2, u'wendy', u'Wendy Williams')]

Another important aspect of bindparam() is that it may be assigned a type. The type of the bind parameter will determine its behavior within expressions and also how the data bound to it is processed before being sent off to the database: 
>>> s = users.select(users.c.name.like(bindparam('username', type_=String) + text("'%'")))
>>> str(s)
"SELECT users.id, users.name, users.fullname \nFROM users \nWHERE users.name LIKE (:username || '%')"
>>> conn.execute(s, username='wendy').fetchall()
2017-12-05 12:36:52,617 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname
FROM users
WHERE users.name LIKE (? || '%')
2017-12-05 12:36:52,617 INFO sqlalchemy.engine.base.Engine ('wendy',)
[(2, u'Wendy', u'Wendy Williams')]

bindparam() constructs of the same name can also be used multiple times, where only a single named value is needed in the execute parameters: 
  1. >>> s = select([users, addresses]).\  
  2. ...     where(  
  3. ...        or_(  
  4. ...          users.c.name.like(  
  5. ...                 bindparam('name', type_=String) + text("'%'")),  
  6. ...          addresses.c.email_address.like(  
  7. ...                 bindparam('name', type_=String) + text("'@%'"))  
  8. ...        )  
  9. ...     ).\  
  10. ...     select_from(users.outerjoin(addresses)).\  
  11. ...     order_by(addresses.c.id)  
  12. >>> conn.execute(s, name='jack').fetchall()  
  13. [(1, u'jack', u'Jack Jones'11, u'jack@yahoo.com'), (1, u'jack', u'Jack Jones'21, u'jack@msn.com')]  
Functions 
SQL functions are created using the func keyword, which generates functions using attribute access: 
>>> from sqlalchemy.sql import func
>>> print(func.now())
now()

>>> 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())
xyz_my_goofy_function()

Certain function names are known by SQLAlchemy, allowing special behavioral rules to be applied. Some for example are “ANSI” functions, which mean they don’t get the parenthesis added after them, such as CURRENT_TIMESTAMP
>>> print(func.current_timestamp())
CURRENT_TIMESTAMP

Functions are most typically used in the columns clause of a select statement, and can also be labeled as well as given a type. Labeling a function is recommended so that the result can be targeted in a result row based on a string name, and assigning it a type is required when you need result-set processing to occur, such as for Unicode conversion and date conversions. Below, we use the result function scalar() to just read the first column of the first row and then close the result; the label, even though present, is not important in this case: 
  1. >>> conn.execute(  
  2. ...     select([  
  3. ...            func.max(addresses.c.email_address, type_=String).  
  4. ...                label('maxemail')  
  5. ...           ])  
  6. ...     ).scalar()  
  7. SELECT max(addresses.email_address) AS maxemail  
  8. FROM addresses  
  9. ()  
  10. u'www@www.org'  
Databases such as PostgreSQL and Oracle which support functions that return whole result sets can be assembled into selectable units, which can be used in statements. Such as, a database function calculate() which takes the parameters x and y, and returns three columns which we’d like to name q, z and r, we can construct using “lexical” column objects as well as bind parameters: 
  1. >>> from sqlalchemy.sql import column  
  2. >>> calculate = select([column('q'), column('z'), column('r')]).\  
  3. ...        select_from(  
  4. ...             func.calculate(  
  5. ...                    bindparam('x'),  
  6. ...                    bindparam('y')  
  7. ...                )  
  8. ...             )  
  9. >>> calc = calculate.alias()  
  10. >>> print(select([users]).where(users.c.id > calc.c.z))  
  11. SELECT users.id, users.name, users.fullname  
  12. FROM users, (SELECT q, z, r  
  13. FROM calculate(:x, :y)) AS anon_1  
  14. WHERE users.id > anon_1.z  
If we wanted to use our calculate statement twice with different bind parameters, the unique_params() function will create copies for us, and mark the bind parameters as “unique” so that conflicting names are isolated. Note we also make two separate aliases of our selectable: 
  1. >>> calc1 = calculate.alias('c1').unique_params(x=17, y=45)  
  2. >>> calc2 = calculate.alias('c2').unique_params(x=5, y=12)  
  3. >>> s = select([users]).\  
  4. ...         where(users.c.id.between(calc1.c.z, calc2.c.z))  
  5. >>> print(s)  
  6. SELECT users.id, users.name, users.fullname  
  7. FROM users,  
  8.     (SELECT q, z, r FROM calculate(:x_1, :y_1)) AS c1,  
  9.     (SELECT q, z, r FROM calculate(:x_2, :y_2)) AS c2  
  10. WHERE users.id BETWEEN c1.z AND c2.z  
  11.   
  12. >>> s.compile().params   
  13. {u'x_2'5, u'y_2'12, u'y_1'45, u'x_1'17}  
There are more sub-sections as below: 
Window Functions
Unions and Other Set Operations
Scalar Selects
Correlated Subqueries
Ordering, Grouping, Limiting, Offset…ing…


Inserts, Updates and Deletes 
We’ve seen insert() demonstrated earlier in this tutorial. Where insert() produces INSERT, the update() method produces UPDATE. Both of these constructs feature a method called values() which specifies the VALUES or SET clause of the statement. 

The values() method accommodates any column expression as a value: 
>>> stmt = users.update().values(fullname='Fullname: ' + users.c.name)
>>> str(stmt)
'UPDATE users SET fullname=(:name_1 || users.name)'
>>> conn.execute(stmt)
2017-12-05 13:25:33,076 INFO sqlalchemy.engine.base.Engine UPDATE users SET fullname=(? || users.name)
2017-12-05 13:25:33,076 INFO sqlalchemy.engine.base.Engine ('Fullname: ',)
2017-12-05 13:25:33,076 INFO sqlalchemy.engine.base.Engine COMMIT

When using insert() or update() in an “execute many” context, we may also want to specify named bound parameters which we can refer to in the argument list. The two constructs will automatically generate bound placeholders for any column names passed in the dictionaries sent to execute() at execution time. However, if we wish to use explicitly targeted named parameters with composed expressions, we need to use the bindparam() construct. When using bindparam()with insert() or update(), the names of the table’s columns themselves are reserved for the “automatic” generation of bind names. We can combine the usage of implicitly available bind names and explicitly named parameters as in the example below: 
  1. >>> stmt = users.insert().\  
  2. ...         values(name=bindparam('_name') + " .. name")  
  3. >>> conn.execute(stmt, [  
  4. ...        {'id':4'_name':'name1'},  
  5. ...        {'id':5'_name':'name2'},  
  6. ...        {'id':6'_name':'name3'},  
  7. ...     ])  
  8. INSERT INTO users (id, name) VALUES (?, (? || ?))  
  9. ((4'name1'' .. name'), (5'name2'' .. name'), (6'name3'' .. name'))  
  10. COMMIT  
  11.   
An UPDATE statement is emitted using the update() construct. This works much like an INSERT, except there is an additional WHERE clause that can be specified: 
  1. >>> stmt = users.update().\  
  2. ...             where(users.c.name == 'jack').\  
  3. ...             values(name='ed')  
  4.   
  5. >>> conn.execute(stmt)  
  6. UPDATE users SET name=? WHERE users.name = ?  
  7. ('ed''jack')  
  8. COMMIT  
  9.   
When using update() in an “executemany” context, we may wish to also use explicitly named bound parameters in the WHERE clause. Again, bindparam() is the construct used to achieve this: 
  1. >>> stmt = users.update().\  
  2. ...             where(users.c.name == bindparam('oldname')).\  
  3. ...             values(name=bindparam('newname'))  
  4. >>> conn.execute(stmt, [  
  5. ...     {'oldname':'jack''newname':'ed'},  
  6. ...     {'oldname':'wendy''newname':'mary'},  
  7. ...     {'oldname':'jim''newname':'jake'},  
  8. ...     ])  
  9. UPDATE users SET name=? WHERE users.name = ?  
  10. (('ed''jack'), ('mary''wendy'), ('jake''jim'))  
  11. COMMIT  
  12.   

Correlated Updates 
A correlated update lets you update a table using selection from another table, or the same table: 
  1. >>> stmt = select([addresses.c.email_address]).\  
  2. ...             where(addresses.c.user_id == users.c.id).\  
  3. ...             limit(1)  
  4. >>> conn.execute(users.update().values(fullname=stmt))  
  5. UPDATE users SET fullname=(SELECT addresses.email_address  
  6.     FROM addresses  
  7.     WHERE addresses.user_id = users.id  
  8.     LIMIT ? OFFSET ?)  
  9. (10)  
  10. COMMIT  
  11.   

Multiple Table Updates 
The PostgreSQL, Microsoft SQL Server, and MySQL backends all support UPDATE statements that refer to multiple tables. For PG and MSSQL, this is the “UPDATE FROM” syntax, which updates one table at a time, but can reference additional tables in an additional “FROM” clause that can then be referenced in the WHERE clause directly. On MySQL, multiple tables can be embedded into a single UPDATE statement separated by a comma. The SQLAlchemy update() construct supports both of these modes implicitly, by specifying multiple tables in the WHERE clause: 
  1. stmt = users.update().\  
  2.         values(name='ed wood').\  
  3.         where(users.c.id == addresses.c.id).\  
  4.         where(addresses.c.email_address.startswith('ed%'))  
  5. conn.execute(stmt)  
The resulting SQL from the above statement would render as: 
UPDATE users SET name=:name FROM addresses
WHERE users.id = addresses.id AND
addresses.email_address LIKE :email_address_1 || '%'

When using MySQL, columns from each table can be assigned to in the SET clause directly, using the dictionary form passed to Update.values()
  1. stmt = users.update().\  
  2.         values({  
  3.             users.c.name:'ed wood',  
  4.             addresses.c.email_address:'ed.wood@foo.com'  
  5.         }).\  
  6.         where(users.c.id == addresses.c.id).\  
  7.         where(addresses.c.email_address.startswith('ed%'))  
The tables are referenced explicitly in the SET clause: 
UPDATE users, addresses SET addresses.email_address=%s,
users.name=%s WHERE users.id = addresses.id
AND addresses.email_address LIKE concat(%s, '%')

SQLAlchemy doesn’t do anything special when these constructs are used on a non-supporting database. The UPDATE FROM syntax generates by default when multiple tables are present, and the statement will be rejected by the database if this syntax is not supported. 

Parameter-Ordered Updates 
The default behavior of the update() construct when rendering the SET clauses is to render them using the column ordering given in the originating Table object. This is an important behavior, since it means that the rendering of a particular UPDATE statement with particular columns will be rendered the same each time, which has an impact on query caching systems that rely on the form of the statement, either client side or server side. Since the parameters themselves are passed to the Update.values() method as Python dictionary keys, there is no other fixed ordering available. 

However in some cases, the order of parameters rendered in the SET clause of an UPDATE statement can be significant. The main example of this is when using MySQL and providing updates to column values based on that of other column values. The end result of the following statement: 
  1. UPDATE some_table SET x = y + 10, y = 20  
Will have a different result than: 
  1. UPDATE some_table SET y = 20, x = y + 10  
This because on MySQL, the individual SET clauses are fully evaluated on a per-value basis, as opposed to on a per-row basis, and as each SET clause is evaluated, the values embedded in the row are changing. To suit this specific use case, the preserve_parameter_order flag may be used. When using this flag, we supply a Python list of 2-tuples as the argument to the Update.values() method: 
  1. stmt = some_table.update(preserve_parameter_order=True).\  
  2.     values([(some_table.c.y, 20), (some_table.c.x, some_table.c.y + 10)])  
The list of 2-tuples is essentially the same structure as a Python dictionary except it is ordered. Using the above form, we are assured that the “y” column’s SET clause will render first, then the “x” column’s SET clause. 

Deletes 
Finally, a delete. This is accomplished easily enough using the delete() construct: 
  1. >>> conn.execute(addresses.delete())  
  2. DELETE FROM addresses  
  3. ()  
  4. COMMIT  
  5.   
  6.   
  7. >>> conn.execute(users.delete().where(users.c.name > 'm'))  
  8. DELETE FROM users WHERE users.name > ?  
  9. ('m',)  
  10. COMMIT  
  11.   
Matched Row Counts 
Both of update() and delete() are associated with matched row counts. This is a number indicating the number of rows that were matched by the WHERE clause. Note that by “matched”, this includes rows where no UPDATE actually took place. The value is available as rowcount
>>> result = conn.execute(users.delete())
DELETE FROM users
()
COMMIT

>>> result.rowcount
1


Further Reference 
Expression Language Reference: SQL Statements and Expressions API 
Database Metadata Reference: Describing Databases with MetaData 
Engine Reference: Engine Configuration 
Connection Reference: Working with Engines and Connections 
Types Reference: Column and Data Types

沒有留言:

張貼留言

[ Py DS ] Ch1 - IPython: Beyond Normal Python

Source From  Here   Keyboard Shortcuts in the IPython Shell   If you spend any amount of time on the computer, you’ve probably found a u...