Python的ORM框架中SQLAlchemy库的查询操作的教程
1. 返回列表和标量(Scalar)
前面我们注意到Query对象可以返回可迭代的值(iterator value)，然后我们可以通过for in来查询。不过Query对象的all()、one()以及first()方法将返回非迭代值(non-iterator value)，比如说all()返回的是一个列表：
>>> query = session.query(User).\
>>> filter(User.name.like('%ed')).order_by(User.id)
>>> query.all()
SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name LIKE ? ORDER BY users.id
('%ed',)
[User('ed','Ed Jones', 'f8s7ccs'), User('fred','Fred Flinstone', 'blah')]
first()方法限制并仅作为标量返回结果集的第一条记录：
>>> query.first()
SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name LIKE ? ORDER BY users.id
LIMIT ? OFFSET ?
('%ed', 1, 0)
<User('ed','Ed Jones', 'f8s7ccs')>
one()方法，完整的提取所有的记录行，并且如果没有明确的一条记录行(没有找到这条记录)或者结果中存在多条记录行，将会引发错误异常NoResultFound或者MultipleResultsFound：
>>> from sqlalchemy.orm.exc import MultipleResultsFound
>>> try:
... user = query.one()
... except MultipleResultsFound, e:
... print e
SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name LIKE ? ORDER BY users.id
('%ed',)
Multiple rows were found for one()
>>> from sqlalchemy.orm.exc import NoResultFound
>>> try:
... user = query.filter(User.id == 99).one()
... except NoResultFound, e:
... print e
SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name LIKE ? AND users.id = ? ORDER BY users.id
('%ed', 99)
No row was found for one()
2. 使用原义SQL (Literal SQL)
Query对象能够灵活的使用原义SQL查询字符串作为查询参数，比如我们之前用过的filter()和order_by()方法：
>>> for user in session.query(User).\
... filter("id<224").\
... order_by("id").all():
... print user.name
SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE id<224 ORDER BY id
()
ed
wendy
mary
fred
当然很多人可能会和我感觉一样，会有些不适应，因为使用ORM就是为了摆脱SQL语句的，没想到现在又看到SQL的影子了。呵呵，SQLAlchemy也要照顾到使用上的灵活性嘛，毕竟有些查询语句直接编入要容易得多。
当然绑定参数也可以用基于字符串的SQL指派，使用冒号来标记替代参数，然后再使用params()方法指定相应的值：
>>> session.query(User).filter("id<:value and name=:name").\
... 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,
users.password AS users_password
FROM users
WHERE id<User('fred','Fred Flinstone', 'blah')>
到这里，SQL语句的样子已经初见端倪了，其实我们可以更极端一点，直接使用SQL语句，什么？这样就失去ORM的价值了！别急，这里只是介绍一下支持这种用法，当然我建议不到万不得已，尽量不要这样写，因为可能会有兼容的问题，毕竟各个数据库的SQL方言不一样。不过有一点需要注意的是，如果要直接使用原生SQL语句，在被query()所查询的映射类中，你必须保证语句所指代的列仍然被映射类所管理，比如接下来的例子：
>>> session.query(User).from_statement(
... "SELECT * FROM users where name=:name").\
... params(name='ed').all()
SELECT * FROM users where name=?
('ed',)
[<User('ed','Ed Jones', 'f8s7ccs')>]
我们还可以在query()中直接使用列名来指派我们想要的列而摆脱映射类的束缚：
>>> session.query("id", "name", "thenumber12").\
... from_statement("SELECT id, name, 12 as "
... "thenumber12 FROM users where name=:name").\
... params(name='ed').all()
SELECT id, name, 12 as thenumber12 FROM users where name=?
('ed',)
[(1, u'ed', 12)]
3. 计数 (Counting)
对于Query来说，计数功能也有个单独的方法称为count()：
>>> 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,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name LIKE ?) AS anon_1
('%ed',)
2
count()方法被用于确定返回的结果集中有多少行，让我们观察一下产生的SQL语句，SQLAlchemy先是取出符合条件的所有行集合，然后再通过SELECT count(*)来统计有多少行。当然有点SQL知识的同学可能知道这条语句可以以更精简的方式写出来，比如SELECT count(*) FROM table，当然现代版本的SQLAlchemy不会去揣摩这样的想法。
假使我们要让查询语句更加精炼或者要明确要统计的列，我们可以通过表达式func.count()直接使用count函数，比如下面的例子介绍统计并返回每个唯一的用户名字：
>>> from sqlalchemy import func >>> 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 () [(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]
对于刚才提到的简单SELECT count(*) FROM table语句，我们可以通过下面的例子来实现：
>>> session.query(func.count('*')).select_from(User).scalar()
SELECT count(?) AS count_1
FROM users
('*',)
4
当然如果我们直接统计User的主键，上面的语句可以更加简练，我们可以省去select_from()方法：
>>> session.query(func.count(User.id)).scalar() SELECT count(users.id) AS count_1 FROM users () 4
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1375
52
What is the reason why PS keeps showing loading?
Apr 06, 2025 pm 06:39 PM
PS "Loading" problems are caused by resource access or processing problems: hard disk reading speed is slow or bad: Use CrystalDiskInfo to check the hard disk health and replace the problematic hard disk. Insufficient memory: Upgrade memory to meet PS's needs for high-resolution images and complex layer processing. Graphics card drivers are outdated or corrupted: Update the drivers to optimize communication between the PS and the graphics card. File paths are too long or file names have special characters: use short paths and avoid special characters. PS's own problem: Reinstall or repair the PS installer.
How to solve the problem of loading when PS is started?
Apr 06, 2025 pm 06:36 PM
A PS stuck on "Loading" when booting can be caused by various reasons: Disable corrupt or conflicting plugins. Delete or rename a corrupted configuration file. Close unnecessary programs or upgrade memory to avoid insufficient memory. Upgrade to a solid-state drive to speed up hard drive reading. Reinstalling PS to repair corrupt system files or installation package issues. View error information during the startup process of error log analysis.
How to solve the problem of loading when the PS opens the file?
Apr 06, 2025 pm 06:33 PM
"Loading" stuttering occurs when opening a file on PS. The reasons may include: too large or corrupted file, insufficient memory, slow hard disk speed, graphics card driver problems, PS version or plug-in conflicts. The solutions are: check file size and integrity, increase memory, upgrade hard disk, update graphics card driver, uninstall or disable suspicious plug-ins, and reinstall PS. This problem can be effectively solved by gradually checking and making good use of PS performance settings and developing good file management habits.
How to solve the problem of loading when PS is always showing that it is loading?
Apr 06, 2025 pm 06:30 PM
PS card is "Loading"? Solutions include: checking the computer configuration (memory, hard disk, processor), cleaning hard disk fragmentation, updating the graphics card driver, adjusting PS settings, reinstalling PS, and developing good programming habits.
How to use mysql after installation
Apr 08, 2025 am 11:48 AM
The article introduces the operation of MySQL database. First, you need to install a MySQL client, such as MySQLWorkbench or command line client. 1. Use the mysql-uroot-p command to connect to the server and log in with the root account password; 2. Use CREATEDATABASE to create a database, and USE select a database; 3. Use CREATETABLE to create a table, define fields and data types; 4. Use INSERTINTO to insert data, query data, update data by UPDATE, and delete data by DELETE. Only by mastering these steps, learning to deal with common problems and optimizing database performance can you use MySQL efficiently.
How does PS feathering control the softness of the transition?
Apr 06, 2025 pm 07:33 PM
The key to feather control is to understand its gradual nature. PS itself does not provide the option to directly control the gradient curve, but you can flexibly adjust the radius and gradient softness by multiple feathering, matching masks, and fine selections to achieve a natural transition effect.
How to optimize database performance after mysql installation
Apr 08, 2025 am 11:36 AM
MySQL performance optimization needs to start from three aspects: installation configuration, indexing and query optimization, monitoring and tuning. 1. After installation, you need to adjust the my.cnf file according to the server configuration, such as the innodb_buffer_pool_size parameter, and close query_cache_size; 2. Create a suitable index to avoid excessive indexes, and optimize query statements, such as using the EXPLAIN command to analyze the execution plan; 3. Use MySQL's own monitoring tool (SHOWPROCESSLIST, SHOWSTATUS) to monitor the database health, and regularly back up and organize the database. Only by continuously optimizing these steps can the performance of MySQL database be improved.
What should I do if the PS card is in the loading interface?
Apr 06, 2025 pm 06:54 PM
The loading interface of PS card may be caused by the software itself (file corruption or plug-in conflict), system environment (due driver or system files corruption), or hardware (hard disk corruption or memory stick failure). First check whether the computer resources are sufficient, close the background program and release memory and CPU resources. Fix PS installation or check for compatibility issues for plug-ins. Update or fallback to the PS version. Check the graphics card driver and update it, and run the system file check. If you troubleshoot the above problems, you can try hard disk detection and memory testing.
