Python psycopg2.connect() Examples

The following are 30 code examples of psycopg2.connect(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module psycopg2 , or try the search function .
Example #1
Source File: test_postgresql.py    From testing.postgresql with Apache License 2.0 10 votes vote down vote up
def test_copy_data_from(self):
        try:
            tmpdir = tempfile.mkdtemp()

            # create new database
            with testing.postgresql.Postgresql(base_dir=tmpdir) as pgsql:
                conn = pg8000.connect(**pgsql.dsn())
                with closing(conn.cursor()) as cursor:
                    cursor.execute("CREATE TABLE hello(id int, value varchar(256))")
                    cursor.execute("INSERT INTO hello values(1, 'hello'), (2, 'ciao')")
                conn.commit()
                conn.close()

            # create another database from first one
            data_dir = os.path.join(tmpdir, 'data')
            with testing.postgresql.Postgresql(copy_data_from=data_dir) as pgsql:
                conn = pg8000.connect(**pgsql.dsn())
                with closing(conn.cursor()) as cursor:
                    cursor.execute('SELECT * FROM hello ORDER BY id')
                    self.assertEqual(cursor.fetchall(), ([1, 'hello'], [2, 'ciao']))
                conn.close()
        finally:
            rmtree(tmpdir) 
Example #2
Source File: test_postgresql.py    From testing.postgresql with Apache License 2.0 8 votes vote down vote up
def test_PostgresqlFactory_with_initialized_handler(self):
        def handler(pgsql):
            conn = pg8000.connect(**pgsql.dsn())
            with closing(conn.cursor()) as cursor:
                cursor.execute("CREATE TABLE hello(id int, value varchar(256))")
                cursor.execute("INSERT INTO hello values(1, 'hello'), (2, 'ciao')")
            conn.commit()
            conn.close()

        Postgresql = testing.postgresql.PostgresqlFactory(cache_initialized_db=True,
                                                          on_initialized=handler)
        try:
            with Postgresql() as pgsql:
                conn = pg8000.connect(**pgsql.dsn())
                with closing(conn.cursor()) as cursor:
                    cursor.execute('SELECT * FROM hello ORDER BY id')
                    self.assertEqual(cursor.fetchall(), ([1, 'hello'], [2, 'ciao']))
                conn.close()
        finally:
            Postgresql.clear_cache() 
Example #3
Source File: test_databases.py    From piicatcher with Apache License 2.0 8 votes vote down vote up
def create_tables(self, request):
        self.conn = psycopg2.connect(host="127.0.0.1",
                                     user="piiuser",
                                     password="p11secret",
                                     database="piidb")

        self.conn.autocommit = True

        with self.conn.cursor() as cursor:
            self.execute_script(cursor, char_data_types)
            cursor.close()

        def drop_tables():
            with self.conn.cursor() as d_cursor:
                d_cursor.execute(self.char_db_drop)
                logging.info("Executed drop script")
                d_cursor.close()
            self.conn.close()

        request.addfinalizer(drop_tables) 
Example #4
Source File: importdb.py    From firmanal with MIT License 7 votes vote down vote up
def process(iid, infile):
    dbh = psycopg2.connect(database="firmware", user="firmadyne",
                           password="firmadyne", host="127.0.0.1")
    cur = dbh.cursor()

    (files, links) = getFileHashes(infile)

    oids = getOids(files, cur)

    fdict = dict([(h, (filename, uid, gid, mode, mime, score)) \
            for (filename, h, uid, gid, mode, mime, score) in files])

    file2oid = [(fdict[h], oid) for (h, oid) in oids.items()]

    insertObjectToImage(iid, file2oid, links, cur)

    dbh.commit()

    dbh.close() 
Example #5
Source File: pgexecute.py    From pgcli with BSD 3-Clause "New" or "Revised" License 7 votes vote down vote up
def __init__(
        self,
        database=None,
        user=None,
        password=None,
        host=None,
        port=None,
        dsn=None,
        **kwargs,
    ):
        self._conn_params = {}
        self.conn = None
        self.dbname = None
        self.user = None
        self.password = None
        self.host = None
        self.port = None
        self.server_version = None
        self.extra_args = None
        self.connect(database, user, password, host, port, dsn, **kwargs)
        self.reset_expanded = None 
Example #6
Source File: makeNetwork.py    From firmanal with MIT License 7 votes vote down vote up
def insert_ip (iid, ip):
    import psycopg2
    db = psycopg2.connect (dbname = "firmware",
                           user = "firmadyne",
                           password = "firmadyne",
                           host = "127.0.0.1")
    try:
        cur = db.cursor()
        cur.execute("UPDATE image SET ip='" + ip + "' WHERE id=" + iid)
        db.commit()
    except BaseException:
        ret = False
        traceback.print_exc()
        db.rollback()
    finally:
        if cur:
            cur.close() 
Example #7
Source File: estimators.py    From naru with Apache License 2.0 7 votes vote down vote up
def __init__(self, database, relation, port=None):
        """Postgres estimator (i.e., EXPLAIN).  Must have the PG server live.
        E.g.,
            def MakeEstimators():
                return [Postgres('dmv', 'vehicle_reg', None), ...]
        Args:
          database: string, the database name.
          relation: string, the relation name.
          port: int, the port.
        """
        import psycopg2

        super(Postgres, self).__init__()

        self.conn = psycopg2.connect(database=database, port=port)
        self.conn.autocommit = True
        self.cursor = self.conn.cursor()

        self.cursor.execute('analyze ' + relation + ';')
        self.conn.commit()

        self.database = database
        self.relation = relation 
Example #8
Source File: init_pg.py    From cassh with Apache License 2.0 6 votes vote down vote up
def init_pg(pg_conn):
    """
    Initialize pg database
    """
    if pg_conn is None:
        print('I am unable to connect to the database')
        sys.exit(1)
    cur = pg_conn.cursor()

    sql_files = [f for f in listdir(SQL_SERVER_PATH) if isfile(join(SQL_SERVER_PATH, f))]

    for sql_file in sql_files:
        with open('%s/%s' % (SQL_SERVER_PATH, sql_file), 'r') as sql_model_file:
            cur.execute(sql_model_file.read())
            pg_conn.commit()

    cur.close()
    pg_conn.close() 
Example #9
Source File: clean_pg.py    From cassh with Apache License 2.0 6 votes vote down vote up
def clean_pg(pg_conn):
    """
    Clean pg database
    """
    if pg_conn is None:
        print('I am unable to connect to the database')
        sys.exit(1)
    cur = pg_conn.cursor()

    cur.execute(
        """
        DELETE FROM USERS
        """)
    cur.execute(
        """
        DELETE FROM REVOCATION
        """)
    pg_conn.commit()

    cur.close()
    pg_conn.close() 
Example #10
Source File: ncbi.py    From hgvs with Apache License 2.0 6 votes vote down vote up
def _connect(self):
        if self.application_name is None:
            st = inspect.stack()
            self.application_name = os.path.basename(st[-1][1])
        conn_args = dict(
            host=self.url.hostname,
            port=self.url.port,
            database=self.url.database,
            user=self.url.username,
            password=self.url.password,
            application_name=self.application_name + "/" + hgvs.__version__,
        )
        if self.pooling:
            _logger.info("Using UTA ThreadedConnectionPool")
            self._pool = psycopg2.pool.ThreadedConnectionPool(
                hgvs.global_config.uta.pool_min, hgvs.global_config.uta.pool_max, **conn_args)
        else:
            self._conn = psycopg2.connect(**conn_args)
            self._conn.autocommit = True

        self._ensure_schema_exists()

        # remap sqlite's ? placeholders to psycopg2's %s
        self._queries = {k: v.replace('?', '%s') for k, v in six.iteritems(self._queries)} 
Example #11
Source File: pool.py    From pg_simple with BSD 3-Clause "New" or "Revised" License 6 votes vote down vote up
def _connect(self, key=None):
        """Create a new connection and assign it to 'key' if not None."""
        if self._dsn:
            conn = psycopg2.connect(self._dsn)
        else:
            conn = psycopg2.connect(**self._db_config)

        if not self._disable_pooling:
            if key is not None:
                self._used[key] = conn
                self._rused[id(conn)] = key
                self._tused[id(conn)] = time.time()
            else:
                self._pool.append(conn)

        self._log('Connection created %s' % conn)
        return conn 
Example #12
Source File: createdb.py    From binaryanalysis with Apache License 2.0 6 votes vote down vote up
def licensefossology((packages)):
	## Also run FOSSology. This requires that the user has enough privileges to actually connect to the
	## FOSSology database, for example by being in the correct group.
	fossologyres = []
	fossscanfiles = map(lambda x: os.path.join(x[2], x[3]), packages)
	scanargs = ["/usr/share/fossology/nomos/agent/nomossa"] + fossscanfiles
	p2 = subprocess.Popen(scanargs, stdin=subprocess.PIPE, stdout=subprocess.PIPE)
	(stanout, stanerr) = p2.communicate()
	if "FATAL" in stanout:
		## TODO: better error handling
		return None
	else:
		fosslines = stanout.strip().split("\n")
		for j in range(0,len(fosslines)):
			fossysplit = fosslines[j].strip().rsplit(" ", 1)
			licenses = fossysplit[-1].split(',')
			fossologyres.append((packages[j][5], set(licenses)))
	return fossologyres

## TODO: get rid of ninkaversion before we call this method
## TODO: process more files at once to reduce overhead of calling ctags 
Example #13
Source File: base.py    From GTDWeb with GNU General Public License v2.0 6 votes vote down vote up
def get_connection_params(self):
        settings_dict = self.settings_dict
        # None may be used to connect to the default 'postgres' db
        if settings_dict['NAME'] == '':
            from django.core.exceptions import ImproperlyConfigured
            raise ImproperlyConfigured(
                "settings.DATABASES is improperly configured. "
                "Please supply the NAME value.")
        conn_params = {
            'database': settings_dict['NAME'] or 'postgres',
        }
        conn_params.update(settings_dict['OPTIONS'])
        conn_params.pop('isolation_level', None)
        if settings_dict['USER']:
            conn_params['user'] = settings_dict['USER']
        if settings_dict['PASSWORD']:
            conn_params['password'] = force_str(settings_dict['PASSWORD'])
        if settings_dict['HOST']:
            conn_params['host'] = settings_dict['HOST']
        if settings_dict['PORT']:
            conn_params['port'] = settings_dict['PORT']
        return conn_params 
Example #14
Source File: base.py    From GTDWeb with GNU General Public License v2.0 6 votes vote down vote up
def get_new_connection(self, conn_params):
        connection = Database.connect(**conn_params)

        # self.isolation_level must be set:
        # - after connecting to the database in order to obtain the database's
        #   default when no value is explicitly specified in options.
        # - before calling _set_autocommit() because if autocommit is on, that
        #   will set connection.isolation_level to ISOLATION_LEVEL_AUTOCOMMIT.
        options = self.settings_dict['OPTIONS']
        try:
            self.isolation_level = options['isolation_level']
        except KeyError:
            self.isolation_level = connection.isolation_level
        else:
            # Set the isolation level to the value from OPTIONS.
            if self.isolation_level != connection.isolation_level:
                connection.set_session(isolation_level=self.isolation_level)

        return connection 
Example #15
Source File: mihome.py    From goodbye-mihome with BSD 2-Clause "Simplified" License 6 votes vote down vote up
def send_command(command, timeout=10):
    _, port = MULTICAST.get('mihome')
    if isinstance(command.get('data'), dict):
        command['data'] = json.dumps(command['data'])
    address = get_store().get('gateway_addr')
    if address is None:
        print("Didn't receive any heartbeat from gateway yet. Delaying request for 10 seconds.")
        time.sleep(10)
    sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    sock.settimeout(timeout)
    sock.connect((address, port))
    sock.send(json.dumps(command).encode('ascii'))
    data = None
    try:
        data, addr = sock.recvfrom(SOCKET_BUFSIZE)
    except ConnectionRefusedError:
        print("send_command :: recvfrom() connection refused: {}:{}".format(address.decode(), port))
    except socket.timeout:
        print("send_command :: recvfrom() timed out: {}:{}".format(address.decode(), port))
    finally:
        sock.close()
    return data 
Example #16
Source File: connection.py    From selene-backend with GNU Affero General Public License v3.0 6 votes vote down vote up
def connect_to_db(connection_config: DatabaseConnectionConfig):
    """
    Return a connection to the mycroft database for the specified user.

    Use this function when connecting to a database in an application that
    does not benefit from connection pooling (e.g. a batch script or a
    python notebook)

    :param connection_config: data needed to establish a connection
    :return: database connection
    """
    log_msg = 'establishing connection to the {db_name} database'
    _log.info(log_msg.format(db_name=connection_config.db_name))
    db = connect(
        host=connection_config.host,
        dbname=connection_config.db_name,
        user=connection_config.user,
        password=connection_config.password,
        port=connection_config.port,
        cursor_factory=connection_config.cursor_factory,
        sslmode=connection_config.sslmode
    )
    db.autocommit = connection_config.autocommit

    return db 
Example #17
Source File: test_databases.py    From piicatcher with Apache License 2.0 6 votes vote down vote up
def create_tables(self, request):
        self.conn = psycopg2.connect(host="127.0.0.1",
                                     user="piiuser",
                                     password="p11secret",
                                     database="piidb")

        self.conn.autocommit = True

        with self.conn.cursor() as cursor:
            self.execute_script(cursor, pii_data_script)
            self.execute_script(cursor, self.second_schema)
            cursor.close()

        def drop_tables():
            with self.conn.cursor() as d_cursor:
                d_cursor.execute(self.pii_db_drop)
                logging.info("Executed drop script")
                d_cursor.close()
            self.conn.close()

        request.addfinalizer(drop_tables) 
Example #18
Source File: bootstrap_mycroft_db.py    From selene-backend with GNU Affero General Public License v3.0 6 votes vote down vote up
def __init__(self, db_name, user=None):
        db_host = environ.get('DB_HOST', '127.0.0.1')
        db_port = environ.get('DB_PORT', 5432)
        db_ssl_mode = environ.get('DB_SSLMODE')
        if db_name in ('postgres', 'defaultdb', 'mycroft_template'):
            db_user = environ.get('POSTGRES_USER', 'postgres')
            db_password = environ.get('POSTGRES_PASSWORD')
        else:
            db_user = environ.get('DB_USER', 'selene')
            db_password = environ['DB_PASSWORD']

        if user is not None:
            db_user = user

        self.db = connect(
            dbname=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port,
            sslmode=db_ssl_mode
        )
        self.db.autocommit = True 
Example #19
Source File: test_databases.py    From piicatcher with Apache License 2.0 6 votes vote down vote up
def create_tables(self, request):
        self.conn = pymysql.connect(host="127.0.0.1",
                                    user="piiuser",
                                    password="p11secret",
                                    database="piidb"
                                    )

        with self.conn.cursor() as cursor:
            self.execute_script(cursor, char_data_types)
            cursor.execute("commit")
            cursor.close()

        def drop_tables():
            with self.conn.cursor() as drop_cursor:
                self.execute_script(drop_cursor, self.char_db_drop)
                logging.info("Executed drop script")
                drop_cursor.close()
            self.conn.close()

        request.addfinalizer(drop_tables) 
Example #20
Source File: postgresql_user_provider.py    From cfn-postgresql-user-provider with Apache License 2.0 5 votes vote down vote up
def update(self):
        try:
            self.connect()
            if self.allow_update:
                self.update_password()
            else:
                self.fail('Only the password of %s can be updated' % self.user)
        except Exception as e:
            self.fail('Failed to update the user, %s' % e)
        finally:
            self.close() 
Example #21
Source File: db.py    From face-search with MIT License 5 votes vote down vote up
def __init__(self, db, user, password):
        try:
            self._db_con = psycopg2.connect(dbname=db, user=user,
                                            password=password)
            self._db_cur = self._db_con.cursor()
        except Exception as e:
            print e 
Example #22
Source File: postgresql_user_provider.py    From cfn-postgresql-user-provider with Apache License 2.0 5 votes vote down vote up
def create(self):
        try:
            self.connect()
            self.create_user()
            self.physical_resource_id = self.url
        except Exception as e:
            self.physical_resource_id = 'could-not-create'
            self.fail('Failed to create user, %s' % e)
        finally:
            self.close() 
Example #23
Source File: JobDispatcher.py    From ReadableWebProxy with BSD 3-Clause "New" or "Revised" License 5 votes vote down vote up
def open_database_connection(self):

		self.db_interface = psycopg2.connect(
				database = settings.DATABASE_DB_NAME,
				user     = settings.DATABASE_USER,
				password = settings.DATABASE_PASS,
				host     = settings.DATABASE_IP,
			) 
Example #24
Source File: postgresql_user_provider.py    From cfn-postgresql-user-provider with Apache License 2.0 5 votes vote down vote up
def connect(self):
        log.info('connecting to database %s on port %d as user %s', self.host, self.port, self.dbowner)
        try:
            self.connection = psycopg2.connect(**self.connect_info)
            self.connection.set_session(autocommit=True)
        except Exception as e:
            raise ValueError('Failed to connect, %s' % e) 
Example #25
Source File: pgmigrate.py    From pgmigrate with PostgreSQL License 5 votes vote down vote up
def _create_raw_connection(conn_string, logger=LOG):
    conn = psycopg2.connect(conn_string, connection_factory=LoggingConnection)
    conn.initialize(logger)

    return conn 
Example #26
Source File: database_and_connection.py    From pgmigrate with PostgreSQL License 5 votes vote down vote up
def step_impl(context):
    context.conn = None
    conn = psycopg2.connect('dbname=postgres')
    conn.autocommit = True
    cur = conn.cursor()
    cur.execute("select pg_terminate_backend(pid) " +
                "from pg_stat_activity where datname='pgmigratetest'")
    cur.execute('drop database if exists pgmigratetest')
    cur.execute('create database pgmigratetest')
    context.conn = psycopg2.connect('dbname=pgmigratetest') 
Example #27
Source File: metrics.py    From cf-mendix-buildpack with Apache License 2.0 5 votes vote down vote up
def _get_db_conn(self):
        if self.db and self.db.closed != 0:
            self.db.close()
            self.db = None

        if not self.db:
            # get_database config may return None or empty
            db_config = database.get_config()
            if not db_config or "DatabaseType" not in db_config:
                raise ValueError(
                    "Database not set as VCAP or DATABASE_URL. Check "
                    "documentation to see supported configuration options."
                )
            if db_config["DatabaseType"] != "PostgreSQL":
                raise Exception(
                    "Metrics only supports postgresql, not %s"
                    % db_config["DatabaseType"]
                )

            host, port = self._get_db_host_and_port(db_config["DatabaseHost"])
            self.db = psycopg2.connect(
                "options='-c statement_timeout=60s'",
                database=db_config["DatabaseName"],
                user=db_config["DatabaseUserName"],
                password=db_config["DatabasePassword"],
                host=host,
                port=port,
                connect_timeout=3,
            )
            self.db.set_isolation_level(
                psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT
            )
        return self.db 
Example #28
Source File: metrics.py    From cf-mendix-buildpack with Apache License 2.0 5 votes vote down vote up
def _get_database_tcp_latency(self, timeout: float = 5):
        db_config = database.get_config()
        host, port = self._get_db_host_and_port(db_config["DatabaseHost"])
        # New Socket and Time out
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        sock.settimeout(timeout)

        # Start a timer
        s_start = timer()

        # Try to Connect
        try:
            sock.connect((host, int(port)))
            sock.shutdown(socket.SHUT_RD)
            sock.close()

        # If something bad happens, the latency is None
        except socket.timeout:
            return None
        except OSError:
            return None

        # Stop Timer
        s_stop = timer()
        s_runtime = "%.2f" % (1000 * (s_stop - s_start))

        return s_runtime 
Example #29
Source File: delete_logs_and_events_from_db.py    From cloudify-manager-blueprints with Apache License 2.0 5 votes vote down vote up
def _connect():
    try:
        conn = psycopg2.connect(
            database=conf.postgresql_db_name,
            user=conf.postgresql_username,
            password=conf.postgresql_password,
            host=conf.postgresql_host,
            port=str(POSTGRESQL_DEFAULT_PORT)
        )
        conn.autocommit = True
        return conn
    except psycopg2.DatabaseError as e:
        raise Exception('Error during connection to postgres: {0}'
                        .format(str(e))) 
Example #30
Source File: test_sample_database.py    From piicatcher with Apache License 2.0 5 votes vote down vote up
def get_connection(cls):
        return psycopg2.connect(host="127.0.0.1",
                                user="piiuser",
                                password="p11secret",
                                database="piidb"
                                )