脚本不执行 cur.execute() 中的命令

Question

我是PostgreSQL和psycopg2的新手，我面临一个问题。

import psycopg2

def create_tables(whichone):
in_str_station = "CREATE TABLE station (id SMALLINT NOT NULL PRIMARY KEY,  name VARCHAR(40) NOT NULL," 
" country VARCHAR(3) NOT NULL, latitude VARCHAR(10),  " 
"longitude VARCHAR(10),height SMALLINT);"
in_str_dailyData = "CREATE TABLE daily_data (id BIGSERIAL NOT NULL PRIMARY KEY," 
"s_id SMALLINT NOT NULL REFERENCES station(id)," 
"d_date DATE NOT NULL, d_mean NUMERIC(6, 1), quality SMALLINT);"
int_str_monthlyMean = "CREATE TABLE monthly_mean (id BIGSERIAL NOT NULL PRIMARY KEY,"
"s_id SMALLINT NOT NULL REFERENCES station(id),"
"m_date DATE NOT NULL, m_mean NUMERIC(9, 3),"
"var NUMERIC(9, 3), std NUMERIC(9, 3));"
in_str_yearlymean = "CREATE TABLE yearly_mean (id BIGSERIAL NOT NULL PRIMARY KEY, " 
"s_id SMALLINT NOT NULL REFERENCES station(id)," 
"y_date DATE NOT NULL, y_mean NUMERIC(9, 3),var NUMERIC(9, 3)," 
"std NUMERIC(9, 3), var_m NUMERIC(9, 3), std_m NUMERIC(9, 3));"
database_list = {'station': in_str_station, 'monthly_mean': int_str_monthlyMean,
'daily_data': in_str_dailyData, 'yearly_mean': in_str_yearlymean}
try:
conn = psycopg2.connect(
host="localhost",
database="climate",
user="postgres",
password="1")
cur = conn.cursor()
in_str = database_list.get(whichone)
cur.execute(in_str)
output_ = cur.fetchall()
print(output_)
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()

运行脚本后，无论选择哪一个in_str_，都不会创建表。我已经检查过，当我复制我在cur.execut中执行的in_str的内容并在PostgreSQL外壳中使用时，一切都正常。

我在哪里犯的错？

Answer 1

在cur.execute()之后但在conn.close()之前调用conn.commit()。事务不是使用psycopg2:隐式提交的

如果在事务进行时连接被关闭(使用close((方法(或销毁(使用del或使其脱离范围(，服务器将丢弃该事务。

Answer 2

我不知道你刚才是不是在这里这样做了，但缩进错误。您需要在函数后面缩进代码。

def foo(a):
pass