def testCopyToWithQuery(self):
try:
cursor = self.db.cursor()
stream = BytesIO()
cursor.copy_to(
stream, query="COPY (SELECT 1 as One, 2 as Two) TO STDOUT "
"WITH DELIMITER 'X' CSV HEADER QUOTE AS 'Y' FORCE QUOTE Two")
self.assertEqual(stream.getvalue(), b('oneXtwo\n1XY2Y\n'))
self.assertEqual(cursor.rowcount, 1)
self.db.rollback()
finally:
cursor.close()
def testCopyToWithQuery(self):
try:
cursor = self.db.cursor()
stream = BytesIO()
cursor.execute(
"COPY (SELECT 1 as One, 2 as Two) TO STDOUT WITH DELIMITER "
"'X' CSV HEADER QUOTE AS 'Y' FORCE QUOTE Two",
stream=stream)
self.assertEqual(stream.getvalue(), b('oneXtwo\n1XY2Y\n'))
self.assertEqual(cursor.rowcount, 1)
self.db.rollback()
finally:
cursor.close()
def testCopyToWithQuery(self):
try:
cursor = self.db.cursor()
stream = BytesIO()
cursor.execute(
"COPY (SELECT 1 as One, 2 as Two) TO STDOUT WITH DELIMITER "
"'X' CSV HEADER QUOTE AS 'Y' FORCE QUOTE Two",
stream=stream,
)
self.assertEqual(stream.getvalue(), b("oneXtwo\n1XY2Y\n"))
self.assertEqual(cursor.rowcount, 1)
self.db.rollback()
finally:
cursor.close()
def testCopyToWithTable(self):
try:
cursor = self.db.cursor()
cursor.execute("INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)", (1, 1, 1))
cursor.execute("INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)", (2, 2, 2))
cursor.execute("INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)", (3, 3, 3))
stream = BytesIO()
cursor.execute("copy t1 to stdout", stream=stream)
self.assertEqual(stream.getvalue(), b("1\t1\t1\n2\t2\t2\n3\t3\t3\n"))
self.assertEqual(cursor.rowcount, 3)
self.db.commit()
finally:
cursor.close()
def testCopyFromWithError(self):
try:
cursor = self.db.cursor()
stream = BytesIO(b("f1Xf2\n\n1XY1Y\n"))
cursor.execute(
"COPY t1 (f1, f2) FROM STDIN WITH DELIMITER 'X' CSV HEADER "
"QUOTE AS 'Y' FORCE NOT NULL f1",
stream=stream)
self.assertTrue(False, "Should have raised an exception")
except:
args_dict = {
0: u('ERROR'),
1: u('22P02'),
2: u('invalid input syntax for integer: ""'),
3: u('COPY t1, line 2, column f1: ""'),
4: u('numutils.c'),
6: u('pg_atoi'),
7: u(''),
8: u('')
}
args = exc_info()[1].args
for k, v in iteritems(args_dict):
self.assertEqual(args[k], v)
finally:
cursor.close()
def testCopyToWithTable(self):
try:
cursor = self.db.cursor()
cursor.execute("INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)",
(1, 1, 1))
cursor.execute("INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)",
(2, 2, 2))
cursor.execute("INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)",
(3, 3, 3))
stream = BytesIO()
cursor.execute("copy t1 to stdout", stream=stream)
self.assertEqual(stream.getvalue(),
b("1\t1\t1\n2\t2\t2\n3\t3\t3\n"))
self.assertEqual(cursor.rowcount, 3)
self.db.commit()
finally:
cursor.close()
def testCopyWithoutTableOrQuery(self):
try:
cursor = self.db.cursor()
stream = BytesIO()
self.assertRaises(dbapi.CopyQueryOrTableRequiredError,
cursor.copy_from, stream)
self.assertRaises(dbapi.CopyQueryOrTableRequiredError,
cursor.copy_to, stream)
self.db.rollback()
finally:
cursor.close()
def testCopyFromWithTable(self):
try:
cursor = self.db.cursor()
stream = BytesIO(b("1\t1\t1\n2\t2\t2\n3\t3\t3\n"))
cursor.execute("copy t1 from STDIN", stream=stream)
self.assertEqual(cursor.rowcount, 3)
cursor.execute("SELECT * FROM t1 ORDER BY f1")
retval = cursor.fetchall()
self.assertEqual(retval, ([1, 1, '1'], [2, 2, '2'], [3, 3, '3']))
self.db.rollback()
finally:
cursor.close()
def testCopyFromWithQuery(self):
try:
cursor = self.db.cursor()
stream = BytesIO(b("f1Xf2\n1XY1Y\n"))
cursor.execute(
"COPY t1 (f1, f2) FROM STDIN WITH DELIMITER 'X' CSV HEADER "
"QUOTE AS 'Y' FORCE NOT NULL f1",
stream=stream)
self.assertEqual(cursor.rowcount, 1)
cursor.execute("SELECT * FROM t1 ORDER BY f1")
retval = cursor.fetchall()
self.assertEqual(retval, ([1, 1, None], ))
self.db.commit()
finally:
cursor.close()