def __init__(self, connectArgs):
connectArgs = string.split(connectArgs, ':')
host = None
if connectArgs and connectArgs[0]: #if host is there
if '|' in connectArgs[0]: #if specified port
host = connectArgs[0].replace('|', ':')
if connectArgs and connectArgs[-1] == 'verbose':
self.verbose = 1
connectArgs = connectArgs[:-1]
else:
self.verbose = 0
if connectArgs and connectArgs[-1] == 'cache':
self.useCacheMod = 1
connectArgs = string.join(connectArgs[:-1], ':')
else:
connectArgs = string.join(connectArgs, ':')
self.useCacheMod = 0
self.connectArgs = connectArgs
if self.useCacheMod:
from PyPgSQLcache import getConnection
self.conn = getConnection(connectArgs)
else:
if host is not None:
self.conn = PgSQL.connect(connectArgs, host=host)
else:
self.conn = PgSQL.connect(connectArgs)
self.bindVariables = 0
def __init__(self, connectArgs):
connectArgs = string.split(connectArgs,':')
host = None
if connectArgs and connectArgs[0]: #if host is there
if '|' in connectArgs[0]: #if specified port
host = connectArgs[0].replace('|', ':')
if connectArgs and connectArgs[-1] == 'verbose':
self.verbose = 1
connectArgs = connectArgs[:-1]
else:
self.verbose=0
if connectArgs and connectArgs[-1] == 'cache':
self.useCacheMod = 1
connectArgs = string.join(connectArgs[:-1], ':')
else:
connectArgs = string.join(connectArgs, ':')
self.useCacheMod =0
self.connectArgs = connectArgs
if self.useCacheMod:
from PyPgSQLcache import getConnection
self.conn = getConnection(connectArgs)
else:
if host is not None:
self.conn = PgSQL.connect(connectArgs, host = host)
else:
self.conn = PgSQL.connect(connectArgs)
self.bindVariables = 0
def __init__(self, db_name, db_host):
from pyPgSQL import PgSQL
if len(db_host) == 0:
db_user = pwd.getpwuid(os.geteuid())[0]
self.connection = PgSQL.connect(database=db_name, user=db_user)
else:
self.connection = PgSQL.connect(database=db_name, host=db_host)
self.connection.autocommit = AUTOCOMMIT
def __init__(self, db_name, db_host):
from pyPgSQL import PgSQL
if len(db_host) == 0:
db_user = pwd.getpwuid(os.geteuid())[0]
self.connection = PgSQL.connect(database=db_name, user=db_user)
else:
self.connection = PgSQL.connect(database=db_name, host=db_host)
self.connection.autocommit = AUTOCOMMIT
def connect(self):
""" Connect to the database"""
if len(self.dbhost):
self.dbh = PgSQL.connect(database=self.dbname, host=self.dbhost,
user=self.dbuser, password=self.dbpass,
client_encoding="utf-8",
unicode_results=1)
else:
self.dbh = PgSQL.connect(database=self.dbname,
user=self.dbuser,
password=self.dbpass,
client_encoding="utf-8",
unicode_results=1)
def connect(self):
""" Connect to the database"""
if len(self.dbhost):
self.dbh = PgSQL.connect(database=self.dbname,
host=self.dbhost,
user=self.dbuser,
password=self.dbpass,
client_encoding="utf-8",
unicode_results=1)
else:
self.dbh = PgSQL.connect(database=self.dbname,
user=self.dbuser,
password=self.dbpass,
client_encoding="utf-8",
unicode_results=1)
def launchCachingProcess(self):
while self.SNMPcc.isRunning():
self.logDebug("SNMP community caching is running, waiting 10 seconds")
time.sleep(10)
try:
pgsqlCon = PgSQL.connect(host=zConfig.pgHost, user=zConfig.pgUser, password=zConfig.pgPwd, database=zConfig.pgDB)
pgcursor = pgsqlCon.cursor()
pgcursor.execute("SELECT ip,name,vendor FROM device ORDER BY ip")
try:
pgres = pgcursor.fetchall()
for idx in pgres:
while self.getThreadNb() >= self.max_threads:
time.sleep(1)
devip = idx[0]
devname = idx[1]
vendor = idx[2]
devcom = self.SNMPcc.getReadCommunity(devname)
if devcom == None:
self.logError("No read community found for %s" % devname)
else:
thread.start_new_thread(self.fetchSNMPInfos,(devip,devname,devcom,vendor))
""" Wait 1 second to lock program, else if script is too fast,it exists without discovering"""
time.sleep(1)
except StandardError, e:
self.logCritical(e)
except PgSQL.Error, e:
self.logCritical("Pgsql Error %s" % e)
return
def __init__(self, dsn = None, dbapi = None):
if dsn == None:
dsn ="localhost:gnumed:gm-dbo:pg"
if dbapi == None:
use_pgdb = '-pgdb' in sys.argv
try:
from pyPgSQL import PgSQL
dbapi = PgSQL
l = dsn.split(':')
if len(l) == 4:
l = [l[0]] + [''] + l[1:]
dsn = ':'.join(l)
except:
print sys.exc_info()[0], sys.exc_info()[1]
use_pgdb = 1
if use_pgdb:
import pgdb
dbapi = pgdb
self.dsn = dsn
try:
self.conn = dbapi.connect(dsn)
return
except:
print sys.exc_info()[0], sys.exc_info()[1]
self.conn = PgSQL.connect(dsn)
def __init__(self):
host = 'localhost'
dbname = 'clue'
user = '******'
passwd = 'clue'
self.cnx = PgSQL.connect('%s::%s:%s:%s' % (host, dbname, user, passwd))
self.cur = self.cnx.cursor()
def getConnection(connUser):
"""
Returns a database connection as defined by
connUser. If this module already has an open
connection for connUser, it returns it; otherwise,
it creates a new connection, stores it, and returns it.
"""
if not _users.has_key(connUser):
raise SkunkStandardError, "user %s is not initialized" % (connUser)
connectParams = _users[connUser]
if not _connections.has_key(connectParams):
try:
connectArgs = string.split(connectParams, ":")
host = None
if connectArgs[0]:
if "|" in connectArgs[0]: # if specified port
host = connectArgs[0].replace("|", ":")
_connections[connectParams] = PgSQL.connect(connectParams, host=host)
except PgSQL.Error:
# XXX Do not raise the connect string! The trace may be seen
# by users!!!
raise SkunkStandardError, ("cannot connect to PostgreSQL: %s" % (sys.exc_info()[1],))
return _connections[connectParams]
def __init__(self,
path,
user=None,
password=None,
host=None,
port=None,
params={}):
if path.startswith('/'):
path = path[1:]
# We support both psycopg and PgSQL but prefer psycopg
global psycopg
global PgSQL
if not psycopg and not PgSQL:
try:
try:
import psycopg2 as psycopg
except ImportError:
import psycopg
except ImportError:
from pyPgSQL import PgSQL
if psycopg:
dsn = []
if path:
dsn.append('dbname=' + path)
if user:
dsn.append('user='******'password='******'host=' + host)
cnx = psycopg.connect(' '.join(dsn))
else:
cnx = PgSQL.connect('', user, password, host, path, port)
ConnectionWrapper.__init__(self, cnx)
def handle_url(bot, user, channel, url, msg):
return
if not config:
return
cx = PgSQL.connect(database=config["database"], host=config["host"], user=config["user"], password=config["password"])
cur = cx.cursor()
# find the oldest instance of given url on this channel
cur.execute("SELECT * FROM pyfibot.urls WHERE channel=%s AND url=%s ORDER BY timestamp;", (channel, url))
res = cur.fetchone()
if res:
url, channel, userhost, timestamp, urlid = res
pastetime = datetime.datetime.fromtimestamp(res[3].ticks())
now = datetime.datetime.now()
age = now - pastetime
agestr = ""
if age.days > 0:
agestr += "%d days " % age.days
secs = age.seconds
hours, minutes, seconds = secs // 3600, secs // 60 % 60, secs % 60
if hours > 0:
agestr += "%d h " % hours
if minutes > 0:
agestr += "%d m " % minutes
if seconds > 0:
agestr += "%d s" % seconds
# don't alert for the same person
if getNick(user) != getNick(userhost):
if channel != "#wow":
bot.say(channel, "%s: wanha. (by %s %s ago)" % (getNick(user), getNick(userhost), agestr))
cur = cx.cursor()
# always store URLs, this structure can handle it, sqlite can't
cur.execute("INSERT INTO pyfibot.urls (userid, channel, url, timestamp) VALUES(%s, %s, %s, NOW());", (user, channel, url))
cx.commit()
cur.close()
cx.close()
def getAConnection():
cnum = -1
# check to see if any existing connections are free. If not,
# create a new one.
while cnum == -1:
for idx in range(len(dblocks)):
if not dblocks[idx].locked():
if dblocks[idx].acquire(0):
cnum = idx
#if checkFreeConnections() != 0:
# notify.set()
break
if cnum == -1:
dblocks.append(thread.allocate_lock())
botdbs.append('')
elif cnum >= num_connections:
print "Not enough connections, spawning a new one."
botdbs[cnum] = PgSQL.connect(host=dbhostname,
user=dbuser,
database=dbname,
port=dbport,
password=dbpass)
printFreeConnections()
return cnum
def cleanRadius(dbhost,dbport,dbname):
global threadCounter
try:
tc_mutex.acquire()
threadCounter += 1
tc_mutex.release()
pgsqlCon = PgSQL.connect(host=netdiscoCfg.pgHost,user=netdiscoCfg.pgUser,password=netdiscoCfg.pgPwd,database=netdiscoCfg.pgDB)
pgcursor = pgsqlCon.cursor()
pgcursor.execute("SELECT login,pwd FROM z_eye_radius_db_list where addr='%s' and port='%s' and dbname='%s'" % (dbhost,dbport,dbname))
pgres2 = pgcursor.fetchone()
if(pgres2):
try:
mysqlconn = MySQLdb.connect(host=dbhost,user=pgres2[0],passwd=pgres2[1],port=dbport,db=dbname)
zeye_log("[Z-Eye][Radius-Cleaner] Connect to MySQL DB %s@%s:%s (user %s)" % (dbname,dbhost,dbport,pgres2[0]))
mysqlcur = mysqlconn.cursor()
mysqlcur.execute("SELECT username from z_eye_radusers WHERE expiration < NOW()")
mysqlres = mysqlcur.fetchall()
for idx in mysqlres:
mysqlcur.execute("DELETE FROM radcheck WHERE username = '******'" % idx[0])
mysqlcur.execute("DELETE FROM radreply WHERE username = '******'" % idx[0])
mysqlcur.execute("DELETE FROM radusergroup WHERE username = '******'" % idx[0])
mysqlconn.commit()
mysqlcur.close()
mysqlconn.close()
except MySQLdb.Error, e:
zeye_log("[Z-Eye][Radius-Cleaner] MySQL Error %s" % e)
sys.exit(1)
tc_mutex.acquire()
threadCounter = threadCounter - 1
tc_mutex.release()
pgsqlCon.close()
def _connect(host="", database="", user="", password=""):
"""Opens a connection to the database.
Normally, this function does not have to be called, because the other
functions of this module connect to the database automatically. If invoked
without parameters, it uses the default connection parameters for the DES
database. If, for some reason, you need to connect to a different database
or use different credentials, you can invoke this function with the desired
parameters. Further calls of functions from this module will then use that
connection.
"""
global _connection
# Use the default values for the connection, if the parameters are not given
if host == "":
host = _HOST
if database == "":
database = _DATABASE
if user == "":
user = _USER
if password == "":
password = _PASSWORD
# Make a connection to the database and check to see if it succeeded.
try:
_connection = PgSQL.connect(host=host, database=database, user=user,\
password=password)
except PgSQL.Error, msg:
errstr = "Connection to database '%s' failed\n%s" % (_DATABASE, msg)
raise Error(errstr.strip())
def launchCfgGenerator(self):
while self.SNMPcc.isRunning():
self.logDebug("SNMP community caching is running, waiting 10 seconds")
time.sleep(10)
self.launchMsg()
try:
pgsqlCon = PgSQL.connect(host=zConfig.pgHost,user=zConfig.pgUser,password=zConfig.pgPwd,database=zConfig.pgDB)
pgcursor = pgsqlCon.cursor()
pgcursor.execute("SELECT ip,name FROM device ORDER BY ip")
try:
pgres = pgcursor.fetchall()
for idx in pgres:
devip = idx[0]
devname = idx[1]
devcom = self.SNMPcc.getReadCommunity(devname)
# Only launch process if SNMP cache is ok
if devcom == None:
self.logError("No read community found for %s" % devname)
else:
thread.start_new_thread(self.fetchMRTGInfos,(devip,devname,devcom))
except StandardError, e:
self.logCritical(e)
return
except PgSQL.Error, e:
self.logCritical("FATAL PgSQL %s" % e)
sys.exit(1);
def __init__(self, dsn=None, dbapi=None):
if dsn == None:
dsn = "localhost:gnumed:gm-dbo:pg"
if dbapi == None:
use_pgdb = '-pgdb' in sys.argv
try:
from pyPgSQL import PgSQL
dbapi = PgSQL
l = dsn.split(':')
if len(l) == 4:
l = [l[0]] + [''] + l[1:]
dsn = ':'.join(l)
except:
print sys.exc_info()[0], sys.exc_info()[1]
use_pgdb = 1
if use_pgdb:
import pgdb
dbapi = pgdb
self.dsn = dsn
try:
self.conn = dbapi.connect(dsn)
return
except:
print sys.exc_info()[0], sys.exc_info()[1]
self.conn = PgSQL.connect(dsn)
def main():
db = PgSQL.connect(database='casemgr')
curs = db.cursor()
curs.execute('create index wqdesc on workqueues (description);')
curs.execute('select unit_id from units where unit_id not in (select unit_id from workqueues where unit_id is not null)')
unit_ids = fetchids(curs)
curs.execute('select user_id from users where user_id not in (select user_id from workqueues where user_id is not null)')
user_ids = fetchids(curs)
print 'Units %d, Users %d' % (len(unit_ids), len(user_ids))
# Create workqueues
many(curs, 'insert wq', 'insert into workqueues (name,description, unit_id, user_id) values (%s,%s,%s,%s)', wq_rows(unit_ids, user_ids))
# Find shared queues
curs.execute("select queue_id from workqueues where unit_id is null and user_id is null and description = 'X'")
shared_queues = fetchids(curs)
# Add members to shared queues
print 'Shared queues %s' % len(shared_queues)
many(curs, 'insert wqm', 'insert into workqueue_members (queue_id, unit_id, user_id) values (%s,%s,%s)', wqm_rows(shared_queues, unit_ids, user_ids))
# Create tasks
curs.execute("select master_id from cases")
case_ids = fetchids(curs)
curs.execute("select queue_id from workqueues where description='X'")
queue_ids = fetchids(curs)
many(curs, 'insert tasks', 'insert into tasks (queue_id, task_description, case_id) values (%s, %s, %s)', task_rows(case_ids, queue_ids))
curs.execute('drop index wqdesc;')
db.commit()
def __init__(self, in_userid):
self.__tasks = []
self.db = PgSQL.connect (database="jackdesert_groove", user="******", password="******")
self.cur = self.db.cursor()
# self.reset_table(self.cur)
self.__userid = int(in_userid)
self.__utcoffset = int(self.retrieve_utcoffset())
def DBExecute(self, query, *args):
"""Execute a query on the database, creating a connection if necessary.
Args:
query: SQL string
Returns:
Full query result in virtual table
"""
if not self._dbh:
if FLAGS['db-type'] == 'mysql':
self._dbh = MySQLdb.connect(host=FLAGS['db-hostname'],
user=FLAGS['db-username'],
passwd=FLAGS['db-password'],
db=FLAGS['db-name'])
elif FLAGS['db-type'] == 'pgsql':
self._dbh = PgSQL.connect(host=FLAGS['db-hostname'],
user=FLAGS['db-username'],
password=FLAGS['db-password'],
database=FLAGS['db-name'])
else:
print 'Invalid db-type: %s' % FLAGS['db-type']
sys.exit(1)
self._cursor = self._dbh.cursor()
self._cursor.execute(query, args)
self._dbh.commit()
try:
result = self._cursor.fetchall()
except MySQLdb.Error:
return None
if not result:
return result
fields = [i[0] for i in self._cursor.description]
return VirtualTable(fields, result)
def __init__(self,
path,
user=None,
password=None,
host=None,
port=None,
params={}):
if path.startswith('/'):
path = path[1:]
# We support both psycopg and PgSQL but prefer psycopg
global psycopg
global PgSQL
global PGSchemaError
if not psycopg and not PgSQL:
try:
import psycopg2 as psycopg
import psycopg2.extensions
from psycopg2 import ProgrammingError as PGSchemaError
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
except ImportError:
from pyPgSQL import PgSQL
from pyPgSQL.libpq import OperationalError as PGSchemaError
if 'host' in params:
host = params['host']
if psycopg:
dsn = []
if path:
dsn.append('dbname=' + path)
if user:
dsn.append('user='******'password='******'host=' + host)
if port:
dsn.append('port=' + str(port))
cnx = psycopg.connect(' '.join(dsn))
cnx.set_client_encoding('UNICODE')
else:
# Don't use chatty, inefficient server-side cursors.
# http://pypgsql.sourceforge.net/pypgsql-faq.html#id2787367
PgSQL.fetchReturnsList = 1
PgSQL.noPostgresCursor = 1
cnx = PgSQL.connect('',
user,
password,
host,
path,
port,
client_encoding='utf-8',
unicode_results=True)
try:
self.schema = None
if 'schema' in params:
self.schema = params['schema']
cnx.cursor().execute('SET search_path TO %s', (self.schema, ))
except PGSchemaError:
cnx.rollback()
ConnectionWrapper.__init__(self, cnx)
def launchCfgGenerator(self):
Logger.ZEyeLogger().write("MRTG configuration discovery started")
starttime = datetime.datetime.now()
try:
pgsqlCon = PgSQL.connect(host=netdiscoCfg.pgHost,user=netdiscoCfg.pgUser,password=netdiscoCfg.pgPwd,database=netdiscoCfg.pgDB)
pgcursor = pgsqlCon.cursor()
pgcursor.execute("SELECT ip,name FROM device ORDER BY ip")
try:
pgres = pgcursor.fetchall()
for idx in pgres:
pgcursor2 = pgsqlCon.cursor()
pgcursor2.execute("SELECT snmpro FROM z_eye_snmp_cache where device = '%s'" % idx[1])
pgres2 = pgcursor2.fetchone()
devip = idx[0]
devname = idx[1]
if pgres2:
devcom = pgres2[0]
else:
devcom = self.defaultSNMPRO
thread.start_new_thread(self.fetchMRTGInfos,(devip,devname,devcom))
except StandardError, e:
Logger.ZEyeLogger().write("MRTG-Config-Discovery: FATAL %s" % e)
return
except PgSQL.Error, e:
Logger.ZEyeLogger().write("MRTG-Config-Discovery: FATAL PgSQL %s" % e)
sys.exit(1);
def getConnection(connUser):
"""
Returns a database connection as defined by
connUser. If this module already has an open
connection for connUser, it returns it; otherwise,
it creates a new connection, stores it, and returns it.
"""
if not _users.has_key(connUser):
raise SkunkStandardError, 'user %s is not initialized' % (connUser)
connectParams=_users[connUser]
if not _connections.has_key(connectParams):
try:
connectArgs=string.split(connectParams,':')
host=None
if connectArgs[0]:
if '|' in connectArgs[0]: #if specified port
host=connectArgs[0].replace('|', ':')
_connections[connectParams]=PgSQL.connect(connectParams,
host=host)
except PgSQL.Error:
# XXX Do not raise the connect string! The trace may be seen
# by users!!!
raise SkunkStandardError, ('cannot connect to PostgreSQL: %s' %
(sys.exc_info()[1],))
return _connections[connectParams]
def handle_url(bot, user, channel, url, msg):
return
if not config:
return
cx = PgSQL.connect(database=config["database"], host=config["host"], user=config["user"], password=config["password"])
cur = cx.cursor()
# find the oldest instance of given url on this channel
cur.execute("SELECT * FROM pyfibot.urls WHERE channel=%s AND url=%s ORDER BY timestamp;", (channel, url))
res = cur.fetchone()
if res:
url, channel, userhost, timestamp, urlid = res
pastetime = datetime.datetime.fromtimestamp(res[3].ticks())
now = datetime.datetime.now()
age = now - pastetime
agestr = ""
if age.days > 0:
agestr += "%d days " % age.days
secs = age.seconds
hours, minutes, seconds = secs // 3600, secs // 60 % 60, secs % 60
if hours > 0:
agestr += "%d h " % hours
if minutes > 0:
agestr += "%d m " % minutes
if seconds > 0:
agestr += "%d s" % seconds
# don't alert for the same person
if getNick(user) != getNick(userhost):
if channel != "#wow":
bot.say(channel, "%s: wanha. (by %s %s ago)" % (getNick(user), getNick(userhost), agestr))
cur = cx.cursor()
# always store URLs, this structure can handle it, sqlite can't
cur.execute("INSERT INTO pyfibot.urls (userid, channel, url, timestamp) VALUES(%s, %s, %s, NOW());", (user, channel, url))
cx.commit()
cur.close()
cx.close()
def __init__(self, host, port, database, username, password) :
IDB.__init__(self)
self.dbh = PgSQL.connect(":".join([host, port, database, username, password]))
self.albumtreestore = gtk.TreeStore(gobject.TYPE_PYOBJECT,
str, gtk.gdk.Pixbuf, int, int)
self.canViewExtensions = [
'.jpg',
'.jpeg',
'.thm',
]
self.canTrackExtensions = [
'.bmp',
'.png',
'.jpeg',
'.jpg',
'.thm',
'.gif',
'.pcx',
'.pnm',
'.tiff',
'.tiff',
'.iff',
'.xpm',
'.ico',
'.cur',
'.ani',
]
def _setPgsqlDictCursor(self):
use=None
try:
from pyPgSQL import PgSQL
use='pgsql'
except:
pass
try:
import psycopg2
import psycopg2.extras
use='psycopg2'
except:
pass
if use=='pgsql':
conn = PgSQL.connect(database=self.getConfig('db'),host=self.getConfig('host'),
user=self.getConfig('user'),password=self.getConfig('pass'))
conn.autocommit=1
self._conn = conn
self._cursor = conn.cursor()
elif use=='psycopg2':
conn = psycopg2.connect("host=%s dbname=%s user=%s password=%s" % (\
self.getConfig('host'), self.getConfig('user'), \
self.getConfig('db'), self.getConfig('pass')))
conn.set_isolation_level(0)
self._conn = conn
self._cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
else:
raise novalidDriver, "no postgres driver available (PgSQL or psycopg2)"
assert(self._cursor)
def save_result(cfg, game_result):
print 'Saving to DB....'
query = 'insert into game(ai_config1,ai_config2,ai_ident1,ai_ident2,duration,faction1,faction2,is_success,local_modifications,map,svn_release,test,end_turn,version_string,winner_side) values (%s,%s,%s,%s,cast(%s as double precision),%s,%s,cast(%s as boolean),cast(%s as boolean),%s,cast(%s as int),%s,cast(%s as int),%s,cast(%s as int))'
db_ip = cfg.get('default', 'db_ip')
db_port = cfg.getint('default', 'db_port')
db_name = cfg.get('default', 'db_name')
db_user = cfg.get('default', 'db_user')
db_pass = cfg.get('default', 'db_pass')
dbconnection = PgSQL.connect(database=db_name,
host=db_ip,
port=db_port,
user=db_user,
password=db_pass)
cu = dbconnection.cursor()
cu.execute(query, game_result.ai_config1, game_result.ai_config2,
game_result.ai_ident1, game_result.ai_ident2,
game_result.duration, game_result.faction1,
game_result.faction2, game_result.is_success,
game_result.local_modifications, game_result.map,
game_result.svn_release, game_result.test, game_result.end_turn,
game_result.version_string, game_result.winner_side)
cu.execute('commit')
dbconnection.close()
print 'Saved to DB'
def connect_by_uri(uri):
"""General URI syntax:
postgres://user:password@host:port/database?opt1=val1&opt2=val2...
where opt_n is in the list of options supported by PostGreSQL:
host,user,password,port,database,client_encoding,unicode_results
NOTE: the authority and the path parts of the URI have precedence
over the query part, if an argument is given in both.
Descriptions of options:
file:///usr/lib/python?.?/site-packages/pyPgSQL/PgSQL.py
"""
puri = urisup.uri_help_split(uri)
params = __dict_from_query(puri[QUERY])
if puri[AUTHORITY]:
user, password, host, port = puri[AUTHORITY]
if user:
params['user'] = user
if password:
params['password'] = password
if host:
params['host'] = host
if port:
params['port'] = port
if puri[PATH]:
params['database'] = puri[PATH]
if params['database'] and params['database'][0] == '/':
params['database'] = params['database'][1:]
__apply_types(params, __typemap)
return PgSQL.connect(**params)
def get_trace(self): """ Do not take any argument. Returns the next triplet (message, cipher, trace), where: - message is an ascii string containing a 64 bits clear message in hexadecimal, - cipher is an ascii string containing a 64 bits ciphered message in hexadecimal, - trace is a float vector containing a trace during the cipher operation. """ if self.__i == len( self.__dbd ): return None, None, None; # Error, since we have reached the last file trace_name= self.__dbd[self.__i] self.__i+= 1; try: cmd= "SELECT message,cryptogram,filecontent FROM "+self.__table+" WHERE filename = '"+trace_name+"'" self.__curs.execute( cmd ) one= self.__curs.fetchone() msg, crypt, raw_data= one if db_name=='pgdb': raw_data= db.unescape_bytea( raw_data ) return msg, crypt, parse_binary( str(raw_data) ) except db.DatabaseError, e: print e sys.exit(1)
def configAndTryConnect (self, dbType, dbHost, dbPort, dbName, dbLogin, dbPwd):
if dbType != "my" and dbType != "pg":
return False
try:
if dbType == "pg":
self.dbConn = PgSQL.connect(host=dbHost,user=dbLogin,password=dbPwd,database=dbName)
if self.dbConn == None:
return False
self.dbCursor = self.dbConn.cursor()
if self.dbCursor == None:
self.dbConn.close()
return False
elif dbType == "my":
self.dbConn = pymysql.connect(host=dbHost, port=dbPort, user=dbLogin, passwd=dbPwd, db=dbName)
if self.dbConn == None:
return False
self.dbCursor = self.dbConn.cursor()
if self.dbCursor == None:
self.dbConn.close()
return False
else:
self.logger.warn("Database '%s' not supported" % dbType)
return False
except Exception, e:
self.logger.error("DBMgr: connection to DB %s:%s/%s failed: %s" % (dbHost,dbPort,dbName,e))
return False
def rellenarTablas(db):
"""Rellena en PostgreSQL las tablas de la base de datos indicada por "db".
@param db: Nombre de la base de datos
@type db: str"""
conexion = PgSQL.connect(database=db)
conexion.autocommit = 1
cursor = conexion.cursor()
sql = "INSERT INTO recetas (nombre, raciones, autor) VALUES ('Paella',4,'Greg Walters')"
cursor.execute(sql)
instrucciones = ['1. Sofreír la carne picada', '2. Mezclar el resto de ingredientes', '3. Dejar que rompa a hervir', '4. Dejar cocer durante 20 minutos o hasta que pierda el agua']
for paso in instrucciones:
sql = "INSERT INTO instrucciones (nombre_receta, paso) VALUES ('Paella','%s')" % paso
cursor.execute(sql)
ingredientes = ['1 taza de arroz', 'carne picada', '2 vasos de agua', '1 lata de salsa de tomate', '1 cebolla picada', '1 ajo', '1 cucharadita de comino', '1 cucharadita de orégano', 'sal y pimienta al gusto', 'salsa al gusto']
for ingrediente in ingredientes:
sql = "INSERT INTO ingredientes (nombre_receta, ingrediente) VALUES ('Paella','%s')" % ingrediente
cursor.execute(sql)
print '"Paella" insertada en el recetario'
cursor.close()
del cursor
del conexion
def transferbuilding():
try:
cx = PgSQL.connect(host=dbhost,database=dbname,user=dbuser,password=dbpasswd)
cu = cx.cursor()
cu.execute('select * from description;')
rows = cu.fetchall()
for building in rows:
bname = building['itemname']
balias = building['textdesc']
bphoto = building['imagedesc']
bid = building['pid']
pgsqlstr = 'select * from gazetteer where pid='+str(bid)+';'
cu.execute(pgsqlstr)
temprow = cu.fetchall()
bloc = temprow[0]['footprint']
#bloc = building['footprint']
newbuilding = CBuilding()
newbuilding.bname = bname
newbuilding.balias = balias
newbuilding.btype = 1
newbuilding.baddr = 'null'
newbuilding.bphoto = bphoto
newbuilding.count = 0
newbuilding.loc = bloc
newbuilding.save()
except StandardError,err:
print 'error',err
def launchCachingProcess(self):
starttime = datetime.datetime.now()
Logger.ZEyeLogger().write("Port ID caching started")
try:
pgsqlCon = PgSQL.connect(host=netdiscoCfg.pgHost,user=netdiscoCfg.pgUser,password=netdiscoCfg.pgPwd,database=netdiscoCfg.pgDB)
pgcursor = pgsqlCon.cursor()
pgcursor.execute("SELECT ip,name,vendor FROM device ORDER BY ip")
try:
pgres = pgcursor.fetchall()
for idx in pgres:
while self.getThreadNb() >= self.max_threads:
time.sleep(1)
pgcursor.execute("SELECT snmpro FROM z_eye_snmp_cache where device = '%s'" % idx[1])
pgres2 = pgcursor.fetchone()
devip = idx[0]
devname = idx[1]
vendor = idx[2]
if pgres2:
devcom = pgres2[0]
else:
devcom = self.defaultSNMPRO
thread.start_new_thread(self.fetchSNMPInfos,(devip,devname,devcom,vendor))
""" Wait 1 second to lock program, else if script is too fast,it exists without discovering"""
time.sleep(1)
except StandardError, e:
Logger.ZEyeLogger().write("Port ID Caching: FATAL %s" % e)
except PgSQL.Error, e:
Logger.ZEyeLogger().write("Port ID Caching: Pgsql Error %s" % e)
sys.exit(1);
def DBExecute(self, query, *args):
"""Execute a query on the database, creating a connection if necessary.
Args:
query: SQL string
Returns:
Full query result in virtual table
"""
if not self._dbh:
if FLAGS['db-type'] == 'mysql':
self._dbh = MySQLdb.connect(host=FLAGS['db-hostname'],
user=FLAGS['db-username'],
passwd=FLAGS['db-password'],
db=FLAGS['db-name'])
elif FLAGS['db-type'] == 'pgsql':
self._dbh = PgSQL.connect(host=FLAGS['db-hostname'],
user=FLAGS['db-username'],
password=FLAGS['db-password'],
database=FLAGS['db-name'])
else:
print 'Invalid db-type: %s' % FLAGS['db-type']
sys.exit(1)
self._cursor = self._dbh.cursor()
self._cursor.execute(query, args)
self._dbh.commit()
try:
result = self._cursor.fetchall()
except MySQLdb.Error:
return None
if not result:
return result
fields = [i[0] for i in self._cursor.description]
return VirtualTable(fields, result)
def connect():
global connection
connection = PgSQL.connect(host="localhost",
database="testdb",
client_encoding="utf-8",
unicode_results=1,
user="******")
def connect(password, database):
global conn
global cursor
conn = PgSQL.connect(host="localhost",
user="******",
database=database,
password=password)
cursor = conn.cursor()
def can_connect(self):
try: from pyPgSQL import PgSQL
except: return False
try:
conn = PgSQL.connect(database=self.DB_NAME, user=self.DB_USER,
password=self.DB_PASS)
conn.close()
return True
except:
return False
def __init__(self): self.date_time_timestamp='Mon Jun 14 15:29:21 -0300 2010' self.date_time = "2010-06-16 10:47:24" self.banco_ip="192.168.1.246" self.banco_nome="rdmine" self.banco_usuario="postgres" self.banco_senha="123456" self.user_id=6 # Usuario Esse que vale self.project_id=1 self.bdcon = PgSQL.connect(host=self.banco_ip, database=self.banco_nome, user=self.banco_usuario, password=self.banco_senha)
def __init__(self, table):
""" No arguments needed """
self.__conn = db.connect(
user = self.__user,
password = self.__pass,
host = self.__host,
database = self.__db
)
self.__curs = self.__conn.cursor()
self.__curs.execute("SELECT message,cryptogram,filecontent FROM \""+table+"\"")
def get_trace(self): """ Do not take any argument. Returns the next couple (message, trace), where: - message is an ascii string containing a 64 bits clear message in hexadecimal, - trace is a float vector containing a trace during the cipher operation. """ msg, crypt, raw_data= self.__curs.fetchone() if db_name=='pgdb': raw_data= db.unescape_bytea( raw_data ) return msg, crypt, parse_binary( str(raw_data) )
def can_connect(self):
try: from pyPgSQL import PgSQL
except: return False
try:
conn = PgSQL.connect(database=self.DB_NAME, user=self.DB_USER,
password=self.DB_PASS)
conn.close()
return True
except:
return False
def launchCleanup(self):
try:
self.pgcon = PgSQL.connect(host=zConfig.pgHost,user=zConfig.pgUser,password=zConfig.pgPwd,database=zConfig.pgDB)
self.pgcursor = self.pgcon.cursor()
self.pgcursor.execute("DELETE FROM z_eye_switch_port_prises WHERE (ip,port) NOT IN (select host(ip),port from device_port)")
self.pgcon.commit()
except Exception, e:
self.logCritical(e)
sys.exit(1);
def __init__(self, table):
""" No arguments needed """
self.__conn = db.connect(
user = self.__user,
password = self.__pass,
host = self.__host,
database = self.__db
)
self.__curs = self.__conn.cursor()
self.__curs.execute("SELECT message,cipher,data FROM "+table+" where key='21c66842f6e96c9a670c9c61abd388f0'")
def connect(self):
conninfo = {
"host":self.entHost.text(),
"port":self.entPort.text(),
"database":self.entDatabase.text(),
"user":self.entUser.text(),
"password":self.entPassword.text()
}
if conninfo['password'] is None:
del conninfo['password']
try:
conn = PgSQL.connect(**conninfo)
conn2 = PgSQL.connect(**conninfo)
return LPathPgSqlDB(conn, conn2, conninfo["user"].ascii())
except PgSQL.libpq.DatabaseError, e:
try:
enc = os.environ['LANG'].split('.')[-1]
msg = e.message.decode(enc)
except:
msg = e.message
raise ConnectionError(msg)
def can_connect(self):
try:
from pyPgSQL import PgSQL # type: ignore[import]
except BaseException:
return False
try:
conn = PgSQL.connect(database=self.DB_NAME,
user=self.DB_USER,
password=self.DB_PASS)
conn.close()
return True
except BaseException:
return False
def connect(self):
try:
self.cnx = PgSQL.connect(database=self.database,
host=self.host,
user=self.user,
password=self.passw,
port=self.port)
self.cur = self.cnx.cursor()
return 1
except PgSQL.Error, msg:
print msg
return 0
def __init__(self, db):
"""Inicializador de la clase Recetario
@param db: Nombre de la base de datos a utilizar
@type db: str"""
global conexion
global cursor
self.cantidadtotal = 0
try:
conexion = PgSQL.connect(database=db)
except libpq.DatabaseError:
print '¡No existe la base de datos "%s"!' % database
print 'Has de crearla primero\n'
uso()
conexion.autocommit = 1
cursor = conexion.cursor()
def crearTablas(db):
"""Crea en PostgreSQL las tablas de la base de datos indicada por "db".
@param db: Nombre de la base de datos
@type db: str"""
conexion = PgSQL.connect(database=db)
conexion.autocommit = 1
cursor = conexion.cursor()
sql = 'CREATE TABLE mp3 (titulo VARCHAR(255), artista VARCHAR(255), album VARCHAR(255), bitrate INT, frecuencia INT, genero VARCHAR(20),'
sql = sql + ' duracion VARCHAR(8), pista SMALLINT, ano SMALLINT, tamano INT, ruta VARCHAR(255), fichero VARCHAR(255));'
cursor.execute(sql)
print 'Creada tabla "mp3"'
cursor.close()
del cursor
del conexion
def borrarBasedeDatos(db):
"""Borra de PostgreSQL la base de datos indicada por "db".
@param db: Nombre de la base de datos
@type db: str"""
conexion = PgSQL.connect(database='template1')
conexion.autocommit = 1
cursor = conexion.cursor()
try:
cursor.execute("DROP DATABASE %s" % db)
except libpq.OperationalError:
print '¡No existe la base de datos "%s"!' % db
sys.exit(1)
print 'Borrada base de datos "%s"' % db
cursor.close()
del cursor
del conexion
def typeCheckAndConvert(self, val, aname, attr):
if val == None:
val = "NULL"
elif _isIntervalKind(attr):
if not isInterval(val):
raise TypeError, (
'trying to assign %s to %s and is not an interval , '\
'being of type %s '
) % (val, aname, type(val))
val = _intervalConvertToDB(val)
elif _isTimeKind(attr):
if not isDateTime(val) and not val == PyDBI.SYSDATE:
raise TypeError, (
'trying to assign %s to %s and is not a time, '\
'being of type %s '
) % (val, aname, type(val))
val = _timeConvertToDB(val)
elif _isDateKind(attr):
if (not isDateTime(val)) and not val == PyDBI.SYSDATE:
raise TypeError,(
'trying to assign %s to %s and is not a date, '\
'being of type %s '
) % (val, aname, type(val))
val = _dateConvertToDB(val)
elif _isNumber(attr):
if attr in ('FLOAT4', 'FLOAT8'):
f = float
elif attr in ('BIGINT', 'INT8'):
f = lambda x: long(x)
else:
f = lambda x: int(float(x))
try:
return f(val)
except:
raise TypeError, ('trying to assign %s to %s and is not'
' a number') % (val, aname)
elif _isString(attr) and not isinstance(val, types.StringType):
raise TypeError, 'trying to assign %s to %s and is not a string' % (
val, aname)
elif _isBinary(attr):
return PgSQL.PgQuoteBytea(val, 0)
elif attr.upper() == 'BOOL':
if val:
return 'TRUE'
else:
return 'FALSE'
return val
def crearBasedeDatos(db):
"""Crea en PostgreSQL la base de datos indicada por "db".
@param db: Nombre de la base de datos
@type db: str"""
conexion = PgSQL.connect(database='template1')
conexion.autocommit = 1
cursor = conexion.cursor()
try:
cursor.execute(
"CREATE DATABASE %s ENCODING 'UTF8' TEMPLATE template1" % db)
except libpq.OperationalError:
print '¡Ya existe la base de datos "%s"!' % db
sys.exit(1)
print 'Creada base de datos "%s"' % db
cursor.close()
del cursor
del conexion
def main():
"""Función main de esta aplicación"""
global conexion
global cursor
#----------------------------------------------
if len(sys.argv) != 2:
uso()
else:
# Manual de uso
if sys.argv[1] in ("-h", "--help"):
uso()
# Borrar la base de datos
elif sys.argv[1] in ("-b", "--borrar"):
resp = raw_input(
'¿SEGURO que quieres borrar la base de datos de música por completo? (S/n) '
)
if string.upper(resp) == 'S':
borrarBasedeDatos(database)
else:
print 'Operación cancelada'
# Crear la base de datos
else:
DirectorioRaiz = sys.argv[1]
# Función de os.path
if not exists(DirectorioRaiz):
print 'Parece que el directorio %s no existe... Saliendo.' % DirectorioRaiz
sys.exit(1)
else:
print 'Vamos allá con %s:' % DirectorioRaiz
# Creamos la base de datos si no existe...
crearBasedeDatos(database)
crearTablas(database)
# Creamos la conexión con la base de datos y el cursor
conexion = PgSQL.connect(database=database)
conexion.autocommit = 1
cursor = conexion.cursor()
# Hacemos el trabajo en sí...
RecorrerLaRuta(DirectorioRaiz)
# Cerramos el cursor y la conexión...
cursor.close()
conexion.close()
# Avisamos al terminar...
print '¡HECHO!'
def RealMain(argv, data=None):
os.environ['LC_ALL'] = 'C'
options, args = parser.parse_args(argv[1:])
srv = GetRpcServer(options)
backup = Proxy(BackupService_Stub(srv))
db = PgSQL.connect(database=options.dbname,
client_encoding="utf-8",
unicode_results=1)
db.cursor().execute("set client_encoding to unicode")
store = LocalStore(db)
print 'BEGIN BACKUP'
for kind_name in KINDS:
sys.stdout.write('\n')
cnt = 0
last_key = ''
while True:
sys.stdout.write('\r%-18s ... ' % kind_name)
r = NextChunkRequest()
r.kind = kind_name
r.last_key = last_key
r = backup.NextChunk(r)
if not r.entity:
break
for entity in r.entity:
cnt += 1
sys.stdout.write('\r%-18s ... %5d ' % (kind_name, cnt))
o = pickle.load(cStringIO.StringIO(entity.data))
getattr(store, 'save_%s' % kind_name)(entity, o)
last_key = entity.key
db.commit()
sys.stdout.write('\n')
print 'BACKUP DONE'
db.commit()
db.close()
def getRuns(queryname):
db = PgSQL.connect(host='postgresql.cs.wisc.edu',
port=49173,
database='cbi')
db.autocommit = False
query = file(queryname).read()
cursor = db.cursor()
cursor.execute(query)
while True:
rows = cursor.fetchmany()
if rows:
for apName, version, release, distribution, numRuns in rows:
yield apName, distribution, version, release, numRuns
else:
break
db.rollback()
db.close()
def main():
#Abort if user passed in wrong number of arguments
if len(sys.argv) != 2:
print "Usage: main.py resultsRootDir"
print "Example: main.py /tmp/fletchal/results"
return
resultsRoot = sys.argv[1]
if not resultsRoot.endswith("/"):
resultsRoot += "/"
# Do query for master list of failed runs
db = PgSQL.connect(host='postgresql.cs.wisc.edu', port=49173, database='cbi')
db.autocommit = False
query = "SELECT application_name, application_version, application_release, build_distribution, COUNT(*) AS \"failed_runs\" FROM run NATURAL JOIN build WHERE exit_signal <> 0 " + "GROUP BY application_name, application_version, application_release, build_distribution" + " HAVING COUNT(*) > 1"
cursor = db.cursor()
cursor.execute(query)
rows = cursor.fetchall()
for name, version, release, distribution, failed_runs in rows:
folder = name+"-"+version+"-"+release
homeDir = resultsRoot+distribution+"/"+folder
#Make sure that the current distribution/release exists in the file system, and that it contains the expected folder structure
if distribution in os.listdir(resultsRoot) and folder in os.listdir(resultsRoot+distribution) and "sites" in os.listdir(homeDir) and "data" in os.listdir(homeDir) and "src" in os.listdir(homeDir):
sys.argv = "junk", "-esp", homeDir
visualize_one.main()
else:
print "Could not find results directory", homeDir, "or it is missing sites and/or data and/or src directories. Skipping this release."
db.rollback()
db.close()
outfile.close()
def crearTablas(db):
"""Crea en PostgreSQL las tablas de la base de datos indicada por "db".
@param db: Nombre de la base de datos
@type db: str"""
conexion = PgSQL.connect(database=db)
conexion.autocommit = 1
cursor = conexion.cursor()
sql = "CREATE TABLE recetas (nombre VARCHAR(40) NOT NULL, raciones INT, autor VARCHAR(20), CONSTRAINT PK_nombre PRIMARY KEY (nombre))"
cursor.execute(sql)
print 'Creada tabla "recetas"'
sql = "CREATE TABLE instrucciones (paso VARCHAR(60) NOT NULL, nombre_receta VARCHAR(40), CONSTRAINT PK_paso PRIMARY KEY (paso), CONSTRAINT FK_nombre_receta FOREIGN KEY (nombre_receta) REFERENCES recetas (nombre) ON DELETE CASCADE ON UPDATE CASCADE)"
cursor.execute(sql)
print 'Creada tabla "instrucciones"'
sql = "CREATE TABLE ingredientes (ingrediente VARCHAR(30) NOT NULL, nombre_receta VARCHAR(40), CONSTRAINT PK_ingrediente PRIMARY KEY (ingrediente), CONSTRAINT FK_nombre_receta FOREIGN KEY (nombre_receta) REFERENCES recetas (nombre) ON DELETE CASCADE ON UPDATE CASCADE)"
cursor.execute(sql)
print 'Creada tabla "ingredientes"'
cursor.close()
del cursor
del conexion
def _setPgsqlDictCursor(self):
use = None
try:
from pyPgSQL import PgSQL
use = 'pgsql'
except:
pass
try:
import psycopg2
import psycopg2.extras
use = 'psycopg2'
except:
pass
if use == 'pgsql':
conn = PgSQL.connect(database=self.getConfig('db'),
host=self.getConfig('host'),
user=self.getConfig('user'),
password=self.getConfig('pass'))
conn.autocommit = 1
self._conn = conn
self._cursor = conn.cursor()
elif use == 'psycopg2':
conn = psycopg2.connect("host=%s dbname=%s user=%s password=%s" % (\
self.getConfig('host'), self.getConfig('user'), \
self.getConfig('db'), self.getConfig('pass')))
conn.set_isolation_level(0)
self._conn = conn
self._cursor = conn.cursor(
cursor_factory=psycopg2.extras.DictCursor)
else:
raise novalidDriver, "no postgres driver available (PgSQL or psycopg2)"
assert (self._cursor)
def rellenarTablas(db):
"""Rellena en PostgreSQL las tablas de la base de datos indicada por "db".
@param db: Nombre de la base de datos
@type db: str"""
conexion = PgSQL.connect(database=db)
conexion.autocommit = 1
cursor = conexion.cursor()
sql = "INSERT INTO recetas (nombre, raciones, autor) VALUES ('Paella',4,'Greg Walters')"
cursor.execute(sql)
instrucciones = [
'1. Sofreír la carne picada', '2. Mezclar el resto de ingredientes',
'3. Dejar que rompa a hervir',
'4. Dejar cocer durante 20 minutos o hasta que pierda el agua'
]
for paso in instrucciones:
sql = "INSERT INTO instrucciones (nombre_receta, paso) VALUES ('Paella','%s')" % paso
cursor.execute(sql)
ingredientes = [
'1 taza de arroz', 'carne picada', '2 vasos de agua',
'1 lata de salsa de tomate', '1 cebolla picada', '1 ajo',
'1 cucharadita de comino', '1 cucharadita de orégano',
'sal y pimienta al gusto', 'salsa al gusto'
]
for ingrediente in ingredientes:
sql = "INSERT INTO ingredientes (nombre_receta, ingrediente) VALUES ('Paella','%s')" % ingrediente
cursor.execute(sql)
print '"Paella" insertada en el recetario'
cursor.close()
del cursor
del conexion