SQL in python script not returning all values

Question

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

Side note: You may want to have a look at the SQL JOIN operation family. — sticky bit
– sticky bit, Commented Mar 25, 2021 at 1:52

Gilmar Vaz · Accepted Answer · 2021-03-25 04:18:05Z

1

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()

answered Mar 25, 2021 at 4:18

Gilmar Vaz

1121 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL in python script not returning all values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related