source: trunk/docs/contents/tutorial.rst @ 908

Last change on this file since 908 was 908, checked in by cito, 3 years ago

Fix typo in tutorial

Pull request #2 by Anton Bobov

File size: 9.0 KB
Line 
1First Steps with PyGreSQL
2=========================
3
4In this small tutorial we show you the basic operations you can perform
5with both flavors of the PyGreSQL interface. Please choose your flavor:
6
7.. contents::
8    :local:
9
10
11First Steps with the classic PyGreSQL Interface
12-----------------------------------------------
13
14.. py:currentmodule:: pg
15
16The first thing you need to do anything with your PostgreSQL database is
17to create a database connection.
18
19To do this, simply import the :class:`DB` wrapper class and create an
20instance of it, passing the necessary connection parameters, like this::
21
22    >>> from pg import DB
23    >>> db = DB(dbname='testdb', host='pgserver', port=5432,
24    ...     user='scott', passwd='tiger')
25
26You can omit one or even all parameters if you want to use their default
27values. PostgreSQL will use the name of the current operating system user
28as the login and the database name, and will try to connect to the local
29host on port 5432 if nothing else is specified.
30
31The `db` object has all methods of the lower-level :class:`Connection` class
32plus some more convenience methods provided by the :class:`DB` wrapper.
33
34You can now execute database queries using the :meth:`DB.query` method::
35
36    >>> db.query("create table fruits(id serial primary key, name varchar)")
37
38You can list all database tables with the :meth:`DB.get_tables` method::
39
40    >>> db.get_tables()
41    ['public.fruits']
42
43To get the attributes of the *fruits* table, use :meth:`DB.get_attnames`::
44
45    >>> db.get_attnames('fruits')
46    {'id': 'int', 'name': 'text'}
47
48Verify that you can insert into the newly created *fruits* table:
49
50    >>> db.has_table_privilege('fruits', 'insert')
51    True
52
53You can insert a new row into the table using the :meth:`DB.insert` method,
54for example::
55
56    >>> db.insert('fruits', name='apple')
57    {'name': 'apple', 'id': 1}
58
59Note how this method returns the full row as a dictionary including its *id*
60column that has been generated automatically by a database sequence. You can
61also pass a dictionary to the :meth:`DB.insert` method instead of or in
62addition to using keyword arguments.
63
64Let's add another row to the table:
65
66   >>> banana = db.insert('fruits', name='banana')
67
68Or, you can add a whole bunch of fruits at the same time using the
69:meth:`Connection.inserttable` method.  Note that this method uses the COPY
70command of PostgreSQL to insert all data in one batch operation, which is much
71faster than sending many individual INSERT commands::
72
73    >>> more_fruits = 'cherimaya durian eggfruit fig grapefruit'.split()
74    >>> data = list(enumerate(more_fruits, start=3))
75    >>> db.inserttable('fruits', data)
76
77We can now query the database for all rows that have been inserted into
78the *fruits* table::
79
80    >>> print(db.query('select * from fruits'))
81    id|   name
82    --+----------
83     1|apple
84     2|banana
85     3|cherimaya
86     4|durian
87     5|eggfruit
88     6|fig
89     7|grapefruit
90    (7 rows)
91
92Instead of simply printing the :class:`Query` instance that has been returned
93by this query, we can also request the data as list of tuples::
94
95    >>> q = db.query('select * from fruits')
96    >>> q.getresult()
97    ... [(1, 'apple'), ..., (7, 'grapefruit')]
98
99Instead of a list of tuples, we can also request a list of dicts::
100
101    >>> q.dictresult()
102    [{'id': 1, 'name': 'apple'}, ..., {'id': 7, 'name': 'grapefruit'}]
103
104You can also return the rows as named tuples::
105
106    >>> rows = q.namedresult()
107    >>> rows[3].name
108    'durian'
109
110Using the method :meth:`DB.get_as_dict`, you can easily import the whole table
111into a Python dictionary mapping the primary key *id* to the *name*::
112
113    >>> db.get_as_dict('fruits', scalar=True)
114    OrderedDict([(1, 'apple'),
115                 (2, 'banana'),
116                 (3, 'cherimaya'),
117                 (4, 'durian'),
118                 (5, 'eggfruit'),
119                 (6, 'fig'),
120                 (7, 'grapefruit')])
121
122To change a single row in the database, you can use the :meth:`DB.update`
123method. For instance, if you want to capitalize the name 'banana'::
124
125    >>> db.update('fruits', banana, name=banana['name'].capitalize())
126    {'id': 2, 'name': 'Banana'}
127    >>> print(db.query('select * from fruits where id between 1 and 3'))
128    id|  name
129    --+---------
130     1|apple
131     2|Banana
132     3|cherimaya
133    (3 rows)
134
135Let's also capitalize the other names in the database::
136
137    >>> db.query('update fruits set name=initcap(name)')
138    '7'
139
140The returned string `'7'` tells us the number of updated rows. It is returned
141as a string to discern it from an OID which will be returned as an integer,
142if a new row has been inserted into a table with an OID column.
143
144To delete a single row from the database, use the :meth:`DB.delete` method::
145
146    >>> db.delete('fruits', banana)
147    1
148
149The returned integer value `1` tells us that one row has been deleted. If we
150try it again, the method returns the integer value `0`. Naturally, this method
151can only return 0 or 1::
152
153    >>> db.delete('fruits', banana)
154    0
155
156Of course, we can insert the row back again::
157
158    >>> db.insert('fruits', banana)
159    {'id': 2, 'name': 'Banana'}
160
161If we want to change a different row, we can get its current state with::
162
163    >>> apple = db.get('fruits', 1)
164    >>> apple
165    {'name': 'Apple', 'id': 1}
166
167We can duplicate the row like this::
168
169    >>> db.insert('fruits', apple, id=8)
170    {'id': 8, 'name': 'Apple'}
171
172 To remove the duplicated row, we can do::
173
174    >>> db.delete('fruits', id=8)
175    1
176
177Finally, to remove the table from the database and close the connection::
178
179    >>> db.query("drop table fruits")
180    >>> db.close()
181
182For more advanced features and details, see the reference: :doc:`pg/index`
183
184First Steps with the DB-API 2.0 Interface
185-----------------------------------------
186
187.. py:currentmodule:: pgdb
188
189As with the classic interface, the first thing you need to do is to create
190a database connection. To do this, use the function :func:`pgdb.connect`
191in the :mod:`pgdb` module, passing the connection parameters::
192
193    >>> from pgdb import connect
194    >>> con = connect(database='testdb', host='pgserver:5432',
195    ...     user='scott', password='tiger')
196
197Note that like in the classic interface, you can omit parameters if they
198are the default values used by PostgreSQL.
199
200To do anything with the connection, you need to request a cursor object
201from it, which is thought of as the Python representation of a database
202cursor. The connection has a method that lets you get a cursor::
203
204   >>> cursor = con.cursor()
205
206The cursor now has a method that lets you execute database queries::
207
208   >>> cursor.execute("create table fruits("
209   ...     "id serial primary key, name varchar)")
210
211
212To insert data into the table, also can also use this method::
213
214   >>> cursor.execute("insert into fruits (name) values ('apple')")
215
216You can pass parameters in a safe way::
217
218   >>> cursor.execute("insert into fruits (name) values (%s)", ('banana',))
219
220For inserting multiple rows at once, you can use the following method::
221
222   >>> more_fruits = 'cherimaya durian eggfruit fig grapefruit'.split()
223   >>> parameters = [(name,) for name in more_fruits]
224   >>> cursor.executemany("insert into fruits (name) values (%s)", parameters)
225
226The cursor also has a :meth:`Cursor.copy_from` method to quickly insert
227large amounts of data into the database, and a :meth:`Cursor.copy_to`
228method to quickly dump large amounts of data from the database, using the
229PostgreSQL COPY command. Note however, that these methods are an extension
230provided by PyGreSQL, they are not part of the DB-API 2 standard.
231
232Also note that the DB API 2.0 interface does not have an autocommit as you
233may be used from PostgreSQL. So in order to make these inserts permanent,
234you need to commit them to the database first::
235
236   >>> con.commit()
237
238If you end the program without calling the commit method of the connection,
239or if you call the rollback method of the connection, then all the changes
240will be discarded.
241
242In a similar way, you can also update or delete rows in the database,
243executing UPDATE or DELETE statements instead of INSERT statements.
244
245To fetch rows from the database, execute a SELECT statement first. Then
246you can use one of several fetch methods to retrieve the results. For
247instance, to request a single row::
248
249   >>> cursor.execute('select * from fruits where id=1')
250   >>> cursor.fetchone()
251   Row(id=1, name='apple')
252
253The result is a named tuple. This means you can access its elements either
254using an index number like in an ordinary tuple, or using the column name
255like you access object attributes.
256
257To fetch all rows of the query, use this method instead::
258
259   >>> cursor.execute('select * from fruits')
260   >>> cursor.fetchall()
261   [Row(id=1, name='apple'), ..., Row(id=7, name='grapefruit')]
262
263The output is a list of named tuples.
264
265If you want to fetch only a limited number of rows from the query::
266
267   >>> cursor.execute('select * from fruits')
268   >>> cursor.fetchmany(2)
269   [Row(id=1, name='apple'), Row(id=2, name='banana')]
270
271Finally, to remove the table from the database and close the connection::
272
273    >>> db.execute("drop table fruits")
274    >>> cur.close()
275    >>> con.close()
276
277For more advanced features and details, see the reference: :doc:`pgdb/index`
Note: See TracBrowser for help on using the repository browser.