def kodiak_query():
"Run a query against DB and then loop thru resulting docs adding to Treeno"
conn = bpgsql.connect(dsn)
cur = conn.cursor()
cur.execute(sql)
while True:
row = cur.fetchone()
if row == None:
break
# Extract fields from row
entitynumber = row[0]
customername = row[1]
appnum = row[2]
schedulenum = row[3]
#title = row[4]
#docid = row[5]
if entitynumber is None:
entitynumber = ""
if appnum is None:
appnum = ""
if schedulenum is None:
schedulenum = ""
#print entitynumber, appnum, schedulenum, title, docid
print entitynumber, appnum, schedulenum
def get_new_connection(self, conn_params):
connection = Database.connect(**conn_params)
# self.isolation_level must be set:
# - after connecting to the database in order to obtain the database's
# default when no value is explicitly specified in options.
# - before calling _set_autocommit() because if autocommit is on, that
# will set connection.isolation_level to ISOLATION_LEVEL_AUTOCOMMIT;
# and if autocommit is off, on psycopg2 < 2.4.2, _set_autocommit()
# needs self.isolation_level.
options = self.settings_dict['OPTIONS']
return connection
def get_new_connection(self, conn_params):
connection = Database.connect(**conn_params)
# self.isolation_level must be set:
# - after connecting to the database in order to obtain the database's
# default when no value is explicitly specified in options.
# - before calling _set_autocommit() because if autocommit is on, that
# will set connection.isolation_level to ISOLATION_LEVEL_AUTOCOMMIT;
# and if autocommit is off, on psycopg2 < 2.4.2, _set_autocommit()
# needs self.isolation_level.
options = self.settings_dict['OPTIONS']
return connection
def __init__(self, dspace_conf):
self.con = None
db = dspace_conf["lr.database"]
u = dspace_conf["lr.db.username"]
p = dspace_conf["lr.db.password"]
if db is None:
_logger.info( "DSpace config could not be parsed correctly" )
return
_logger.info( "Trying to connect to [%s] under [%s]", db, u )
import bpgsql
self.con = bpgsql.connect(
username=u, password=p, host="127.0.0.1", dbname=db )
self.cursor = None
def get_data(self):
import bpgsql
import datetime
conn = bpgsql.connect(username="******",host=PGSQL_SERVER,password="******", dbname="airtime")
cur = conn.cursor()
cur.execute("select * from cc_show_instances")
ret = cur.fetchall()
start_time = ""
end_time = ""
cnt = len(ret)
i = 0
while (i<cnt):
show_id = ret[i][0]
start_time = ret[i][1]
#self.outdata.append(start_time)
cur.execute("select * from cc_schedule where instance_id='"+str(show_id)+"'")
ret2 = cur.fetchall()
cnt_j = len(ret2)
j = 0
#if (cnt_j>0):
# playlist_id = ret2[0][1]
# cur.execute ("select * from cc_playlist where id='"+str(playlist_id)+"'")
# ret21 = cur.fetchall()
while(j<cnt_j):
cur.execute("select * from cc_files where id='"+str(ret2[j][5])+"'")
ret3 = cur.fetchall()
if (j+1==cnt_j):
end_time = str(ret[i][2])
element_len = ret3[0][16]
element_desc = ret3[0][19]
element_name = str(ret3[0][2])
element_len_timedelta = datetime.timedelta(0,element_len.second,element_len.microsecond,0, element_len.minute, element_len.hour,0)
element_end_time = start_time + element_len_timedelta
self.outdata.append([str(start_time),element_name,element_desc,str(element_end_time)])
start_time = element_end_time
j+=1
i+=1
#self.outdata.append(end_time)
return self.outdata
print "***"
print "*** Processing data .... "
print "***"
print
print "Opening file: " + s_path
print "***"
t_name = s_path.replace('.csv', '_log.txt')
total_txt = open(t_name, 'w')
localtime = time.asctime(time.localtime(time.time()))
total_txt.write('logfile for ' + s_path + ' at ' + localtime + '\n\n')
##Edit accordingly
pgconn = pgs.connect(username='******',
password='******',
host='localhost',
port='5432',
dbname='oceansmap_obs')
curs = pgconn.cursor()
print "connected to Database, Yay. Now insert records..."
totalRecords = 0
a = 0
startatLine = 0
infile = open(s_path)
inlines = infile.readlines()
infile.close()
for line in inlines:
startatLine += 1 # keep track of lines
def db_assetstore(env):
"""
Get assetstore id and filename from db scanning local.conf or dspace.cfg
:(.
"""
# try local.config
for dspace_cfg in env["config_dist_relative"]:
dspace_cfg = os.path.join( os.getcwd( ), dspace_cfg )
if os.path.exists( dspace_cfg ):
env["config_dist_relative"][0] = dspace_cfg
break
dspace_cfg = os.path.join( os.getcwd( ), env["config_dist_relative"][0] )
prefix = "lr."
if not os.path.exists( dspace_cfg ):
_logger.info( "Could not find [%s]", dspace_cfg )
dspace_cfg, prefix = os.path.join( os.getcwd( ), env["dspace_cfg_relative"] ), ""
# try dspace.cfg
if not os.path.exists( dspace_cfg ):
_logger.info( "Could not find [%s]", dspace_cfg )
dspace_cfg = None
# not found
if dspace_cfg is None:
return None
# get the variables
#
_logger.info( "Parsing for [%s]", dspace_cfg )
db_username = None
db_pass = None
db_table = None
db_port = ""
if os.path.exists( dspace_cfg ):
lls = open( dspace_cfg, "r" ).readlines( )
for l in lls:
l = l.strip( )
if l.startswith( prefix + "db.username" ):
db_username = l.strip( ).split( "=" )[1].strip( )
if l.startswith( prefix + "db.password" ):
db_pass = l.strip( ).split( "=" )[1].strip( )
if db_table is None and l.startswith( prefix + "db.url" ):
db_table = l.strip( ).split( "/" )[-1].strip( )
db_port = l.strip( ).split( ":" )[-1].split( "/" )[0]
if l.startswith( prefix + "database " ) or l.startswith( prefix + "database=" ):
db_table = l.strip( ).split( "=" )[1].split( "/" )[-1].strip( )
_logger.info( "Trying to connect to [%s] under [%s] at port [%s]",
db_table, db_username, db_port )
# get the db table
import bpgsql
try:
con = bpgsql.connect(
username=db_username, password=db_pass, host="127.0.0.1", dbname=db_table, port=db_port )
cursor = con.cursor( )
cursor.execute( """
select text_value, internal_id from bitstream as b JOIN metadatavalue as md ON md.resource_id = b.bitstream_id NATURAL JOIN metadatafieldregistry NATURAL JOIN metadataschemaregistry where md.resource_type_id = 0 and short_id='dc' and element='title' and qualifier is null;
""" )
objs = cursor.fetchall( )
# better explicitly
cursor.close( )
con.close( )
return dict( [(y, x) for x, y in objs] )
except Exception, e:
_logger.exception( "No connection could be made" )
def setUp(self):
self.cnx = bpgsql.connect(self.TEST_DSN)
self.cur = self.cnx.cursor()
def connect(self):
if self._dsn is not None:
self._conn = dbapi.connect(self._dsn)
def db_assetstore(env):
"""
Get assetstore id and filename from db scanning local.conf or dspace.cfg
:(.
"""
# try local.config
for dspace_cfg in env["config_dist_relative"]:
dspace_cfg = os.path.join( os.getcwd( ), dspace_cfg )
if os.path.exists( dspace_cfg ):
env["config_dist_relative"][0] = dspace_cfg
break
dspace_cfg = os.path.join( os.getcwd( ), env["config_dist_relative"][0] )
prefix = "lr."
if not os.path.exists( dspace_cfg ):
_logger.info( "Could not find [%s]", dspace_cfg )
dspace_cfg, prefix = os.path.join( os.getcwd( ), env["dspace_cfg_relative"] ), ""
# try dspace.cfg
if not os.path.exists( dspace_cfg ):
_logger.info( "Could not find [%s]", dspace_cfg )
dspace_cfg = None
# not found
if dspace_cfg is None:
return None
# get the variables
#
_logger.info( "Parsing for [%s]", dspace_cfg )
db_username = None
db_pass = None
db_table = None
if os.path.exists( dspace_cfg ):
lls = open( dspace_cfg, "r" ).readlines( )
for l in lls:
l = l.strip( )
if l.startswith( prefix + "db.username" ):
db_username = l.strip( ).split( "=" )[1].strip( )
if l.startswith( prefix + "db.password" ):
db_pass = l.strip( ).split( "=" )[1].strip( )
if db_table is None and l.startswith( prefix + "db.url" ):
db_table = l.strip( ).split( "/" )[-1].strip( )
if l.startswith( prefix + "database " ) or l.startswith( prefix + "database=" ):
db_table = l.strip( ).split( "=" )[1].split( "/" )[-1].strip( )
_logger.info( "Trying to connect to [%s] under [%s]",
db_table, db_username )
# get the db table
import bpgsql
try:
con = bpgsql.connect(
username=db_username, password=db_pass, host="127.0.0.1", dbname=db_table )
cursor = con.cursor( )
cursor.execute( "select name, internal_id from bitstream" )
objs = cursor.fetchall( )
# better explicitly
cursor.close( )
con.close( )
return dict( [(y, x) for x, y in objs] )
except Exception, e:
_logger.exception( "No connection could be made" )
def setUp(self):
self.cnx = bpgsql.connect(self.TEST_DSN)
self.cur = self.cnx.cursor()
