使用SQLAlchemy将数据添加到PostgreSQL数据库中

1 年 ago

逸, 科

2 minutes

首先

使用Python的SQLAlchemy包将数据添加到postgreSQL中。
由于遇到了许多错误，因此还记录了处理方法。

开发环境

VSCode

ターミナル

gitbash
Windows PowerShell

データベース

postgreSQL 15.3

言語

Python 3.11.3

Pythonのパッケージ

psycopg2 2.9.6
python-dotenv 1.0.0
SQLAlchemy 2.0.16

安装PostgreSQL

从官方网站下载postgreSQL。
https://www.postgresql.org/download/
进行密码、选项等设置（本次默认进行）。

确认安装

确认安装是否成功

启动终端

如果使用默认设置，用户名将为postgres。

psql -U ユーザー名
"psql: command not found"

因为环境变量没有设置通路，所以被告知没有psql这个命令，因此需要设置通路来解决。

设定环境变量

如果是Windows系统的情况下

setx PATH "PAYH%;C:\Program Files\PostgreSQL\version\bin"

version 指定了实际安装的 postgreSQL 版本。

对于macOS/Linux的情况而言

export PATH="/usr/local/pgsql/bin:$PATH"

如果路径设置正确，请重新输入psql命令。

psql -U ユーザー名
psql: could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

下一个问题是，被告知无法连接到PostgreSQL服务器。
由于服务器设置为默认，因此主机名和端口号不应该出错…

由于PostgreSQL服务器未运行，所以需要启动服务器。

启动postgreSQL服务器

pg_ctl start
pg_ctl: no database directory specified and environment variable PGDATA unset

由于未设置环境变量PGDATA，服务器无法启动，出现错误。

设置环境变量PGDATA

如果是Windows终端的情况下

set PGDATA=C:\Program Files\PostgreSQL\data

只需一种选择，将以下内容以中文本地方式改写：PowerShell的情况

$env:PGDATA="C:\Program Files\PostgreSQL\data

对于 macOS/Linux 的情况下

export PGDATA="/usr/local/pgsql/data:$PATH"

如果设置了环境变量，就启动服务器。

pg_ctl start
server starting

如果服务器无法正常启动，需要检查服务器的状态。

pg_ctl status

您可以通过运行 pg_ctl –help 命令来查看有关 pg_ctl 命令的详细信息。
要停止服务器，请输入 pg_ctl stop。

终于能够启动服务器了，所以我要确认 PostgreSQL 是否正确安装。

psql -U ユーザー名
postgres password:

请提供密码，输入安装时设置的密码。

只要切换到SQL输入，就能成功！

当要结束时，请按\q键或按Ctrl+C键。

创建数据库

createdb -U ユーザー名 データベース名

如果默认情况下，用户名是postgres，并且要创建的数据库的名称是数据库名。

如果要求输入密码，那就输入密码。

下一步，我们要编辑已创建的数据库，因此需要连接到数据库。

psql -U ユーザー名　データベース名

数据库名称是刚才创建的数据库。

创建桌子

一旦连接到数据库，使用SQL语句创建数据表

这次，我们使用SQL来创建表格和列，并使用Python进行行的添加。

CREATE TABLE テーブル名 (id INT SERIAL PRIMARY KEY,Column1 VARCHAR[50],Column2 TEXT);

创建表的表名

将id设为主键（PRIMARY KEY）。

Column1和Column2是列名
在列名后面写入该列中的值的类型

SQL的数据类型

整数型（Integer）: 整数値を格納するためのデータ型例: INT, INTEGER, SMALLINT, BIGINTなど。

浮動小数点型（Floating-Point）: 浮動小数点数を格納するためのデータ型例: FLOAT, REAL, DOUBLE PRECISIONなど。

文字列型（String）: 文字列を格納するためのデータ型例: CHAR, VARCHAR, TEXTなど。

日付型（Date）: 日付を格納するためのデータ型例: DATE, TIMESTAMPなど。

真偽値型（Boolean）: 真偽値（True/False）を格納するためのデータ型例: BOOLEANなど。

その他の型: 上記以外にも、バイナリデータを格納するためのバイナリ型、JSONデータを格納するためのJSON型、配列データを格納するための配列型など。

确认所创建的表格

显示该数据库中的表格列表。

显示`テーブル名’列的表

显示表名为”表格名”的表的所有内容。

添加一行

使用Python的SQLAlchemy与数据库建立连接。

使用想要添加的数据来描述的csv文件来进行行的添加

Column1,Column2
a,hogehoge
b,hogehoge

作为安全措施，将在Python源代码中使用的postgreSQL用户名和密码存储在.env文件中。

SQL_USERNAME=ユーザー名
SQL_PASSWORD=パスワード

安装软件包

安装Python所需的软件包

如果在venv虚拟环境中进行安装

python3 -m venv 仮想環境名
\仮想環境名\Scripts\Activate

結束虛擬環境時，請執行「deactivate」。

安装软件包

pip install SQLAlchemy
pip install python-dotenv
pip install psycopg2

使用Python添加行

断开与postgreSQL的连接

import csv
from sqlalchemy import create_engine,text
import os
from dotenv import load_dotenv

#.envファイルの読み込み
load_dotenv()

#.envファイルからpostgreSQLのユーザー名とパスワードを取得
username=os.getenv("SQL_USERNAME")
password=os.getenv("SQL_PASSWORD")

#postgreSQLに接続
engine=create_engine(f"postgresql://{username}:{password}@localhost:5432/データベース名")

with open("csvファイルのパス","r",encoding="utf-8") as csvfile:
    reader=csv.reader(csvfile)
    next(reader) #csvファイルの1行目(列名)を除く
    for row in reader:
        sql=text(f"INSERT INTO テーブル名 (Column1,Column2) VALUES ('{row[0]}','{row[1]}');")
        with engine.begin() as connection:
            connection.execute(sql)

engine.dispose() #postgreSQLの接続を切る

执行

启动PostgreSQL服务器。

pg_ctl start

进入存放有Python文件的根目录，并执行该Python文件。

python3 pythonファイル名

请确认数据是否已添加。

psql -U ユーザー名 データベース名

SELECT * FROM テーブル名

只需一个选项，用中文原生地表达以下内容：\q结束。

总结

操作步骤

使用psql连接到数据库
使用SQL创建表和列
使用Python添加行
确认表格

错误的主要原因和处理方法

環境変数の設定

postgreSQLサーバが起動されていない

pg_ctl statusでサーバ状態の確認

pg_ctl startでサーバ起動