Forum Discussion

krishnanchittur's avatar
krishnanchittur
Qrew Member
6 years ago

Looking for simplest example to access quickbase using python

Can someone point me to examples that show how a quickbase/db can be accessed from the command line/using python -?  We would like to access, add, delete, change records and create reports - and in addition to the GUI provided, do such using CLI tools and python (saw pybase on github, am having some difficulty with it - unless someone can provide a complete example -?)

22 Replies

    • PushpakumarGna1's avatar
      PushpakumarGna1
      Qrew Assistant Captain
      Hi All,

      Can you please let me know the steps to follow to extract  data using python from quickbase
      My ask is to create a python code that extracts the data from the report and save in the same location as csv file?

      Can some one help me in this.

      Thanks
      Pushpakumar

      ------------------------------
      Pushpakumar Gnanadurai
      ------------------------------
      • AustinK's avatar
        AustinK
        Qrew Commander
        If you follow the instructions listed in the link of the post you replied to you should be able to get to the point where you extract information from Quickbase. In the link it is the first 2 things listed. "Create the client" first and then you can do one of the things below that, DoQuery sounds like what you want. So set up your Python file as the link shows.

        import pyqb
        # def Client(url="http://www.quickbase.com", database=None, proxy=None, user_token=None):
        qbc = pyqb.Client(url='http://my_domain.quickbase.com')
        # Below authenticate is not required if `user_token` argument is passed to pyqb.Client() above
        qbc.authenticate(username='myusername', password='mypassword')

        # doquery(query=None, qid=None, qname=None, database=None, fields=None, fmt=False, rids=False, sort_fields=None, options=False): qbc.doquery(qid=64) qbc.doquery(query='{"6".EX."myval"}', database='asdfasdf') qbc.doquery(qid=64, fields=["3", "4"], fmt=True, rids=False)
        If you need more context on what the "6".EX."myval" and other parts mean then the QuickBase api help docs should get you there. You can choose to grab a saved report or do your own query. https://help.quickbase.com/api-guide/do_query.html

        Is there any specific part that you need help with? Some part you have attempted but not been able to get to work? Maybe post the code you have tried that failed and someone can help you fix it.

        When you say you would like the code to extract the data and save it in the same place as the csv do you mean save to the same place your browser would save a csv if you clicked "Save as csv" on a report? Like your downloads folder? I assume you want the data to also be the same exact csv format as you would get from QuickBase? You may need to create the csv in code then, depending on what is returned via Python. I would assume you will get XML returned though.

        The post below mine by David Choi is a great example of how to connect to QuickBase without using a library like the one above. So you are not limited to what the library can do, you can make whatever API call you need. It is more involved though and probably more difficult at times.
  • I'm all set on getting Python to work with the API, but if you have any suggestions or resources I'd love to hear about them and/or share what I've done

  • I know that this is old...but I got here from a Google search for simple example so I wanted to help the next guy/gal out.  Here is my "simplest example."
    #!python3
    import requests
    import xmltodict
    from xml.etree import ElementTree as et
    import pprint
    pp = pprint.PrettyPrinter(indent=2)
    
    # ----------Add your personal stuff here:-----------
    yourEmail = 'your@email.here'
    yourPassword = 'verygoodpassword'
    aTableID = 'xxx5yyyyy'
    companyURLPrefix = 'joescookies'
    
    # -------------Authenticate to get a ticket--------------
    # one could use a user token here instead.
    headers = {
        "Content-Type": "application/xml",
        "Accept-Charset": "utf-8",
        "QUICKBASE-ACTION": "API_Authenticate"
    }
    # build an XML payload
    payload = et.Element('qdbapi')
    username = et.SubElement(payload, 'username')
    username.text = yourEmail
    password = et.SubElement(payload, 'password')
    password.text = yourPassword
    # hours = et.SubElement(payload, 'hours')  # Optional
    # hours.text = '24'  # Optional
    # make the payload ready for shipping
    humanPayload = et.tostring(payload)
    url2 = f'https://{companyURLPrefix}.quickbase.com/db/main'
    resp = requests.post(url2, data=humanPayload, headers=headers)
    # pp.pprint(vars(resp))
    # Parse the response so we can pull out the ticket
    parsed = et.XML(resp.text)
    authticket = parsed.find('ticket').text
    
    # -----------Using the authticket we can actually do some work--------------
    # this example is to get the API_GetDBInfo
    url3 = f'https://{companyURLPrefix}.quickbase.com/db/{aTableID}'
    headersGetDB = {
        "Content-Type": "application/xml",
        "Accept-Charset": "utf-8",
        "QUICKBASE-ACTION": "API_GetDBInfo"
    }
    # build another XML payload
    payloadGetDB = et.Element('qdbapi')
    ticket = et.SubElement(payloadGetDB, 'ticket')
    ticket.text = authticket
    # make the payload ready for shipping
    humanPayloadGetDB = et.tostring(payloadGetDB)
    respgetdb = requests.post(url3, data=humanPayloadGetDB, headers=headersGetDB)
    # print(et.XML(respgetdb.text).find('errtext').text)  # this checks for the errtext
    # pp.pprint(vars(respgetdb))  # this pumps out the full response
    # This makes the response a little nicer to sort through
    parsedGetDB = et.XML(respgetdb.text)
    # This is some of the info enbedded into the XML response.
    parsedGetDB.find('dbname').text
    parsedGetDB.find('lastModifiedTime').text
    parsedGetDB.find('lastRecModTime').text
    parsedGetDB.find('createdTime').text
    parsedGetDB.find('numRecords').text
    parsedGetDB.find('mgrID').text
    parsedGetDB.find('mgrName').text
    parsedGetDB.find('time_zone').text
    parsedGetDB.find('version').text
    ​


    ------------------------------
    David Choi
    ------------------------------
    • Udaya_NarayanaP's avatar
      Udaya_NarayanaP
      Qrew Member
      # -----------Using the authticket we can actually do some work--------------
      # this example is to get the API_GetDBInfo
      url4 = f'https://{companyURLPrefix}.quickbase.com/db/{aTableID}'
      headersGetDB = {
      "Content-Type": "application/xml",
      "Accept-Charset": "utf-8",
      "QUICKBASE-ACTION": "API_GenResultsTable"
      }
      # build another XML payload
      payloadGetDB = et.Element('qdbapi')
      ticket = et.SubElement(payloadGetDB, 'ticket')

      ticket.text = authticket
      #print(et.tostring(payloadGetDB))
      apptoken = et.SubElement(payloadGetDB, 'apptoken')
      apptoken.text = 'xxxxxx'
      #print(et.tostring(payloadGetDB))
      qid = et.SubElement(payloadGetDB, 'qid')
      #qid's are 1 & 2 & 9 for reporting list
      qid.text = '9'
      jht = et.SubElement(payloadGetDB, 'jht')
      #qid's are 1 & 2 & 9 for reporting list
      jht.text = '1'
      fmt = et.SubElement(payloadGetDB, 'fmt')
      fmt.text = 'structured'
      #print(et.tostring(payloadGetDB))
      options = et.SubElement(payloadGetDB, 'options')
      options.text = 'num-4.sortorder-D'
      #print(et.tostring(payloadGetDB))


      #slist = et.SubElement(payloadGetDB, 'slist')
      #slist.text = '7.23'
      #make the payload ready for shipping
      print("\n\n\n\n\n\n\n\n\n...................printing before API_GenResultsTable................\n\n\n\n\n\n\n\n\n")

      humanPayloadGetDB = et.tostring(payloadGetDB)
      print(humanPayloadGetDB)
      pp.pprint(url4)
      respgetdb = requests.post(url4, data=humanPayloadGetDB, headers=headersGetDB)

      print("\n\n\n\n\n\n\n\n\nprinting API_GenResultsTable................\n\n\n\n\n\n\n\n\n")


      pp.pprint(respgetdb.text)


      print(et.XML(respgetdb.text).find('errtext').text) # this checks for the errtext
      pp.pprint(vars(respgetdb)) # this pumps out the full response
      # This makes the response a little nicer to sort through
      parsedGetDB = et.XML(respgetdb.text)
      # This is some of the info enbedded into the XML response.

      print(parsedGetDB)

      I am getting below error


         print(et.XML(respgetdb.text).find('errtext').text)  # this checks for the errtext

        File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/xml/etree/ElementTree.py", line 1315, in XML

          parser.feed(text)

      xml.etree.ElementTree.ParseError: syntax error: line 1, column 0



      ------------------------------
      Udaya Narayana Pakalapati
      ------------------------------
      • AustinK's avatar
        AustinK
        Qrew Commander
        Do you know for a fact that the XML being returned is actual XML and not some big error itself? The error you got is what happens when the thing you are trying to parse is not XML.

        Your best bet is going to be to break the code into parts and run the things individually line by line until you hit the issue. Try and pull an XML response and manually check that response to see if it is what you expect it to be.
  • I use qunect and pyodbc.  See qunect.com.  It's so much easier to use SQL than the half-baked open source libraries like pyqb

    ------------------------------
    John Hubert
    ------------------------------