def load_SQL_database(self):
table_columns = [
"ResultID", "FeatureCode", "Variable", "Unit", "Type",
"Organization", "Date Created"
]
self.table.set_columns(table_columns)
db = self.get_selected_database()
if not len(db):
return
if db["args"]["engine"] == "sqlite":
session_factory = dbconnection2.createConnection(
engine=db["args"]["engine"], address=db["args"]["address"])
session = db2.connect(session_factory)
series = session.getAllSeries()
elif db["args"]["engine"] == "postgresql": # db is postresql
session_factory = dbUtilities.build_session_from_connection_string(
db["connection_string"])
session = db2.connect(session_factory)
series = session.getAllSeries()
else:
# Fails if db is not sqlite or postresql
raise Exception("Failed to load database")
if not series:
self.table.empty_list_message.Show()
return
self.table.empty_list_message.Hide()
data = self.series_to_table_data(series)
self.table.set_table_content(data)
def build_db(self):
"""
Builds a populated sqlite (in-memory) database for testing
:return: None
"""
# path to the ddl script for building the database
ddlpath= abspath(join(dirname(__file__), 'data/populated.sql'))
# create and empty sqlite database for testing
db = dbconnection.createConnection('sqlite', ':memory:')
# read the ddl script and remove the first (BEGIN TRANSACTION) and last (COMMIT) lines
ddl = open(ddlpath, 'r').read()
ddl = ddl.replace('BEGIN TRANSACTION;','')
ddl = ddl.replace('COMMIT;','')
# execute each statement to build the odm2 database
for line in ddl.split(');')[:-1]:
try:
db.engine.execute(line + ');')
except Exception as e:
print e
self.reader = ReadODM2(db)
self.engine= db.engine
globals['reader'] = self.reader
globals['engine'] = self.engine
globals['db'] = db
def connect_to_db(title, desc, engine, address, db=None, user=None, pwd=None):
d = {}
session = dbconnection2.createConnection(engine, address, db, user, pwd)
if not session:
elog.error('Could not establish a connection with the database')
sPrint('Could not establish a connection with the database', MessageType.ERROR)
return
# adjusting timeout
session.engine.pool._timeout = 30
connection_string = session.engine.url
# save this session in the db_connections object
db_id = uuid.uuid4().hex[:5]
d[db_id] = {'name':title,
'session': session,
'connection_string':connection_string,
'description':desc,
'args': dict(address=connection_string, desc=desc, engine=engine,id=db_id,name=db,
user=None, pwd=None,default=False,db=None)}
elog.info('Connected to : %s [%s]'%(connection_string.__repr__(),db_id))
sPrint('Connected to : %s [%s]'%(connection_string.__repr__(),db_id))
return d
def build_db(self):
"""
Builds a populated sqlite (in-memory) database for testing
:return: None
"""
# path to the ddl script for building the database
ddlpath = abspath(join(dirname(__file__), 'data/populated.sql'))
# create and empty sqlite database for testing
db = dbconnection.createConnection('sqlite', ':memory:')
# read the ddl script and remove the first (BEGIN TRANSACTION) and last (COMMIT) lines
ddl = open(ddlpath, 'r').read()
ddl = ddl.replace('BEGIN TRANSACTION;', '')
ddl = ddl.replace('COMMIT;', '')
# execute each statement to build the odm2 database
for line in ddl.split(');')[:-1]:
try:
db.engine.execute(line + ');')
except Exception as e:
print(e)
self.reader = ReadODM2(db)
self.engine = db.engine
globals_vars['reader'] = self.reader
globals_vars['engine'] = self.engine
globals_vars['db'] = db
def connect_to_ODM2_db(title, desc, engine, address, db, user, pwd):
# create a db session
session = dbconnection2.createConnection(engine, address, db, user, pwd)
db_connections = {}
if session:
# get the connection string
connection_string = session.engine.url
# save this session in the db_connections object
db_id = uuid.uuid4().hex[:5]
d['id'] = db_id
db_connections[db_id] = {'name':title,
'session': session,
'connection_string':connection_string,
'description':desc,
'args': {'name':title,'desc':desc ,'engine':engine,'address':address,'db': db, 'user': user,'pwd': pwd}}
elog.info('Connected to : %s [%s]'%(connection_string.__repr__(),db_id))
sPrint('Connected to : %s [%s]'%(connection_string.__repr__(),db_id))
else:
elog.error('Could not establish a connection with the database')
sPrint('Could not establish a connection with the database', MessageType.ERROR)
return None
return db_connections
def load_SQL_database(self):
table_columns = ["ResultID", "FeatureCode", "Variable", "Unit", "Type", "Organization", "Date Created"]
self.table.set_columns(table_columns)
db = self.get_selected_database()
if not len(db):
return
if db["args"]["engine"] == "sqlite":
session_factory = dbconnection2.createConnection(engine=db["args"]["engine"], address=db["args"]["address"])
session = db2.connect(session_factory)
series = session.getAllSeries()
elif db["args"]["engine"] == "postgresql": # db is postresql
session_factory = dbUtilities.build_session_from_connection_string(db["connection_string"])
session = db2.connect(session_factory)
series = session.getAllSeries()
else:
# Fails if db is not sqlite or postresql
raise Exception("Failed to load database")
if not series:
self.table.empty_list_message.Show()
return
self.table.empty_list_message.Hide()
data = self.series_to_table_data(series)
self.table.set_table_content(data)
def create_database_connections_from_file(ini):
# database connections dictionary
db_connections = {}
# parse the dataabase connections file
cparser = ConfigParser.ConfigParser(None, multidict)
cparser.read(ini)
sections = cparser.sections()
# create a session for each database connection in the ini file
for s in sections:
# put ini args into a dictionary
d = {}
options = cparser.options(s)
d['name'] = s
for option in options:
d[option] = cparser.get(s, option)
# build database connection
session = dbconnection2.createConnection(d['engine'], d['address'],
d['database'], d['username'],
d['password'])
if session:
# adjusting timeout
session.engine.pool._timeout = 30
connection_string = session.engine.url
# add connection string to dictionary (for backup/debugging)
d['connection_string'] = connection_string
# save this session in the db_connections object
db_id = uuid.uuid4().hex[:5]
d['id'] = db_id
db_connections[db_id] = {
'name': d['name'],
'session': session,
'connection_string': connection_string,
'description': d['description'],
'args': d
}
elog.info('Connected to : %s [%s]' %
(connection_string.__repr__(), db_id))
sPrint('Connected to : %s [%s]' %
(connection_string.__repr__(), db_id))
else:
msg = 'Could not establish a connection with the following database: ***@%s/%s' % (
d['address'], d['database'])
elog.error(msg)
sPrint(msg, MessageType.ERROR)
return db_connections
def loadDatabase(self):
if self.odm2db is None:
display(HTML('<b style="color:red">Failed to load the database. '
'Please make sure that the content for this resource '
'contains a *.sqlite file.'))
# load odm2 database
self._session = dbconnection.createConnection('sqlite', self.odm2db)
self.read = ReadODM2(self._session)
self.write = CreateODM2(self._session)
def __init__(self, request):
db = request.param
print ('dbtype', db[0], db[1])
session_factory = dbconnection.createConnection(db[1], db[2], db[3], db[4], db[5], echo=True)
assert session_factory is not None, ('failed to create a session for ', db[0], db[1])
assert session_factory.engine is not None, ('failed: session has no engine ', db[0], db[1])
insp = reflection.Inspector.from_engine(session_factory.engine)
insp.get_table_names()
self.session = session_factory.getSession()
def create_database_connections_from_file(ini):
# database connections dictionary
db_connections = {}
# parse the dataabase connections file
params = {}
cparser = ConfigParser.ConfigParser(None, multidict)
cparser.read(ini)
sections = cparser.sections()
# create a session for each database connection in the ini file
for s in sections:
# put ini args into a dictionary
options = cparser.options(s)
d = {}
for option in options:
d[option] = cparser.get(s,option)
# build database connection
#dbconn = odm2.api.dbconnection()
session = dbconnection.createConnection(d['engine'],d['address'],d['db'],d['user'],d['pwd'])
if session:
# adjusting timeout
session.engine.pool._timeout = 30
connection_string = session.engine.url
# add connection string to dictionary (for backup/debugging)
d['connection_string'] = connection_string
# save this session in the db_connections object
db_id = uuid.uuid4().hex[:5]
d['id'] = db_id
db_connections[db_id] = {'name':d['name'],
'session': session,
'connection_string':connection_string,
'description':d['desc'],
'args': d}
print 'Connected to : %s [%s]'%(connection_string.__repr__(),db_id)
else:
print 'ERROR | Could not establish a connection with the database'
#return None
return db_connections
def loadDatabase(self):
if self.odm2db is None:
display(
HTML('<b style="color:red">Failed to load the database. '
'Please make sure that the content for this resource '
'contains a *.sqlite file.'))
# load odm2 database
self._session = dbconnection.createConnection('sqlite', self.odm2db)
self.read = ReadODM2(self._session)
self.write = CreateODM2(self._session)
def __init__(self, simulationName, database_args, user, datasets):
self.simulationName = simulationName
self.database_args = database_args
self.user = user
self.datasets = datasets
self.session = dbconnection.createConnection(engine=database_args['engine'],
address=database_args['address'],
db=database_args['db'],
user=database_args['user'],
password=database_args['pwd'])
def create_database_connections_from_file(ini):
# database connections dictionary
db_connections = {}
# parse the dataabase connections file
params = {}
cparser = ConfigParser.ConfigParser(None, multidict)
cparser.read(ini)
sections = cparser.sections()
# create a session for each database connection in the ini file
for s in sections:
# put ini args into a dictionary
options = cparser.options(s)
d = {}
for option in options:
d[option] = cparser.get(s, option)
# build database connection
#dbconn = odm2.api.dbconnection()
session = dbconnection.createConnection(d['engine'], d['address'],
d['db'], d['user'], d['pwd'])
if session:
# adjusting timeout
session.engine.pool._timeout = 30
connection_string = session.engine.url
# add connection string to dictionary (for backup/debugging)
d['connection_string'] = connection_string
# save this session in the db_connections object
db_id = uuid.uuid4().hex[:5]
d['id'] = db_id
db_connections[db_id] = {
'name': d['name'],
'session': session,
'connection_string': connection_string,
'description': d['desc'],
'args': d
}
print 'Connected to : %s [%s]' % (connection_string.__repr__(),
db_id)
else:
print 'ERROR | Could not establish a connection with the database'
#return None
return db_connections
def temp_build_database(dbpath):
# remove temp database
if os.path.exists(dbpath):
os.remove(dbpath)
# connect to each database
db = dbconnection.createConnection('sqlite', dbpath)
db_instance = sqlite(db)
return db_instance
def __init__(self, simulationName, database_args, user, datasets):
self.simulationName = simulationName
self.database_args = database_args
self.user = user
self.datasets = datasets
self.session = dbconnection.createConnection(
engine=database_args["engine"],
address=database_args["address"],
db=database_args["db"],
user=database_args["user"],
password=database_args["pwd"],
)
def temp_build_database(dbpath):
# remove temp database
if os.path.exists(dbpath):
os.remove(dbpath)
# connect to each database
db = dbconnection.createConnection('sqlite', dbpath)
db_instance = sqlite(db)
return db_instance
def create_database_connections_from_file(ini):
# database connections dictionary
db_connections = {}
# parse the dataabase connections file
cparser = ConfigParser.ConfigParser(None, multidict)
cparser.read(ini)
sections = cparser.sections()
# create a session for each database connection in the ini file
for s in sections:
# put ini args into a dictionary
d = {}
options = cparser.options(s)
d['name'] = s
for option in options:
d[option] = cparser.get(s,option)
# build database connection
session = dbconnection2.createConnection(d['engine'],d['address'],d['database'],d['username'],d['password'])
if session:
# adjusting timeout
session.engine.pool._timeout = 30
connection_string = session.engine.url
# add connection string to dictionary (for backup/debugging)
d['connection_string'] = connection_string
# save this session in the db_connections object
db_id = uuid.uuid4().hex[:5]
d['id'] = db_id
db_connections[db_id] = {'name':d['name'],
'session': session,
'connection_string':connection_string,
'description':d['description'],
'args': d}
elog.info('Connected to : %s [%s]'%(connection_string.__repr__(),db_id))
sPrint('Connected to : %s [%s]'%(connection_string.__repr__(),db_id))
else:
msg = 'Could not establish a connection with the following database: ***@%s/%s' % (d['address'],d['database'])
elog.error(msg)
sPrint(msg, MessageType.ERROR)
return db_connections
def setUp(self):
# define the paths for the empty and populated temp databases
dirpath = os.path.dirname(os.path.abspath(__file__))
self.empty_db_path = os.path.join(dirpath,'data/temp_empty.db')
self.pop_db_path = os.path.join(dirpath, 'data/temp_pop.db')
# remove temp databases
if os.path.exists(self.empty_db_path):
os.remove(self.empty_db_path)
if os.path.exists(self.pop_db_path):
os.remove(self.pop_db_path)
# connect to each database
empty_connection = dbconnection.createConnection('sqlite', self.empty_db_path)
pop_connection = dbconnection.createConnection('sqlite', self.pop_db_path)
self.emptysqlite = sqlite(empty_connection)
self.popsqlite = sqlite(pop_connection)
# initialize the in-memory database, loop through each command (skip first and last lines)
empty_dump_script = open( os.path.join(dirpath, 'data/empty_dump.sql'),'r').read()
for line in empty_dump_script.split(';\n'):
self.emptysqlite.cursor.execute(line)
populated_dump_script = open(os.path.join(dirpath, 'data/populated_dump.sql'),'r').read()
for line in populated_dump_script.split(';\n'):
self.popsqlite.cursor.execute(line)
# initialize environment variables
environment.getEnvironmentVars()
if sys.gettrace():
print 'Detected Debug Mode'
# initialize debug listener (reroute messages to console)
self.d = sprint.DebugListener()
sprint.PrintTarget.CONSOLE = 1134
def setup(request):
# build an empty database for testing
# conn = dbconnection.createConnection('sqlite', ':memory:')
db = request.param
print("dbtype", db[0], db[1])
session_factory = dbconnection.createConnection(db[1],
db[2],
db[3],
db[4],
db[5],
echo=False)
assert session_factory is not None, ("failed to create a session for ",
db[0], db[1])
assert session_factory.engine is not None, (
"failed: session has no engine ", db[0], db[1])
# dbconnection._setSchema(conn.engine)
dbConn = odmConnection
# build connectors for read, write, update, and delete operations
dbConn.odmread = ReadODM2(session_factory)
dbConn.odmcreate = CreateODM2(session_factory)
dbConn.odmupdate = UpdateODM2(session_factory)
dbConn.odmdelete = DeleteODM2(session_factory)
s = session_factory.getSession()
# initialize the in-memory database, loop through each command (skip first and last lines)
#build = open('./tests/spatialite/build_empty.sqlite').read()
if (db[2] == ':memory:'):
build = open('./tests/schemas/sqlite/ODM2_for_SQLite.sql').read()
for line in build.split(';\n'):
s.execute(line)
s.flush()
# s.invalidate()
print 'database initialization completed successfully'
def fin():
print("teardown odm2 test connection")
del dbConn.odmread
del dbConn.odmcreate
del dbConn.odmupdate
del dbConn.odmdelete
session_factory.engine.dispose()
session_factory.test_engine.dispose()
s.invalidate()
request.addfinalizer(fin)
return dbConn
def create_database_connections_from_args(title, desc, engine, address, db,
user, pwd):
d = {
'name': title,
'desc': desc,
'engine': engine,
'address': address,
'db': db,
'user': user,
'pwd': pwd
}
# database connections dictionary
db_connections = {}
# build database connection
#dbconn = odm2.api.dbconnection()
session = dbconnection.createConnection(engine, address, db, user, pwd)
# add connection string to dictionary (for backup/debugging)
# d['connection_string'] = connection_string
# create a session
if session:
# get the connection string
connection_string = session.engine.url
# save this session in the db_connections object
db_id = uuid.uuid4().hex[:5]
d['id'] = db_id
db_connections[db_id] = {
'name': d['name'],
'session': session,
'connection_string': connection_string,
'description': d['desc'],
'args': d
}
print 'Connected to : %s [%s]' % (connection_string.__repr__(), db_id)
else:
print 'ERROR | Could not establish a connection with the database'
return None
return db_connections
def get_dbsession(db_cred):
"""
Function to get Database Connection Session
db_cred = {
'address': '127.0.0.1',
'db': 'dbname',
'user': '******',
'password': '******'
}
"""
session_factory = dbconnection.createConnection('postgresql', **db_cred)
DBSession = session_factory.getSession()
DBSession.expire_on_commit = False
return DBSession
def __init__(self, request):
#session_factory = dbconnection.createConnection('mysql', 'localhost', 'odm2', 'ODM', 'odm')
db = request.param
print("dbtype", db[0], db[1])
session_factory = dbconnection.createConnection(db[1],
db[2],
db[3],
db[4],
db[5],
echo=True)
assert session_factory is not None, ("failed to create a session for ",
db[0], db[1])
assert session_factory.engine is not None, (
"failed: session has no engine ", db[0], db[1])
insp = reflection.Inspector.from_engine(session_factory.engine)
tables = insp.get_table_names()
self.session = session_factory.getSession()
def create_database_connections_from_args(title, desc, engine, address, db, user, pwd):
d = {'name':title,
'desc':desc ,
'engine':engine,
'address':address,
'db': db,
'user': user,
'pwd': pwd}
# database connections dictionary
db_connections = {}
# build database connection
#dbconn = odm2.api.dbconnection()
session = dbconnection.createConnection(engine,address,db,user,pwd)
# add connection string to dictionary (for backup/debugging)
# d['connection_string'] = connection_string
# create a session
if session:
# get the connection string
connection_string = session.engine.url
# save this session in the db_connections object
db_id = uuid.uuid4().hex[:5]
d['id'] = db_id
db_connections[db_id] = {'name':d['name'],
'session': session,
'connection_string':connection_string,
'description':d['desc'],
'args': d}
print 'Connected to : %s [%s]'%(connection_string.__repr__(),db_id)
else:
print 'ERROR | Could not establish a connection with the database'
return None
return db_connections
def get_database_session(self):
"""
Connect to the respective database and return the session
Exception is raised if it is not a sqlite or postresql
:return:
"""
db = self.get_selected_database()
if db["args"]['engine'] == "sqlite":
session_factory = dbconnection.createConnection(engine=db["args"]["engine"], address=db["args"]["address"],
db=db["args"]["db"], user=db["args"]["user"],
password=db["args"]["pwd"])
session = db2.connect(session_factory)
return session
elif db["args"]["engine"] == "postgresql":
session_factory = dbUtilities.build_session_from_connection_string(db['connection_string'])
session = db2.connect(session_factory)
return session
else:
raise Exception("Failed to load simulations database")
def connect_to_db(title, desc, engine, address, db=None, user=None, pwd=None):
d = {}
session = dbconnection2.createConnection(engine, address, db, user, pwd)
if not session:
elog.error('Could not establish a connection with the database')
sPrint('Could not establish a connection with the database',
MessageType.ERROR)
return
# adjusting timeout
session.engine.pool._timeout = 30
connection_string = session.engine.url
# save this session in the db_connections object
db_id = uuid.uuid4().hex[:5]
d[db_id] = {
'name':
title,
'session':
session,
'connection_string':
connection_string,
'description':
desc,
'args':
dict(address=connection_string,
desc=desc,
engine=engine,
id=db_id,
name=db,
user=None,
pwd=None,
default=False,
db=None)
}
elog.info('Connected to : %s [%s]' % (connection_string.__repr__(), db_id))
sPrint('Connected to : %s [%s]' % (connection_string.__repr__(), db_id))
return d
def connect_to_ODM2_db(title, desc, engine, address, db, user, pwd):
# create a db session
session = dbconnection2.createConnection(engine, address, db, user, pwd)
db_connections = {}
if session:
# get the connection string
connection_string = session.engine.url
# save this session in the db_connections object
db_id = uuid.uuid4().hex[:5]
d['id'] = db_id
db_connections[db_id] = {
'name': title,
'session': session,
'connection_string': connection_string,
'description': desc,
'args': {
'name': title,
'desc': desc,
'engine': engine,
'address': address,
'db': db,
'user': user,
'pwd': pwd
}
}
elog.info('Connected to : %s [%s]' %
(connection_string.__repr__(), db_id))
sPrint('Connected to : %s [%s]' %
(connection_string.__repr__(), db_id))
else:
elog.error('Could not establish a connection with the database')
sPrint('Could not establish a connection with the database',
MessageType.ERROR)
return None
return db_connections
def get_database_session(self):
"""
Connect to the respective database and return the session
Exception is raised if it is not a sqlite or postresql
:return:
"""
db = self.get_selected_database()
if db["args"]['engine'] == "sqlite":
session_factory = dbconnection.createConnection(
engine=db["args"]["engine"],
address=db["args"]["address"],
db=db["args"]["db"],
user=db["args"]["user"],
password=db["args"]["pwd"])
session = db2.connect(session_factory)
return session
elif db["args"]["engine"] == "postgresql":
session_factory = dbUtilities.build_session_from_connection_string(
db['connection_string'])
session = db2.connect(session_factory)
return session
else:
raise Exception("Failed to load simulations database")
def setup(request):
# build an empty database for testing
# conn = dbconnection.createConnection('sqlite', ':memory:')
db = request.param
print('dbtype', db[0], db[1])
session_factory = dbconnection.createConnection(db[1], db[2], db[3], db[4], db[5], echo=False)
assert session_factory is not None, ('failed to create a session for ', db[0], db[1])
assert session_factory.engine is not None, ('failed: session has no engine ', db[0], db[1])
# dbconnection._setSchema(conn.engine)
dbConn = odmConnection
# build connectors for read, write, update, and delete operations
dbConn.odmread = ReadODM2(session_factory)
dbConn.odmcreate = CreateODM2(session_factory)
dbConn.odmupdate = UpdateODM2(session_factory)
dbConn.odmdelete = DeleteODM2(session_factory)
s = session_factory.getSession()
if (db[2] == ':memory:'):
build = open('./tests/schemas/sqlite/ODM2_for_SQLite.sql').read()
for line in build.split(';\n'):
s.execute(line)
s.flush()
print('database initialization completed successfully')
def fin():
print('teardown odm2 test connection')
del dbConn.odmread
del dbConn.odmcreate
del dbConn.odmupdate
del dbConn.odmdelete
session_factory.engine.dispose()
session_factory.test_engine.dispose()
s.invalidate()
request.addfinalizer(fin)
return dbConn
#import matplotlib.pyplot as plt
from odm2api.ODMconnection import dbconnection
from odm2api.ODM2.services.readService import *
from odm2api.ODM2.services import CreateODM2
# Create a connection to the ODM2 database
# ----------------------------------------
#connect to database
# createconnection (dbtype, servername, dbname, username, password)
# session_factory = dbconnection.createConnection('connection type: sqlite|mysql|mssql|postgresql', '/your/path/to/db/goes/here', 2.0)#sqlite
session_factory = dbconnection.createConnection('postgresql', 'localhost', 'odm2', 'ODM', 'odm')
# session_factory = dbconnection.createConnection('mysql', 'localhost', 'odm2', 'ODM', 'odm')#mysql
# session_factory= dbconnection.createConnection('mssql', "(local)", "ODM2", "ODM", "odm")#win MSSQL
# session_factory= dbconnection.createConnection('mssql', "arroyoodm2", "", "ODM", "odm")#mac/linux MSSQL
# session_factory = dbconnection.createConnection('sqlite', '/Users/stephanie/DEV/YODA-Tools/tests/test_files/XL_specimen.sqlite', 2.0)
def build_ts_db():
# create connection to temp sqlite db
db_path = os.path.join(curr_folder, 'test_files', 'ODM2_ts.sqlite')
session_factory = dbconnection.createConnection('sqlite', db_path, 2.0)
# _engine = session_factory.engine
return session_factory
"""
from odm2api.ODMconnection import dbconnection
from odm2api.ODM2.services.readService import *
from odm2api.ODM2.services import CreateODM2
# Create a connection to the ODM2 database
# ----------------------------------------
#connect to database
# createconnection (dbtype, servername, dbname, username, password)
# session_factory = dbconnection.createConnection('connection type: sqlite|mysql|mssql|postgresql', '/your/path/to/db/goes/here', 2.0)#sqlite
#session_factory = dbconnection.createConnection('postgresql', 'localhost', 'postgres', 'postgres', '89635241')
# session_factory = dbconnection.createConnection('mysql', 'localhost', 'odm2', 'ODM', 'odm')#mysql
#session_factory= dbconnection.createConnection('mssql', "(local)", "ODM2", "ODM", "odm")#win MSSQL
# session_factory= dbconnection.createConnection('mssql', "arroyoodm2", "", "ODM", "odm")#mac/linux MSSQL
session_factory = dbconnection.createConnection('sqlite', 'ODM2.sqlite', 2.0)
_session = session_factory.getSession()
read = ReadODM2(session_factory)
c
# Run some basic sample queries.
# ------------------------------
# Get all of the variables from the database and print their names to the console
allVars = read.getVariables()
print("\n-------- Information about Variables ---------")
print(allVars)
for x in allVars:
print(x.VariableCode + ": " + x.VariableNameCV)
# this_file = os.path.realpath(__file__)
# directory = os.path.dirname(os.path.dirname(this_file))
# print directory
# sys.path.insert(0, directory)
from odm2api.ODMconnection import dbconnection
from odm2api.ODM2.services.readService import *
# Create a connection to the ODM2 database
# ----------------------------------------
# connect to database
# createconnection (dbtype, servername, dbname, username, password)
# session_factory = dbconnection.createConnection('mysql', 'localhost', 'odm2', 'ODM', 'odm')
session_factory = dbconnection.createConnection("sqlite", "/Users/stephanie/DEV/DBs/ODM2.sqlite", 2.0)
# session_factory= dbconnection.createConnection('mssql')
# _session = session_factory.getSession()
read = ReadODM2(session_factory)
# Run some basic sample queries.
# ------------------------------
# Get all of the variables from the database and print their names to the console
allVars = read.getVariables()
for x in allVars:
print x.VariableCode + ": " + x.VariableNameCV
import sys
import os
from odm2api.ODMconnection import dbconnection
import pprint
from odm2api.ODM1_1_1.services import SeriesService
this_file = os.path.realpath(__file__)
directory = os.path.dirname(this_file)
sys.path.insert(0, directory)
# ----------------------------------------
conns = [
#connection to the ODM1 database
dbconnection.createConnection('mysql', 'jws.uwrl.usu.edu', 'odm', "ODM", "ODM123!!", 1.1),
#connection to the ODM2 database
dbconnection.createConnection('mysql', 'jws.uwrl.usu.edu', 'odm2', 'ODM', 'ODM123!!', 2.0)]
for conn in conns:
pp = pprint.PrettyPrinter(indent=8)
print
print "************************************************"
print "\t\tODM2 -> ODM1 Demo: "
print "************************************************"
print
odm1service = SeriesService(conn)
odm1service.refreshDB(conn.version)
from matplotlib.dates import DateFormatter
from sqlalchemy import create_engine
from sqlalchemy import orm
from utilities import hydroshare
from odm2api.ODM2.services.readService import *
from odm2api.ODM2.services.createService import *
from odm2api.ODMconnection import dbconnection
import odm2api.ODM2.services.readService as odm2
from odm2api.ODM2.services.readService import *
from odm2api.ODM2.services.createService import *
from odm2api.ODM2.models import *
# conda install --no-deps -c conda-forge odm2api
# hs = hydroshare.hydroshare('miguelcleon')
# content = hs.getResourceFromHydroShare('e049f19dc8ba46c98754711da2ab6030')
# LCZOSensorDB = hs.content['LCZO-ODM2-MariaTimeSeries.sqlite']
# engine = create_engine('sqlite:///C:\\Users\\12672\\Box\\data\\NEON\\lczodata\\LCZO-ODM2-MariaTimeSeries3.sqlite')
# loc = 'C:\\Users\\12672\\Box\\data\\NEON\\lczodata\\LCZO-ODM2-MariaTimeSeries3.sqlite'
loc = 'C:\\Users\\leonmi\\Desktop\\BoxUNH\\Box Sync\\data\\NEON\\lczodata\\LCZO-ODM2-MariaTimeSeries3.sqlite'
session_factory = dbconnection.createConnection('sqlite', loc)
dbsession = session_factory.getSession()
read = ReadODM2(session_factory)
write = CreateODM2(session_factory)
samplingfeatures = read.getSamplingFeatures()
samplingfeatureids = []
samplingfeaturenames = {}
for sf in samplingfeatures:
print(sf)
import sys
import os
from odm2api.ODMconnection import dbconnection
import pprint
from odm2api.ODM1_1_1.services import SeriesService
this_file = os.path.realpath(__file__)
directory = os.path.dirname(this_file)
sys.path.insert(0, directory)
# ----------------------------------------
conns = [
#connection to the ODM1 database
dbconnection.createConnection('mysql', 'jws.uwrl.usu.edu', 'odm', "ODM", "ODM123!!", 1.1),
#connection to the ODM2 database
dbconnection.createConnection('mssql', '(local)', 'odm2', 'ODM', 'odm', 2.0)]
for conn in conns:
pp = pprint.PrettyPrinter(indent=8)
print
print "************************************************"
print "\t\tODM2 -> ODM1 Demo: "
print "************************************************"
print
odm1service = SeriesService(conn)
# from ODM2PythonAPI.src.api.new_services import createService
from odm2api.ODMconnection import dbconnection
from yodatools.yodaparser.yamlFunctions import YamlFunctions
except ImportError as e:
print(e)
sys.exit(0)
# Create a connection to the ODM2 database
session_factory = dbconnection.createConnection('sqlite', './ODM2.sqlite', 2.0)
# Create a connection for each of the schemas. Currently the schemas each have a different
# connection but it will be changed to all the services sharing a connection
# ----------------------------------------------------------------------------------------
_session = session_factory.getSession()
_engine = session_factory.engine
# Demonstrate loading a yaml file into an ODM2 database
# Demonstrate loading a yaml file into an ODM2 database
print()
print("---------------------------------------------------------------------")
print("--------- ----------")
#import matplotlib.pyplot as plt
from odm2api.ODMconnection import dbconnection
from odm2api.ODM2.services.readService import *
from odm2api.ODM2.services import CreateODM2
# Create a connection to the ODM2 database
# ----------------------------------------
#connect to database
# createconnection (dbtype, servername, dbname, username, password)
# session_factory = dbconnection.createConnection('connection type: sqlite|mysql|mssql|postgresql', '/your/path/to/db/goes/here', 2.0)#sqlite
# session_factory = dbconnection.createConnection('postgresql', 'localhost', 'odm2', 'ODM', 'odm')
# session_factory = dbconnection.createConnection('mysql', 'localhost', 'odm2', 'ODM', 'odm')#mysql
session_factory = dbconnection.createConnection('mssql', "(local)", "ODM2",
"ODM", "odm") #win MSSQL
# session_factory= dbconnection.createConnection('mssql', "arroyoodm2", "", "ODM", "odm")#mac/linux MSSQL
# session_factory = dbconnection.createConnection('sqlite', 'path/to/ODM2.sqlite', 2.0)
#_session = session_factory.getSession()
read = ReadODM2(session_factory)
create = CreateODM2(session_factory)
# Run some basic sample queries.
# ------------------------------
# Get all of the variables from the database and print their names to the console
allVars = read.getVariables()
print("\n-------- Information about Variables ---------")
for x in allVars:
print(x.VariableCode + ": " + x.VariableNameCV)
from odm2api.ODMconnection import dbconnection
import odm2api.ODM2.services.readService as odm2
from odm2api.ODM2.services.readService import *
from odm2api.ODM2.services.createService import *
from odm2api.ODM2.models import *
import dateutil.parser
import csv
from datetime import datetime
# display LCZO and NEON air temp data together
# path for desktop
# C:\\Users\\12672\\Box\\data\\NEON\\lczodata\\LCZO-ODM2-MariaTimeSeries3.sqlite
session_factory = dbconnection.createConnection(
'sqlite',
'C:\\Users\\leonmi\\Desktop\\BoxUNH\\Box Sync\\data\\NEON\\lczodata\\LCZO-ODM2-MariaTimeSeries3.sqlite'
)
dbsession = session_factory.getSession()
read = ReadODM2(session_factory)
write = CreateODM2(session_factory)
samplingfeatures = read.getSamplingFeatures()
samplingfeatureids = []
samplingfeaturenames = {}
for sf in samplingfeatures:
print(sf)
resultidlist = 17268
airtempQ3 = read.getResultValues(resultidlist,
starttime='2017-01-01',
__author__ = 'stephanie'
#import matplotlib.pyplot as plt
from odm2api.ODMconnection import dbconnection
from odm2api.ODM2.services.readService import *
from odm2api.ODM2.services import CreateODM2
# Create a connection to the ODM2 database
# ----------------------------------------
#connect to database
# createconnection (dbtype, servername, dbname, username, password)
# session_factory = dbconnection.createConnection('connection type: sqlite|mysql|mssql|postgresql', '/your/path/to/db/goes/here', 2.0)#sqlite
session_factory = dbconnection.createConnection('postgresql', 'localhost',
'odm2', 'ODM', 'odm')
# session_factory = dbconnection.createConnection('mysql', 'localhost', 'odm2', 'ODM', 'odm')#mysql
# session_factory= dbconnection.createConnection('mssql', "(local)", "ODM2", "ODM", "odm")#win MSSQL
# session_factory= dbconnection.createConnection('mssql', "arroyoodm2", "", "ODM", "odm")#mac/linux MSSQL
# session_factory = dbconnection.createConnection('sqlite', '/Users/stephanie/DEV/YODA-Tools/tests/test_files/XL_specimen.sqlite', 2.0)
#_session = session_factory.getSession()
read = ReadODM2(session_factory)
create = CreateODM2(session_factory)
# Run some basic sample queries.
# ------------------------------
# Get all of the variables from the database and print their names to the console
allVars = read.getVariables()
print("\n-------- Information about Variables ---------")
def setup(self):
session_factory = dbconnection.createConnection('mysql', 'localhost', 'odm2', 'ODM', 'odm')
self.session = session_factory.getSession()
from odm2api.ODMconnection import dbconnection
import odm2api.services.readService as odm2rs
# A SQLite file-based connection
session_factory = dbconnection.createConnection('sqlite',
'/myfilepath/odm2db.sqlite')
read = odm2rs.ReadODM2(session_factory)
# A connection to a server-based database system
db_credentials = {
'address': 'ip-or-domainname',
'db': 'dbname',
'user': '******',
'password': '******'
}
session_factory = dbconnection.createConnection('postgresql', **db_credentials)
read = odm2rs.ReadODM2(session_factory)
