I have simple, 1 table, data source that I want to be able to query through an API.
I want to be able to query on any one of the columns. Ideally, I could query for multiple values per column. One column has text, for which I need to be able to do partial and approximate matches (as well as handle diacriticals)
I am wondering what packages people recommend for building this. I realize its something simple, but it seems like a common enough goal that I thought there would be something available to do all the heavy lifting.
Ideally, I'd like to work with python/django, but could also do PHP. Is there something available to help with this?
I assume since you mentioned django, that you are asking to create a web facing API (like what twitter and facebook provide), if that's the case there are a few options:
You may not have to write anything at all. There are platforms like apigee that allow you to create an API just by clicking a few links. They have free accounts, like almost everyone on the net.
If that doesn't work for you, you can use flask-rest (optimized for the Flask microframework) or tastypie which works great with django for Python. Both will allow you to easily create APIs for your data source and they will take care of most of the boilerplate code for you.
For PHP, someone already asked the same question earlier so that would be a good resource.
If you just want to create an API on top of an existing database and use this API to query the database in other applications, then the de-facto standard for Python is SQLAlchemy.
import sqlite3
conn = sqlite3.connect("some_db.sqlite")
conn.execute("SELECT * FROM TABLE WHERE some_field=?",('field_value',)) #this should properly escape values
# or
conn.execute("SELECT * FROM TABLE WHERE some_field LIKE ?",('%parial_match%',))
not sure if thats what you are asking for (this is python)
Probably you may want to check out this link, that will give you some lights about structuring and some template code (in PHP) for building an API:
http://net.tutsplus.com/tutorials/php/creating-an-api-centric-web-application/
From the formatting of the URL to the call of the methods called in it. I.e.:
$controller = ucfirst(strtolower($params['controller']));