PyGreSQL

https://www.pygresql.org/
Admin
PyGreSQL is an open-source Python module that interfaces to a PostgreSQL database, wrapping the...
Tokens:163,155
Snippets:1,314
Trust Score:5
Update:1 month ago
Show doc for...
Tokens:
Raw
### Define Setup file variables

Source: https://www.pygresql.org/_sources/contents/install.rst.txt

Reference variables for the Setup file configuration.

```text
PGINC = path to the PostgreSQL client include files (see above)
PSINC = path to the PostgreSQL server include files (see above)
PGLIB = path to the PostgreSQL object code libraries (see above)
```

--------------------------------

### Build and install with Distutils

Source: https://www.pygresql.org/_sources/contents/install.rst.txt

Standard command to build and install the package from source using setup.py.

```bash
python setup.py install
```

--------------------------------

### Add module to Python Setup file

Source: https://www.pygresql.org/_sources/contents/install.rst.txt

Register the _pg module in the Python source hierarchy Setup file.

```text
_pg  pgmodule.c -I$PGINC -I$PSINC -L$PGLIB -lpq
```

--------------------------------

### Build and install PyGreSQL with Distutils

Source: https://www.pygresql.org/contents/install.html

Run this command to build and install PyGreSQL from the source tarball. Requires Python and PostgreSQL header files and pg_config for precompiled PostgreSQL.

```bash
python setup.py install

```

--------------------------------

### Install PyGreSQL with pip

Source: https://www.pygresql.org/_sources/contents/install.rst.txt

The standard command to install PyGreSQL using the pip package manager.

```bash
pip install PyGreSQL
```

--------------------------------

### Build PyGreSQL with custom features

Source: https://www.pygresql.org/_sources/contents/install.rst.txt

Examples of building the extension with specific feature flags enabled or disabled.

```bash
python setup.py build_ext --no-memory-size
```

```bash
python setup.py build_ext --memory-size
```

--------------------------------

### Test the PyGreSQL module

Source: https://www.pygresql.org/_sources/contents/install.rst.txt

Verify the installation by importing the module and performing a basic database query.

```bash
$ python
```

```python
>>> import _pg
>>> db = _pg.connect('thilo','localhost')
>>> db.query("INSERT INTO test VALUES ('ping','pong')")
18304
>>> db.query("SELECT * FROM test")
eins|zwei
----+----
ping|pong
(1 row)
```

--------------------------------

### DB.prepare and DB.query_prepared workflow

Source: https://www.pygresql.org/_sources/contents/pg/db_wrapper.rst.txt

Example of creating a named prepared statement and executing it repeatedly within a loop.

```python
db.prepare('change phone',
    "update employees set phone=$2 where ein=$1")
while True:
    ein = input("Employee ID? ")
    if not ein:
        break
    phone = input("Phone? ")
    db.query_prepared('change phone', ein, phone)
```

--------------------------------

### Test the compiled module

Source: https://www.pygresql.org/contents/install.html

Verify the installation by importing the _pg module and performing basic database operations.

```python
$ python

>>> import _pg
>>> db = _pg.connect('thilo','localhost')
>>> db.query("INSERT INTO test VALUES ('ping','pong')")
18304
>>> db.query("SELECT * FROM test")
eins|zwei
----+----
ping|pong
(1 row)
```

--------------------------------

### get_defopt / set_defopt

Source: https://www.pygresql.org/_sources/contents/pg/module.rst.txt

Get and set the default connection options. The setter returns the previous setting.

```APIDOC
## get_defopt()

### Description
Get the default connection options specification.

### Method
Function call

### Parameters
None

### Returns
- **options** (str or None) - The current default options specification, or None if environment variables should be used.

### Raises
- TypeError - too many arguments

## set_defopt(options)

### Description
Set the default connection options for new connections. If None is supplied, environment variables will be used in future connections.

### Method
Function call

### Parameters
- **options** (str or None) - The new default connection options. Use None to use environment variables.

### Returns
- **previous** (str or None) - The previous default options specification.

### Raises
- TypeError - bad argument type, or too many arguments
```

--------------------------------

### DB.start()

Source: https://www.pygresql.org/_sources/contents/pg/db_wrapper.rst.txt

This is the same as the DB.begin method.

```APIDOC
## DB.start()

### Description
This is the same as the DB.begin method.
```

--------------------------------

### List build options

Source: https://www.pygresql.org/_sources/contents/install.rst.txt

Command to display all available build options for the extension.

```bash
python setup.py build_ext --help
```

