1 # copy_from.py -- example about copy_from
3 # Copyright (C) 2002 Tom Jenkins <tjenkins@devis.com>
4 # Copyright (C) 2005 Federico Di Gregorio <fog@initd.org>
6 # This program is free software; you can redistribute it and/or modify
7 # it under the terms of the GNU General Public License as published by the
8 # Free Software Foundation; either version 2, or (at your option) any later
11 # This program is distributed in the hope that it will be useful, but
12 # WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY
13 # or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
17 ## put in DSN your DSN string
21 ## don't modify anything below tis line (except for experimenting)
31 print "Opening connection using dns:", DSN
32 conn = psycopg2.connect(DSN)
33 print "Encoding for this connection is", conn.encoding
37 curs.execute("CREATE TABLE test_copy (fld1 text, fld2 text, fld3 int4)")
40 curs.execute("DROP TABLE test_copy")
41 curs.execute("CREATE TABLE test_copy (fld1 text, fld2 text, fld3 int4)")
44 # copy_from with default arguments, from open file
46 io = open('copy_from.txt', 'wr')
47 data = ['Tom\tJenkins\t37\n',
49 'Federico\tDi Gregorio\t\N\n']
53 io = open('copy_from.txt', 'r')
54 curs.copy_from(io, 'test_copy')
55 print "1) Copy %d records from file object " % len(data) + \
56 "using defaults (sep: \\t and null = \\N)"
59 curs.execute("SELECT * FROM test_copy")
60 rows = curs.fetchall()
61 print " Select returned %d rows" % len(rows)
64 print " %s %s\t%s" % (r[0], r[1], r[2])
65 curs.execute("delete from test_copy")
68 # copy_from using custom separator, from open file
70 io = open('copy_from.txt', 'wr')
71 data = ['Tom:Jenkins:37\n',
73 'Federico:Di Gregorio:\N\n']
77 io = open('copy_from.txt', 'r')
78 curs.copy_from(io, 'test_copy', ':')
79 print "2) Copy %d records from file object using sep = :" % len(data)
82 curs.execute("SELECT * FROM test_copy")
83 rows = curs.fetchall()
84 print " Select returned %d rows" % len(rows)
87 print " %s %s\t%s" % (r[0], r[1], r[2])
88 curs.execute("delete from test_copy")
91 # copy_from using custom null identifier, from open file
93 io = open('copy_from.txt', 'wr')
94 data = ['Tom\tJenkins\t37\n',
95 'Madonna\tNULL\t45\n',
96 'Federico\tDi Gregorio\tNULL\n']
100 io = open('copy_from.txt', 'r')
101 curs.copy_from(io, 'test_copy', null='NULL')
102 print "3) Copy %d records from file object using null = NULL" % len(data)
105 curs.execute("SELECT * FROM test_copy")
106 rows = curs.fetchall()
107 print " Select using cursor returned %d rows" % len(rows)
110 print " %s %s\t%s" % (r[0], r[1], r[2])
111 curs.execute("delete from test_copy")
114 # copy_from using custom separator and null identifier
116 io = open('copy_from.txt', 'wr')
117 data = ['Tom:Jenkins:37\n', 'Madonna:NULL:45\n', 'Federico:Di Gregorio:NULL\n']
121 io = open('copy_from.txt', 'r')
122 curs.copy_from(io, 'test_copy', ':', 'NULL')
123 print "4) Copy %d records from file object " % len(data) + \
124 "using sep = : and null = NULL"
127 curs.execute("SELECT * FROM test_copy")
128 rows = curs.fetchall()
129 print " Select using cursor returned %d rows" % len(rows)
132 print " %s %s\t%s" % (r[0], r[1], r[2])
133 curs.execute("delete from test_copy")
136 # anything can be used as a file if it has .read() and .readline() methods
138 data = StringIO.StringIO()
139 data.write('\n'.join(['Tom\tJenkins\t37',
141 'Federico\tDi Gregorio\t\N']))
144 curs.copy_from(data, 'test_copy')
145 print "5) Copy 3 records from StringIO object using defaults"
147 curs.execute("SELECT * FROM test_copy")
148 rows = curs.fetchall()
149 print " Select using cursor returned %d rows" % len(rows)
152 print " %s %s\t%s" % (r[0], r[1], r[2])
153 curs.execute("delete from test_copy")
158 print "6) About to raise an error"
159 data = StringIO.StringIO()
160 data.write('\n'.join(['Tom\tJenkins\t37',
162 'Federico\tDi Gregorio\taaa']))
166 curs.copy_from(data, 'test_copy')
167 except StandardError, err:
169 print " Catched error (as expected):\n", err
173 curs.execute("DROP TABLE test_copy")
174 os.unlink('copy_from.txt')