add database rows to dataframe

[cspower]

I'm trying to build a dataframe from rows I select incrementally from a database.

SQL = '''select ..... where userid = ? and roleName = ?"" I use the UserId and Role Name from the file in my where clause.

for index, row in data.iterrows():
        userid, rolename = row[['User Id', 'Role Name']]
        sql_query = pd.read_sql_query((SQL, (userid, rolename)), conn) <- This does not seem to be correct
        sqldf = pd.Dataframe(sql_query, columns = ['ApplicationName', 'Environment', 'LastName', 'FirstName', 'UniqueIdentifier', 'RoleName', 'UserId', 'AccountType'])
        conn.close()
        
return

[ibreeden]

You write: "This does not seem to be correct". How can you tell? Does it generate an error message? In that case: show us the exact error message.

[cspower]

This is the error
"pandas.io.sql.DatabaseEror: Execution failed on sql"

What I need to accomplish is loop through the data in a csv file.
take two of the columns and use them in my sql statement, then execute the sql.
Using SQL Server, pyodbc
This requires an new database connection for each row in the file, unless there is a way to do it all with one connection.
then I need to return each result to a new csv file.

Any advice will be greatly appreciated.

[ibreeden]

I am sure Python gives you more information about the error than "pandas.io.sql.DatabaseEror: Execution failed on sql". It will tell you the exact line number in each module. I guess it will go wrong in "pd.read_sql_query()".
Best way is to add debug lines in your program to test whether variables contain what you expect.
First: somewhere in your program you must have logged in to the SQL Server database. Did that succeed? Can you test that with a simple query?
Second: just before executing the query: add statements to print the contents of the variables that matter.

        userid, rolename = row[['User Id', 'Role Name']]
        print("userid   = " + userid)
        print("rolename = " + rolename)
        Print("SQL      = " + SQL)
        sql_query = pd.read_sql_query((SQL, (userid, rolename)), conn)

What does it show?
Third: try to simplify the SQL. Do it without the variable substitution by SQL Server.

SQL = "select * from table_name where userid = 'John' and roleName = 'administrator'"
...
    sql_query = pd.read_sql_query(SQL, conn)

(First fill in the correct table_name, userid and rolename.) What is the result of that?

[cspower]

If I hard code the userid and rolename it works,
but I need to do it with variables in the sql statment.

sql_query = pd.read_sql_query((SQL, [b](userid, rolename)[/b]), conn)

but the userid, rolename in bold does not seem to be the right syntax.

[cspower]

I have also tried this syntax and it does not work either. gives a syntax error at the =.

sql_query = pd.read_sql_query((SQL, conn, params = (userid, rolename)))

[ibreeden]

I am not aquainted with SQL Server and neither with pandas. But are you sure you are using the right syntax? Should it not be:

pd.read_sql_query(SQL, conn, params=(userid, rolename))

And when everything fails you can always change the SQL string:

...
userid, rolename = row[['User Id', 'Role Name']]
SQL = f"select * from tablename where userid = '{userid}' and roleName = '{rolename}'"
sql_query = pd.read_sql_query(SQL, conn)
...

And don't forget, if anything happens you don't understand: insert print statements to see what the program really does.

---

Just saw your message. I think you have too many brackets ().
And post the error message if you have one. Don't take it personal but people seem to not realize the error is not just some text, it is designed to tell you exact why python cannot execute what you coded.
Note there is a button to post error messages.

[cspower]

The f-string is not working because it does not like some of the keywords from the sql statement.

Error:
Unexpected token AS RequestType,