Discussions

Back to discussions
Expand all | Collapse all

Looking for simplest example to access quickbase using python

Jake Rattner03-29-2019 17:37

Has anyone gotten this to work?

Thomas Boswick07-15-2019 16:13

Yes, do you still need help?

  • 1.  Looking for simplest example to access quickbase using python

    Posted 08-11-2018 17:02
    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 -?)


  • 2.  RE: Looking for simplest example to access quickbase using python

     
    Posted 08-14-2018 16:34
    Use the API --> https://help.quickbase.com/api-guide/index.html

    I use node.js so I'm not familiar with Python, but it all essentially works the same.

    See this library:

    http://docs.python-requests.org/en/master/

    This should allow you to do whatever your heart desires.

    Hope this helps,

    ~Rob


  • 3.  RE: Looking for simplest example to access quickbase using python

    Posted 03-29-2019 17:37
    Has anyone gotten this to work?


  • 4.  RE: Looking for simplest example to access quickbase using python

    Posted 07-15-2019 16:13
    Yes, do you still need help?


  • 5.  RE: Looking for simplest example to access quickbase using python

    Posted 08-05-2019 00:53
    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



  • 6.  RE: Looking for simplest example to access quickbase using python

    Posted 08-06-2019 15:54
    Hi,

    Been using pyqb (https://github.com/sjmh/pyqb) for all of my python work w/the Quickbase API. It's not complete, but easy to fork and add to.


  • 7.  RE: Looking for simplest example to access quickbase using python

    Posted 11-26-2019 08:18
    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
    ------------------------------

    Original Message


  • 8.  RE: Looking for simplest example to access quickbase using python

    Posted 11-26-2019 11:09
    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.
    Original Message


  • 9.  RE: Looking for simplest example to access quickbase using python

    Posted 11-27-2019 00:14
    Hi Austin,

    Thanks a lot for the detailed description. I will try the code and let you know if i face any issues?

    Thanks

    ------------------------------
    Pushpakumar Gnanadurai
    ------------------------------

    Original Message


  • 10.  RE: Looking for simplest example to access quickbase using python

    Posted 11-27-2019 00:17
    My thought process is i should be able to run a python code from unix box. Extract the data from quickbase, store it in a csv format and load the data to PostgreSQL server.

    Thanks
    Pushpakumar

    ------------------------------
    Pushpakumar Gnanadurai
    ------------------------------

    Original Message


  • 11.  RE: Looking for simplest example to access quickbase using python

    Posted 11-27-2019 10:39
    Without checking I am fairly sure the data is returned as XML. However the Python library there does say it uses XMLtoDict, so it is possible it automatically turns it into a Python dict. You will have to test this yourself to see what is returned to be sure.

    I know with JavaScript when I connect and pull data in a similar way I need to often create the csv file myself in the code. A quick Google search gave me this link, which says it can take the XML and convert to a csv, might be worth trying it out considering it is like 3 lines of code and uses XMLtoDict already. https://gist.github.com/JoaoCarabetta/fcc2ce166fea58b0397e7b1f77b96f34

    If nothing else works for you there are integrations with Workato and Zapier that I believe allow you to connect QuickBase to PostgreSQL.

    In the code I posted before the simplest possible option for pulling data would be to use this for the DoQuery. It uses a saved report on the table to pull data from and just needs the report ID. 
    qbc.doquery(qid=64)

    Original Message


  • 12.  RE: Looking for simplest example to access quickbase using python

    Posted 01-01-2020 06:51
    Hi Austin,

    I tried executing the commands you gave me and i get the following error. Is that something you can help me?

    >>> qbc.doquery(qid=1);

    Traceback (most recent call last):

      File "<stdin>", line 1, in <module>

      File "/anaconda3/lib/python3.7/site-packages/pyqb/__init__.py", line 150, in doquery

        res = self.__request('DoQuery', database, req)

      File "/anaconda3/lib/python3.7/site-packages/pyqb/__init__.py", line 95, in __request

        url = self.url + "/db/" + db

    TypeError: can only concatenate str (not "NoneType") to str

    >>>



    ------------------------------
    Pushpakumar Gnana
    ------------------------------

    Original Message


  • 13.  RE: Looking for simplest example to access quickbase using python

    Posted 01-01-2020 06:52
    i am trying to retrieve a report from the app. it is a list all report from the test table

    ------------------------------
    Pushpakumar Gnana
    ------------------------------

    Original Message


  • 14.  RE: Looking for simplest example to access quickbase using python

    Posted 01-02-2020 14:11
    The error is saying one of your variables is a NoneType. So one of them is not being populated as you expect it to be.

    You have no other issues with the code? Do you know if you are able to authenticate fine? 

    I think I may have made a slight mistake and you may need to do this command instead, as there is no database being set. Assuming you used my exact code above without any changes. The "database" part just needs to be your table ID.

    qbc.doquery(qid=1, database='your_database')


    Original Message


  • 15.  RE: Looking for simplest example to access quickbase using python

    Posted 01-03-2020 00:11
    Hi Austin,

    Thanks a lot for the note. Let me try with table id and get back to you if i face any issues?

    Thanks
    Pushpakumar

    ------------------------------
    Pushpakumar Gnanadurai
    ------------------------------

    Original Message


  • 16.  RE: Looking for simplest example to access quickbase using python

    Posted 01-03-2020 10:53
    Hi Austin,

    I changed the code to the table_id and ran the script. Still i have the following error. Can you please help?

    >>> qbc.doquery(qid=1,database='bp7ejv2ma');

    Traceback (most recent call last):

      File "<stdin>", line 1, in <module>

      File "/anaconda3/lib/python3.7/site-packages/pyqb/__init__.py", line 150, in doquery

        res = self.__request('DoQuery', database, req)

      File "/anaconda3/lib/python3.7/site-packages/pyqb/__init__.py", line 112, in __request

        raise ResponseError('Received err #{0} : {1}'.format(errcode, errtext))

    pyqb.ResponseError: Received err #83 : Invalid error code: 83



    ------------------------------
    Pushpakumar Gnana
    ------------------------------

    Original Message


  • 17.  RE: Looking for simplest example to access quickbase using python

    Posted 01-03-2020 11:31
    If for some reason the library above is failing there is also the example at the bottom of this post on how to do this with Requests. It is a little more involved but the example file should have everything you need.

    What Python version are you using for this? Looking at pyqb it has some potential issues with newer Pythons but not much is documented. I am testing it again myself and running into some issues using the newest Python. It worked fine for me before. I will let you know if I find anything out.

    So far I have tried to authenticate via username and password, user token, and app token. None of them seemed to work but I may be missing something easy here.
    Original Message


  • 18.  RE: Looking for simplest example to access quickbase using python

    Posted 01-03-2020 13:39
    I have figured out part of the issue, on my end anyway. I was using http rather than https, that was all that stopped me from connecting with the authenticate command. 

    After connecting I am still have some small issues running doquery. I am unsure if this is an issue with my current realm or what but I will try and check into it later.
    Original Message


  • 19.  RE: Looking for simplest example to access quickbase using python

    Posted 01-05-2020 00:48
    Hi Austin,
    Thanks a lot for your support. Finally, I am able to connect from python. Here is what I did
    Step 1 : Install pyqb in python
    Step 2: Create an user token from my preferences and assign that to the table which you want to connect from python and keep the following ready
    1. Quickbase app URL ; 2. database name ; 3. table name and 4.user token
    Step 3:  from python run the following commands
    import pyqb;  

    qbc = pyqb.Client(url='https://your_quickbase _name .quickbase.com/',user_token='xxxxxxxx',database='xxxxxxx'); 
    result = qbc.doquery(qid=1,database='XXXX');
    print(result)
     qid=1 is the list all report of the table (you can create any report and get the qid from the URL generated in the address bar)          database is the table_id
    result will be in OrderedDict in python. you can parse and get the desired format.

    Thanks a lot! This was really helpful to achieve the result.




    i






    ------------------------------
    Pushpakumar Gnana
    ------------------------------

    Original Message


  • 20.  RE: Looking for simplest example to access quickbase using python

    Posted 11-07-2019 17:21
    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
    ------------------------------

    Original Message


  • 21.  RE: Looking for simplest example to access quickbase using python

    Posted 05-05-2020 16:33
    # -----------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
    ------------------------------

    Original Message


  • 22.  RE: Looking for simplest example to access quickbase using python

    Posted 05-06-2020 16:22
    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.
    Original Message


  • 23.  RE: Looking for simplest example to access quickbase using python

    Posted 02-14-2020 15:22
    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
    ------------------------------

    Original Message