Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Why is this particular database table returning empty data frame through Python?
Forum Update on Monday, May 27th 2025

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

Scheduled Pinned Locked Moved Unsolved General and Desktop
14 Posts 4 Posters 1.7k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • C Offline
    C Offline
    CEO.
    wrote on 13 Aug 2021, 15:35 last edited by CEO.
    #1

    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)
    
    1 Reply Last reply
    0
    • S Offline
      S Offline
      SGaist
      Lifetime Qt Champion
      wrote on 13 Aug 2021, 18:22 last edited by
      #2

      Hi,

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

      Interested in AI ? www.idiap.ch
      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

      C 1 Reply Last reply 13 Aug 2021, 20:16
      0
      • S SGaist
        13 Aug 2021, 18:22

        Hi,

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

        C Offline
        C Offline
        CEO.
        wrote on 13 Aug 2021, 20:16 last edited by
        #3

        @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 [ ]

        E 1 Reply Last reply 13 Aug 2021, 20:32
        0
        • C CEO.
          13 Aug 2021, 20:16

          @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 [ ]

          E Offline
          E Offline
          eyllanesc
          wrote on 13 Aug 2021, 20:32 last edited by eyllanesc
          #4

          @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))
          

          If you want me to help you develop some work then you can write to my email: e.yllanescucho@gmal.com.

          C 1 Reply Last reply 13 Aug 2021, 22:33
          0
          • E eyllanesc
            13 Aug 2021, 20:32

            @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))
            
            C Offline
            C Offline
            CEO.
            wrote on 13 Aug 2021, 22:33 last edited by
            #5

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

            E 1 Reply Last reply 13 Aug 2021, 22:40
            0
            • C CEO.
              13 Aug 2021, 22:33

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

              E Offline
              E Offline
              eyllanesc
              wrote on 13 Aug 2021, 22:40 last edited by
              #6

              @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)

              If you want me to help you develop some work then you can write to my email: e.yllanescucho@gmal.com.

              C 1 Reply Last reply 13 Aug 2021, 22:45
              0
              • E eyllanesc
                13 Aug 2021, 22:40

                @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)

                C Offline
                C Offline
                CEO.
                wrote on 13 Aug 2021, 22:45 last edited by CEO.
                #7

                @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()
                   
                
                
                1 Reply Last reply
                0
                • S Offline
                  S Offline
                  SGaist
                  Lifetime Qt Champion
                  wrote on 14 Aug 2021, 07:55 last edited by
                  #8

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

                  Interested in AI ? www.idiap.ch
                  Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                  C 1 Reply Last reply 14 Aug 2021, 09:21
                  0
                  • S SGaist
                    14 Aug 2021, 07:55

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

                    C Offline
                    C Offline
                    CEO.
                    wrote on 14 Aug 2021, 09:21 last edited by
                    #9

                    @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

                    S 1 Reply Last reply 14 Aug 2021, 11:10
                    0
                    • C CEO.
                      14 Aug 2021, 09:21

                      @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

                      S Offline
                      S Offline
                      SGaist
                      Lifetime Qt Champion
                      wrote on 14 Aug 2021, 11:10 last edited by
                      #10

                      @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.

                      Interested in AI ? www.idiap.ch
                      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                      C 1 Reply Last reply 14 Aug 2021, 22:42
                      1
                      • S SGaist
                        14 Aug 2021, 11:10

                        @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.

                        C Offline
                        C Offline
                        CEO.
                        wrote on 14 Aug 2021, 22:42 last edited by CEO.
                        #11

                        @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.

                        1 Reply Last reply
                        0
                        • C Offline
                          C Offline
                          ChrisW67
                          wrote on 15 Aug 2021, 05:16 last edited by ChrisW67
                          #12

                          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).

                          C 1 Reply Last reply 15 Aug 2021, 09:13
                          1
                          • C ChrisW67
                            15 Aug 2021, 05:16

                            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).

                            C Offline
                            C Offline
                            CEO.
                            wrote on 15 Aug 2021, 09:13 last edited by
                            #13

                            @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.

                            1 Reply Last reply
                            0
                            • S Offline
                              S Offline
                              SGaist
                              Lifetime Qt Champion
                              wrote on 15 Aug 2021, 10:38 last edited by
                              #14

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

                              Interested in AI ? www.idiap.ch
                              Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                              1 Reply Last reply
                              1

                              4/14

                              13 Aug 2021, 20:32

                              topic:navigator.unread, 10
                              • Login

                              • Login or register to search.
                              4 out of 14
                              • First post
                                4/14
                                Last post
                              0
                              • Categories
                              • Recent
                              • Tags
                              • Popular
                              • Users
                              • Groups
                              • Search
                              • Get Qt Extensions
                              • Unsolved