CODEWITHSUNDEEP
pythonpostgresqldockerpsycopg2
Kurt Peek
Kurt Peek

Reputation: 57381

How to create a table in PostgreSQL using pyscopg2?

I'm running a Docker Compose application which includes postgres as a service. Here is a portion of docker-compose.yml:

version: '3'

services:
  postgres:
    image: postgres
    environment:
      - POSTGRES_PASSWORD=foo
    ports:
      - 5432:5432

After docker-compose build followed by docker-compose up I see the container is running if I do docker ps:

kurt@kurt-ThinkPad:~$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
8a7a20686728        postgres            "docker-entrypoint..."   28 minutes ago      Up 17 seconds       0.0.0.0:5432->5432/tcp   apkapi_postgres_1

Since port 5432 is mapped to the localhost, I figured I could do the following (in ipython):

In [1]: import psycopg2

In [2]: conn = psycopg2.connect(dbname="postgres", user="postgres", password="fo
   ...: o", host="localhost")

In [3]: cursor = conn.cursor()

In [4]: cursor.execute("CREATE TABLE apks (s3_uri text);")

All these commands execute without any error. To check the result, I tried (using the container ID from docker ps):

kurt@kurt-ThinkPad:~$ docker exec -it 8a7a20686728 bash
root@8a7a20686728:/# psql -U postgres
psql (9.6.3)
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | mytable | table | postgres
(1 row)

The problem is that I see a table mytable which I had created before, but not the table apks which I tried to create using psycopg2. Am I not creating the table correctly?

Upvotes: 3

Views: 2992

Answers (1)

user7954200
user7954200

Reputation: 66

It sounds like you may not have autocommit enabled so any changes you make to the database aren't being saved. Try enabling it using conn.autocommit = True or, after the execute command, conn.commit().

Upvotes: 5

Related Questions