PostgreSQL relation doesn't exist (Python)

Question

I created a database in psql and in it, created a table called "tweet".

CREATE TABLE tweet 
 ( tid CHARACTER VARYING NOT NULL, DATA json, 
   CONSTRAINT tid_pkey PRIMARY KEY (tid) );

Then when I use

SELECT * FROM tweet; 

in the psql window it works and shows an empty table.

Now I have a python script that takes JSON data and is loading it into this table.

conn_string = "host='localhost' port=5432 dbname='tweetsql' user='tweetsql' password='tweetsql'"

conn = psycopg2.connect(conn_string)
cur = conn.cursor()

That sets up the connection and I don't think it had any issues. Now I have some logic to read in the JSON file and then to add it in, I say:

cur.execute("INSERT INTO tweet (tid, data) VALUES (%s, %s)", (cur_tweet['id'], json.dumps(cur_tweet, cls=DecimalEncoder), ))

But this always says that the relation tweet doesn't exist. Am I missing something here? Is there an issue with my connection or can my script somehow not see the table? For reference I'm using psycopg2 for the connection.

EDIT: I updated the DDL to include a transaction I could commit but that didn't fix it either. Is it a schema issue?

This is what I did regarding the table creation to commit:

BEGIN;
CREATE TABLE tweet 
 ( tid CHARACTER VARYING NOT NULL, DATA json, 
   CONSTRAINT tid_pkey PRIMARY KEY (tid) );
COMMIT;

EDIT 2: I'm posting some code here...

import psycopg2
import json 
import decimal 
import os 
import ctypes 

conn_string = "host='localhost' port=5432 dbname='tweetsql' user='tweetsql' password='tweetsql'"
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
cur.execute("CREATE TABLE tweet (tid CHARACTER VARYING NOT NULL, DATA json, CONSTRAINT tid_pkey PRIMARY KEY (tid) );")
cur.commit() 


for file in os.listdir(path):    
    if not is_hidden(file):     
        with open(path+file, encoding='utf-8') as json_file:
            tweets = json.load(json_file, parse_float=decimal.Decimal)
            for cur_tweet in tweets:
                cur.execute("INSERT INTO tweet (tid, data) VALUES (%s, %s)", (cur_tweet['id'], json.dumps(cur_tweet, cls=DecimalEncoder), ))

cur.commit()
cur.close()
conn.close()

Answer 1

You're probably not committing the table creation, and, (I'm assuming; not seeing your complete code) you're starting a new connection via psycopg2 each time. You need to commit right after the table creation, and not in a new connection, as each connection is its own implicit transaction. So, your code flow should be something like this:

1. connect to the db
2. create the table using the cursor
3. fill the table
4. commit and disconnect from db.

Or, if you must separate creation from filling, just commit and disconnect after (2) and then reconnect before (3).