I am trying to create a table that logs my app in postgresql. Below works well.
conn = psycopg2.connect(database="db1", user = "postgres", password = "", host = "myhost", port = "5432")
cur = conn.cursor()
cur.execute('''CREATE TABLE login
(USERNAME VARCHAR(20) NOT NULL,
TS TIMESTAMP);''')
cur.execute("""INSERT INTO public.login (USERNAME,TS) \
VALUES ('TEST','2019-12-03')""");
my_table = pd.read_sql('SELECT * FROM public.login', conn)
conn.close()
I thought the table/relation would have been created and saved. But when I reconnect I get error that UndefinedTable: relation "public.login" does not exist.
conn = psycopg2.connect(database="db1", user = "postgres", password = "", host = "myhost", port = "5432")
print ("Opened database successfully")
cur = conn.cursor()
cur.execute("""INSERT INTO public.login (USERNAME,TS) \
VALUES ('TEST','2019-12-03')""");
my_table = pd.read_sql('SELECT * FROM public.login', conn)
The table is not there with below, as answered in How do I get tables in postgres using psycopg2?
cur.execute("""SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'""")
for table in cur.fetchall():
print(table)
I am confused why closing connection would cause this.
