未加星标

Formatted SQL in Python with Psycopg’s Mogrify

字体大小 | |
[开发(python) 所属分类 开发(python) | 发布者 店小二03 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

In this Compose Write Stuff Addon, Lucero Del Alba takes a look at the problem of viewing queries sent to a server, and how to solve this problem by using Psycopg's mogrify. Do you want to shed light on a favorite feature in your preferred database? Why not write a short "Addon" forWrite Stuff?

When using templates and variables within your script, it's hard to tell what the final query that was sent to the server was, which makes debugging tricky. This is where mogrify can help you.

The Problem

If you have a buggy query, the python interpreter is going to tell you where the syntax error is, and it will do so by passing you back the DB response. Sometimes, however, it’s not quite a syntax error that's bugging you, but just an empty result set that makes you wonder what went wrong, like:

did you query the right tables? was there some typo on the conditional clauses? did you use a lot of substitutions or build the query in such a way that you lost track of it?

Let’s take a step backwards for a moment.

If you use the Python DB API v2 properly you don’t need to worry about input sanitation because the driver does it transparently for you. For example, consider the following query:

SELECT * FROM brokers WHERE broker_name LIKE 'A%' AND broker_id > 10

If A% and 10 need to be variables, you can do the following:

SQL = 'SELECT * FROM brokers WHERE broker_name LIKE %s AND broker_id > %s' broker_name = 'A%' broker_id = 10 cursor.execute(SQL, (broker_name, broker_id))

In that snippet, execute takes a string with the query as a mandatory argument and a tuple as an optional one. The driver will sanitize the tuple’s content for you ― in this case broker_name and broker_id ― to prevent some SQL injection tricks, and it will treat them as string and integer, accordingly.

The problem is that you just can’t print out cursor.execute() to see what the query was , and even if you turn that line into a print() statement, it’ll most likely not return the actual query unless you’re only dealing with integers:

print(SQL % (broker_name, broker_id)) SELECT * FROM brokers WHERE broker_name LIKE A% AND broker_id > 10

That is not the query that’s sent to the server ― there are no string escaping characters (no apostrophes around A% ), let alone any sanitation. If you try to run that query on the DB, you’ll receive an error.

Mogrify It

The PostgreSQL driver for Python, Psycopg, comes with a very handy method for dealing with these situations: mogrify . From the Psycopg documentation, mogrify :

Return[s] a query string after arguments binding. The string returned is exactly the one that would be sent to the database running the execute() method or similar

You can run mogrify() with the same arguments you would use for execute() , and the result will be as expected:

print(cursor.mogrify(SQL, (broker_name, broker_id))) b"SELECT * FROM brokers WHERE broker_name LIKE 'A%' AND broker_id > 10"

Now this was a simple enough query, but that complexity may escalate very quickly as you start joining tables using Python’s templates, such as {table_name} , to format later with the format() method when building your SQL code, and adding lots of variable substitutions … yes, things will get messy.

Take It Further

In fact, you can implement mogrify() in your workflow and use it as an intermediate step when querying the database, so that you can benefit from easier debugging later:

SQL = "SELECT broker_id, broker_name FROM brokers WHERE broker_name LIKE %s" query = cursor.mogrify(SQL, ('A%', )) cursor.execute(query) fetchall_brokers = cursor.fetchall()

Notice that instead of doing the substitutions on execute() , you first mogrify the query and use that output for execution. Now you have the SQL template on a SQL constant, and the actual query in a query variable. Should you have a problem later, you just check that variable.

Not a Python DB API Feature … But There’s a Workaround

Unfortunately, mogrify is not a method defined by the Python DB API, but instead an add-on of the Psycopg driver.

If you're using mysql, you have a workaround to this problem so that you can see the actual query:

import MySQLdb conn = MySQLdb.connect() cursor = conn.cursor() cursor.execute('SELECT %s, %s', ('bar', 1)) cursor._executed b"SELECT 'bar', 1"

However, this is a post hoc analysis (after the fact) will not allow you to see the full query if it provoked an exception in the program (e.g. a syntax error).

In PostgreSQL, you can also do this with the Psycopg driver by replacing _executed with query in the last line, like this:

import psycopg2 conn = psycopg2.connect() cursor = conn.cursor() cursor.execute('SELECT %s, %s', ('bar', 1)) cursor.query b"SELECT 'bar', 1" Conclusions

We've shown ways in Python to see what’s the actual query that’s been sent to the server, and this will save you quality time when debugging. If you want to go further, consider that Psycopg's mogrify for PostgreSQL allows you to cache the actual executed statement so you can reuse it whenever you need it.


Formatted SQL in Python with Psycopg’s Mogrify
Lucero dances, plays music , writes about random topics, leads projects to varying and doubtful degrees of success, and keeps trying to be real and he keeps failing.
Formatted SQL in Python with Psycopg’s Mogrify

This article is licensed with CC-BY-NC-SA 4.0 by Compose.

本文开发(python)相关术语:python基础教程 python多线程 web开发工程师 软件开发工程师 软件开发流程

主题: SQLMySQLPostgreSQLPython
分页:12
转载请注明
本文标题:Formatted SQL in Python with Psycopg’s Mogrify
本站链接:http://www.codesec.net/view/481256.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 开发(python) | 评论(0) | 阅读(41)