Python looping to obtain different dataframes from a SQL database

Question

I'm trying to connect to an SQL database and, within a loop, create separate dataframes for each different instance of Id, containing all the data related to that Id. I've tried a number of ways, without any success so far. I'm pretty new to all of this, so I'm probably making some rookie mistakes.

Attempt 1:

import pandas as pd
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
 'Server=Server_name;'
 'Database=Database;'
 'UID=Username;'
 'PWD=password;'
 'Trusted_Connection=yes;')
Name = ['HR','ZA','PR','FW']
for x in Name:
 
 SQL = '''
 SELECT *
 FROM Database
 WHERE Id = {x}'''.format(x = x)
 cursor = conn.cursor()
 cursor.execute(SQL)
 df = pd.read_sql_query(SQL)

On this code, I get an 'invalid column name' programming error on the first Name 'HL'.

Attempt 2:

import pandas as pd
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
 'Server=Server_name;'
 'Database=Database;'
 'UID=Username;'
 'PWD=password;'
 'Trusted_Connection=yes;')
 SQL = '''
 SELECT *
 FROM Database
 
conn.autocommit = True
cursor.execute(SQL)
for [Id] in cursor:
 df = pd.Dataframe(SQL,conn)
 

On this code, I get a 'ValueError: too many values to unpack (expected 1)' - on the for statement.

I want to put a lot more code in the for loop so I need it to be set up to work through each Id. I hope that makes sense. Any guidance would be greatly appreciated. Thanks

UPDATE:

Thanks for all comments/answers. For some reason I just couldn't get it to work in either of the formats above so I took it back to where I started from now I understand how to include the syntax for the loop variable. The following now works:

import pandas as pd
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
 'Server=Server_name;'
 'Database=Database;'
 'UID=Username;'
 'PWD=password;'
 'Trusted_Connection=yes;')
 
Name = ['HR','ZA','PR','FW']
for x in Name:
 SQL = pd.read_sql_query(
 '''
 SELECT *
 FROM Database_table
 WHERE Id = '{x}'
 '''.format(x = x), conn)
 df = pd.DataFrame(SQL)
 

• Yaakov Bressler: Does changing WHERE Id = {x} to WHERE Id = '{x}' solve this for your in attempt 1?
• Yaakov Bressler: Also, I'd suggest utilizing sqlalchemy as an interface to your db, it's well supported and will be useful for you if your db interactions grow more complex
• AdamB: Thanks @YaakovBressler - if I do this, I then get a different error message - TypeError: read_sql_query() missing 1 required positional argument: 'con'
• Yaakov Bressler: That's odd. The only thing you changed was the string formatting?
• AdamB: Thanks for your guidance - I've got it working now - see above

Answer 1

I think that if you try a variation on your first attempt like:

for x in Name:
 SQL = '''
 SELECT *
 FROM Database
 WHERE Id = ?'''
 cursor = conn.cursor()
 cursor.execute(SQL)
 df = pd.read_sql_query(SQL, params={x})

It should probably work :)

• AdamB: Thanks. If I change that, I then get the following message (which is probably a separate issue?) - Error: ('07002', '[07002] [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')
• Active_Learner: Hm, interesting. Is Database the name of a database or of a database table?
• AdamB: The Database referred to in Conn is a database name but the Database referred to in SQL is a database table. I should have made this more clear in my summarised code, sorry. Thanks for all your help and guidance. As you can see above, I've got it working - thanks
• Active_Learner: Glad to hear :)