CODEWITHSUNDEEP
pythonsqlsqlite
archercomprehensive
archercomprehensive

Reputation: 11

SQL in python script not returning all values

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

Answers (1)

Gilmar Vaz
Gilmar Vaz

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

Related Questions