[ Sqlite3 Obtaining Data From Rows with Similar values in columns ]
I have a table set up like:
Mobs
========
Name Room
-----------
a cat 2
fido 2
human 1
I am trying to set an sql query statement that extracts the name if each row has the same value of room.
This is my code so far, but it returns an empty list:
class DBConnect(object):
gamedb = 'game.db'
def __init__(self):
pass
############ TABLE PlayerInfo ###################
def modify_data(self, string, dbfile, mode='get', fetch='all'):
db = sqlite3.connect(dbfile)
db.row_factory = lambda cursor, row: row[0]
c = db.cursor()
data = ''
if mode == 'get':
c.execute(string)
if fetch == 'all':
data = c.fetchall()
elif fetch == 'one':
data = c.fetchone()
db.close()
return data
elif mode == 'update' or mode == 'insert':
# update data
c.execute(string)
db.commit()
db.close()
else:
print 'Something went wrong\nAborting Program...'
sys.exit()
def mob_getname(self, value, using="id"):
if using == "id":
query = "SELECT name FROM Mobs WHERE ID=%s" % value
return DBConnect().modify_data(query, DBConnect.gamedb)[0]
elif using == "room":
query = "SELECT name, room from Mobs " \
"GROUP BY name, room " \
"HAVING COUNT(name) > 1"
return DBConnect().modify_data(query, DBConnect.gamedb)
else:
print 'Wrong value of using'
return None
Trying:
print Mobs().mob_getname(2, using="room")
Comes up with an empty list?
I want to get a list that shows
['a cat', 'fido']
Thank you
UPDATE:::
I have tried replace the query that gets executed with:
query = "SELECT COUNT(*), room FROM Mobs GROUP BY Mobs " \
"SELECT COUNT(*), room FROM Mobs GROUP BY room HAVING COUNT(*)>1 " \
"SELECT name FROM Mobs WHERE room=%s " % value
Now I get an error: sqlite3.OperationalError: near "SELECT": syntax error
Answer 1
You cannot just put multiple independent SELECT queries one after the other.
You need a GROUP BY to determine rooms with more than one entry, and you then need a separate query to return all entries of those rooms:
SELECT Name, Room
FROM Mobs
WHERE Room IN (SELECT Room
FROM Mobs
GROUP BY Room
HAVING COUNT(*) > 1);
Answer 2
To obtain the desired result list that you have given, your query would simply be:
select Name from Mobs where Room = 2
Answer 3
You are not using group by and having clauses properly. Group by will try to group all the columns where that column matches. So if you group by name none of your rows will collapse and none will match the criteria of having count > 1
If I'm reading your question you simply need to
SELECT name FROM mobs WHERE room = %s
since you know the room number ahead of time.
Note, your second attempt is failing because you need ; between your queries