--------------------------------

### DB.get_parameter(parameter)

Source: https://www.pygresql.org/_sources/contents/pg/db_wrapper.rst.txt

Gets the value of run-time parameters. Supports getting a single parameter, multiple parameters, or all parameters.

```APIDOC
## DB.get_parameter(parameter)

### Description
Get the value of run-time parameters. If the parameter is a string, the return value will also be a string that is the current setting of the run-time parameter with that name. You can get several parameters at once by passing a list, set or dict. When passing a list of parameter names, the return value will be a corresponding list of parameter settings. When passing a set of parameter names, a new dict will be returned, mapping these parameter names to their settings. Finally, if you pass a dict as parameter, its values will be set to the current parameter settings corresponding to its keys. By passing the special name 'all' as the parameter, you can get a dict of all existing configuration parameters.

### Parameters
- **parameter** (str, tuple, list or dict) - Required - The run-time parameter(s) to get

### Returns
- str, list or dict - The current value(s) of the run-time parameter(s)

### Raises
- TypeError - Invalid parameter type(s)
- pg.ProgrammingError - Invalid parameter name(s)
```

--------------------------------

### Show all PyGreSQL build options

Source: https://www.pygresql.org/contents/install.html

Run this command to list all possible build options for PyGreSQL.

```bash
python setup.py build_ext --help

```

--------------------------------

### get_pqlib_version()

Source: https://www.pygresql.org/contents/pg/index.html

Gets the version of libpq.

```APIDOC
## get_pqlib_version()

### Description
Gets the version of libpq.

### Method
Function

### Endpoint
`get_pqlib_version()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
Returns the version string.

#### Response Example
No response example provided.
```

--------------------------------

### Connect to a PostgreSQL database

Source: https://www.pygresql.org/_sources/contents/pg/module.rst.txt

Demonstrates various ways to open a connection using positional arguments, keyword arguments, a connection string, or a connection URI.

```python
import pg

con1 = pg.connect('testdb', 'myhost', 5432, None, 'bob', None)
con2 = pg.connect(dbname='testdb', host='myhost', user='bob')
con3 = pg.connect('host=myhost user=bob dbname=testdb connect_timeout=10')
con4 = pg.connect('postgresql://bob@myhost/testdb?connect_timeout=10')
```

--------------------------------

### escape_string example: safely insert user input into SQL query

Source: https://www.pygresql.org/contents/pg/module.html

Use pg.escape_string to escape a string before embedding it in an SQL command to prevent SQL injection. The example reads a name from input and uses it in a query.

```python
name = input("Name? ")
phone = con.query("select phone from employees"
                  f" where name='{escape_string(name)}'").singlescalar()

```

--------------------------------

### Connect to a Database with pg.DB

Source: https://www.pygresql.org/_sources/contents/tutorial.rst.txt

Import the DB wrapper class and create an instance with connection parameters.

```python
>>> from pg import DB
>>> db = DB(dbname='testdb', host='pgserver', port=5432,
...     user='scott', passwd='tiger')
```

--------------------------------

### TypeCache.get_fields()

Source: https://www.pygresql.org/contents/pgdb/index.html

Gets the fields of a type from the cache.

```APIDOC
## TypeCache.get_fields() n
### Description
Gets the fields of a type from the cache.

### Method
Method

### Endpoint
TypeCache.get_fields()

### Parameters
No parameters documented.

### Returns
The fields.
```

--------------------------------

### TypeCache.get_typecast()

Source: https://www.pygresql.org/contents/pgdb/index.html

Gets the typecast function for a type from the cache.

```APIDOC
## TypeCache.get_typecast() n
### Description
Gets the typecast function for a type from the cache.

### Method
Method

### Endpoint
TypeCache.get_typecast() n
### Parameters
No parameters documented.

### Returns
The typecast function.
```

--------------------------------

### Connect to PostgreSQL with DB parameters

Source: https://www.pygresql.org/_sources/contents/postgres/basic.rst.txt

Connects to a PostgreSQL database by passing the database name, host, port, and login credentials as parameters to the DB constructor.

```python
>>> db = DB(dbname='testdb', host='pgserver', port=5432,
...     user='scott', passwd='tiger')
```

--------------------------------

### Connection.parameter()

Source: https://www.pygresql.org/contents/pg/index.html

Get a current server parameter setting.

```APIDOC
## Connection.parameter()

### Description
Get a current server parameter setting.

### Method
Method

### Endpoint
`Connection.parameter()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
Returns the parameter value.

#### Response Example
No response example provided.
```

