Repository: GoogleCloudPlatform/pgadapter
Pull request #661 (2023-02-15 13:54:37)

feat: allow timestamp param values in brackets
PostgreSQL allows textual parameter values to be given as any constant expression. Most clients will only send literals, but SQLAlchemy2 will (in some cases) send an expression that encloses the timestamp value in brackets and double quotes. This change adds support for that specific type of expression.


Patch:
diff --git a/.github/workflows/integration.yaml b/.github/workflows/integration.yaml
index d02ccf2e..4381e65e 100644
--- a/.github/workflows/integration.yaml
+++ b/.github/workflows/integration.yaml
@@ -85,7 +85,6 @@ jobs:
       - uses: actions/setup-python@v4
         with:
           python-version: '3.9'
-      - run: pip install -r ./src/test/python/requirements.txt
       - uses: actions/setup-node@v3
         with:
           node-version: 16
diff --git a/.github/workflows/units.yaml b/.github/workflows/units.yaml
index c6d00163..b4a2f396 100644
--- a/.github/workflows/units.yaml
+++ b/.github/workflows/units.yaml
@@ -25,7 +25,6 @@ jobs:
         with:
           python-version: '3.9'
       - run: python --version
-      - run: pip install -r ./src/test/python/requirements.txt
       - uses: actions/setup-node@v3
         with:
           node-version: 16
@@ -69,7 +68,6 @@ jobs:
         with:
           python-version: '3.9'
       - run: python --version
-      - run: pip install -r ./src/test/python/requirements.txt
       - uses: actions/setup-dotnet@v3
         with:
           dotnet-version: '6.0.x'
