用python生成SQL语句 [英] Generate SQL statements with python

问题描述

我需要从html文件生成一个插入语句（用于postgresql）列表，是否有适用于python的库来帮助我正确地转义并引用名称/值？在PHP中，我使用PDO进行转义和引用，是否有python的等效库？

I need to generate a list of insert statements (for postgresql) from html files, is there a library available for python to help me properly escape and quote the names/values? in PHP i use PDO to do the escaping and quoting, is there any equivalent library for python?

编辑：我需要生成一个带有sql语句的文件，以便以后执行

I need to generate a file with sql statements for execution later

推荐答案

我知道这是一个老问题，但是我经常想要OP想要的东西：一个非常简单的库生成基本的SQL。

I know this is an old question, but I've often wanted what it seems the OP wants: A VERY simple library for generating basic SQL.

下面的函数就是这样做的。您为他们提供一个表名和一个包含要使用的数据的字典，它们将返回所需操作的SQL查询。

The below functions do just that. You give them a table name and a dictionary containing the data you want to use and they return the SQL query for the operation you need.

键/值对表示字段数据库行中的名称和值。

The key/value pairs represent field names and values in the database rows.

def read(table, **kwargs):
    """ Generates SQL for a SELECT statement matching the kwargs passed. """
    sql = list()
    sql.append("SELECT * FROM %s " % table)
    if kwargs:
        sql.append("WHERE " + " AND ".join("%s = '%s'" % (k, v) for k, v in kwargs.iteritems()))
    sql.append(";")
    return "".join(sql)


def upsert(table, **kwargs):
    """ update/insert rows into objects table (update if the row already exists)
        given the key-value pairs in kwargs """
    keys = ["%s" % k for k in kwargs]
    values = ["'%s'" % v for v in kwargs.values()]
    sql = list()
    sql.append("INSERT INTO %s (" % table)
    sql.append(", ".join(keys))
    sql.append(") VALUES (")
    sql.append(", ".join(values))
    sql.append(") ON DUPLICATE KEY UPDATE ")
    sql.append(", ".join("%s = '%s'" % (k, v) for k, v in kwargs.iteritems()))
    sql.append(";")
    return "".join(sql)


def delete(table, **kwargs):
    """ deletes rows from table where **kwargs match """
    sql = list()
    sql.append("DELETE FROM %s " % table)
    sql.append("WHERE " + " AND ".join("%s = '%s'" % (k, v) for k, v in kwargs.iteritems()))
    sql.append(";")
    return "".join(sql)

您可以这样使用。只需为其提供一个表名和一个字典（或使用python的** kwargs功能）：

You use it like so. Just give it a table name and a dictionary (or use the **kwargs feature of python):

>>> upsert("tbl", LogID=500, LoggedValue=5)
"INSERT INTO tbl (LogID, LoggedValue) VALUES ('500', '5') ON DUPLICATE KEY UPDATE LogID = '500', LoggedValue = '5';"

>>> read("tbl", **{"username": "morten"})
"SELECT * FROM tbl WHERE username = 'morten';"

>>> read("tbl", **{"user_type": 1, "user_group": "admin"})
"SELECT * FROM tbl WHERE user_type = '1' AND user_group = 'admin';"

但是要小心SQL注入攻击

看看当您的代码的恶意用户这样做时会发生什么：

Look what happens when a malicious user of your code does this:

>>> read("tbl", **{"user_group": "admin'; DROP TABLE tbl; --"})
"SELECT * FROM tbl WHERE user_group = 'admin'; DROP TABLE tbl; --';"

很容易做出自己的临时ORM，但您只能得到所看到的-必须逃避您自己输入：）

It's easy to make your own makeshift ORM but you only get what you see -- you have to escape the input yourself :)

这篇关于用python生成SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！