Python pyPgSQL.PgSQL类代码示例

 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 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_by_uri(uri):
    """General URI syntax:

    postgres://user:[email protected]: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 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 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 __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, in_userid):
     self.__tasks = []
     self.db = PgSQL.connect (database="jackdesert_groove", user="jackdesert_groove", password="123567")
     self.cur = self.db.cursor()
     # self.reset_table(self.cur)
     self.__userid = int(in_userid)
     self.__utcoffset = int(self.retrieve_utcoffset())

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 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 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 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 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 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);