Reputation: 11
I am trying to run an execute statement with python in sql to return all the ids that match up with one value, in this case x. There are multiple items that should match the variable(x) that I am trying to pass through but when I run this line I only get one item in the list and the rest get left behind. When I execute the line in the sqlite browser it give me a table with all the matching data I want but for some reason this doesn't pass it all into the list.
cur.execute('SELECT movie_id FROM MG WHERE genre_id = ?', (x, ))
for x in cur:
midg.append(y[0])
Here is my entire code so far:
gnames = list()
cur.execute('SELECT genre_name FROM Genre')
for row in cur:
gnames.append(row[0])
print(gnames)
gid = list()
midg = list()
grating = list()
grate = dict()
for namew in gnames:
gid.clear()
grating.clear()
midg.clear()
cur.execute('SELECT genre_id FROM Genre WHERE genre_name = ?', (namew, ))
x = cur.fetchone()[0]
cur.execute('SELECT movie_id FROM MG WHERE genre_id = ?', (x, ))
for x in cur:
midg.append(y[0])
for z in midg:
cur.execute('SELECT movie_rating FROM Movie WHERE movie_id = ?', (z, ))
for row in cur:
grating.append(row[0])
gaverage = sum(grating)/len(grating)
grate[namew] = gaverage
Upvotes: 1
Views: 495
Reputation: 102
try it.
#Install package
#pip install pyodbc
import pyodbc
server = 'server'
database = 'database'
username = 'username'
password = 'password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT * from INFORMATION_SCHEMA.TABLES")
row = cursor.fetchone()
while row:
#TABLE_CATALOG
print(row[0])
print()
#TABLE_SCHEMA
print(row[1])
print()
#TABLE_NAME
print(row[2])
print()
# TABLE_TYPE
print(row[3])
print()
print('all columns')
print(str(row[0]), "-",str(row[1]), "-",str(row[2]), "-",str(row[3]))
row = cursor.fetchone()
Upvotes: 1