django / python：具有多个表的原始sql

I need to perform a raw sql on multiple tables. I then render the result set. For one table I would do:

我需要在多个表上执行原始sql。然后我渲染结果集。我会做一个表:

sql = "select * from my_table"
results = my_table.objects.raw(sql)

For multiple tables I am doing:

对于我正在做的多个表:

sql = "select * from my_table, my_other_table where ...."
results = big_model.objects.raw(sql)

But, do I really need to create a table/model/class big_model, which contains all fields that I may need? I will never actually store any data in this "table".

但是,我真的需要创建一个表/模型/类big_model,其中包含我可能需要的所有字段吗?我永远不会在这个“表”中存储任何数据。

ADDED:

I have a table my_users. I have a table my_listings. These are defined in Models.py. The table my_listings has a foreign key to my_users, indicating who created the listing.

我有一张桌子my_users。我有一个表my_listings。这些在Models.py中定义。表my_listings具有my_users的外键,指示谁创建了列表。

The SQL is

SQL是

"select user_name, listing_text from my_listings, my_users where my_users.id = my_listings.my_user_id".

I want this SQL to generate a result set that I can use to render my page in django.

我希望这个SQL生成一个结果集,我可以使用它来在django中呈现我的页面。

The question is: Do I have to create a model that contains the fields user_name and listing_text? Or is there some better way that still uses raw SQL (select, from, where)? Of course, my actual queries are more complicated than this example. (The models that I define in models.py become actual tables in the database hence the use of the model/table term. Not sure how else to refer to them, sorry.) I use raw sql because I found that python table references only work with simple data models.

问题是:我是否必须创建包含字段user_name和listing_text的模型?或者还有一些更好的方法仍然使用原始SQL(select,from,where)?当然,我的实际查询比这个例子更复杂。 (我在models.py中定义的模型成为数据库中的实际表,因此使用了模型/表术语。不确定如何引用它们,抱歉。)我使用原始sql因为我发现只有python表引用使用简单的数据模型。

2 个解决方案

#1

This works. Don't know why it didn't before :( From Dennis Baker's comment:

这很有效。不知道为什么之前没有:(来自Dennis Baker的评论:

You do NOT need to have a model with all the fields in it, you just need the first model and fields from that. You do need to have the fields with unique names and as far as I know you should use "tablename.field as fieldname" to make sure you have all unique fields. I've done some fairly complex queries with 5+ tables this way and always tie them back to a single model. –

您不需要拥有包含其中所有字段的模型,您只需要第一个模型和字段。您确实需要具有唯一名称的字段,据我所知,您应该使用“tablename.field as fieldname”来确保您拥有所有唯一字段。我用这种方式用5+表做了一些相当复杂的查询,并且总是把它们绑回到单个模型。 -

2 . Another solution is to use a cursor. However, a cursor has to be changed from a list of tuples to a list of dictionaries. I'm sure there are cleaner ways using iterators, but this function works. It takes a string, which is the raw sql query, and returns a list which can be rendered and used in a template.

2。另一种解决方案是使用游标。但是,必须将游标从元组列表更改为字典列表。我确信使用迭代器有更简洁的方法,但这个功能有效。它需要一个字符串,它是原始的sql查询,并返回一个可以在模板中呈现和使用的列表。

from django.db import connection, transaction

def sql_select(sql):
    cursor = connection.cursor()
    cursor.execute(sql)
    results = cursor.fetchall()
    list = []
    i = 0
    for row in results:
        dict = {} 
        field = 0
        while True:
           try:
                dict[cursor.description[field][0]] = str(results[i][field])
                field = field +1
            except IndexError as e:
                break
        i = i + 1
        list.append(dict) 
    return list

2 个解决方案

#1

更多相关文章

随机推荐