--------------------------------

### get_typecast() / set_typecast()

Source: https://www.pygresql.org/contents/pg/index.html

Get or set custom typecasting.

```APIDOC
## get_typecast() / set_typecast()

### Description
Get or set custom typecasting.

### Method
Function

### Endpoint
`get_typecast()` / `set_typecast()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
`get_typecast()` returns the current typecast function. `set_typecast()` returns nothing.

#### Response Example
No response example provided.
```

--------------------------------

### List tables with db.get_tables()

Source: https://www.pygresql.org/_sources/contents/postgres/basic.rst.txt

Returns a list of all tables in the database. The output shows the tables created in the previous example.

```python
>>> db.get_tables()
['public.cities', 'public.weather']
```

--------------------------------

### Create and execute a prepared statement with DB.prepare and DB.query_prepared

Source: https://www.pygresql.org/contents/pg/db_wrapper.html

Shows how to create a prepared statement named 'change phone' that updates an employee's phone number using positional parameters $1 and $2, then repeatedly prompts for employee ID and phone and executes the statement with DB.query_prepared. Note that unnamed statements have a limited lifetime and can be replaced or destroyed, so named queries are recommended.

```python
db.prepare('change phone',
    "update employees set phone=$2 where ein=$1")
while True:
    ein = input("Employee ID? ")
    if not ein:
        break
    phone = input("Phone? ")
    db.query_prepared('change phone', ein, phone)

```

--------------------------------

### Connection.transaction()

Source: https://www.pygresql.org/_sources/contents/pg/connection.rst.txt

Gets the current in-transaction status of the server.

```APIDOC
## Connection.transaction()

### Description
Gets the current in-transaction status of the server.

### Method
N/A (Python method)

### Parameters
None

### Returns
- **int** - The current in-transaction status. Possible values: TRANS_IDLE (currently idle), TRANS_ACTIVE (a command is in progress), TRANS_INTRANS (idle, in a valid transaction block), TRANS_INERROR (idle, in a failed transaction block), TRANS_UNKNOWN (connection is bad).

### Raises
- **TypeError** - Too many (any) arguments.
- **TypeError** - Invalid connection.
```

--------------------------------

### Connection.get_notice_receiver() / set_notice_receiver()

Source: https://www.pygresql.org/contents/pg/index.html

Get or set the custom notice receiver.

```APIDOC
## Connection.get_notice_receiver() / set_notice_receiver()

### Description
Get or set the custom notice receiver.

### Method
Method

### Endpoint
`Connection.get_notice_receiver()` / `Connection.set_notice_receiver()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
`get_notice_receiver()` returns the current notice receiver. `set_notice_receiver()` returns nothing.

#### Response Example
No response example provided.
```

--------------------------------

### Connection.get_cast_hook() / set_cast_hook()

Source: https://www.pygresql.org/contents/pg/index.html

Get or set the fallback typecast function.

```APIDOC
## Connection.get_cast_hook() / set_cast_hook()

### Description
Get or set the fallback typecast function.

### Method
Method

### Endpoint
`Connection.get_cast_hook()` / `Connection.set_cast_hook()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
`get_cast_hook()` returns the current cast hook. `set_cast_hook()` returns nothing.

#### Response Example
No response example provided.
```

--------------------------------

### Connect to PostgreSQL with PyGreSQL

Source: https://www.pygresql.org/contents/postgres/advanced.html

Establishes a connection to the PostgreSQL database and assigns the query method to a variable for use in subsequent examples.

```python
>>> from pg import DB
>>> db = DB()
>>> query = db.query

```

--------------------------------

### get_jsondecode() / set_jsondecode()

Source: https://www.pygresql.org/contents/pg/index.html

Get or set the JSON decoding behavior.

```APIDOC
## get_jsondecode() / set_jsondecode()

### Description
Get or set the JSON decoding behavior.

### Method
Function

### Endpoint
`get_jsondecode()` / `set_jsondecode()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
`get_jsondecode()` returns the current setting. `set_jsondecode()` returns nothing.

#### Response Example
No response example provided.
```

--------------------------------

### pg.set_defopt(options)

Source: https://www.pygresql.org/contents/pg/module.html

Sets the default connection options for new connections. If None is supplied, environment variables will be used in future connections. Returns the previous setting.