diff --git a/samples/python/django/requirements.txt b/samples/python/django/requirements.txt
new file mode 100644
index 00000000..bb812644
--- /dev/null
+++ b/samples/python/django/requirements.txt
@@ -0,0 +1,3 @@
+psycopg2-binary~=2.9.3
+pytz~=2022.1
+Django~=4.1.1
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/parsers/TimestampParser.java b/src/main/java/com/google/cloud/spanner/pgadapter/parsers/TimestampParser.java
index 54682366..2945b1f5 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/parsers/TimestampParser.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/parsers/TimestampParser.java
@@ -124,7 +124,8 @@ public class TimestampParser extends Parser<Timestamp> {
    * Converts the given string value to a {@link Timestamp}. The given timezone is used to determine
    * the actual point in time if the timestamp string itself does not contain a timezone offset.
    */
-  public static Timestamp toTimestamp(String value, ZoneId timezone) {
+  public static Timestamp toTimestamp(@Nonnull String value, @Nonnull ZoneId timezone) {
+    value = stripBracketsAndQuotes(value);
     try {
       String stringValue = toPGString(value);
       TemporalAccessor temporalAccessor = TIMESTAMPTZ_INPUT_FORMATTER.parse(stringValue);
@@ -158,6 +159,28 @@ public class TimestampParser extends Parser<Timestamp> {
     }
   }
 
+  static String stripBracketsAndQuotes(@Nonnull String value) {
+    while (value.length() > 1
+        && (value.charAt(0) == '('
+            || value.charAt(0) == '"'
+            || value.charAt(0) == '\''
+            || Character.isWhitespace(value.charAt(0))
+            || Character.isWhitespace(value.charAt(value.length() - 1)))) {
+      if (value.charAt(value.length() - 1) == value.charAt(0)) {
+        value = value.substring(1, value.length() - 1);
+      } else if (value.charAt(0) == '(' && value.charAt(value.length() - 1) == ')') {
+        value = value.substring(1, value.length() - 1);
+      } else if (Character.isWhitespace(value.charAt(0))) {
+        value = value.substring(1);
+      } else if (Character.isWhitespace(value.charAt(value.length() - 1))) {
+        value = value.substring(0, value.length() - 1);
+      } else {
+        break;
+      }
+    }
+    return value;
+  }
+
   @Override
   public String stringParse() {
     return this.item == null ? null : toPGString(this.item, sessionState.getTimezone());


Test patch:
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/parsers/TimestampParserTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/parsers/TimestampParserTest.java
index 229afcbd..be462464 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/parsers/TimestampParserTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/parsers/TimestampParserTest.java
@@ -134,5 +134,49 @@ public class TimestampParserTest {
     assertEquals(
         Timestamp.parseTimestamp("2022-12-28T07:30Z"),
         TimestampParser.toTimestamp("2022-12-28 10:00+02:30", ZoneId.of("CET")));
+    assertEquals(
+        Timestamp.parseTimestamp("2011-11-04T00:05:23.123456Z"),
+        TimestampParser.toTimestamp("(\"2011-11-04 00:05:23.123456+00:00\")", ZoneId.of("UTC")));
+    assertEquals(
+        Timestamp.parseTimestamp("2011-11-04T00:05:23.123456Z"),
+        TimestampParser.toTimestamp("('2011-11-04 00:05:23.123456+00:00')", ZoneId.of("UTC")));
+    assertEquals(
+        Timestamp.parseTimestamp("2011-11-04T00:05:23.123456Z"),
+        TimestampParser.toTimestamp("'2011-11-04 00:05:23.123456+00:00'", ZoneId.of("UTC")));
+    assertThrows(PGException.class, () -> TimestampParser.toTimestamp("", ZoneId.of("UTC")));
+    assertThrows(PGException.class, () -> TimestampParser.toTimestamp("(", ZoneId.of("UTC")));
+    assertThrows(PGException.class, () -> TimestampParser.toTimestamp(")", ZoneId.of("UTC")));
+    assertThrows(
+        PGException.class,
+        () -> TimestampParser.toTimestamp("'2011-11-04 00:05:23.123456+00:00')", ZoneId.of("UTC")));
+    assertThrows(
+        PGException.class,
+        () -> TimestampParser.toTimestamp("('2011-11-04 00:05:23.123456+00:00'", ZoneId.of("UTC")));
+    assertThrows(PGException.class, () -> TimestampParser.toTimestamp("()", ZoneId.of("UTC")));
+    assertThrows(PGException.class, () -> TimestampParser.toTimestamp("''", ZoneId.of("UTC")));
+    assertThrows(PGException.class, () -> TimestampParser.toTimestamp("'2000'", ZoneId.of("UTC")));
+    assertEquals(
+        Timestamp.parseTimestamp("2000-01-01T00:00:00Z"),
+        TimestampParser.toTimestamp("'2000-01-01'", ZoneId.of("UTC")));
+    assertEquals(
+        Timestamp.parseTimestamp("2011-11-04T00:05:23.123456Z"),
+        TimestampParser.toTimestamp(" (\"2011-11-04 00:05:23.123456+00:00\")", ZoneId.of("UTC")));
+    assertEquals(
+        Timestamp.parseTimestamp("2011-11-04T00:05:23.123456Z"),
+        TimestampParser.toTimestamp("(\"2011-11-04 00:05:23.123456+00:00\") ", ZoneId.of("UTC")));
+    assertEquals(
+        Timestamp.parseTimestamp("2011-11-04T00:05:23.123456Z"),
+        TimestampParser.toTimestamp("( \"2011-11-04 00:05:23.123456+00:00\" )", ZoneId.of("UTC")));
+    assertEquals(
+        Timestamp.parseTimestamp("2011-11-04T00:05:23.123456Z"),
+        TimestampParser.toTimestamp("(\" 2011-11-04 00:05:23.123456+00:00\")", ZoneId.of("UTC")));
+    assertEquals(
+        Timestamp.parseTimestamp("2011-11-04T00:05:23.123456Z"),
+        TimestampParser.toTimestamp(
+            "\n(  \"2011-11-04 00:05:23.123456+00:00  \" )", ZoneId.of("UTC")));
+    assertEquals(
+        Timestamp.parseTimestamp("2011-11-04T00:05:23.123456Z"),
+        TimestampParser.toTimestamp(
+            "\t\n( \"  2011-11-04 00:05:23.123456+00:00  \n\t\" )", ZoneId.of("UTC")));
   }
 }
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/python/PythonTestUtil.java b/src/test/java/com/google/cloud/spanner/pgadapter/python/PythonTestUtil.java
index 35aeb9a7..fd638a94 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/python/PythonTestUtil.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/python/PythonTestUtil.java
@@ -65,7 +65,7 @@ public class PythonTestUtil {
       error.append(errorScanner.nextLine()).append("\n");
     }
     int result = process.waitFor();
-    assertEquals(error.toString(), 0, result);
+    assertEquals(output + "\n\n\n" + error, 0, result);
 
     return output.toString();
   }
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoAggregationTests.java b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoAggregationTests.java
index e122cb25..87301193 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoAggregationTests.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoAggregationTests.java
@@ -21,7 +21,6 @@ import com.google.cloud.spanner.Statement;
 import com.google.cloud.spanner.pgadapter.python.PythonTest;
 import com.google.common.collect.ImmutableList;
 import com.google.spanner.v1.ExecuteSqlRequest;
-import java.io.IOException;
 import java.util.ArrayList;
 import java.util.List;
 import org.junit.Test;
@@ -43,7 +42,7 @@ public class DjangoAggregationTests extends DjangoTestSetup {
   }
 
   @Test
-  public void simpleCountTest() throws IOException, InterruptedException {
+  public void simpleCountTest() throws Exception {
 
     String sql = "SELECT COUNT(*) AS \"__count\" FROM \"singers\"";
 
@@ -69,7 +68,7 @@ public class DjangoAggregationTests extends DjangoTestSetup {
   }
 
   @Test
-  public void aggregateAvg() throws IOException, InterruptedException {
+  public void aggregateAvg() throws Exception {
 
     String sql = "SELECT AVG(\"singers\".\"singerid\") AS \"singerid__avg\" FROM \"singers\"";
 
@@ -95,7 +94,7 @@ public class DjangoAggregationTests extends DjangoTestSetup {
   }
 
   @Test
-  public void aggregateMin() throws IOException, InterruptedException {
+  public void aggregateMin() throws Exception {
 
     String sql = "SELECT MIN(\"singers\".\"singerid\") AS \"singerid__min\" FROM \"singers\"";
 
@@ -121,7 +120,7 @@ public class DjangoAggregationTests extends DjangoTestSetup {
   }
 
   @Test
-  public void aggregateMax() throws IOException, InterruptedException {
+  public void aggregateMax() throws Exception {
 
     String sql = "SELECT MAX(\"singers\".\"singerid\") AS \"singerid__max\" FROM \"singers\"";
 
@@ -147,7 +146,7 @@ public class DjangoAggregationTests extends DjangoTestSetup {
   }
 
   @Test
-  public void aggregateMultiple() throws IOException, InterruptedException {
+  public void aggregateMultiple() throws Exception {
 
     String sql =
         "SELECT MAX(\"singers\".\"singerid\") AS \"singerid__max\", MIN(\"singers\".\"singerid\") AS \"singerid__min\", AVG(\"singers\".\"singerid\") AS \"singerid__avg\" FROM \"singers\"";
@@ -180,7 +179,7 @@ public class DjangoAggregationTests extends DjangoTestSetup {
   }
 
   @Test
-  public void annotateCount() throws IOException, InterruptedException {
+  public void annotateCount() throws Exception {
 
     String sql =
         "SELECT \"singers\".\"firstname\", COUNT(\"singers\".\"firstname\") AS \"firstname__count\" FROM \"singers\" GROUP BY \"singers\".\"firstname\" LIMIT 21";
@@ -215,7 +214,7 @@ public class DjangoAggregationTests extends DjangoTestSetup {
   }
 
   @Test
-  public void annotateMin() throws IOException, InterruptedException {
+  public void annotateMin() throws Exception {
 
     String sql =
         "SELECT \"singers\".\"firstname\", MIN(\"singers\".\"singerid\") AS \"singerid__min\" FROM \"singers\" GROUP BY \"singers\".\"firstname\" LIMIT 21";
@@ -249,7 +248,7 @@ public class DjangoAggregationTests extends DjangoTestSetup {
   }
 
   @Test
-  public void annotateMax() throws IOException, InterruptedException {
+  public void annotateMax() throws Exception {
 
     String sql =
         "SELECT \"singers\".\"firstname\", MAX(\"singers\".\"singerid\") AS \"singerid__max\" FROM \"singers\" GROUP BY \"singers\".\"firstname\" LIMIT 21";
@@ -284,7 +283,7 @@ public class DjangoAggregationTests extends DjangoTestSetup {
   }
 
   @Test
-  public void annotateAvg() throws IOException, InterruptedException {
+  public void annotateAvg() throws Exception {
 
     String sql =
         "SELECT \"singers\".\"firstname\", AVG(\"singers\".\"singerid\") AS \"singerid__avg\" FROM \"singers\" GROUP BY \"singers\".\"firstname\" LIMIT 21";
@@ -319,7 +318,7 @@ public class DjangoAggregationTests extends DjangoTestSetup {
   }
 
   @Test
-  public void annotateMultiple() throws IOException, InterruptedException {
+  public void annotateMultiple() throws Exception {
 
     String sql =
         "SELECT \"singers\".\"singerid\", AVG(\"singers\".\"singerid\") AS \"singerid__avg\", COUNT(\"singers\".\"singerid\") AS \"singerid__count\", MAX(\"singers\".\"singerid\") AS \"singerid__max\", MIN(\"singers\".\"singerid\") AS \"singerid__min\" FROM \"singers\" GROUP BY \"singers\".\"singerid\" LIMIT 21";
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoBasicTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoBasicTest.java
index 4f2c65ed..c31cbff0 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoBasicTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoBasicTest.java
@@ -32,9 +32,9 @@ import com.google.spanner.v1.StructType;
 import com.google.spanner.v1.StructType.Field;
 import com.google.spanner.v1.Type;
 import com.google.spanner.v1.TypeCode;
-import java.io.IOException;
 import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Collections;
 import java.util.List;
 import org.junit.Test;
 import org.junit.experimental.categories.Category;
@@ -91,7 +91,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testSelectAll() throws IOException, InterruptedException {
+  public void testSelectAll() throws Exception {
     String sqlSelectAll =
         "SELECT \"singers\".\"singerid\", \"singers\".\"firstname\", \"singers\".\"lastname\" FROM \"singers\"";
 
@@ -103,7 +103,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
     String expectedOutput =
         "{'singerid': 1, 'firstname': 'hello', 'lastname': 'world'}\n"
             + "{'singerid': 2, 'firstname': 'hello', 'lastname': 'django'}\n";
-    List<String> options = new ArrayList<>(Arrays.asList("all"));
+    List<String> options = new ArrayList<>(Collections.singletonList("all"));
     String actualOutput = executeBasicTests(pgServer.getLocalPort(), host, options);
     assertEquals(expectedOutput, actualOutput);
 
@@ -113,7 +113,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testInsert() throws IOException, InterruptedException {
+  public void testInsert() throws Exception {
     String sqlUpdate =
         "UPDATE \"singers\" SET \"firstname\" = 'john', \"lastname\" = 'doe' WHERE \"singers\".\"singerid\" = 4";
     String sqlInsert =
@@ -134,7 +134,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testUpdate() throws IOException, InterruptedException {
+  public void testUpdate() throws Exception {
     String sqlUpdate =
         "UPDATE \"singers\" SET \"firstname\" = 'john', \"lastname\" = 'doe' WHERE \"singers\".\"singerid\" = 4";
 
@@ -153,7 +153,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testSimpleFilter() throws IOException, InterruptedException {
+  public void testSimpleFilter() throws Exception {
     String sqlSelect =
         "SELECT \"singers\".\"singerid\", \"singers\".\"firstname\", \"singers\".\"lastname\" FROM \"singers\" WHERE \"singers\".\"firstname\" = 'hello'";
 
@@ -193,7 +193,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testInsertAllTypes() throws IOException, InterruptedException {
+  public void testInsertAllTypes() throws Exception {
 
     String sqlUpdate =
         "UPDATE \"all_types\" "
@@ -230,7 +230,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
     mockSpanner.putStatementResult(StatementResult.update(Statement.of(sqlUpdate), 0));
     mockSpanner.putStatementResult(StatementResult.update(Statement.of(sqlInsert), 1));
     String expectedOutput = "Insert Successful\n";
-    List<String> options = new ArrayList<>(Arrays.asList("all_types_insert"));
+    List<String> options = new ArrayList<>(Collections.singletonList("all_types_insert"));
     String actualOutput = executeBasicTests(pgServer.getLocalPort(), host, options);
     assertEquals(expectedOutput, actualOutput);
 
@@ -240,7 +240,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testInsertAllTypesAllNull() throws IOException, InterruptedException {
+  public void testInsertAllTypesAllNull() throws Exception {
 
     String sqlInsert =
         "INSERT INTO \"all_types\" "
@@ -258,7 +258,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
 
     mockSpanner.putStatementResult(StatementResult.update(Statement.of(sqlInsert), 1));
     String expectedOutput = "Insert Successful\n";
-    List<String> options = new ArrayList<>(Arrays.asList("all_types_insert_null"));
+    List<String> options = new ArrayList<>(Collections.singletonList("all_types_insert_null"));
     String actualOutput = executeBasicTests(pgServer.getLocalPort(), host, options);
     assertEquals(expectedOutput, actualOutput);
 
@@ -273,7 +273,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testSelectAllNull() throws IOException, InterruptedException {
+  public void testSelectAllNull() throws Exception {
 
     String sqlQuery =
         "SELECT "
@@ -314,7 +314,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
             + "'col_date': None, "
             + "'col_varchar': None, "
             + "'col_jsonb': None}\n";
-    List<String> options = new ArrayList<>(Arrays.asList("select_all_null"));
+    List<String> options = new ArrayList<>(Collections.singletonList("select_all_null"));
     String actualOutput = executeBasicTests(pgServer.getLocalPort(), host, options);
     assertEquals(expectedOutput, actualOutput);
 
@@ -323,7 +323,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testSelectAllTypes() throws IOException, InterruptedException {
+  public void testSelectAllTypes() throws Exception {
 
     String sqlQuery =
         "SELECT \"all_types\".\"col_bigint\", "
@@ -340,7 +340,6 @@ public class DjangoBasicTest extends DjangoTestSetup {
 
     ResultSet resultSet = createAllTypesResultSet("", true);
     mockSpanner.putStatementResult(StatementResult.query(Statement.of(sqlQuery), resultSet));
-    System.out.println(resultSet.getRows(0).getValues(6).getStringValue());
     String expectedOutput =
         "col_bigint : 1 , "
             + "col_bool : True , "
@@ -352,7 +351,7 @@ public class DjangoBasicTest extends DjangoTestSetup {
             + "col_date : 2022-03-29 , "
             + "col_varchar : test , "
             + "col_jsonb : {'key': 'value'} , \n";
-    List<String> options = new ArrayList<>(Arrays.asList("select_all_types"));
+    List<String> options = new ArrayList<>(Collections.singletonList("select_all_types"));
     String actualOutput = executeBasicTests(pgServer.getLocalPort(), host, options);
     assertEquals(expectedOutput, actualOutput);
 
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoConditionalExpressionsTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoConditionalExpressionsTest.java
index c95296b0..c492db75 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoConditionalExpressionsTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoConditionalExpressionsTest.java
@@ -21,7 +21,6 @@ import com.google.cloud.spanner.Statement;
 import com.google.cloud.spanner.pgadapter.python.PythonTest;
 import com.google.common.collect.ImmutableList;
 import com.google.spanner.v1.ExecuteSqlRequest;
-import java.io.IOException;
 import java.util.ArrayList;
 import java.util.List;
 import org.junit.Test;
@@ -43,7 +42,7 @@ public class DjangoConditionalExpressionsTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testConditionalUpdate() throws IOException, InterruptedException {
+  public void testConditionalUpdate() throws Exception {
     String sqlUpdate =
         "UPDATE \"singers\" SET \"firstname\" = CASE WHEN (\"singers\".\"firstname\" = 'hello') THEN 'h' WHEN (\"singers\".\"firstname\" = 'world') THEN 'w' ELSE 'n' END";
 
@@ -60,7 +59,7 @@ public class DjangoConditionalExpressionsTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testConditionalAggregation() throws IOException, InterruptedException {
+  public void testConditionalAggregation() throws Exception {
     String sqlSelect =
         "SELECT COUNT(\"singers\".\"singerid\") FILTER (WHERE \"singers\".\"firstname\" = 'hello') AS \"hello_count\", COUNT(\"singers\".\"singerid\") FILTER (WHERE \"singers\".\"firstname\" = 'world') AS \"world_count\" FROM \"singers\"";
 
@@ -89,7 +88,7 @@ public class DjangoConditionalExpressionsTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testConditionalFilter() throws IOException, InterruptedException {
+  public void testConditionalFilter() throws Exception {
     String sqlSelect =
         "SELECT \"singers\".\"singerid\", \"singers\".\"firstname\", \"singers\".\"lastname\" FROM \"singers\" WHERE UPPER(\"singers\".\"firstname\"::text) LIKE '%' || UPPER(REPLACE(REPLACE(REPLACE((CASE WHEN \"singers\".\"lastname\" = 'world' THEN 'w' WHEN \"singers\".\"lastname\" = 'hello' THEN 'h' ELSE NULL END), E'\\\\', E'\\\\\\\\'), E'%', E'\\\\%'), E'_', E'\\\\_')) || '%'";
 
@@ -126,7 +125,7 @@ public class DjangoConditionalExpressionsTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testConditionalAnnotation() throws IOException, InterruptedException {
+  public void testConditionalAnnotation() throws Exception {
     String sqlSelect =
         "SELECT \"singers\".\"singerid\", \"singers\".\"firstname\", CASE WHEN UPPER(\"singers\".\"firstname\"::text) LIKE UPPER('%hello%') THEN 'hello singer' WHEN UPPER(\"singers\".\"firstname\"::text) LIKE UPPER('%world%') THEN 'world singer' ELSE NULL END AS \"type\" FROM \"singers\" LIMIT 21";
 
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoMockServerTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoMockServerTest.java
deleted file mode 100644
index 39105520..00000000
--- a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoMockServerTest.java
+++ /dev/null
@@ -1,30 +0,0 @@
-// Copyright 2022 Google LLC
-//
-// Licensed under the Apache License, Version 2.0 (the "License");
-// you may not use this file except in compliance with the License.
-// You may obtain a copy of the License at
-//
-//      http://www.apache.org/licenses/LICENSE-2.0
-//
-// Unless required by applicable law or agreed to in writing, software
-// distributed under the License is distributed on an "AS IS" BASIS,
-// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-// See the License for the specific language governing permissions and
-// limitations under the License.
-
-package com.google.cloud.spanner.pgadapter.python.django;
-
-import com.google.cloud.spanner.MockSpannerServiceImpl;
-import com.google.cloud.spanner.pgadapter.AbstractMockServerTest;
-import java.util.Arrays;
-import org.junit.BeforeClass;
-
-public class DjangoMockServerTest extends AbstractMockServerTest {
-
-  @BeforeClass
-  public static void startMockSpannerAndPgAdapterServers() throws Exception {
-    // Django needs postgres version to be greater than or equal to 11
-    doStartMockSpannerAndPgAdapterServers(
-        new MockSpannerServiceImpl(), "d", Arrays.asList("--server-version", "11.1"));
-  }
-}
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoPgAggregatesTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoPgAggregatesTest.java
index b70d895d..58343357 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoPgAggregatesTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoPgAggregatesTest.java
@@ -21,8 +21,6 @@ import com.google.cloud.spanner.Statement;
 import com.google.cloud.spanner.pgadapter.python.PythonTest;
 import com.google.common.collect.ImmutableList;
 import com.google.spanner.v1.ExecuteSqlRequest;
-import com.google.spanner.v1.ResultSet;
-import java.io.IOException;
 import java.util.ArrayList;
 import java.util.List;
 import org.junit.Test;
@@ -44,7 +42,7 @@ public class DjangoPgAggregatesTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testStrAgg() throws IOException, InterruptedException {
+  public void testStrAgg() throws Exception {
 
     String sql =
         "SELECT \"singers\".\"firstname\", STRING_AGG(\"singers\".\"firstname\", '|' ) AS \"str\" FROM \"singers\" GROUP BY \"singers\".\"firstname\" LIMIT 21";
@@ -80,7 +78,7 @@ public class DjangoPgAggregatesTest extends DjangoTestSetup {
   }
 
   @Test
-  public void testArrAgg() throws IOException, InterruptedException {
+  public void testArrAgg() throws Exception {
 
     String sql = "SELECT ARRAY_AGG(\"singers\".\"firstname\" ) AS \"arr\" FROM \"singers\"";
 
@@ -90,7 +88,6 @@ public class DjangoPgAggregatesTest extends DjangoTestSetup {
     result.add("arr");
 
     result.add("[hello,beautiful,world,]");
-    ResultSet rs = createResultSet(result, 2);
 
     mockSpanner.putStatementResult(
         StatementResult.query(Statement.of(sql), createResultSet(result, 1)));
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoTestSetup.java b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoTestSetup.java
index bd8dd7ac..4a0fba93 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoTestSetup.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoTestSetup.java
@@ -14,10 +14,10 @@
 
 package com.google.cloud.spanner.pgadapter.python.django;
 
-import static org.junit.Assert.assertEquals;
-
 import com.google.cloud.spanner.MockSpannerServiceImpl.StatementResult;
 import com.google.cloud.spanner.Statement;
+import com.google.cloud.spanner.pgadapter.AbstractMockServerTest;
+import com.google.cloud.spanner.pgadapter.python.PythonTestUtil;
 import com.google.protobuf.ListValue;
 import com.google.protobuf.Value;
 import com.google.spanner.v1.ResultSet;
@@ -27,15 +27,13 @@ import com.google.spanner.v1.StructType.Field;
 import com.google.spanner.v1.Type;
 import com.google.spanner.v1.TypeCode;
 import java.io.File;
-import java.io.IOException;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collections;
 import java.util.List;
-import java.util.Scanner;
 import org.junit.BeforeClass;
 
-public class DjangoTestSetup extends DjangoMockServerTest {
+public class DjangoTestSetup extends AbstractMockServerTest {
 
   public static ResultSet createResultSet(List<String> rows, int numCols) {
     ResultSet.Builder resultSetBuilder = ResultSet.newBuilder();
@@ -90,6 +88,11 @@ public class DjangoTestSetup extends DjangoMockServerTest {
     return resultSetBuilder.build();
   }
 
+  @BeforeClass
+  public static void createVirtualEnv() throws Exception {
+    PythonTestUtil.createVirtualEnv(DJANGO_UNIT_PATH);
+  }
+
   @BeforeClass
   public static void mockDriverInternalSql() {
     String unnecessarySql =
@@ -124,63 +127,49 @@ public class DjangoTestSetup extends DjangoMockServerTest {
     }
   }
 
-  private static String DJANGO_UNIT_PATH = "./src/test/python/django";
-  private static String DJANGO_IT_PATH = "./samples/python/django";
+  static final String DJANGO_UNIT_PATH = "./src/test/python/django";
+  static final String DJANGO_SAMPLES_PATH = "./samples/python/django";
 
   private static String execute(
       int port, String host, List<String> options, String testFileName, String djangoPath)
-      throws IOException, InterruptedException {
+      throws Exception {
+    File directory = new File(djangoPath);
     List<String> runCommand =
-        new ArrayList<>(Arrays.asList("python3", testFileName, host, Integer.toString(port)));
+        new ArrayList<>(
+            Arrays.asList(
+                directory.getAbsolutePath() + "/venv/bin/python3",
+                testFileName,
+                host,
+                Integer.toString(port)));
     runCommand.addAll(options);
-    ProcessBuilder builder = new ProcessBuilder();
-    builder.command(runCommand);
-    builder.directory(new File(djangoPath));
-    Process process = builder.start();
-
-    Scanner errorScanner = new Scanner(process.getErrorStream());
-    StringBuilder error = new StringBuilder();
-    while (errorScanner.hasNextLine()) {
-      error.append(errorScanner.nextLine()).append("\n");
-    }
-
-    Scanner scanner = new Scanner(process.getInputStream());
-    StringBuilder output = new StringBuilder();
-    while (scanner.hasNextLine()) {
-      output.append(scanner.nextLine()).append("\n");
-    }
-    int result = process.waitFor();
-    assertEquals(error.toString(), 0, result);
-
-    return output.toString();
+    return PythonTestUtil.run(runCommand.toArray(new String[0]), djangoPath);
   }
 
-  public String executeBasicTests(int port, String host, List<String> options)
-      throws IOException, InterruptedException {
+  public String executeBasicTests(int port, String host, List<String> options) throws Exception {
     return execute(port, host, options, "basic_test.py", DJANGO_UNIT_PATH);
   }
 
   public String executeTransactionTests(int port, String host, List<String> options)
-      throws IOException, InterruptedException {
+      throws Exception {
     return execute(port, host, options, "transaction_test.py", DJANGO_UNIT_PATH);
   }
 
   public String executePgAggregateTests(int port, String host, List<String> options)
-      throws IOException, InterruptedException {
+      throws Exception {
     return execute(port, host, options, "pg_aggregates_test.py", DJANGO_UNIT_PATH);
   }
 
   public String executeConditionalTests(int port, String host, List<String> options)
-      throws IOException, InterruptedException {
+      throws Exception {
     return execute(port, host, options, "conditional_expressions_test.py", DJANGO_UNIT_PATH);
   }
 
   public String executeAggregationTests(int port, String host, List<String> options)
-      throws IOException, InterruptedException {
+      throws Exception {
     return execute(port, host, options, "aggregation_tests.py", DJANGO_UNIT_PATH);
   }
 
-  public String executeIntegrationTests() throws IOException, InterruptedException {
-    return execute(0, "", Collections.emptyList(), "sample.py", DJANGO_IT_PATH);
+  public String executeIntegrationTests() throws Exception {
+    return execute(0, "", Collections.emptyList(), "sample.py", DJANGO_SAMPLES_PATH);
   }
 }
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoTransactionsTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoTransactionsTest.java
index 157bb3ce..98ae2b76 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoTransactionsTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/DjangoTransactionsTest.java
@@ -20,18 +20,9 @@ import com.google.cloud.spanner.MockSpannerServiceImpl.StatementResult;
 import com.google.cloud.spanner.Statement;
 import com.google.cloud.spanner.pgadapter.python.PythonTest;
 import com.google.common.collect.ImmutableList;
-import com.google.protobuf.ListValue;
-import com.google.protobuf.Value;
 import com.google.spanner.v1.CommitRequest;
-import com.google.spanner.v1.ResultSet;
-import com.google.spanner.v1.ResultSetMetadata;
 import com.google.spanner.v1.RollbackRequest;
-import com.google.spanner.v1.StructType;
-import com.google.spanner.v1.StructType.Field;
-import com.google.spanner.v1.Type;
-import com.google.spanner.v1.TypeCode;
 import io.grpc.Status;
-import java.io.IOException;
 import java.util.ArrayList;
 import java.util.List;
 import org.junit.Test;
@@ -52,44 +43,8 @@ public class DjangoTransactionsTest extends DjangoTestSetup {
     return ImmutableList.of(new Object[] {"localhost"}, new Object[] {"/tmp"});
   }
 
-  private ResultSet createResultSet(List<String> rows) {
-    ResultSet.Builder resultSetBuilder = ResultSet.newBuilder();
-
-    resultSetBuilder.setMetadata(
-        ResultSetMetadata.newBuilder()
-            .setRowType(
-                StructType.newBuilder()
-                    .addFields(
-                        Field.newBuilder()
-                            .setType(Type.newBuilder().setCode(TypeCode.INT64).build())
-                            .setName("singerid")
-                            .build())
-                    .addFields(
-                        Field.newBuilder()
-                            .setType(Type.newBuilder().setCode(TypeCode.STRING).build())
-                            .setName("firstname")
-                            .build())
-                    .addFields(
-                        Field.newBuilder()
-                            .setType(Type.newBuilder().setCode(TypeCode.STRING).build())
-                            .setName("lastname")
-                            .build())
-                    .build())
-            .build());
-    for (int i = 0; i < rows.size(); i += 3) {
-      String singerid = rows.get(i), firstname = rows.get(i + 1), lastname = rows.get(i + 2);
-      resultSetBuilder.addRows(
-          ListValue.newBuilder()
-              .addValues(Value.newBuilder().setStringValue(singerid).build())
-              .addValues(Value.newBuilder().setStringValue(firstname).build())
-              .addValues(Value.newBuilder().setStringValue(lastname).build())
-              .build());
-    }
-    return resultSetBuilder.build();
-  }
-
   @Test
-  public void transactionCommitTest() throws IOException, InterruptedException {
+  public void transactionCommitTest() throws Exception {
 
     String updateSQL1 =
         "UPDATE \"singers\" SET \"firstname\" = 'hello', \"lastname\" = 'world' WHERE \"singers\".\"singerid\" = 1";
@@ -116,7 +71,7 @@ public class DjangoTransactionsTest extends DjangoTestSetup {
   }
 
   @Test
-  public void transactionRollbackTest() throws IOException, InterruptedException {
+  public void transactionRollbackTest() throws Exception {
 
     String updateSQL1 =
         "UPDATE \"singers\" SET \"firstname\" = 'hello', \"lastname\" = 'world' WHERE \"singers\".\"singerid\" = 1";
@@ -144,7 +99,7 @@ public class DjangoTransactionsTest extends DjangoTestSetup {
   }
 
   @Test
-  public void transactionAtomicTest() throws IOException, InterruptedException {
+  public void transactionAtomicTest() throws Exception {
 
     String updateSQL1 =
         "UPDATE \"singers\" SET \"firstname\" = 'hello', \"lastname\" = 'world' WHERE \"singers\".\"singerid\" = 1";
@@ -171,7 +126,7 @@ public class DjangoTransactionsTest extends DjangoTestSetup {
   }
 
   @Test
-  public void transactionNestedAtomicTest() throws IOException, InterruptedException {
+  public void transactionNestedAtomicTest() throws Exception {
 
     String updateSQL1 =
         "UPDATE \"singers\" SET \"firstname\" = 'hello', \"lastname\" = 'world' WHERE \"singers\".\"singerid\" = 1";
@@ -198,7 +153,7 @@ public class DjangoTransactionsTest extends DjangoTestSetup {
   }
 
   @Test
-  public void transactionErrorTest() throws IOException, InterruptedException {
+  public void transactionErrorTest() throws Exception {
 
     String updateSQL1 =
         "UPDATE \"singers\" SET \"firstname\" = 'hello', \"lastname\" = 'world' WHERE \"singers\".\"singerid\" = 1";
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/ITDjangoTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/ITDjangoTest.java
index 3be5204c..866b6dea 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/python/django/ITDjangoTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/python/django/ITDjangoTest.java
@@ -19,6 +19,7 @@ import static org.junit.Assert.assertTrue;
 import com.google.cloud.spanner.Database;
 import com.google.cloud.spanner.pgadapter.IntegrationTest;
 import com.google.cloud.spanner.pgadapter.PgAdapterTestEnv;
+import com.google.cloud.spanner.pgadapter.python.PythonTestUtil;
 import java.io.FileReader;
 import java.io.FileWriter;
 import java.io.IOException;
@@ -36,10 +37,9 @@ import org.junit.runners.JUnit4;
 public class ITDjangoTest extends DjangoTestSetup {
 
   private static final PgAdapterTestEnv testEnv = new PgAdapterTestEnv();
-  private static Database database;
-  private static String DJANGO_SETTING_FILE = "samples/python/django/setting.py";
+  private static final String DJANGO_SETTING_FILE = "samples/python/django/setting.py";
   private static String originalDjangoSetting = "";
-  private static String DEFAULT_PORT = "5432";
+  private static final String DEFAULT_PORT = "5432";
 
   @BeforeClass
   public static void setup() throws Exception {
@@ -50,11 +50,12 @@ public class ITDjangoTest extends DjangoTestSetup {
           "python has not been set up on this system and allowSkipUnsupportedEcosystemTest has not been set");
     }
 
+    PythonTestUtil.createVirtualEnv(DJANGO_SAMPLES_PATH);
     testEnv.setUp();
-    database = testEnv.createDatabase(Collections.emptyList());
+    Database database = testEnv.createDatabase(Collections.emptyList());
     testEnv.startPGAdapterServerWithDefaultDatabase(database.getId(), Collections.emptyList());
 
-    // Update Django setting.py fiel with the PG Adapter Port.properties
+    // Update Django setting.py file with the PG Adapter Port.properties
     StringBuilder original = new StringBuilder();
     try (Scanner scanner = new Scanner(new FileReader(DJANGO_SETTING_FILE))) {
       while (scanner.hasNextLine()) {
@@ -80,7 +81,7 @@ public class ITDjangoTest extends DjangoTestSetup {
   }
 
   @Test
-  public void test() throws IOException, InterruptedException {
+  public void testRunSample() throws Exception {
     String actualOutput = executeIntegrationTests();
     String expectedOutput = "Django Sample Completed Successfully\n";
     assertTrue(actualOutput, actualOutput.contains(expectedOutput));
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/python/psycopg2/AbstractPsycopg2Test.java b/src/test/java/com/google/cloud/spanner/pgadapter/python/psycopg2/AbstractPsycopg2Test.java
index e72e4ec6..1ad920b5 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/python/psycopg2/AbstractPsycopg2Test.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/python/psycopg2/AbstractPsycopg2Test.java
@@ -16,6 +16,7 @@ package com.google.cloud.spanner.pgadapter.python.psycopg2;
 
 import com.google.cloud.spanner.pgadapter.AbstractMockServerTest;
 import com.google.cloud.spanner.pgadapter.python.PythonTestUtil;
+import java.io.File;
 import java.util.ArrayList;
 import java.util.List;
 import org.junit.BeforeClass;
@@ -45,9 +46,10 @@ abstract class AbstractPsycopg2Test extends AbstractMockServerTest {
 
   static String executeWithoutParameters(
       String pgVersion, String host, int port, String sql, String statementType) throws Exception {
+    File directory = new File(PSYCOPG2_PATH);
     return PythonTestUtil.run(
         new String[] {
-          "python3",
+          directory.getAbsolutePath() + "/venv/bin/python3",
           "StatementsWithoutParameters.py",
           pgVersion,
           host,
@@ -66,8 +68,9 @@ abstract class AbstractPsycopg2Test extends AbstractMockServerTest {
       String statementType,
       ArrayList<String> parameters)
       throws Exception {
+    File directory = new File(PSYCOPG2_PATH);
     List<String> runCommand = new ArrayList<>();
-    runCommand.add("python3");
+    runCommand.add(directory.getAbsolutePath() + "/venv/bin/python3");
     runCommand.add("StatementsWithParameters.py");
     runCommand.add(pgVersion);
     runCommand.add(host);
@@ -86,8 +89,9 @@ abstract class AbstractPsycopg2Test extends AbstractMockServerTest {
       String statementType,
       ArrayList<String> parameters)
       throws Exception {
+    File directory = new File(PSYCOPG2_PATH);
     List<String> runCommand = new ArrayList<>();
-    runCommand.add("python3");
+    runCommand.add(directory.getAbsolutePath() + "/venv/bin/python3");
     runCommand.add("StatementsWithNamedParameters.py");
     runCommand.add(pgVersion);
     runCommand.add(host);
@@ -106,8 +110,9 @@ abstract class AbstractPsycopg2Test extends AbstractMockServerTest {
       String statementType,
       ArrayList<String> parameters)
       throws Exception {
+    File directory = new File(PSYCOPG2_PATH);
     List<String> runCommand = new ArrayList<>();
-    runCommand.add("python3");
+    runCommand.add(directory.getAbsolutePath() + "/venv/bin/python3");
     runCommand.add("Batching.py");
     runCommand.add(pgVersion);
     runCommand.add(host);
@@ -120,8 +125,9 @@ abstract class AbstractPsycopg2Test extends AbstractMockServerTest {
 
   static String executeTransactions(
       String pgVersion, String host, int port, List<String> statements) throws Exception {
+    File directory = new File(PSYCOPG2_PATH);
     List<String> runCommand = new ArrayList<>();
-    runCommand.add("python3");
+    runCommand.add(directory.getAbsolutePath() + "/venv/bin/python3");
     runCommand.add("StatementsWithTransactions.py");
     runCommand.add(pgVersion);
     runCommand.add(host);
@@ -131,9 +137,15 @@ abstract class AbstractPsycopg2Test extends AbstractMockServerTest {
   }
 
   static String executeCopy(int port, String sql, String file, String copyType) throws Exception {
+    File directory = new File(PSYCOPG2_PATH);
     return PythonTestUtil.run(
         new String[] {
-          "python3", "StatementsWithCopy.py", Integer.toString(port), sql, file, copyType
+          directory.getAbsolutePath() + "/venv/bin/python3",
+          "StatementsWithCopy.py",
+          Integer.toString(port),
+          sql,
+          file,
+          copyType
         },
         PSYCOPG2_PATH);
   }
diff --git a/src/test/python/django/basic_test.py b/src/test/python/django/basic_test.py
index 272e0fbf..ac429f52 100644
--- a/src/test/python/django/basic_test.py
+++ b/src/test/python/django/basic_test.py
@@ -38,9 +38,9 @@ def create_django_setup(host, port):
                 'options': '-c TimeZone=UTC',
               },
               'TIME_ZONE': 'UTC'
-            }
           }
       }
+  }
   conf['DATABASES']['default']['PORT'] = port
   conf['DATABASES']['default']['HOST'] = host
   conf['USE_TZ'] = True
diff --git a/src/test/python/django/requirements.txt b/src/test/python/django/requirements.txt
new file mode 100644
index 00000000..bb812644
--- /dev/null
+++ b/src/test/python/django/requirements.txt
@@ -0,0 +1,3 @@
+psycopg2-binary~=2.9.3
+pytz~=2022.1
+Django~=4.1.1
diff --git a/src/test/python/psycopg2/requirements.txt b/src/test/python/psycopg2/requirements.txt
index 13873106..da25373b 100644
--- a/src/test/python/psycopg2/requirements.txt
+++ b/src/test/python/psycopg2/requirements.txt
@@ -1,2 +1,2 @@
-psycopg2~=2.9.3
+psycopg2-binary~=2.9.3
 pytz~=2022.1
diff --git a/src/test/python/requirements.txt b/src/test/python/requirements.txt
deleted file mode 100644
index 62775e8b..00000000
--- a/src/test/python/requirements.txt
+++ /dev/null
@@ -1,3 +0,0 @@
-psycopg2~=2.9.3
-pytz~=2022.1
-Django~=4.1.1
\ No newline at end of file