source: trunk/docs/contents/pgdb/types.rst @ 774

Last change on this file since 774 was 774, checked in by cito, 4 years ago

Add support for JSON and JSONB to pg and pgdb

This adds all necessary functions to make PyGreSQL automatically
convert between JSON columns and Python objects representing them.

The documentation has also been updated, see there for the details.

Also, tuples automatically bind to ROW expressions in pgdb now.

File size: 4.6 KB
Line 
1Type -- Type objects and constructors
2=====================================
3
4.. py:currentmodule:: pgdb
5
6Type constructors
7-----------------
8
9For binding to an operation's input parameters, PostgreSQL needs to have
10the input in a particular format.  However, from the parameters to the
11:meth:`Cursor.execute` and :meth:`Cursor.executemany` methods it is not
12always obvious as which PostgreSQL data types they shall be bound.
13For instance, a Python string could be bound as a simple ``char`` value,
14or also as a ``date`` or a ``time``.  Or a list could be bound as a
15``array`` or a ``json`` object.  To make the intention clear in such cases,
16you can wrap the parameters in type helper objects.  PyGreSQL provides the
17constructors defined below to create such objects that can hold special values.
18When passed to the cursor methods, PyGreSQL can then detect the proper type
19of the input parameter and bind it accordingly.
20
21The :mod:`pgdb` module exports the following constructors that as part of
22the DB-API 2 standard:
23
24.. function:: Date(year, month, day)
25
26    Construct an object holding a date value
27
28.. function:: Time(hour, minute=0, second=0, microsecond=0)
29
30    Construct an object holding a time value
31
32.. function:: Timestamp(year, month, day, hour=0, minute=0, second=0, microsecond=0)
33
34    Construct an object holding a time stamp value
35
36.. function:: DateFromTicks(ticks)
37
38    Construct an object holding a date value from the given *ticks* value
39
40.. function:: TimeFromTicks(ticks)
41
42    Construct an object holding a time value from the given *ticks* value
43
44.. function:: TimestampFromTicks(ticks)
45
46    Construct an object holding a time stamp from the given *ticks* value
47
48.. function:: Binary(bytes)
49
50    Construct an object capable of holding a (long) binary string value
51
52Additionally, PyGreSQL provides the following constructors for PostgreSQL
53specific data types:
54
55.. function:: Json(obj, [encode])
56
57    Construct a wrapper for holding an object serializable to JSON.
58
59    You can pass an optional serialization function as a parameter.
60    By default, PyGreSQL uses :func:`json.dumps` to serialize it.
61
62Example for using a type constructor::
63
64    >>> cursor.execute("create table jsondata (data jsonb)")
65    >>> data = {'id': 1, 'name': 'John Doe', 'kids': ['Johnnie', 'Janie']}
66    >>> cursor.execute("insert into jsondata values (%s)", [Json(data)])
67
68.. note::
69
70    SQL NULL values are always represented by the Python *None* singleton
71    on input and output.
72
73Type objects
74------------
75
76.. class:: Type
77
78The :attr:`Cursor.description` attribute returns information about each
79of the result columns of a query. The *type_code* must compare equal to one
80of the :class:`Type` objects defined below.  Type objects can be equal to
81more than one type code (e.g. :class:`DATETIME` is equal to the type codes
82for ``date``, ``time`` and ``timestamp`` columns).
83
84The pgdb module exports the following :class:`Type` objects as part of the
85DB-API 2 standard:
86
87.. object:: STRING
88
89    Used to describe columns that are string-based (e.g. ``char``, ``varchar``, ``text``)
90
91.. object:: BINARY
92
93    Used to describe (long) binary columns (``bytea``)
94
95.. object:: NUMBER
96
97    Used to describe numeric columns (e.g. ``int``, ``float``, ``numeric``, ``money``)
98
99.. object:: DATETIME
100
101    Used to describe date/time columns (e.g. ``date``, ``time``, ``timestamp``, ``interval``)
102
103.. object:: ROWID
104
105    Used to describe the ``oid`` column of PostgreSQL database tables
106
107.. note::
108
109  The following more specific types are not part of the DB-API 2 standard.
110
111.. object:: BOOL
112
113    Used to describe ``boolean`` columns
114
115.. object:: SMALLINT
116
117    Used to describe ``smallint`` columns
118
119.. object:: INTEGER
120
121    Used to describe ``integer`` columns
122
123.. object:: LONG
124
125    Used to describe ``bigint`` columns
126
127.. object:: FLOAT
128
129    Used to describe ``float`` columns
130
131.. object:: NUMERIC
132
133    Used to describe ``numeric`` columns
134
135.. object:: MONEY
136
137    Used to describe ``money`` columns
138
139.. object:: DATE
140
141    Used to describe ``date`` columns
142
143.. object:: TIME
144
145    Used to describe ``time`` columns
146
147.. object:: TIMESTAMP
148
149    Used to describe ``timestamp`` columns
150
151.. object:: INTERVAL
152
153    Used to describe date and time ``interval`` columns
154
155.. object:: JSON
156
157    Used to describe ``json`` and ``jsonb`` columns
158
159Example for using some type objects::
160
161    >>> cursor = con.cursor()
162    >>> cursor.execute("create table jsondata (created date, data jsonb)")
163    >>> cursor.execute("select * from jsondata")
164    >>> (created, data) = (d.type_code for d in cursor.description)
165    >>> created == DATE
166    True
167    >>> created == DATETIME
168    True
169    >>> created == TIME
170    False
171    >>> data == JSON
172    True
173    >>> data == STRING
174    False
Note: See TracBrowser for help on using the repository browser.