Psycopg2无法创建表 [英] Psycopg2 can not create table

问题描述

从Jupiter笔记本中，我能够使用Psycopg2创建数据库. 但是以某种方式，我无法创建表并在其中存储元素.

From Jupiter notebook, I was able to create Database with Psycopg2. But somehow I was not able to create Table and store element in it.

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

con = psycopg2.connect("user=postgres password='abc'");
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT);

cursor          = con.cursor();
name_Database   = "socialmedia";

sqlCreateDatabase = "create database "+name_Database+";"

cursor.execute(sqlCreateDatabase);

使用上面的代码，我可以从psql(Windows命令提示符)中看到名为"socialmedia"的数据库.

With the above code, I can see database named "socialmedia" from psql (windows command prompt).

但是使用下面的代码，我无法从psql中看到名为"test_table"的表.

But with the below code, I can not see table named "test_table" from psql.

import psycopg2

# Open a DB session
dbSession = psycopg2.connect("dbname='socialmedia' user='postgres' password='abc'");
# Open a database cursor
dbCursor = dbSession.cursor();
# SQL statement to create a table
sqlCreateTable  = "CREATE TABLE test_table(id bigint, cityname varchar(128), latitude numeric, longitude numeric);";
# Execute CREATE TABLE command
dbCursor.execute(sqlCreateTable);
# Insert statements
sqlInsertRow1  = "INSERT INTO test_table values(1, 'New York City', 40.73, -73.93)";
sqlInsertRow2  = "INSERT INTO test_table values(2, 'San Francisco', 37.733, -122.446)";


# Insert statement
dbCursor.execute(sqlInsertRow1);
dbCursor.execute(sqlInsertRow2);

# Select statement
sqlSelect = "select * from test_table";
dbCursor.execute(sqlSelect);
rows = dbCursor.fetchall();

# Print rows
for row in rows:
    print(row);

我只能从Jupyter笔记本中获取元素，而不能从psql中获取元素. 而且似乎元素是临时存储的.

I can get elements only from Jupyter notebook, but not from psql. And it seems elements are stored temporarily.

如何查看psql中的表和元素并永久保留该元素?

How can I see table and elements from psql and keep the element permanently?

推荐答案

我在问题的第二部分看不到任何dbCursor.execute('commit')吗?

I don't see any dbCursor.execute('commit') in the second part of your question?

您提供了一个可以使用AUTOCOMMIT的示例，并且您正在问为什么不使用AUTOCOMMIT时为什么results are stored temporarly? 好吧，他们没有犯！

You have provided an example with AUTOCOMMIT which works, and you are asking why results are stored temporarly when you are not using AUTOCOMMIT? Well, they are not commited!

它们仅在当前会话中存储，这就是为什么您可以从Jupyter会话中获取它们

They are stored only for the current session, that's why you can get it from your Jupyter session

也:

• 您不需要在Python代码中添加分号
• 您不需要在SQL代码中添加分号(执行多个语句时除外，此处不是这种情况)

这篇关于Psycopg2无法创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！