Why is this particular database table returning empty data frame through Python?

[eyllanesc]

@CEO What do you get when you run?

self.cursor.execute('SELECT * FROM purRec1 WHERE fReqNo LIKE ?', (schbox,))

and

self.cursor.execute('SELECT * FROM purRec1 WHERE fReqNo LIKE ? OR itemcd LIKE ?', (schbox, schbox))

[CEO.]

@eyllanesc
"connected to database"
"Data retrieved"
[ ]

[eyllanesc]

@CEO In both cases does it return an empty list? If so, then either the connection is not open or the cursor does not belong to the new connection. I can't say more because of the little code you provide. If you want more help then you must provide a minimum verifiable example (the same as asked in SO)

[CEO.]

@eyllanesc thanks for your contribution. Here's more of the code:

def db_connection(self):
        try:
            
            self.conn = 
    pyodbc.connect('Driver={SQL Server}; Server=mine1;database=logistics; Trusted_Connection=yes;')
            self.cursor = 
    self.conn.cursor()
            print("Connected to database")
            
    

        except Exception as err:
            print("couldn't connect")
            print("General error :: ", err)

    def retrieve1(self):
        try:
            self.db_connection()
            schbox = 
    searchtxtab.text()  
            self.cursor.execute( 'SELECT * FROM purRec1 WHERE fReqNo LIKE ? OR itID LIKE ? OR grade LIKE ?',
     (schbox, schbox, schbox))
            result = 
    self.cursor.fetchall()
            print(result)

            for row_number, row_data in enumerate(result):
                self.tab.insertRow(row_number)
                for column_number, data in enumerate(row_data):
                    self.tab.setItem(row_number, column_number, QTableWidgetItem(str(data)))

            print(self.cursor.rowcount, "row(s) affected")
            self.conn.commit()
            print("DATA RETRIEVED")
            
        except Exception as err:
            print(err)
            self.conn.close()
            

        self.show()
   

[SGaist]

If everything returns empty lists and the connection is good, what about the table being empty ?

[CEO.]

@SGaist did you read my response to ellyase? I said if I use 'SELECT * FROM mytable', it works fine and retrieves all the records in the file but when I include the WHERE condition, it then returns blank data set

[SGaist]

@CEO said in Why is this particular database table returning empty data frame through Python?:

@SGaist did you read my response to ellyase? I said if I use 'SELECT * FROM mytable', it works fine and retrieves all the records in the file but when I include the WHERE condition, it then returns blank data set

That answer was to me and you used myTablename in it. Which is different from purRec1 hence my question about the emptiness of the table.

In any case, LIKE the way you use it is for an exact match, so are you sure that schbox contains something valid in that regard ?

You can try the techniques used here to see the query that was used. It may also shed some light.

[CEO.]

@SGaist thanks for your follow up. I had to use

cursor.execute( "SELECT * FROM table WHERE column1 LIKE '%{}%' OR column2 LIKE '%{}%' ".format(schbox,schbox))

I usually use this whenever I'm using pymysql module but I had to test it with my code when every step failed and it works fine with pyodbc but in pymysql, it is. "%{}%". Pyodbc uses single quote while pymysql uses double quote.

I am still surprised how pyodbc ?,?,? parameter marker could work in retrieving data from other tables but not this one. It is strange to me.

[ChrisW67]

What you did originally would work if the operator was "=" not "LIKE", or if the value of schbox was a pattern suitable for LIKE. What LIKE does with patterns that do not contain wildcards varies from database engine to engine in my experience.

Constructing SQL by string building, as in your solution, is dangerous if schbox originates with a user. Consider what happens if the user supplies a value containing a single quote. Safer to use your original approach and modify the arguments:

self.cursor.execute(
   'SELECT * FROM thetable WHERE column1 LIKE ? OR column2 LIKE ? or column3 LIKE ? or column4 LIKE ?', 
   ('%' + schbox + '%' , ) * 4
)

or something even smarter to adapt the user input (e.g. only add the wildcards if the user's string contains neither % nor _, or escape any wildcards the user has supplied).

[CEO.]

@ChrisW67 I said I've been using that method before and it's been working perfectly well. Although an idea struck me when I woke up from sleep and will try it soon. As for your suggestion, %s is mysql parameter marker, not Microsoft parameter marker. I have used LIKE = %s to test it yesterday, it didn't work.

[SGaist]

If you need to support different database backend within your applications, if not using Qt's own SQL module, you should consider using SQLAlchemy.