License GNU General Public License, version 3
Lines 52
Keywords
adodb (1)
Permissions
Owner: wildintellect
Viewable by Everyone
Editable by All Siafoo Users
Hide
Stay up to dateembedded code automagically updates, each snippet and article has a feed 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.