```APIDOC
## pg.set_defopt(options)

### Description
Set the default connection options. Sets the default connection options value for new connections. If None is supplied as parameter, environment variables will be used in future connections. Returns the previous setting for default options.

### Method
Function call

### Endpoint
pg.set_defopt(options)

### Parameters
- **options** (str or None) - Required - the new default connection options

### Returns
- **Return type**: str or None
- **Description**: previous default options specification

### Raises
- **TypeError** – bad argument type, or too many arguments

### Example
```python
import pg
previous = pg.set_defopt("host=localhost port=5432")
```
```

--------------------------------

### get_datestyle() / set_datestyle()

Source: https://www.pygresql.org/contents/pg/index.html

Get or set the assumed fixed date style.

```APIDOC
## get_datestyle() / set_datestyle()

### Description
Get or set the assumed fixed date style.

### Method
Function

### Endpoint
`get_datestyle()` / `set_datestyle()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
`get_datestyle()` returns the current date style. `set_datestyle()` returns nothing.

#### Response Example
No response example provided.
```

--------------------------------

### Connect to PostgreSQL and create query object

Source: https://www.pygresql.org/_sources/contents/postgres/advanced.rst.txt

Establishes a connection to the PostgreSQL database and creates a query object for executing SQL commands.

```python
>>> from pg import DB
>>> db = DB()
>>> query = db.query
```

--------------------------------

### get_typecast

Source: https://www.pygresql.org/_sources/contents/pgdb/module.rst.txt

Gets the global cast function for the given database type.

```APIDOC
## get_typecast(typ)

### Description
Gets the global cast function for the given database type.

### Parameters
- **typ** (str) - Required - PostgreSQL type name or type code.

### Returns
- **function or None** - The typecast function for the specified type.
```

--------------------------------

### Get Table Attributes

Source: https://www.pygresql.org/_sources/contents/tutorial.rst.txt

Retrieve the column names and types of a specific table.

```python
>>> db.get_attnames('fruits')
{'id': 'int', 'name': 'text'}
```

--------------------------------

### Connection.prepare(name, command)

Source: https://www.pygresql.org/contents/pg/connection.html

Creates a prepared statement with the given name and SQL command for later execution with query_prepared(). The command may contain positional parameters like $1, $2, etc.

```APIDOC
## Connection.prepare(name, command)

### Description
Creates a prepared statement with the given name and SQL command for later execution with query_prepared(). The command may contain positional parameters like $1, $2, etc.

### Method
prepare

### Parameters
- **name** (str) - Required - Name of the prepared statement.
- **command** (str) - Required - SQL command to prepare.

### Returns
- **None** - No return value.

### Raises
- **TypeError** - Bad argument types, or wrong number of arguments.
- **TypeError** - Invalid connection.
- **pg.ProgrammingError** - Error in query or duplicate query.

### Notes
An empty name creates an unnamed statement, replacing any existing unnamed statement. Otherwise, a pg.ProgrammingError is raised if the name is already defined. Positional parameters in the command must be supplied later via query_prepared(). New in version 5.1.
```

--------------------------------

### get_bytea_escaped() / set_bytea_escaped()

Source: https://www.pygresql.org/contents/pg/index.html

Get or set whether bytea data is returned escaped.

```APIDOC
## get_bytea_escaped() / set_bytea_escaped()

### Description
Get or set whether bytea data is returned escaped.

### Method
Function

### Endpoint
`get_bytea_escaped()` / `set_bytea_escaped()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
`get_bytea_escaped()` returns the current setting. `set_bytea_escaped()` returns nothing.

#### Response Example
No response example provided.
```

--------------------------------

### get_array() / set_array()

Source: https://www.pygresql.org/contents/pg/index.html

Get or set whether arrays are returned as list objects.

```APIDOC
## get_array() / set_array()

### Description
Get or set whether arrays are returned as list objects.

### Method
Function

### Endpoint
`get_array()` / `set_array()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
`get_array()` returns the current setting. `set_array()` returns nothing.

#### Response Example
No response example provided.
```

--------------------------------

### Create EMP table and insert sample data

Source: https://www.pygresql.org/_sources/contents/postgres/func.rst.txt

Creates the EMP table and inserts five employee records.

```python
>>> query("""CREATE TABLE EMP (
...     name   text,
...     salary int4,
...     age f   int4,
...     dept   varchar(16))""")
>>> emps = ["'Sam', 1200, 16, 'toy'",
...     "'Claire', 5000, 32, 'shoe'",
...     "'Andy', -1000, 2, 'candy'",
...     "'Bill', 4200, 36, 'shoe'",
...     "'Ginger', 4800, 30, 'candy'"]
>>> for emp in emps:
...     query(f"INSERT INTO EMP VALUES ({emp})")
```

--------------------------------

### get_decimal_point() / set_decimal_point()

Source: https://www.pygresql.org/contents/pg/index.html

Get or set the decimal mark used for monetary values.

```APIDOC
## get_decimal_point() / set_decimal_point()

