import urllib
import json
import string
from pymongo import Connection
def runQuery(query, pp, pages):
ret = []
for pg in range(1, pages+1):
print 'page...' + str(pg)
p = urllib.urlopen('http://search.twitter.com/search.json?q=' + query + '&rpp=' + str(pp) + '&page=' + str(pg))
s = json.load(p)
dic = json.dumps(s)
dic = string.replace(dic, 'null', '"none"')
dx = eval(dic)
listOfResults = dx['results']
for result in listOfResults:
ret.append( { 'id':result['id'], 'from_user':result['from_user'], 'created_at':result['created_at'], 'text': result['text'] } )
completeRet = {"results": ret}
return completeRet
c = Connection()
d = c.twitterdb
coll = d.postbucket
res = runQuery('Iran', 100, 15)
ptrData = res.get('results')
for item in ptrData:
coll.save(item)
import urllib
import json
import string
import SimpleXMLRPCServer
from SimpleXMLRPCServer import SimpleXMLRPCServer
from SimpleXMLRPCServer import SimpleXMLRPCRequestHandler
def runQuery(query, pp, pages):
p = urllib.urlopen('http://search.twitter.com/search.json?q=' + query + '&rpp=' + str(pp) + '&page=' + str(pages))
s = json.load(p)
dic = json.dumps(s)
dic = string.replace(dic, 'null', '"none"')
dx = eval(dic)
listOfResults = dx['results']
ret = []
for result in listOfResults:
ret.append( { 'id':result['id'], 'from_user':result['from_user'], 'created_at':result['created_at'], 'text': result['text'] } )
completeRet = {"results": json.dumps(str(ret))}
return str(completeRet)
class RequestHandler(SimpleXMLRPCRequestHandler):
rpc_paths=('/RPC2')
server=SimpleXMLRPCServer(("localhost", 8000), requestHandler=RequestHandler)
server.register_introspection_functions()
server.register_function(runQuery, 'qry')
server.serve_forever()
>>> import xmlrpclib
>>> s = xmlrpclib.ServerProxy('http://localhost:8000')
>>> print s.qry('Bumrungrad', 10, 1)
Using Python, NHS open data (NHS Choices), and Mongo: for example, getting the name and the web sites of all the providers in the Wigan area (why Wigan? No idea, just that their football team seems to be pretty bad recently defeated Arsenal, and the name stuck with me).
Start the database: go to the bin subdirectory of the install directory, and type mongod –dbpath .\
I will connect to the database using the Python API (pymongo).
NHS choices offers several health data feeds:
As mentioned, I will use the second; to access it, you need to get a password and a login (apply for one here).
The basic Python code to query for providers and extract their names and web addresses is this:
First, build a list of services, as per the NHS documentation (the service code and the location are two required parameters):
services = [[1, 'GPs'], [2,'Dentists'], etc]
Then, query the web service:
for x in range(0, len(services)):
endpoint='http://www.nhs.uk/NHSCWS/Services/ServicesSearch.aspx?user=__login__&pwd=__password__&q=Wigan&type=' + str(x)
usock=urllib.urlopen(endpoint)
xmldoc=minidom.parse(usock)
usock.close()
nodes = xmldoc.getElementsByTagName("Service")
for node in nodes:
website = node.getElementsByTagName("Website")
name = node.getElementsByTagName("Name")
if website[0].firstChild <> None:
xmldoc.unlink()
The response will have a 3-item dataset, the service type, the provider name, and the web site (if one exists).
Mongo is a bit different in that the 'server' does not create a database physically until something is written to that database, so from the console client (launch, in \bin\: mongo) you can connect to a database that does not exist yet (use NHS in this case will create the NHS database - in effect, it will create files named NHS in the current directory).
Creating the 'table' from the console client: NHS = { service : "service", name : "name", website : "website" };
db.data.save(NHS); will create a collection (similar to SQL namespaces) and save the NHS table into it. The mongo client uses JavaScript as language and JSON notation to define the tables.
To access this collection in Python:
>>> from pymongo import Connection
>>> connection = Connection()
>>> db=connection.NHS
>>> storage=db.data
>>> post={"service" : 1, "name" : "python", "website" : "mongo" }
>>> storage.insert(post)
Here is the full code in Python to populate the database:
import urllib
from xml.dom import minidom
from pymongo import Connection
print "Building list of services..."
services = [[1, 'GPs'], [2,'Dentists'], [3, 'Pharmacists'], [4, 'Opticians'], [5, 'Hospitals'], [7, 'Walk-in centres'],[9, 'Stop-smoking services'], [10, 'NHS trusts'], [11, 'Sexual health services'], [12,' DISABLED (Maternity units)'], [13, 'Sport and fitness services'], [15, 'Parenting & Childcare services'], [17, 'Alcohol services'], [19, 'Services for carers'], [20, 'Renal Services'], [21, 'Minor injuries units'], [22, 'Mental health services'], [23, 'Breast cancer screening'], [24, 'Support for independent living'], [26, 'Memory problems'], [27, 'Termination of pregnancy (abortion) clinics'], [28, 'Foot services'], [29, 'Diabetes clinics'], [30, 'Asthma clinics'], [31,' Midwifery teams'], [32, 'Community clinics']]
print "Connecting to the database..."
connection = Connection()
db = connection.NHS
storage = db.data
print "Scanning the web service..."
for x in range(0, len(services)):
print '*** ' + services[x][1] + ' ***'
endpoint='http://www.nhs.uk/NHSCWS/Services/ServicesSearch.aspx?user=__login__&pwd=__password__&q=Wigan&type=' + str(x)
usock=urllib.urlopen(endpoint)
xmldoc=minidom.parse(usock)
usock.close()
nodes = xmldoc.getElementsByTagName("Service")
for node in nodes:
website = node.getElementsByTagName("Website")
name = node.getElementsByTagName("Name")
namei = name[0].firstChild.nodeValue
if website[0].firstChild <> None:
websitei = ' ' + website[0].firstChild.nodeValue
else:
websitei = 'none'
post = { "service" : x, "name" : namei, "website" : websitei }
storage.insert(post)
xmldoc.unlink()
To see the results from the Mongo client:
> db.data.find({service:5}).forEach(function(x){print(tojson(x));});
Will return all the hospitals inserted in the database (service for hospitals = 5); the response looks like this:
{
"_id" : ObjectId("4bc062dbc7ccc10428000032"),
"website" : " http://www.wiganleigh.nhs.uk/Internet/Home/Hospitals/tlc.asp",
"name" : "Thomas Linacre Outpatient Centre",
"service" : 5
}
Next, it might be interesting to try this using Mongo's REST API, and perhaps to build a GoogleApp to do so.
I'm really curious what effect will this have on databases. In theory, with the right authentication in place, everything could be exposed online and EDI would be vastly simplified, from sneakernet to HL7, everything would be replaced by REST calls.
At any rate, Freebase's attempt to organize everything is ambitious/stunning.
Wikipedia's own API, here.
Related: Talis.
Excel (2007) is great as a quick database front end tool – reasonably versatile and easy to set up. This ease of use might make many want to be able to update a database from Excel, and that is not so easy.
It has to be done in code. One way would be through linking the Excel file to the database server (there are different mechanisms for doing this, e.g. OPENROWSET in SQL Server or the SQL Gateway in Cache). However, if you're running Office in 64-bit mode you soon discover that there is no 64-bit compliant Excel ODBC driver.
A quick and more natural solution is to embed the database update code in Excel itself; in the case of a Cache backend, there are two ways to write this code:
Dim cm As Command
Dim cn As Connection
Dim cns As String
cns = "DSN=CacheWeb User"
Set cn = New Connection
cn.Open cns
Set cm = New Command
cm.ActiveConnection = cn
cm.CommandType = adCmdText
cm.CommandText = "INSERT INTO Income(UserId, Income, Country) VALUES('@u', @i, '@c')"
cm.Execute
A bit more interesting since it gets into some Cache internals:
Dim Factory As CacheObject.Factory
Set Factory = New CacheObject.Factory
If Factory.Connect("cn_iptcp:127.0.0.1[1972]:TESTDBNS") = False Then
MsgBox "Failed to connect"
Exit Function
End If
Dim Income As Object
Set Income = Factory.New("Income")
Income.UserId = "2"
Income.Income = 120
Income.country = "PAK"
Income.sys_Save
Income.sys_Close
Set Income = Nothing
Incidentally, using the Oracle ODP.NET driver in Visual Studio yields a much better experience with an Oracle database than using the standard MS drivers. You actually get a return (XML-formatted) when querying object tables (the MS driver reports it as 'unsupported data type') and can interact with the underlying database much more, including tuning advisor, deeper database object introspection, etc.
Even PostgreSQL (which I find quite cool actually) does portray itself as having object/relational features - table structures can be inherited.
Objects can be created (%New)/opened(%OpenId) from code, but to be saved (%Save: which will update the database), the restrictions must be met (required properties, unique indexes, etc).
Also, I finally got the .NET gateway generator to work: it creates native .NET classes that can communicate with Cache objects. Here is a sample of the client code:
InterSystems.Data.CacheClient.CacheConnection cn = new InterSystems.Data.CacheClient.CacheConnection("Server=Irikiki; Port=1972;" +
"Log File = D:\\CacheNet\\DotNetCurrentAccess.log; Namespace = USER;" +
"Password = ______; USER ID = ____");
cn.Open();
PatientInfo pi = new PatientInfo(cn);
pi.PatientName = "New Patient";
pi.PatientID = new byte[1]{6};
InterSystems.Data.CacheTypes.CacheStatus x = pi.Save();
Console.WriteLine(x.Message);
PatientInfo is a class defined in Cache, as follows:
