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

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

Cast to proper type when adapting datetime values

Otherwise PostgreSQL needs to guess the type from the context
which is not always possible.

File size: 5.4 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], [second], [microsecond], [tzinfo])
31
32    Construct an object holding a time value
33
34.. function:: Timestamp(year, month, day, [hour], [minute], [second], [microsecond], [tzinfo])
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:: Hstore(dict)
64
65    Construct a wrapper for holding an hstore dictionary
66
67.. versionadded:: 5.0
68
69.. function:: Json(obj, [encode])
70
71    Construct a wrapper for holding an object serializable to JSON
72
73    You can pass an optional serialization function as a parameter.
74    By default, PyGreSQL uses :func:`json.dumps` to serialize it.
75
76.. function:: Literal(sql)
77
78    Construct a wrapper for holding a literal SQL string
79
80.. versionadded:: 5.0
81
82Example for using a type constructor::
83
84    >>> cursor.execute("create table jsondata (data jsonb)")
85    >>> data = {'id': 1, 'name': 'John Doe', 'kids': ['Johnnie', 'Janie']}
86    >>> cursor.execute("insert into jsondata values (%s)", [Json(data)])
87
88.. note::
89
90    SQL ``NULL`` values are always represented by the Python *None* singleton
91    on input and output.
92
93.. _type_objects:
94
95Type objects
96------------
97
98.. class:: Type
99
100The :attr:`Cursor.description` attribute returns information about each
101of the result columns of a query.  The *type_code* must compare equal to one
102of the :class:`Type` objects defined below.  Type objects can be equal to
103more than one type code (e.g. :class:`DATETIME` is equal to the type codes
104for ``date``, ``time`` and ``timestamp`` columns).
105
106The pgdb module exports the following :class:`Type` objects as part of the
107DB-API 2 standard:
108
109.. object:: STRING
110
111    Used to describe columns that are string-based (e.g. ``char``, ``varchar``, ``text``)
112
113.. object:: BINARY
114
115    Used to describe (long) binary columns (``bytea``)
116
117.. object:: NUMBER
118
119    Used to describe numeric columns (e.g. ``int``, ``float``, ``numeric``, ``money``)
120
121.. object:: DATETIME
122
123    Used to describe date/time columns (e.g. ``date``, ``time``, ``timestamp``, ``interval``)
124
125.. object:: ROWID
126
127    Used to describe the ``oid`` column of PostgreSQL database tables
128
129.. note::
130
131  The following more specific type objects are not part of the DB-API 2 standard.
132
133.. object:: BOOL
134
135    Used to describe ``boolean`` columns
136
137.. object:: SMALLINT
138
139    Used to describe ``smallint`` columns
140
141.. object:: INTEGER
142
143    Used to describe ``integer`` columns
144
145.. object:: LONG
146
147    Used to describe ``bigint`` columns
148
149.. object:: FLOAT
150
151    Used to describe ``float`` columns
152
153.. object:: NUMERIC
154
155    Used to describe ``numeric`` columns
156
157.. object:: MONEY
158
159    Used to describe ``money`` columns
160
161.. object:: DATE
162
163    Used to describe ``date`` columns
164
165.. object:: TIME
166
167    Used to describe ``time`` columns
168
169.. object:: TIMESTAMP
170
171    Used to describe ``timestamp`` columns
172
173.. object:: INTERVAL
174
175    Used to describe date and time ``interval`` columns
176
177.. object:: HSTORE
178
179    Used to describe ``hstore`` columns
180
181.. versionadded:: 5.0
182
183.. object:: JSON
184
185    Used to describe ``json`` and ``jsonb`` columns
186
187.. versionadded:: 5.0
188
189.. object:: ARRAY
190
191    Used to describe columns containing PostgreSQL arrays
192
193.. versionadded:: 5.0
194
195.. object:: RECORD
196
197    Used to describe columns containing PostgreSQL records
198
199.. versionadded:: 5.0
200
201Example for using some type objects::
202
203    >>> cursor = con.cursor()
204    >>> cursor.execute("create table jsondata (created date, data jsonb)")
205    >>> cursor.execute("select * from jsondata")
206    >>> (created, data) = (d.type_code for d in cursor.description)
207    >>> created == DATE
208    True
209    >>> created == DATETIME
210    True
211    >>> created == TIME
212    False
213    >>> data == JSON
214    True
215    >>> data == STRING
216    False
Note: See TracBrowser for help on using the repository browser.