At work, part of our job involves getting data from a bunch of different databases and verifying that certain relationships hold among them, or finding other kinds of problems or bugs; we usually end up pasting our results into Excel, and sending the spreadsheets around.
One of our teammates had the idea of writing a script that writes directly to Excel; I'm still amazed it didn't occur to me :) but to compensate, I figured I'd write a program to do the same; since I couldn't find any simple .net libraries to write to excel, I figured I'd try with python; found openpyxl (great simple library, although its docs are outdated), so I wrote a simple script that takes queries in JSON and produces an excel spreadsheet with the data produced by those queries.
The Json file looks like this:
Notice we can specify several sheets, and for every sheet we specify queries; for each query we specify its DBI driver (you need to have those libraries available in your system), connection string and query string.
The python code is amazingly simple:
And you call it by passing it the name of the json file, and the name of the output you want.
Showing posts with label python. Show all posts
Showing posts with label python. Show all posts
Friday, July 4, 2014
Thursday, November 15, 2012
A simple blogroll with python and feedparser
A friend wanted to use a blog as a sort of database, allowing several people to create posts on the same blog, and then transform the posts into some sort of listing; most blogging software will let you export a list of posts as an rss feed, so this seemed like a good time to go learn how to parse and use rss.
While I'll let my friend figure out his own application, this prompted me to write a simple 'blogroll' program, that would read several rss (and atom) feeds and would produce html showing the appropriate titles and links.
Since python is my scripting language of choice for now, I googled rss parser libraries, and found feedparser.
We can call feedparser.parse, and give it a url; it will then return an object representing the feed; the object contains a field called feed, which contains information about the feed, like its title and its link (url); the object also contains entries, which is a list of objects, each representing one entry in the rss feed; for each entry, you have fields like its title and its link.
So, it is just a matter of iterating over a list of urls, parsing the feed for each, and going over its entries, producing html as we go, writing everything to a file. The final code looks like:
Sunday, October 14, 2012
Getting started with Amazon's DynamoDB in Python
Amazon provides many cloud services, both for Infrastructure as a Service (running Virtual Machines in EC2) and Platform as a Service (providing the actual services). One of the services is DynamoDB, a simple NoSQL database, hosted on Amazon's servers.DynamoDB, like many NoSQL Databases is a simple key-value storage, with some added capabilities.
Basically, you store objects (representable in JSON), matched to a key. Your key can be a string or an int (and actually, DynamoDB allows your key to be composed of two fields, a hash and a range, but here we will only do the hash). Your object is just a dictionary mapping fields to values.
The best python library for DynamoDB (and for most other aws services) is boto. The easiest way to install boto is with pip; just do pip install boto or sudo pip install boto.
Amazon offers a free tier for DynamoDB, which should be enough for playing and small applications (the DB has to be less than 100M in size, and you get 5KB in reads and 1KB in writes per second), so head down to aws.amazon.com and create your account, if you don't have it; after making your account, go to security credentials and find out your access key id and your secret access key. You may want to store your credentials in a file called .boto in your home folder, as explained here. After that, you can connect to your dynamodb as follows:
Now, let's try to create one table. Our table will be called users, and its key will be a string. We first need to create a schema (the schema only specifies the name and type of your key; your 'table' can contain any kind of objects, with any attributes), and then use that schema to create a table, as follows:
Once we have created the table, we go onto adding items; we create a dictionary containing our data, then use the new_item method of our table to create the item, and finally the put method of that item to actually add it to the database, as follows: And, after creating several items we can obtain an element (knowing its key) with the get_item method of a table, as follows (notice it will throw an exception if the key doesn't exist): To modify the item in the DB, we would just modify it in memory (it looks like a dictionary) and then call its put method again.
Most of the times you will be searching for a given key (or at least will know a part of the key), but if not, you can use the scan method (notice this method goes through EVERY object on the table, and so may be slow and consume a lot of I/O). You can pass a list of conditions (as of now, the documentation is incorrect, stating you specify the conditions with strings; you need to use elements coming from boto.dynamodb.condition). A scan would look like: For more information, check:
Basically, you store objects (representable in JSON), matched to a key. Your key can be a string or an int (and actually, DynamoDB allows your key to be composed of two fields, a hash and a range, but here we will only do the hash). Your object is just a dictionary mapping fields to values.
The best python library for DynamoDB (and for most other aws services) is boto. The easiest way to install boto is with pip; just do pip install boto or sudo pip install boto.
Amazon offers a free tier for DynamoDB, which should be enough for playing and small applications (the DB has to be less than 100M in size, and you get 5KB in reads and 1KB in writes per second), so head down to aws.amazon.com and create your account, if you don't have it; after making your account, go to security credentials and find out your access key id and your secret access key. You may want to store your credentials in a file called .boto in your home folder, as explained here. After that, you can connect to your dynamodb as follows:
Now, let's try to create one table. Our table will be called users, and its key will be a string. We first need to create a schema (the schema only specifies the name and type of your key; your 'table' can contain any kind of objects, with any attributes), and then use that schema to create a table, as follows:
Once we have created the table, we go onto adding items; we create a dictionary containing our data, then use the new_item method of our table to create the item, and finally the put method of that item to actually add it to the database, as follows: And, after creating several items we can obtain an element (knowing its key) with the get_item method of a table, as follows (notice it will throw an exception if the key doesn't exist): To modify the item in the DB, we would just modify it in memory (it looks like a dictionary) and then call its put method again.
Most of the times you will be searching for a given key (or at least will know a part of the key), but if not, you can use the scan method (notice this method goes through EVERY object on the table, and so may be slow and consume a lot of I/O). You can pass a list of conditions (as of now, the documentation is incorrect, stating you specify the conditions with strings; you need to use elements coming from boto.dynamodb.condition). A scan would look like: For more information, check:
- Amazon's DynamoDB page
- boto's github
- Boto's DynamoDB tutorial
- Boto's DynamoDB API reference
Subscribe to:
Posts (Atom)