Ejemplo n.º 1
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
Ejemplo n.º 2
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
Ejemplo n.º 3
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
Ejemplo n.º 4
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
Ejemplo n.º 5
0
 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)
Ejemplo n.º 6
0
 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)
Ejemplo n.º 7
0
	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
Ejemplo n.º 8
0
	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)
Ejemplo n.º 9
0
 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()
Ejemplo n.º 10
0
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]
Ejemplo n.º 11
0
 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)
Ejemplo n.º 12
0
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()
Ejemplo n.º 13
0
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
Ejemplo n.º 14
0
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()
Ejemplo n.º 15
0
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())
Ejemplo n.º 16
0
	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);	
Ejemplo n.º 17
0
    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)
Ejemplo n.º 18
0
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()
Ejemplo n.º 19
0
 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())
Ejemplo n.º 20
0
    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)
Ejemplo n.º 21
0
    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)
Ejemplo n.º 22
0
	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);	
Ejemplo n.º 23
0
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]
Ejemplo n.º 24
0
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()
Ejemplo n.º 25
0
 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',
         ]
Ejemplo n.º 26
0
    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)
Ejemplo n.º 27
0
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'
Ejemplo n.º 28
0
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)
Ejemplo n.º 29
0
	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)
Ejemplo n.º 30
0
	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
Ejemplo n.º 31
0
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
Ejemplo n.º 32
0
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
Ejemplo n.º 33
0
	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);	
Ejemplo n.º 34
0
  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)
Ejemplo n.º 35
0
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()
Ejemplo n.º 37
0
 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
Ejemplo n.º 38
0
	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)
Ejemplo n.º 39
0
	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+"\"")
Ejemplo n.º 40
0
	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) )
Ejemplo n.º 41
0
 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
Ejemplo n.º 42
0
	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);
Ejemplo n.º 43
0
	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'")
Ejemplo n.º 44
0
 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)
Ejemplo n.º 45
0
 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
Ejemplo n.º 46
0
    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
Ejemplo n.º 47
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()
Ejemplo n.º 48
0
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
Ejemplo n.º 49
0
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
Ejemplo n.º 50
0
 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
Ejemplo n.º 51
0
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
Ejemplo n.º 52
0
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!'
Ejemplo n.º 53
0
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()
Ejemplo n.º 54
0
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()
Ejemplo n.º 55
0
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()
Ejemplo n.º 56
0
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
Ejemplo n.º 57
0
    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)
Ejemplo n.º 58
0
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