search

xmunoz / sodapy

Python client for the Socrata Open Data API
MIT License
402 stars 114 forks source link

Putting the data from Socrata into SQL #52

Closed kgpavinash closed 5 years ago

kgpavinash commented 5 years ago

I was just wondering if there is support for this.

xmunoz commented 5 years ago

Can you provide example? I'm not sure what you mean...

kgpavinash commented 5 years ago

I apologize for not being clear. I will try to explain through an example.

Let us say I obtain 5 entries through this code: data = client.get("nimj-3ivp", limit=5)

Is there some function I could call to put the 5 entries into an existing sql table?

Right now, I a converting the variable named 'data' to json, extracting each value and then putting them into sqlite3 table. It works but it is a bit tedious.

This is not important or anything. I was just curious about it.

xmunoz commented 5 years ago

Ok, yes. So this is an example of post-processing, and I believe this functionality to be outside the scope of this library. If you want an easier way to work with an sqlite database, take a look at this: https://docs.python.org/3/library/sqlite3.html

If you use this interface, there is no need to convert to json. Simply reference the dictionary parameters from the returned data directly in the SQL command. Here is an example:

import sodapy
import sqlite3

client = sodapy.Socrata("opendata.socrata.com", None)
data = client.get("n2rk-fwkj", limit=5)

conn = sqlite3.connect('example.db')
c = conn.cursor()
values = [(d['first_name'], d['address'], d['balance']) for d in data]
c.executemany('INSERT INTO bankdata VALUES (?,?,?)', values)

Of course, that database and schema must already exist before inserting this data. Let me know if you have any other questions about this.

kgpavinash commented 5 years ago

Ah I see. The way you provided seems to be an easier way than what I am doing currently, Thanks! Also, great job in creating this library. It is well explained and nice to use.