使用 mysql-python 执行不同的查询 [英] Executing different queries using mysql-python

问题描述

我正在使用远程数据库将数据导入我的 Django proyect 数据库.

I'm working with a remote db for importing data to my Django proyect's db.

在 MySQLdb 的帮助下，我轻松地创建了如下所示的导入函数:

With the help of MySQLdb I've managed with ease to create an importing function like the following:

def connect_and_get_data(useful_string):
    CONNECTION = MySQLdb.connect(host=..., port=...,
                                 user=..., passwd=..., db=...,
                                 cursorclass=MySQLdb.cursors.DictCursor,
                                 charset = "utf8")
    cursor = CONNECTION.cursor()
    cursor.execute("SELECT ... FROM ... WHERE ... AND some_field=%s", (useful_string))
    result = cursor.fetchall()
    cursor.close()

对此非常满意，按预期工作.

Very happy with that, works as expected.

但是继续编写代码，我注意到有时我需要再次连接到数据库，以便执行其他不同的查询.

But going on with the code, I've noticed that sometimes I'll need to connect to the db again, in order to execute other different queries.

对我来说，第一个想法很合乎逻辑:对于我需要的每个查询，定义一个函数，该函数使用给定的查询作为参数调用 connect_and_get_data ......像这样:

The first idea was quite logical, to me: for every query I'll need, define a function which calls connect_and_get_data with the given query as parameter... something like this:

def get_data_about_first_amazing_topic(useful_string):
    query = "SELECT ... FROM ... WHERE ... AND some_field=%s" %(useful_string)
    connect_and_get_data(query)
    ...

def get_data_about_second_amazing_topic(other_useful_string):
    query = "SELECT ... FROM ... WHERE ... AND some_field=%s" %(other_useful_string)
    connect_and_get_data(query)
    ...

通过对connect_and_get_data的修改:

def connect_and_get_data(query):
    ...
    cursor.execute(query)
    ...

正如您已经想象的那样，这个解决方案失败了.

As you already might imagine, this solutions fails.

阅读 mluebke 对问题的回答 python mysql fetch query

Reading mluebke's answer to the question python mysql fetch query

您正在将参数传递给执行函数，而不是执行 python 字符串替换"

我立刻明白我错在哪里；但我仍然觉得缺少一些东西:我尝试了不同的解决方案，但我肯定对所有解决方案都不满意.

I understood immediately where I was wrong; but I still feel that something is missing: I've tried different solutions, but I'm definitely unhappy with all of them.

是否有一种好" 方法来封装我的 connect_and_get_data(query) 函数，以便按照我想要的方式为我服务，或者我完全处于这条路走错了吗?

Is there a "good" way to encapsulate my connect_and_get_data(query) function, in order to serve me the way I want, or I'm totally in the wrong path with this?

在这种情况下，哪些被视为最佳实践"?

Which ones are considered "best practices" in this situation?

推荐答案

我认为这就是您要找的.

I think this is what you're looking for.

def connect_and_get_data(query, data):
    ...
    cursor.execute(query, data)
    ...

def get_data_about_first_amazing_topic(useful_string):
    query = "SELECT ... FROM ... WHERE ... AND some_field=%s"
    connect_and_get_data(query, ("one","two","three"))
    ...

但是，如果您要快速进行多个查询，最好重用您的连接，因为连接太多会浪费时间.

But, if you're going to be making several queries quickly, it would be better to reuse your connection, since making too many connections can waste time.

...
CONNECTION = MySQLdb.connect(host=..., port=...,
                             user=..., passwd=..., db=...,
                             cursorclass=MySQLdb.cursors.DictCursor,
                             charset = "utf8")
cursor = CONNECTION.cursor()
cursor.execute("SELECT ... FROM ... WHERE ... AND some_field=%s", ("first", "amazing", "topic"))
first_result = cursor.fetchall()

cursor.execute("SELECT ... FROM ... WHERE ... AND some_field=%s", (("first", "amazing", "topic")))
second_result = cursor.fetchall()

cursor.close()
...

这将使您的代码执行得更好.

This will make your code perform much better.

这篇关于使用 mysql-python 执行不同的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！