Import/Export Flat Files#
Easily go between database objects and text files.
Import#
Many times, database tables are represented as fixed-format text files. Pisces makes it easy to go between database objects and these external flat file representations.
Here, we’ll import the following KB Core Site flat file, “TA.site”.
Each line in the text file is passed to Site.from_string, which is a
Pisces-specific method that uses the underlying column descriptions in the class
to interpret lines in the flat file and to populate a Site row instance.
Finally, session.add(isite) and session.commit() add and write rows to the
database.
TA.site#
K02A -1 2286324 42.766700 -123.489800 0.9630 Glendale, Oregon,U.S.A. ss K02A 0.0000 0.0000 2009-04-15 15:55:50
I03D 2009307 2286324 43.697200 -123.348700 0.1400 Drain, OR, USA ss I03D 0.0000 0.0000 2011-10-11 13:07:17
P01C -1 2286324 39.469000 -123.337500 0.4409 Double 8 Ranch, Willits, California,U.S.A. ss P01C 0.0000 0.0000 2009-04-15 15:55:50
N02C -1 2286324 40.822000 -123.305700 0.7170 Big Bar, California,U.S.A. ss N02C 0.0000 0.0000 2009-04-15 15:55:50
H03A -1 2286324 44.676500 -123.292300 0.2143 Soap Creek Ranch, Albany, Oregon,U.S.A. ss H03A 0.0000 0.0000 2009-04-15 15:55:50
G03A -1 2286324 45.315300 -123.281100 0.2080 Yamhill, Oregon,U.S.A. ss G03A 0.0000 0.0000 2009-04-15 15:55:50
I03A -1 2286324 43.972600 -123.277700 0.2057 Eugene, Oregon,U.S.A. ss I03A 0.0000 0.0000 2009-04-15 15:55:50
G03D 2009297 2286324 45.211500 -123.264100 0.2220 McMinnville, OR, USA ss G03D 0.0000 0.0000 2011-10-11 13:07:17
D03D 2010237 2286324 47.534700 -123.089400 0.2620 Eldon, WA, USA ss D03D 0.0000 0.0000 2011-10-11 13:07:17
F04D 2009318 2286324 46.082900 -123.010800 0.2360 Rainier, OR, USA ss F04D 0.0000 0.0000 2011-10-11 13:07:17
Read a flat file Site table into a database
import pisces.schema.kbcore as kb
class Site(kb.Site):
__tablename__ = 'site'
session = sa.orm.Session(engine)
with open('TA.site') as ffsite:
for line in ffsite:
isite = Site.from_string(line)
session.add(isite)
session.commit()
Export#
Next, we’ll write database results to a flat file. These work because the info dictionary in the underlying columns tell the class what the string version of itself should look like.
Here, we write 30 origins to a flat file.
with open('TA.origin', 'w') as fforigin:
for iorigin in session.query(Origin).filter(Origin.auth == 'REB-IDC').limit(30):
fforigin.write(str(iorigin) + os.linesep)
TA.origin#
-6.121700 130.688500 0.0000 954927209.28000 620218 316285 2000096 60 40 -1 280 24 qp -999.0000 g 5.60 299796 4.30 299797 6.10 299795 man:inversion REB-IDC -1 2002-06-11 00:00:00
-4.824500 102.976700 47.7000 954988491.91000 620219 316334 2000097 20 17 -1 274 24 qp -999.0000 f 4.10 299798 3.20 299799 -999.00 -1 man:inversion REB-IDC -1 2002-06-11 00:00:00
39.195900 24.608700 0.0000 954974602.21000 620220 316319 2000096 16 15 -1 365 30 qp -999.0000 g 3.80 299801 3.20 299802 3.80 299800 man:inversion REB-IDC -1 2002-06-11 00:00:00
22.291000 143.776500 115.8000 954732444.39000 620221 316167 2000094 15 13 -1 213 18 qp -999.0000 f 3.50 299803 -999.00 -1 -999.00 -1 man:inversion REB-IDC -1 2002-06-11 00:00:00
-9.797000 66.893100 0.0000 954980391.62000 620222 316324 2000097 19 11 -1 429 33 qp -999.0000 g 4.20 299804 4.10 299805 -999.00 -1 man:inversion REB-IDC -1 2002-06-11 00:00:00
Write flat files from any combination of columns#
Ad-hoc collections of columns can be also written to well-formed flat files, with the right schema-specific format.
Queries on specific columns return a list of tuple-like objects called KeyedTuples,
where values in individual records can be indexed into like a tuple, e.g. record[0],
or accessed via attributes, e.g. record.lat, record.lon.
Let’s write some network-station records to a text file. First, get the string formatter for the columns you’ll be using.
string_formatter returns the correct format string from the Python format specification mini-language for the columns you’ll be writing.
The columns must be known to Base. That is, each column must have been defined in at least one table that inherited from Base.
fmt = ps.string_formatter(Base.metadata, ['net', 'sta', 'lat', 'lon', 'elev'])
print(fmt)
fmt looks like this:
"{0:8.8s} {1:6.6s} {2:11.6f} {3:11.6f} {4:9.4f}"
Now, get the records and write them to file.
q = session.query(Affiliation.net, Site.sta, Site.lat, Site.lon, Site.elev)
q = q.filter(Site.sta == Affiliation.sta)
q = q.filter(Affiliation.net.in_(['TA', 'UU']))
import os
with open('adhoc.txt', 'w') as f:
for netsta in q:
f.write(fmt.format(*netsta) + os.linesep)
adhoc.txt#
TA Y53A 33.855400 -83.583600 0.2340
TA Y54A 33.862100 -82.688000 0.1760
TA Z38A 33.259900 -94.985100 0.1160
TA Z49A 33.194200 -86.531100 0.1340
TA Z50A 33.254000 -85.922600 0.3700
TA Z51A 33.316700 -85.174700 0.2490
TA Z52A 33.189300 -84.417600 0.2520
TA Z53A 33.280100 -83.571300 0.1440
TA Z54A 33.236200 -82.841700 0.1340
TA Z55A 33.221100 -82.135900 0.1000
UU EOCU 40.777000 -111.899100 1.3560
UU QJMH 40.703500 -111.866100 1.3120
UU QJOT 40.741600 -111.493900 1.9770
UU QKSL 40.377100 -111.861000 1.3790
UU QLIN 40.347200 -111.694700 1.5380
UU QMDS 40.729000 -111.816100 1.4050
UU QNRL 41.740700 -111.824900 1.4070
UU QPAY 40.053000 -111.728400 1.4040
UU QPML 40.057800 -111.953900 1.3340
UU QRJG 40.260900 -111.632700 1.5300
...