Forum Discussion
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
------------------------------
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')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
# 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)
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.
- PushpakumarGna16 years agoQrew Assistant CaptainHi 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
------------------------------ - PushpakumarGna16 years agoQrew Assistant CaptainMy 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
------------------------------- AustinK6 years agoQrew CommanderWithout 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)
- PushpakumarGnan6 years agoQrew MemberHi 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
------------------------------- PushpakumarGnan6 years agoQrew Memberi am trying to retrieve a report from the app. it is a list all report from the test table
------------------------------
Pushpakumar Gnana
------------------------------- AustinK6 years agoQrew CommanderThe 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')
- PushpakumarGnan6 years agoQrew MemberHi 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
------------------------------