CODEWITHSUNDEEP
pythonpandasloopspyodbc
AdamB
AdamB

Reputation: 31

Python looping to obtain different dataframes from a SQL database

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)

Upvotes: 2

Views: 510

Answers (1)

Active_Learner
Active_Learner

Reputation: 173

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

Upvotes: 1

Related Questions