Xem thảo luận
Cải thiện bài viết
Lưu bài viết
Xem thảo luận
Cải thiện bài viết
Lưu bài viết
Đọc
Bàn luận is one of the Data Definition Language [ DDL ] statements supported by the PostgreSQL Database Management System. It is used to create database in PostgreSQL. Database name should be always unique. If it already exists then it shows that the particular database already exists.
Trong bài viết này, chúng tôi sẽ thảo luận về cách tạo cơ sở dữ liệu trong psotgresql bằng pysopg2 trong Python. CREATE DATABASE database_name;
Tạo cơ sở dữ liệu là một trong những câu lệnh Ngôn ngữ định nghĩa dữ liệu [DDL] được hỗ trợ bởi hệ thống quản lý cơ sở dữ liệu PostgreSQL. Nó được sử dụng để tạo cơ sở dữ liệu trong PostgreSQL. Tên cơ sở dữ liệu phải luôn luôn duy nhất. Nếu nó đã tồn tại thì nó cho thấy cơ sở dữ liệu cụ thể đã tồn tại. & NBSP;
Python3
Cú pháp: Tạo cơ sở dữ liệu cơ sở dữ liệu_name;
Ví dụ: Tạo cơ sở dữ liệu bằng PysCOPG2 & NBSP;
con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
3con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
4con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
1con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
6con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
7con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
8con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
9con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
1InternalError: CREATE DATABASE cannot run inside a transaction block
1con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
7con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
8InternalError: CREATE DATABASE cannot run inside a transaction block
4con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
1InternalError: CREATE DATABASE cannot run inside a transaction block
6con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
7con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
8InternalError: CREATE DATABASE cannot run inside a transaction block
9con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
1psql -postgres -U UserName
1con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
7import
psycopg2
psql -postgres -U UserName
7con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
0____11 con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
2con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
8psql -postgres -U UserName
4con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
1 psql -postgres -U UserName
6psql -postgres -U UserName
8con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
1 create database test;
0create database test;
7create database test;
8create database test;
90Code language: Python [python]
#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
1Code language: Python [python]
#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
2Code language: Python [python]
#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
1create database test;
1 con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
3
create database test;
Database has been created successfully !!
create database test;
4con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
1 create database test;
6PostgreSQL:
create database test;
con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
create database test;
# ----- ví dụ chương trình Python để tạo cơ sở dữ liệu trong PostgreSQL & NBSP; sử dụng psycopg2 -----
# Nhập máy khách PostgreSQL cho Python
Nhập PSYCOPG2
từ psycopg2.Extensions nhập khẩu solation_level_autocommit
# Kết nối với PostgreSQL DBMS
con = psycopg2.connect ["user = test password = 'test'"];
con.set_isolation_level[ISOLATION_LEVEL_AUTOCOMMIT];
# Có được một con trỏ DB
con trỏ & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; & nbsp; = con.cursor [];
name_database & nbsp; & nbsp; = "SocialMedia";
# Tạo câu lệnh bảng
sqlCreatedatabase = "Tạo cơ sở dữ liệu"+name_database+";"
# Tạo bảng trong cơ sở dữ liệu PostgreSQL
cursor.execute[sqlCreateDatabase];
Tôi muốn tạo cơ sở dữ liệu Postgres bằng Python.
con = psql.connect[dbname='postgres',
user=self.user_name, host='',
password=self.password]
cur = con.cursor[]
cur.execute["CREATE DATABASE %s ;" % self.db_name]
Tôi nhận được lỗi sau:
InternalError: CREATE DATABASE cannot run inside a transaction block
Tôi đang sử dụng psycopg2 để kết nối. Tôi không hiểu vấn đề là gì. Tôi đang cố gắng làm gì là kết nối với cơ sở dữ liệu [Postgres]:
psql -postgres -U UserName
Và sau đó tạo một cơ sở dữ liệu khác:
create database test;
Đây là những gì tôi thường làm và tôi muốn tự động hóa điều này bằng cách tạo tập lệnh Python.
Tóm tắt: Trong hướng dẫn này, bạn sẽ tìm hiểu cách tạo các bảng mới trong cơ sở dữ liệu PostgreSQL bằng Python.: in this tutorial, you will learn how to create new tables in the PostgreSQL database using Python.
Hướng dẫn này giả định rằng bạn biết cách viết
3Statement. Nếu đây không phải là trường hợp, bạn nên xem hướng dẫnCode language: Python [python]
#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
3.Code language: Python [python]
#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
Các bước để tạo bảng PostgreSQL trong Python
Để tạo một bảng mới trong cơ sở dữ liệu PostgreSQL, bạn sử dụng các bước sau:
- Đầu tiên, xây dựng tạo câu lệnh bảng.
- Tiếp theo, kết nối với cơ sở dữ liệu PostgreSQL bằng cách gọi hàm
5. Hàm
Code language: Python [python]#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
5 trả về một đối tượng
Code language: Python [python]#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
7.
Code language: Python [python]#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
- Sau đó, tạo đối tượng ________ 58 & nbsp; bằng cách gọi phương thức
9 của đối tượng
Code language: Python [python]#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
7.
Code language: Python [python]#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
- Sau đó, thực hiện
3 bằng cách gọi phương thức
Code language: Python [python]#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
2 của đối tượng
Code language: CSS [css]python create_table.py
8.
Code language: Python [python]#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
- Cuối cùng, đóng giao tiếp với máy chủ cơ sở dữ liệu PostgreSQL bằng cách gọi các phương thức
4 của các đối tượng
Code language: CSS [css]python create_table.py
8 và
Code language: Python [python]#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
7.
Code language: Python [python]#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
Tạo bảng trong ví dụ Python
1] Tạo chương trình Python
Đầu tiên, tạo một tệp mới có tên
7.Code language: CSS [css]
python create_table.py
Thứ hai, bên trong tệp
7, xác định một hàm mới gọi làCode language: CSS [css]
python create_table.py
9.Code language: CSS [css]
python create_table.py
Hàm
9 tạo ra bốn bảng trong cơ sở dữ liệuCode language: CSS [css]
python create_table.py
1:Code language: PHP [php]
suppliers=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors | table | postgres [4 rows]
2,Code language: PHP [php]
suppliers=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors | table | postgres [4 rows]
3,Code language: PHP [php]
suppliers=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors | table | postgres [4 rows]
4 vàCode language: PHP [php]
suppliers=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors | table | postgres [4 rows]
5.Code language: PHP [php]
suppliers=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors | table | postgres [4 rows]
Code language: Python [python]
#!/usr/bin/python import psycopg2 from config import config def create_tables[]: """ create tables in the PostgreSQL database""" commands = [ """ CREATE TABLE vendors [ vendor_id SERIAL PRIMARY KEY, vendor_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE parts [ part_id SERIAL PRIMARY KEY, part_name VARCHAR[255] NOT NULL ] """, """ CREATE TABLE part_drawings [ part_id INTEGER PRIMARY KEY, file_extension VARCHAR[5] NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """, """ CREATE TABLE vendor_parts [ vendor_id INTEGER NOT NULL, part_id INTEGER NOT NULL, PRIMARY KEY [vendor_id , part_id], FOREIGN KEY [vendor_id] REFERENCES vendors [vendor_id] ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY [part_id] REFERENCES parts [part_id] ON UPDATE CASCADE ON DELETE CASCADE ] """] conn = None try: # read the connection parameters params = config[] # connect to the PostgreSQL server conn = psycopg2.connect[**params] cur = conn.cursor[] # create table one by one for command in commands: cur.execute[command] # close communication with the PostgreSQL database server cur.close[] # commit the changes conn.commit[] except [Exception, psycopg2.DatabaseError] as error: print[error] finally: if conn is not None: conn.close[] if __name__ == '__main__': create_tables[]
2] Thực hiện chương trình Python
Để thực hiện chương trình Python, bạn sử dụng lệnh sau:
Code language: CSS [css]
python create_table.py
3] Xác minh việc tạo bảng
Đầu tiên, đăng nhập vào máy chủ cơ sở dữ liệu PostgreSQL bằng chương trình PSQL.
Thứ hai, sử dụng lệnh
6 để hiển thị danh sách bảng từ cơ sở dữ liệuCode language: PHP [php]
suppliers=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors | table | postgres [4 rows]
1.Code language: PHP [php]
suppliers=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors | table | postgres [4 rows]
Code language: PHP [php]
suppliers=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors | table | postgres [4 rows]
Như bạn thấy có thể thấy rõ từ đầu ra, chúng tôi có bốn bảng được tạo thành công trong cơ sở dữ liệu
1.Code language: PHP [php]
suppliers=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors | table | postgres [4 rows]
Nếu bạn sử dụng công cụ máy khách khác như PGADMIN, bạn có thể xem các bảng thông qua danh sách bảng trong lược đồ
9.Code language: PHP [php]
suppliers=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors | table | postgres [4 rows]
Trong hướng dẫn này, bạn đã học từng bước làm thế nào để tạo các bảng PostgreSQL mới trong Python bằng bộ điều hợp cơ sở dữ liệu PSYCOPG.
Hướng dẫn này có hữu ích không?