pyodbc and some t-sql

This is the place for queries that don't fit in any of the other categories.

pyodbc and some t-sql

Postby TheFunk » Fri Aug 30, 2013 6:53 pm

I've been having some issues with Python 3.3 (32 bit) and pyodbc 3.0.7 (32 bit). I'm not sure if my problem lies with bad SQL queries or improper use of cursor.execute() or what. IDLE tells me that there's a "Programming Error" with my first select statement, and that my "previous SQL was not a query." I'd read up on this, and it was mentioned somewhere that I should do "SET NOCOUNT ON" before running queries to ensure only the proper results are returned. Anyway, I've attached the code below, hopefully someone out there has experienced this issue before, or I'm just doing something stupid. I'm just getting used to both Python 3 and T-SQL so go easy on a brother.

I'm aware the code isn't as short or efficient as it could be (waiting until it works first), and that there are superfluous print statements for debugging purposes, sorry. I should also mention that the program is meant to be a simple login/logout time manager. Kinda like a work/punch clock. Thanks! :D


Here's the bit that doesn't want to cooperate so far...

Code: Select all
                        cnxn = pyodbc.connect(r'DRIVER={SQL Server Native Client 10.0};SERVER=localhost;DATABASE=practicedb;User ID=mydomain\myname;PWD=mypass;Trusted_Connection=yes')
                        cursor = cnxn.cursor()
                        # Selects some user specific fields from the DataLog table based on users card number
                        for row in cursor.execute('SET NOCOUNT ON;' + 'SELECT CardNum, IsHere, LastLoginTime FROM DataLog WHERE CardNum LIKE ?;', login):
                                print(row.IsHere)
                                if row.IsHere == True:
# Clock Out
                                        cursor.execute('SET NOCOUNT ON;' + 'UPDATE DataLog SET IsHere = ?;', False)
                                        cnxn.commit()
                                        print('IT WORKS')
                                elif row.IsHere == False:
# Clock In
                                        cursor.execute('SET NOCOUNT ON;' + 'UPDATE DataLog SET IsHere = ?;', True)
                                        cnxn.commit()
                                else:
                                        print("Trouble at the mill.")
TheFunk
 
Posts: 27
Joined: Fri Aug 30, 2013 5:46 pm

Re: pyodbc and some t-sql

Postby ochichinyezaboombwa » Fri Aug 30, 2013 8:41 pm

It is always helpful if you provide full traceback / exact error ("I somehow have a hard time believing that IDLE just says "You have a programming error").

One thing I don't like in your SQL is your "SELECT ... LIKE" query. Usually it requires single quotes ( '' ) and some % sign(s) somewhere as a wildcard, e.g.
Code: Select all
SELECT * from  table1 WHERE name LIKE 'San %';


And in your case it seems that you need the "CardNum = login" condition so there seems to be no need for the LIKE operator at all.
ochichinyezaboombwa
 
Posts: 200
Joined: Tue Jun 04, 2013 7:53 pm

Re: pyodbc and some t-sql

Postby TheFunk » Mon Sep 02, 2013 5:14 am

I'll try to post the exact error tomorrow, but as hard to believe as it sounds, that's pretty much all it says. It gives a Programming Error and says my SQL was not a query. Normally, yes you would need quotes around a value placed after the LIKE operator, however in the pyodbc documentation, the author says that question marks can be used in place of values, and then gives an example that looks similar to my first SQL query. That could be causing the issue though, I'll look into that.
TheFunk
 
Posts: 27
Joined: Fri Aug 30, 2013 5:46 pm

Re: pyodbc and some t-sql

Postby TheFunk » Tue Sep 03, 2013 5:01 pm

Good News!

ochichinyezaboombwa, you're a genius! It was the quotes after all. When I declared the login variable, I hadn't escaped the single quotes surrounding it, so the proper variable wasn't being passed to SQL Server. IDLE is not giving an error any longer.

Bad News!

Unfortunately, the first query isn't working still. I'm guessing either the query doesn't complete (because test never prints), or it's doing something weird. Here's a bit more code so that things make some more sense.

Code: Select all
class User:

        # Method run when user logs in
        def scancard(*args):
                while True:
                        # Gets user ID number from tkinter entry field
                        global cardnum
                        login = '\''+ cardnum.get() + '\''
                        # Clears tkinter entry field for next use
                        global num_entry
                        num_entry.delete(0, END)
                        # Establishes a SQL Server 2008 R2 Connection with the practice database
                        cnxn = pyodbc.connect(r'DRIVER={SQL Server Native Client 10.0};SERVER=localhost;DATABASE=DBpractice;Trusted_Connection=yes;User ID=somename;PWD=yadayadayada')
                        cursor = cnxn.cursor()
                        # Loops through DataLog table, pulls values
                        for row in cursor.execute('SELECT CardNum, IsHere, LastLoginTime FROM DataLog WHERE CardNum LIKE ?;', login):
                                # doesn't print past this point, no errors according to IDLE
                                print("test")
                                print(row.IsHere)
                                if row.IsHere == True:
                                        cursor.execute('UPDATE DataLog SET IsHere = ?;', 'False')
                                        cnxn.commit()
                                        print('IT WORKS')
                                elif row.IsHere == False:
                                        cursor.execute('UPDATE DataLog SET IsHere = ?;', 'True')
                                        cnxn.commit()
                                else:
                                        print("Trouble at the mill.")
                        break


Unrelated News!

My fantasy football team looks great this year!
TheFunk
 
Posts: 27
Joined: Fri Aug 30, 2013 5:46 pm


Return to General Coding Help

Who is online

Users browsing this forum: Google [Bot] and 2 guests

cron