У меня возникли проблемы с sqlite3 и sqlalchemy. Некоторое время я пытался сделать какой-то конкретный запрос и в каком-то смысле у меня ничего не получалось. База данных состоит из двух таблиц пользователей и свойств. Эти таблицы имеют схему, показанную ниже.

sqlite> .schema users
CREATE TABLE users (
    id INTEGER NOT NULL, 
    name VARCHAR(50) NOT NULL, 
    PRIMARY KEY (id)
);

sqlite> .schema properties
CREATE TABLE properties (
    id INTEGER NOT NULL, 
    property_number INTEGER, 
    user_id INTEGER, 
    PRIMARY KEY (id), 
    FOREIGN KEY(user_id) REFERENCES users (id)
);

Содержимое таблицы пользователей довольно простое, но свойства заслуживают некоторой дозы пояснений. В столбце property_number я храню различные свойства, каждое со своим уникальным номером, например: свойство bald имеет номер 3, а свойство tan - номер 4 и т. Д. Если у пользователя есть несколько свойств, каждое из них занимает одну строку в таблице свойств. Я выбрал этот стиль, чтобы упростить добавление новых свойств, не вмешиваясь в миграции и тому подобное.

Проблема в том, что вы не знаете, как сделать запрос, состоящий из нескольких свойств. На данный момент лучшее решение - запрашивать каждое свойство в отдельном запросе. Это дает mi список наборов, два разных. Один для положительного и один для отрицательного экземпляра данного свойства (положительное равно тому, что я хотел бы иметь у пользователя, отрицательное равно тому, что я не хотел бы, чтобы пользователь имел). И на следующем этапе я различаю эти два подмножества и получаю окончательный список, который содержит идентификаторы пользователей, у которых есть интересующие меня свойства. Затем я запрашиваю имена этих пользователей. Вроде бы очень сложно, может и так, но уж точно некрасиво. Я также не люблю делать отдельные запросы для каждого отдельного свойства. Код Python, если кому-то интересно.

def prop_dicts():
    """Create dictionaries of properties
    contained in table properties in db.

    Returns:
        touple:
            prop_names (dict)
            prom_values (dict)."""

    prop_names = {'higins': 10000,
                  'tall': 1,
                  'fat': 2,
                  'bald': 3,
                  'tan': 4,
                  'hairry': 5}
    prop_values = {1000: 'higins',
                   1: 'tal',
                   2: 'fat',
                   3: 'bald',
                   4: 'tan',
                   5: 'hairry'}
    dictionaries = (prop_names, prop_values)
    return dictionaries


 def list_of_sets_intersection(set_list):
    """Makes intersection of all sets in list.

    Args:
        param1 (list): list containing sets to check.

    Returns:
        set (values): contains intersectred values."""

    if not set_list:
        return set()
    result = set_list[0]
    for s in set_list[1:]:
        result &= s
    return result


def list_of_sets_union(set_list):
    """Makes union of elements in all sets in list.

    Args:
        param1 (list): list containing sets to check.

    Returns:
        set (values): contains union values."""

    if not set_list:
        return set()
    result = set_list[0]
    for s in set_list[1:]:
        result |= s
    return result


def db_search():
    """Search database against positiv and negative values.

    Returns:
        list (sets): one set in list for every property in
                     table properties db."""

    n, v = prop_dicts()

    positive = [2, 3]
    negative = [4, 5]
    results_p = []
    results_n = []

    #Positive properties.
    for element in xrange(0, len(positive)):
        subresult = []

        for u_id, in db.query(Property.user_id).\
                              filter_by(property_number = positive[element]):
            subresult.append(u_id)

        subresult = set(subresult)
        results_p.append(subresult)

    #Negative properties.
    for element in xrange(0, len(negative)):
        subresult = []

        for u_id, in db.query(Property.user_id).\
                              filter_by(property_number = negative[element]):
            subresult.append(u_id)

        subresult = set(subresult)
        results_n.append(subresult)

    print 'positive --> ', results_p
    print 'negative --> ', results_n

    results_p = list_of_sets_intersection(results_p)
    results_n = list_of_sets_union(results_n)

    print 'positive --> ', results_p
    print 'negative --> ', results_n

    final_result = results_p.difference(results_n)
    return list(final_result)


print db_search()    

Это способ сделать это одним запросом? Я новичок в области баз данных и прошу прощения, если качество вопроса кажется неуместным. Существует так много возможностей, что я действительно не знаю, как это сделать «правильным» способом. Я провел поиск по этой теме в огромном проценте Интернета, и лучшим решением, которое я нашел, было то, что оно содержало причину «WHERE» и оператор «AND». Но эти два не работают, если вы соедините два одинаковых столбца одной таблицы.

SELECT user_id FROM properties WHERE property_number=3 AND property_number=4;

Или в sqlalchemy.

db.query(User.user_id).join(Property).filter(and_(property_number=3, property_number=4)).all()

Этот пример sqlalchemy может содержать некоторую ошибку, потому что у меня нет для него предварительного просмотра, но вы наверняка поймете, в чем смысл этого.

frankot

Ответов: 1

Ответы (1)

Это можно сделать с помощью агрегирования

SELECT user_id
FROM properties
WHERE property_number in (3, 4)
GROUP BY user_id
HAVING count(*) = 2

в SQLAlchemy

from sqlalchemy import func

properties = [3, 4]
db.session.query(Property.user_id)\
    .filter(Property.property_number.in_(properties))\
    .group_by(Property.user_id)\
    .having(func.count()==len(properties))\
    .all()

обновить

positive = [2, 3]
negative = [4, 5]

positive_query = db.session.query(Property.user_id)\
    .filter(Property.property_number.in_(positive))\
    .group_by(Property.user_id)\
    .having(func.count()==len(positive))

negative_query = db.session.query(Property.user_id)\
    .filter(Property.property_number.in_(negative))\
    .distinct()

final_result = positive_query.except_(negative_query).all()

2022 WebDevInsider