CODEWITHSUNDEEP
pythonsql-serverpypyodbc
mp252
mp252

Reputation: 475

Cannot run mssql select query with where clause within python using pypyodbc

I am using the library pypyodbc and am trying to run a select query in mssql using a where clause.

However the error I am getting indicates the syntax is wrong.

Error here ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near the keyword 'User'.")

my query within my function is as follows;

try:

    selectUserDetails = "SELECT Username,Password FROM User WHERE Username = ?"
   cursor.execute(selectUserDetails,(username,))

    for row in cursor:
        print(row);

except Exception as e:

    print('Error here ' + str(e))

My function to get the cursor is as follows;

def msDbCred():


try:
    from msDb import connection_string

    conn = pypyodbc.connect(connection_string)
    cursor = conn.cursor();
    return cursor;

except pymysql.err.OperationalError:
    sys.exit("Invalid Input: Wrong username/db or password found, please try again")

cursor = msDbCred()

and my db credentials are stored in a different file named msDb.py

db_host = '127.0.0.1'
db_name = 'TD_1.0'
db_user = 'ReadOnly'
db_password = 'mypaswword'

connection_string = 'Driver=SQL Server;Server=' + db_host + ';Database=' + db_name + ';uid=' + db_user + ';PWD=' + db_password + ';'

My question here is, is there something wrong with the query itself. As I am using this same format on another query elsewhere in my code and it works. Or is there something else wrong with my functions?

UPDATE:

@Gord Thompson said I should change my query.

I have changed, my query to;

selectUserDetails = "SELECT Username FROM [TD_1.0].[dbo].[User] WHERE Username = ?"
cursor.execute(selectUserDetails,(username,))

However now I get the error;

('HY010', '[HY010] [unixODBC][Driver Manager]Function sequence error')

Upvotes: 2

Views: 1059

Answers (1)

mp252
mp252

Reputation: 475

I managed to figure out where the error was and how to fix it.

In my msDbCred() function I was directly returning the cursor. I was testing in another file and figured this was the problem.

So instead I created an empty array and appended the cursor to the array and then returned the array.

Then used the first index of the array as the cursor elsewhere in my code

def msDbCred():

    cred = []
    try:
        from msDb import connection_string
        conn = pypyodbc.connect(connection_string)
        cursor = conn.cursor();
        cred.append(cursor)
        return cred
    except pymysql.err.OperationalError:
        sys.exit("Invalid Input: Wrong username/db or password found, please try again")

cursor = msDbCred()

Then use the first index of cursor array

try:
    selectUserDetails = "SELECT Username,Password,UserId FROM [User] WHERE Username = '"+str(username)+"' AND IsEditor = '"+str(True)+"'"
           cursor[0].execute(selectUserDetails)

    for row in cursor[0]:
        return  row[0] == username and row[1] == password
except Exception as e:
     print('Error is ' + str(e))

this is now working.

Upvotes: 1

Related Questions