### Description
Get or set the decimal mark used for monetary values.

### Method
Function

### Endpoint
`get_decimal_point()` / `set_decimal_point()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
`get_decimal_point()` returns the current decimal mark. `set_decimal_point()` returns nothing.

#### Response Example
No response example provided.
```

--------------------------------

### Connect to PostgreSQL with pg.DB()

Source: https://www.pygresql.org/_sources/contents/postgres/basic.rst.txt

Creates a connection to the local PostgreSQL database using the default database name (same as the current user) and login. No parameters are required for this default connection.

```python
>>> from pg import DB
>>> db = DB()
```

--------------------------------

### get_decimal() / set_decimal()

Source: https://www.pygresql.org/contents/pg/index.html

Get or set the decimal type to be used for numeric values.

```APIDOC
## get_decimal() / set_decimal()

### Description
Get or set the decimal type to be used for numeric values.

### Method
Function

### Endpoint
`get_decimal()` / `set_decimal()`

### Parameters
No parameters documented.

### Request Example
No request example provided.

### Response
#### Success Response
`get_decimal()` returns the current decimal type. `set_decimal()` returns nothing.

#### Response Example
No response example provided.
```

--------------------------------

### DB.get(table, row, [keyname])

Source: https://www.pygresql.org/_sources/contents/pg/db_wrapper.rst.txt

Gets a row from a database table or view.

```APIDOC
## DB.get(table, row, [keyname])

### Description
Get a row from a database table or view. This method is the basic mechanism to get a single row. It assumes that the keyname specifies a unique row. It must be the name of a single column or a tuple of column names. If keyname is not specified, then the primary key for the table is used.

### Method
get

### Parameters
#### Required Parameters
- **table** (str) - Required - Name of table or view.
- **row** (dict or value) - Required - Either a dictionary or the value to be looked up.

#### Optional Parameters
- **keyname** (str) - Optional - Name of field to use as key.

### Returns
- **dict** - A dictionary - the keys are the attribute names, the values are the row values.

### Raises
- **pg.ProgrammingError** - Table has no primary key or missing privilege.
- **KeyError** - Missing key value for the row.
```

--------------------------------

### Configure paths using pg_config

Source: https://www.pygresql.org/_sources/contents/install.rst.txt

Use the pg_config tool to automatically set PostgreSQL include and library paths.

```bash
PGINC=`pg_config --includedir`
PSINC=`pg_config --includedir-server`
PGLIB=`pg_config --libdir`
```

--------------------------------

### Query.fieldname(num)

Source: https://www.pygresql.org/_sources/contents/pg/query.rst.txt

Get field name from its number. Useful for displaying a result.

```APIDOC
## Query.fieldname(num)

### Description
Get field name from its number. Useful for displaying a result.

### Method
Query.fieldname(num)

### Parameters
#### Path Parameters
- **num** (int) - Required - field number

### Returns
- **str** - field name

### Exceptions
- **TypeError** - invalid connection, bad parameter type, or too many parameters
- **ValueError** - invalid field number
```

--------------------------------

### pg.get_defopt()

Source: https://www.pygresql.org/contents/pg/module.html

Returns the current default connection options specification, or None if environment variables should be used. Environment variables are not looked up.

```APIDOC
## pg.get_defopt()

### Description
Get the default connection options. Returns the current default options specification, or None if environment variables should be used. Environment variables are not looked up.

### Method
Function call

### Endpoint
pg.get_defopt()

### Parameters
None

### Returns
- **Return type**: str or None
- **Description**: the current default options specification

### Raises
- **TypeError** – too many arguments

### Example
```python
import pg
current_options = pg.get_defopt()
```
```

--------------------------------

### Print contents of temporary table

Source: https://www.pygresql.org/_sources/contents/postgres/basic.rst.txt

Prints the contents of the temporary table 'temptab' created by the previous SELECT INTO example. The output shows the same row as the original query.

```python
>>> print(db.query("SELECT * from temptab"))
    city     |temp_lo|temp_hi|prcp|   date
-------------+-------+-------+----+----------
San Francisco|     46|     50|0.25|1994-11-27
(1 row)
```
Tokens:
Raw