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

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

Support the hstore data type

Added adaptation and typecasting of the hstore type as Python dictionaries.
For the typecasting, a fast parser has been added to the C extension.

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