Hello, I want to take any string fields in my resultset and do the
following to them before writing them out to CSV:
unicodedata.normalize('NFKD', s).encode('ASCII', 'ignore')
I dont really think my SA program is necessary, but may as well
include it to be safe:
#!/usr/bin/env python
import csv, os, pprint, sys
import pysqlite2
from sqlalchemy import *
report_every = 1000
engine = create_engine('sqlite:///mechanism.sqlite3')
#engine = 'sqlite:///:memory:'
metadata = BoundMetaData(engine)
metadata.engine.echo = False
mech_t = Table('mechanism', metadata,
Column('mechanism_id', String(length=255)),
Column('drug_id', String(length=255),
index=True),
Column('mechanism', String(length=255))
)
drug_t = Table('drug', metadata,
Column('drug_id', String(length=255), index=True),
Column('company_id', String(length=255)),
Column('company', String(length=255)),
Column('phase', String(length=255)),
Column('product', String(length=255)),
Column('tradename', String(length=255)),
Column('indication', String(length=255)),
Column('partner', String(length=255)),
)
csvs = {
'drugs' : ['input/', 'drugs.txt', "drug_id company_id company
phase product tradename indication partner mechanism".split(),
drug_t],
'mechanism' : ['input/', 'mechanism.txt',"mechanism_id drug_id
mechanism".split(), mech_t],
}
csvs['output'] = list(csvs['drugs'])
csvs['output'][0] = 'output/'
csvs['output'][1] = 'output.txt'
class MyCSV:
def __init__(self, dir, filename, headers, table):
self.filename = dir + filename
self.headers = headers
self.table = table
def mkrdr(self):
return csv.DictReader(open(self.filename, 'rb'),
fieldnames=self.headers,
delimiter='|')
def mkwriter(self):
f = open(self.filename, 'wb')
print dir(f)
f.write("|".join(self.headers))
f.write("\n")
#return csv.DictWriter(f, fieldnames=self.headers,
delimiter='|')
return csv.writer(f, delimiter='|')
def store(self, row):
#pprint.pprint(row)
#pprint.pprint(self.table)
self.table.insert().execute(**row)
def load_table(dict_key):
csv = csvs[dict_key]
o = MyCSV(*csv)
rdr = o.mkrdr()
#pprint.pprint(rdr)
for i, row in enumerate(rdr):
#print i
o.store(row)
if i % report_every == 0: print i+1, 'rows stored'
print i+1, "total rows stored"
def gen_csv():
csv = csvs['output']
o = MyCSV(*csv)
writer = o.mkwriter()
q = """
SELECT
drug.drug_id, company_id, company, phase, product, tradename,
indication, partner, mechanism.mechanism
FROM
drug, mechanism
WHERE
drug.drug_id = mechanism.drug_id
"""
rp = engine.execute(q)
for i, row in enumerate(rp):
j = i + 1
pprint.pprint(row)
writer.writerow(row)
if j % report_every == 0: print j, 'rows written to output'
print i+1, "total rows written"
def init_dbs():
print "CWD", os.getcwd()
try:
mech_t.drop()
drug_t.drop()
except:
pass
drug_t.create()
mech_t.create()
def main():
#init_dbs()
print "CSVS", pprint.pformat(csvs)
#load_table('mechanism')
#load_table('drugs')
gen_csv()
if __name__ == '__main__':
main()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
