def __init__(self, **kwargs):
"""
Initialize a database connection.
Parameters
----------
backend: string, optional
One of the SQLAlchemy connection strings from
http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html#database-urls
user: string, optional
Not required for sqlite.
psswd: string, optional
Not needed for sqlite. Prompted if needed and not provided.
server: string, optional
Database host server.
port: string or integer, optional
Port on remote server.
instance: string, optional
For sqlite, this is the database file name.
conn: string, optional
A fully-formed SQLAlchemy style connection string.
Examples
--------
>>> from pisces.client import Client
>>> client = Client(user='******', backend='oracle',
server='my.server.edu', port=1521,
instance='mydb')
"""
session = ps.db_connect(**kwargs)
self.metadata = sa.MetaData(session.bind)
self.session = session
self.tables = {}
def test_reflected_declarative():
from pisces.schema.util import PiscesMeta
session = db_connect()
Base = declarative_base(metaclass=PiscesMeta)
class Site(Base, DeferredReflection):
__tablename__ = 'site'
Site.prepare(session.bind)
t.assert_true(Site.__tablename__ == 'site')
t.assert_true(Site.__table__.name == 'site')
def db_connect(dialect,
hostname,
db_name,
port=None,
username="",
password="",
driver=""):
"""
Connect to a database to do database things...
Parameters
----------
dialect: str
Type of database. (examples: oracle, mysql)
hostname : str
The url of the database. (example: mydb.home.org)
db_name: str
Name of your database
port: int or string
Port number of your database
username: str
login name for your database connection. For self authenticated sites, leave this as an empty string
password: str
used in conjunction with username. For self authenticated sites, leave this as an empty string
driver: str
driver api used for connection/ (example: pymysql)
Returns
-------
session : bound SQLAlchemy session instance
"""
# before we do anything, lets clean up some of the input...
# in case someone passed the port as an integer, first convert it to a string
str_port = str(port)
# deal with special characters in the username and password
if password:
password = urllib.parse.quote_plus(password)
#make sure things that need to be lower case are lower case
dialect = dialect.lower()
if driver:
driver = driver.lower()
driver = '+' + driver
connect_str = dialect + driver + "://" + username + ":" + password + "@" + hostname + ":" + str_port + "/" + db_name
return ps.db_connect(connect_str)
def test_reflect_qualified_tablename(self):
"""
Reflect an actual schema-qualified database table.
"""
class Site(util.ORMBase, DeferredReflection):
__tablename__ = 'jkmacc.site'
session = db_connect()
Site.prepare(session.bind)
self.assertTrue(Site.__tablename__ == 'site')
self.assertTrue(Site.__table__.name == 'site')
self.assertTrue(Site.__table__.schema == 'jkmacc')
def test_reflect_qualified_tablename(self):
"""
Reflect an actual schema-qualified database table.
"""
class Site(util.ORMBase, DeferredReflection):
__tablename__ = 'jkmacc.site'
session = db_connect()
Site.prepare(session.bind)
self.assertTrue(Site.__tablename__ == 'site')
self.assertTrue(Site.__table__.name == 'site')
self.assertTrue(Site.__table__.schema == 'jkmacc')
def __init__(self, **kwargs):
"""
Initialize a database connection.
Parameters
----------
backend: string, optional
One of the SQLAlchemy connection strings from
http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html#database-urls
user: string, optional
Not required for sqlite.
psswd: string, optional
Not needed for sqlite. Prompted if needed and not provided.
server: string, optional
Database host server.
port: string or integer, optional
Port on remote server.
instance: string, optional
For sqlite, this is the database file name.
conn: string, optional
A fully-formed SQLAlchemy style connection string.
session : sqlalchemy.orm.Session
An existing session instance.
Examples
--------
>>> from pisces.client import Client
>>> client = Client(user='******', backend='oracle',
server='my.server.edu', port=1521,
instance='mydb')
"""
if kwargs.get('session', None):
session = session
else:
session = ps.db_connect(**kwargs)
self.metadata = sa.MetaData(session.bind)
self.session = session
self.tables = {}
def main(argv=None):
"""If imported, use with this syntax:
>>> from traces2db import main
>>> main(['-f','*.sac','dbout'])
Returns exit_code
"""
if argv is None:
argv = sys.argv
exit_code = 0
try:
parser = OptionParser(
usage="Usage: %prog [options] ",
description=
"""Write data from sac or mseed headers into a database. Currently, only SAC files are supported.""",
version='0.1')
parser.add_option(
'-f',
'--files',
default=None,
help="Unix-style file name expansion for trace files.",
type='string',
action="callback",
callback=expand_glob,
dest='f')
parser.add_option(
'-l',
'--list',
default=None,
help="A text file containing a column of trace file names.",
type='string',
dest='l')
parser.add_option(
'-t',
'--tables',
help="Only parse into this comma-seperated list of tables.",
default=None,
action='callback',
callback=split_comma,
dest='t')
parser.add_option(
'--conn',
default=None,
help="SQLAlchemy-style output database connection string.",
type='string',
dest='conn')
parser.add_option(
'-u',
'--user',
default='',
help="Database user name. Not needed for sqlite and remotely \
authenticated connections.",
type='string',
dest='user')
parser.add_option('-b',
'--backend',
default='sqlite',
help="SQLAlchemy-style backend and driver string.",
type='string',
dest='backend')
parser.add_option(
'-p',
'--psswd',
default='',
help="Database password. Not needed for sqlite and remotely \
authenticated connections. Prompted for if needed and \
not given.",
type='string',
dest='psswd')
parser.add_option(
'-s',
'--server',
default='',
help="Local or remote database server. Not needed for sqlite.",
type='string',
dest='server')
parser.add_option('--port',
default='',
help="Port on database server. Optional.",
type='string',
dest='port')
parser.add_option(
'-i',
'--instance',
default='',
help="Database instance name. Optional for some backends.\
For sqlite, this is the file name.",
type='string',
dest='instance')
parser.add_option(
'--affiliation',
default=None,
help="Name of desired output affiliation table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='affiliation')
parser.add_option(
'--arrival',
default=None,
help="Name of desired output arrival table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='arrival')
parser.add_option(
'--assoc',
default=None,
help="Name of desired output assoc table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='assoc')
parser.add_option(
'--event',
default=None,
help="Name of desired output event table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='event')
parser.add_option(
'--instrument',
default=None,
help="Name of desired output instrument table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='instrument')
parser.add_option(
'--origin',
default=None,
help="Name of desired output origin table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='origin')
parser.add_option('--site',
default=None,
help="Name of desired output site table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='site')
parser.add_option(
'--sitechan',
default=None,
help="Name of desired output sitechan table. Optional.",
type='string',
metavar='owner.tablename',
dest='sitechan')
parser.add_option(
'--wfdisc',
default=None,
help="Name of desired output wfdisc table. Optional. \
No owner for sqlite. Currently always adds wfdisc rows.",
type='string',
metavar='owner.tablename',
dest='wfdisc')
parser.add_option(
'--lastid',
default=None,
help="Name of desired output lastid table. Required. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='lastid')
parser.add_option(
'--all_tables',
default=None,
help="Convenience flag. Attempt to fill all tables. \
e.g. jon.test_ will attempt to produce tables like \
jon.test_origin, jon.test_sitechan, ...\
Not yet implemented.",
type='string',
metavar='owner.prefix',
dest='all_tables')
parser.add_option(
'--gndd',
default=False,
help=
"""Convenience flag fills in server, port, instance, backend for gnem database users.""",
action='store_true',
dest='gndd')
parser.add_option(
'--rel_path',
default=False,
help="Write directory entries as relative paths, not absolute.",
action='store_true',
dest='rel_path')
# parser.add_option('-t','--type',
# default=None,
# help="File type: 'SAC' or 'MSEED'",
# type='string',
# dest='t')
# parser.add_option('--db',
# help="Also return formatted flat files.",
# default=False,
# action='store_true',
# dest='db')
options, args = parser.parse_args(argv)
#print options, args
if len(args) > 1:
"""Unknown positional arguments given."""
print parser.print_help()
exit_code = 1
else:
########### MAKE CONNECTIONS ###########
if options.gndd:
#TODO: add a password option here
try:
from pisces_gndd import gndd_connect
session = gndd_connect(options.user)
except ImportError as e:
raise e
else:
if options.conn:
session = ps.db_connect(conn=options.conn)
else:
session = ps.db_connect(user=options.user,
psswd=options.psswd,
backend=options.backend,
server=options.server,
port=options.port,
instance=options.instance)
tables = reflect_or_create_tables(options)
try:
files = get_file_iterator(options)
except IOError as e:
# file list doesn't exist
raise e
exit_code = 1
except ValueError:
# input neither a file list nor input files
print parser.print_help()
exit_code = 1
if exit_code != 1:
######### GET ALL IDS #########
# "last" is a dict of initialized id generators.
# use it like last.arid.next()
# update lastid table after file loop!
#XXX: experimental!
lastid = tables['lastid']
last = AttribDict(
db.util.get_id_gen(
['arid', 'orid', 'evid', 'wfid', 'chanid', 'inid'],
session, lastid))
# last = AttribDict()
# #pdb.set_trace()
# for iid in ['arid', 'orid', 'evid', 'wfid', 'chanid', 'inid']:
# try:
# val = session.query(lastid.keyvalue).filter(lastid.keyname == iid).one()[0]
# except NoResultFound:
# # no id. make one, starting at zero
# val = 0
# last[iid] = db.gen_id(val)
# requested tables, minus lastid
core_tabs = tables.keys()[:]
core_tabs.remove('lastid')
############# ITERATE OVER FILES #############
for ifile in files:
print ifile
try:
tr = read(ifile, headonly=True)[0]
trtables = db.trace2tables(tr, tables=core_tabs)
# trtables is {'site': mapped instance, ...} for all
# tables requested or available from header
# FILL IN AUTOMATICALLY GENERATED TABLES
#TODO: this section needs better logic, talk to Richard
# combine session.add(_all), session.commit,
# exception catching with id management.
affil = trtables.get('affiliation')
arrivals = trtables.get('arrival')
site = trtables.get('site')
sitechan = trtables.get('sitechan')
instrument = trtables.get('instrument')
assocs = trtables.get('assoc')
event = trtables.get('event')
origin = trtables.get('origin')
wfdisc = trtables.get('wfdisc')
# FILL IN IDs AND OTHER IMPORTANT VALUES
#if affil:
# # affiliation.ontime
# pass
if arrivals:
# arrival.arid
for arrival in arrivals:
if not arrival.arid:
arrival.arid = last.arid.next()
if origin:
# origin.orid
if not origin.orid:
#XXX: always increments orid.
# combine with table add logic
origin.orid = last.orid.next()
if assocs and arrivals:
# assoc.arid
# assoc.orid
#XXX: assumes arrivals are related to origin
# and assocs and arrivals are in the same order
for (assoc, arrival) in zip(assocs, arrivals):
assoc.arid = arrival.arid
if hasattr(origin, 'orid'):
assoc.orid = origin.orid
#if site:
# always gonna be a site, right?
# # site.ondate
# pass
if sitechan:
# sitechan.ondate
# sitechan.chanid
if not sitechan.chanid:
sitechan.chanid = last.chanid.next()
#if instrument:
# # instrument.inid
# # instrument.dir
# # instrument.dfile
# # instrument.rsptype
# pass
if wfdisc:
# XXX: Always gonna be a wfdisc, right?
# XXX: Always writes a _new_ row b/c always new wfid
# wfdisc.dir
# wfdisc.dfile
# wfdisc.wfid
if options.rel_path:
wfdisc.dir = os.path.dirname(ifile)
else:
wfdisc.dir = os.path.abspath(
os.path.dirname(ifile))
wfdisc.dfile = os.path.basename(ifile)
wfdisc.wfid = last.wfid.next()
######### ADD TABLES TO DATABASE #########
#pdb.set_trace()
for tableval in trtables.values():
if tableval:
try:
session.add(tableval)
session.commit()
except UnmappedInstanceError:
#tableval was a list. i.e. arrival or assoc
#may need some IntegrityError catching here
session.rollback()
#XXX: is add_all what I mean to do here?
session.add_all(tableval)
except IntegrityError, e:
#duplicate or nonexistant primary/unique keys
#TODO: make this more descriptive
print " Duplicate row in {0}".format( \
tableval.__table__.name)
session.rollback()
except OperationalError, e:
# no such table name. shouldn't happen.
# database is locked.
session.rollback()
raise e
#except InvalidRequestError:
# #XXX: what is this error?
# #maybe tried to insert None
# session.rollback()
except (IOError, TypeError):
#can't read file or doesn't exist.
print "Couldn't read file {0}.".format(ifile)
fname = args.fname
print('setting outputfile:', fname)
name = 1
else:
fname = None
name = 0
class Fk_results(schema.fk_results):
__tablename__ = fkresultsT
class Fk_params(schema.fk_params):
__tablename__ = 'FK_PARAMS'
try:
if sq.count('oracle') > 0:
session = ps.db_connect(sq)
session_type = 'oracle'
from global_ import Site, Origin, Wfdisc_raw
elif sq.count('sqlite') > 0:
print('SQLITE database')
session = ps.db_connect(sq)
session_type = 'sqlite'
from pisces.tables.kbcore import Site, Origin, Wfdisc
'''
class Site(kba.Site):
__tablename__ = 'site'
class Wfdisc(kba.Wfdisc):
__tablename__ = 'wfdisc'
'''
def main(argv=None):
"""If imported, use with this syntax:
>>> from traces2db import main
>>> main(['-f','*.sac','dbout'])
Returns exit_code
"""
if argv is None:
argv = sys.argv
exit_code = 0
try:
parser = OptionParser(usage="Usage: %prog [options] ",
description="""Write data from sac or mseed headers into a database. Currently, only SAC files are supported.""",
version='0.1')
parser.add_option('-f','--files',
default=None,
help="Unix-style file name expansion for trace files.",
type='string',
action="callback",
callback=expand_glob,
dest='f')
parser.add_option('-l','--list',
default=None,
help="A text file containing a column of trace file names.",
type='string',
dest='l')
parser.add_option('-t','--tables',
help="Only parse into this comma-seperated list of tables.",
default=None,
action='callback',
callback=split_comma,
dest='t')
parser.add_option('--conn',
default=None,
help="SQLAlchemy-style output database connection string.",
type='string',
dest='conn')
parser.add_option('-u', '--user',
default='',
help="Database user name. Not needed for sqlite and remotely \
authenticated connections.",
type='string',
dest='user')
parser.add_option('-b', '--backend',
default='sqlite',
help="SQLAlchemy-style backend and driver string.",
type='string',
dest='backend')
parser.add_option('-p', '--psswd',
default='',
help="Database password. Not needed for sqlite and remotely \
authenticated connections. Prompted for if needed and \
not given.",
type='string',
dest='psswd')
parser.add_option('-s', '--server',
default='',
help="Local or remote database server. Not needed for sqlite.",
type='string',
dest='server')
parser.add_option('--port',
default='',
help="Port on database server. Optional.",
type='string',
dest='port')
parser.add_option('-i', '--instance',
default='',
help="Database instance name. Optional for some backends.\
For sqlite, this is the file name.",
type='string',
dest='instance')
parser.add_option('--affiliation',
default=None,
help="Name of desired output affiliation table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='affiliation')
parser.add_option('--arrival',
default=None,
help="Name of desired output arrival table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='arrival')
parser.add_option('--assoc',
default=None,
help="Name of desired output assoc table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='assoc')
parser.add_option('--event',
default=None,
help="Name of desired output event table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='event')
parser.add_option('--instrument',
default=None,
help="Name of desired output instrument table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='instrument')
parser.add_option('--origin',
default=None,
help="Name of desired output origin table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='origin')
parser.add_option('--site',
default=None,
help="Name of desired output site table. Optional. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='site')
parser.add_option('--sitechan',
default=None,
help="Name of desired output sitechan table. Optional.",
type='string',
metavar='owner.tablename',
dest='sitechan')
parser.add_option('--wfdisc',
default=None,
help="Name of desired output wfdisc table. Optional. \
No owner for sqlite. Currently always adds wfdisc rows.",
type='string',
metavar='owner.tablename',
dest='wfdisc')
parser.add_option('--lastid',
default=None,
help="Name of desired output lastid table. Required. \
No owner for sqlite.",
type='string',
metavar='owner.tablename',
dest='lastid')
parser.add_option('--all_tables',
default=None,
help="Convenience flag. Attempt to fill all tables. \
e.g. jon.test_ will attempt to produce tables like \
jon.test_origin, jon.test_sitechan, ...\
Not yet implemented.",
type='string',
metavar='owner.prefix',
dest='all_tables')
parser.add_option('--gndd',
default=False,
help="""Convenience flag fills in server, port, instance, backend for gnem database users.""",
action='store_true',
dest='gndd')
parser.add_option('--rel_path',
default=False,
help="Write directory entries as relative paths, not absolute.",
action='store_true',
dest='rel_path')
# parser.add_option('-t','--type',
# default=None,
# help="File type: 'SAC' or 'MSEED'",
# type='string',
# dest='t')
# parser.add_option('--db',
# help="Also return formatted flat files.",
# default=False,
# action='store_true',
# dest='db')
options, args = parser.parse_args(argv)
#print options, args
if len(args) > 1:
"""Unknown positional arguments given."""
print parser.print_help()
exit_code = 1
else:
########### MAKE CONNECTIONS ###########
if options.gndd:
#TODO: add a password option here
try:
from pisces_gndd import gndd_connect
session = gndd_connect(options.user)
except ImportError as e:
raise e
else:
if options.conn:
session = ps.db_connect(conn=options.conn)
else:
session = ps.db_connect(user=options.user,
psswd=options.psswd,
backend=options.backend,
server=options.server,
port=options.port,
instance=options.instance)
tables = reflect_or_create_tables(options)
try:
files = get_file_iterator(options)
except IOError as e:
# file list doesn't exist
raise e
exit_code = 1
except ValueError:
# input neither a file list nor input files
print parser.print_help()
exit_code = 1
if exit_code != 1:
######### GET ALL IDS #########
# "last" is a dict of initialized id generators.
# use it like last.arid.next()
# update lastid table after file loop!
#XXX: experimental!
lastid = tables['lastid']
last = AttribDict(db.util.get_id_gen(['arid','orid','evid',
'wfid', 'chanid','inid'],
session, lastid))
# last = AttribDict()
# #pdb.set_trace()
# for iid in ['arid', 'orid', 'evid', 'wfid', 'chanid', 'inid']:
# try:
# val = session.query(lastid.keyvalue).filter(lastid.keyname == iid).one()[0]
# except NoResultFound:
# # no id. make one, starting at zero
# val = 0
# last[iid] = db.gen_id(val)
# requested tables, minus lastid
core_tabs = tables.keys()[:]
core_tabs.remove('lastid')
############# ITERATE OVER FILES #############
for ifile in files:
print ifile
try:
tr = read(ifile, headonly=True)[0]
trtables = db.trace2tables(tr, tables=core_tabs)
# trtables is {'site': mapped instance, ...} for all
# tables requested or available from header
# FILL IN AUTOMATICALLY GENERATED TABLES
#TODO: this section needs better logic, talk to Richard
# combine session.add(_all), session.commit,
# exception catching with id management.
affil = trtables.get('affiliation')
arrivals = trtables.get('arrival')
site = trtables.get('site')
sitechan = trtables.get('sitechan')
instrument = trtables.get('instrument')
assocs = trtables.get('assoc')
event = trtables.get('event')
origin = trtables.get('origin')
wfdisc = trtables.get('wfdisc')
# FILL IN IDs AND OTHER IMPORTANT VALUES
#if affil:
# # affiliation.ontime
# pass
if arrivals:
# arrival.arid
for arrival in arrivals:
if not arrival.arid:
arrival.arid = last.arid.next()
if origin:
# origin.orid
if not origin.orid:
#XXX: always increments orid.
# combine with table add logic
origin.orid = last.orid.next()
if assocs and arrivals:
# assoc.arid
# assoc.orid
#XXX: assumes arrivals are related to origin
# and assocs and arrivals are in the same order
for (assoc, arrival) in zip(assocs, arrivals):
assoc.arid = arrival.arid
if hasattr(origin, 'orid'):
assoc.orid = origin.orid
#if site:
# always gonna be a site, right?
# # site.ondate
# pass
if sitechan:
# sitechan.ondate
# sitechan.chanid
if not sitechan.chanid:
sitechan.chanid = last.chanid.next()
#if instrument:
# # instrument.inid
# # instrument.dir
# # instrument.dfile
# # instrument.rsptype
# pass
if wfdisc:
# XXX: Always gonna be a wfdisc, right?
# XXX: Always writes a _new_ row b/c always new wfid
# wfdisc.dir
# wfdisc.dfile
# wfdisc.wfid
if options.rel_path:
wfdisc.dir = os.path.dirname(ifile)
else:
wfdisc.dir = os.path.abspath(os.path.dirname(ifile))
wfdisc.dfile = os.path.basename(ifile)
wfdisc.wfid = last.wfid.next()
######### ADD TABLES TO DATABASE #########
#pdb.set_trace()
for tableval in trtables.values():
if tableval:
try:
session.add(tableval)
session.commit()
except UnmappedInstanceError:
#tableval was a list. i.e. arrival or assoc
#may need some IntegrityError catching here
session.rollback()
#XXX: is add_all what I mean to do here?
session.add_all(tableval)
except IntegrityError, e:
#duplicate or nonexistant primary/unique keys
#TODO: make this more descriptive
print " Duplicate row in {0}".format( \
tableval.__table__.name)
session.rollback()
except OperationalError, e:
# no such table name. shouldn't happen.
# database is locked.
session.rollback()
raise e
#except InvalidRequestError:
# #XXX: what is this error?
# #maybe tried to insert None
# session.rollback()
except (IOError, TypeError):
#can't read file or doesn't exist.
print "Couldn't read file {0}.".format(ifile)
print("default table FD_RESULTS")
outtext=None
fid=None
class Assoc_params(ab.assoc_params):
__tablename__ = 'assoc_params'
AssocT=type(assocresultsT,(ab.assoc_results,),{'__tablename__':assocresultsT})
'''
class Assoc_results(ab.assoc_results):
__tablename__ = assocresultsT
'''
try:
session=ps.db_connect(sq)
if sq.count('oracle')>0:
session_type='oracle'
else:
import pisces.schema.kbcore as kba
'''
class Site(kba.Site):
__tablename__ = 'site'
class Wfdisc(kba.Wfdisc):
__tablename__ = 'wfdisc'
'''
elif sq.count('sqlite')>0:
print('SQLITE database')
session_type='sqlite'
Usage: db2db.py mydb
Converts mydb.wfdisc, mydb.origin, etc… CSS3 text files into wfdisc, origin
tables in mydb.sqlite database.
"""
import sys
import glob
import pisces as ps
import pisces.tables.css3 as css3
db = sys.argv[1]
session = ps.db_connect('sqlite:///' + db + '.sqlite')
def fill_table(session, table, Table):
Table.__table__.create(session.bind, checkfirst=True)
with open('table', 'r') as f:
for line in f:
row = Table.from_string(line)
session.add(row)
session.commit()
for table in glob.glob(db + '.*'):
if table.endswith('origin'):
fill_table(session, table, css3.Origin)
get_ipython().run_line_magic(
'run', '../../scripts/update_refsta.py sqlite:///test.sqlite LCM')
get_ipython().run_line_magic(
'run', '../../scripts/update_refsta.py sqlite:///test.sqlite PSU')
get_ipython().run_line_magic(
'run', '../../scripts/update_refsta.py sqlite:///test.sqlite WMU')
# ## Querying the Database
# We can now connect to our database and practice querying
# In[6]:
import pisces as ps
session = ps.db_connect(backend='sqlite', instance='test.sqlite')
# In[7]:
session
# The tables used in this tutorial are all standard CSS3.0 tables, we define these tables in order to query the database we have created.
# In[8]:
from pisces.tables.css3 import Site, Wfdisc, Origin
# In[9]:
q = session.query(Site).all()
Converts mydb.wfdisc, mydb.origin, etc… CSS3 text files into wfdisc, origin
tables in mydb.sqlite database.
"""
import sys
import glob
import pisces as ps
import pisces.tables.css3 as css3
db = sys.argv[1]
session = ps.db_connect('sqlite:///' + db + '.sqlite')
def fill_table(session, table, Table):
Table.__table__.create(session.bind, checkfirst=True)
with open('table', 'r') as f:
for line in f:
row = Table.from_string(line)
session.add(row)
session.commit()
for table in glob.glob(db + '.*'):
if table.endswith('origin'):
fill_table(session, table, css3.Origin)
else:
wf = 0
else:
wf = 0
class Fk_params(schema.fk_params):
__tablename__ = 'FK_PARAMS'
class Fk_results(schema.fk_results):
__tablename__ = fkresultsT
#import pdb; pdb.set_trace()
try:
if sq.count('oracle') > 0:
session = ps.db_connect(sq)
session_type = 'oracle'
from global_ import Site, Origin, Wfdisc_raw
elif sq.count('sqlite') > 0:
print('SQLITE database')
session = ps.db_connect(sq)
session_type = 'sqlite'
from pisces.tables.kbcore import Site, Origin, Wfdisc
'''
class Site(kba.Site):
__tablename__ = 'site'
class Wfdisc(kba.Wfdisc):
__tablename__ = 'wfdisc'
'''