文章目录

SQLAlchemy Basics TutorialInstall a Postgres server locally and create a databaseInstall requirements创建用户与密码设置创建数据库并指定一个拥有者删除数据库查看数据库切换到另一个数据库sqlalchemy连接数据库Create a table查看表格是否创建成功Inserting rowsQuerying rows Load CSVs Into PostgreSQL by SQLAlchemy

SQLAlchemy Basics Tutorial

入口 讲了不少基本概念和基本操作，建议首先学习下

Install a Postgres server locally and create a database

Install requirements

PostgreSQL ubuntu安装教程Psycopg2SQLAlchemy 安装SQLAlchemy, Psycopg2 pip install sqlalchemy pip install psycopg2

Ubuntu 安装 PostgreSQL

sudo apt-get update sudo apt-get install postgresql postgresql-client

安装完毕后，系统会创建一个数据库超级用户 postgres，密码为空。

sudo -i -u postgres # 这时使用以下命令进入 postgres，输出以下信息，说明安装成功： ~$ psql psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)) Type "help" for help. postgres=#

创建用户与密码设置

postgres=# \password postgres # 为postgres用户设置一个密码 postgres=# CREATE USER zdx WITH PASSWORD '123456'; # 创建用户

创建数据库并指定一个拥有者

postgres=# CREATE DATABASE wordcount_dev OWNER zdx;

删除数据库

postgres=# DROP DATABASE wordcount_dev;

查看数据库

postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ---------------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres wordcount_dev | zdx | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows)

切换到另一个数据库

postgres=# \c wordcount_dev You are now connected to database "wordcount_dev" as user "postgres". wordcount_dev=# Using Python with SQLAlchemy to connect to the database and create tables

到这里，数据库已经建好了，所以下面就开始使用python 中的SQLAlchemy库来连接数据库并创建一些表

sqlalchemy连接数据库

import sqlalchemy as db # Scheme: "postgres+psycopg2://<USERNAME>:<PASSWORD>@<IP_ADDRESS>:<PORT>/<DATABASE_NAME>" DATABASE_URI = 'postgres+psycopg2://zdx:123456@localhost:5432/wordcount_dev' engine = db.create_engine(DATABASE_URI)

Create a table

from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Date Base = declarative_base() class Book(Base): __tablename__ = 'books' id = Column(Integer, primary_key=True) title = Column(String) author = Column(String) pages = Column(Integer) published = Column(Date) def __repr__(self): return "<Book(title='{}', author='{}', pages={}, published={})>"\ .format(self.title, self.author, self.pages, self.published) Base.metadata.create_all(engine)

查看表格是否创建成功

wordcount_dev=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+--------------+----------+-------+------------+------------- public | books | table | zdx | 8192 bytes | public | books_id_seq | sequence | zdx | 8192 bytes | (2 rows)

Destroy all table in the database

Base.metadata.drop_all(engine)

Inserting rows

from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind=engine) s = Session() # Working with sessions from datetime import datetime book = Book( title='Deep Learning', author='Ian Goodfellow', pages=775, published=datetime(2016, 11, 18) ) s.add(book) s.commit()

查看刚插入的记录

wordcount_dev=# select * from books; id | title | author | pages | published ----+---------------+----------------+-------+------------ 1 | Deep Learning | Ian Goodfellow | 775 | 2016-11-18 (1 row)

Querying rows

s.query(Book).first() Out: <Book(title='Deep Learning', author='Ian Goodfellow', pages=775, published=2016-11-18 00:00:00)> s.close() # close the current session

Load CSVs Into PostgreSQL by SQLAlchemy

By pandastransaction commands, which means that all commands must be done successfully or all should rollback in case of an error比较完整的一个大例子 （但还是插入单个表，但代码的注释写的很详细，有助于了解更多的细节）