I have encountered some problems with sqlite3 and sqlalchemy. From some time I try to make some specific query and in some way I failed. The database is composed from two tables users, and Properties. Those tables have schema as shown bellow.
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)
);
The content of users table is pretty straightforward, but properties deserves for some dose of explanations. In property_number column I store different properties, each with its unique number, for example: property bald has number 3 and property tan has number 4 etc. If the User have multiple properties, every one of them occupies one row in the properties table. I choosed this style for easy way to add new properties without messing with migrations and stuff like that.
The problem is, a do not know how to make query which consist of multiple properties. My current best solution is, ask for every single property in separate query. This gives mi list of sets, two different ones. One for the positive and one for the negative instance of given property (positive equals stuff I would like user to have, negative equals stuff I would not like user to have). And in next step I make difference of the two subsets, and get final list which contains users' ids who have interesting for me properties. Then I make query for those users' names. It seems to be very complicated, maybe it is, but for sure it is ugly. I also do not like make single query for every single property. Python code if somone is interested.
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()
Is it a way for do it in one single query? I am new in the field of databases and sorry if the quality of the question seems to be lame. There is so many possibilities that I really do not know how to do it in the "right" way. I have searched the vast percent of the internet regarding this topic and best solution I have found was this containing "WHERE" Cause and "AND" Operator. But those two do not work if you connects two the same columns of the one table.
SELECT user_id FROM properties WHERE property_number=3 AND property_number=4;
Or in sqlalchemy.
db.query(User.user_id).join(Property).filter(and_(property_number=3, property_number=4)).all()
This sqlalchemy example may contain some error, because I have no preview for it, but for sure you will understand what is the point of this.
You can do this by using aggregation
SELECT user_id
FROM properties
WHERE property_number in (3, 4)
GROUP BY user_id
HAVING count(*) = 2
In 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()
update
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()
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments