License GNU General Public License, version 3
Lines 52
Keywords
adodb (1)
Permissions
Owner: wildintellect
Viewable by Everyone
Editable by All Siafoo Users
Hide
Easily highlight source code for your blog with our Syntax Highlighter. Join Siafoo Now or Learn More

Tranpose column to row Atom Feed 0

In Brief Transpose a column returned from a SQL query into a row. Iterate for distinct sections of a table, basically a common foreign key.... more
# 's
 1#! /usr/bin/env python
2import sys, adodb
3from os import path as path
4from pysqlite2 import dbapi2 as sqlite
5
6#Tranpose field names into a csv
7#Alex Mandel
8#Copyright 2008
9#TODO: lookup sql alchemy
10
11def queryData():
12 try:
13 #open sqlite file
14 db = adodb.NewADOConnection('sqlite')
15 db.Connect(database = sqlFile)
16
17 #set basic query
18 sqlquery = "SELECT DISTINCT col_1 FROM tablelabels"
19 #Select Distinct values from the key column
20 list = db.GetAll(sqlquery)
21 #For each item in list query for records
22 for x in list:
23 sqlquery = ("SELECT col_2 FROM tablelabels WHERE col_1='"+x[0]+"'")
24 sublist = db.GetAll(sqlquery)
25 #tranpose the results
26 test = transposeData(x[0], sublist)
27 db.Close()
28
29 except Exception,e:
30 db.Close()
31 outcsv.close()
32 print "Error reading Database:"+str(e)
33
34def transposeData(item, sublist):
35 try:
36 #take list and write out
37 a=sublist[0]
38 y=a[0]
39 for x in sublist[1:]:
40 y = y+","+x[0]
41 #write line to csv
42 outcsv.writelines(y+"\n")
43 return 0
44
45 except Exception,e:
46 outcsv.close()
47 return 1
48 print "Error Writing Output:"+ str(e)
49
50#select your database
51sqlFile = "/home/madadh/ucd/geo290/census/census.db"
52#open a blank csv file to write to
53outcsv = open("/home/madadh/ucd/geo290/census/transpose.txt","w")
54test = queryData()
55#close csv
56outcsv.close()

Transpose a column returned from a SQL query into a row. Iterate for distinct sections of a table, basically a common foreign key.

Happens to produce a csv file of headers for US Census block group SF3 tables. Requires sqlite database, adodb python package.