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

CEO.

There's this particular table, whenever I try to retrieve its data through my python program, it returns an empty data frame even though it has records in it but when I retrieve data from other tables, their records are returned.

I need help here please.

Here's the code:.

self.db_connection()
schbox = searchtxtab.text()  # to make the searchbox active, ensure it is coded close to the db connection.
self.cursor.execute( 'SELECT * FROM purRec1 WHERE fReqNo LIKE ? OR itID LIKE ? OR grade LIKE ?',
 (schbox, schbox, schbox))
 result = self.cursor.fetchall()
 print(result)

SGaist

Hi,

Check for error conditions with whatever library you are using to connect to your database.

CEO.

@SGaist hello, when I reduce the sql statement to only 'SELECT * FROM myTablename', it retrieves all the data but when I put the WHERE conditions, it would return blank data set.

This is the same code I've used in numerous projects and when I copy some of those project sql retrieve statement into this my project, it works fine. Immediately I want to edit them so as to substitute their parameters with this table's, it will retrieve empty data set. Mind you, there's no error. It says database connected, data retrieved, then [ ]

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.