Python Data Access 简明教程

Python PostgreSQL - Create Table

可以使用 CREATE TABLE 语句在 PostgreSQL 中创建一个新表。在执行此操作时，您需要指定表名、列名及其数据类型。

You can create a new table in a database in PostgreSQL using the CREATE TABLE statement. While executing this you need to specify the name of the table, column names and their data types.

Syntax

以下是 PostgreSQL 中 CREATE TABLE 语句的语法。

Following is the syntax of the CREATE TABLE statement in PostgreSQL.

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

Example

以下示例在 PostgreSQL 中创建一个名为 CRICKETERS 的表。

Following example creates a table with name CRICKETERS in PostgreSQL.

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age INT,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
CREATE TABLE
postgres=#

您可以使用 \dt 命令检索 PostgreSQL 中数据库中的表列表。创建表后，如果您确认表列表，您可以按如下方式观察其中新创建的表：

You can get the list of tables in a database in PostgreSQL using the \dt command. After creating a table, if you can verify the list of tables you can observe the newly created table in it as follows −

postgres=# \dt
            List of relations
Schema  | Name       | Type  | Owner
--------+------------+-------+----------
public  | cricketers | table | postgres
(1 row)
postgres=#

同样，您可以使用 \d 获取已创建表的描述，如下所示：

In the same way, you can get the description of the created table using \d as shown below −

postgres=# \d cricketers
               Table "public.cricketers"
Column          | Type                   | Collation | Nullable | Default
----------------+------------------------+-----------+----------+---------
first_name      | character varying(255) |           |          |
last_name       | character varying(255) |           |          |
age             | integer                |           |          |
place_of_birth  | character varying(255) |           |          |
country         | character varying(255) |           |          |
postgres=#

Creating a table using python

要使用 Python 创建表，您需要使用 pyscopg2 的光标的 execute() 方法来执行 CREATE TABLE 语句。

To create a table using python you need to execute the CREATE TABLE statement using the execute() method of the Cursor of pyscopg2.

Example

以下 Python 示例创建一个名为 employee 的表。

The following Python example creates a table with name employee.

import psycopg2

#Establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)
#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#Creating table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)
print("Table created successfully........")
conn.commit()
#Closing the connection
conn.close()

Output

Table created successfully........