如何在 SQLAlchemy 中删除表? [英] How to delete a table in SQLAlchemy?

问题描述

我想使用 SQLAlchemy 删除一个表.

因为我反复测试，我想删除表my_users，这样我每次都可以从头开始.

到目前为止，我使用 SQLAlchemy 通过 engine.execute() 方法:

sql = text('DROP TABLE IF EXISTS my_users;')结果 = engine.execute(sql)

但是，我想知道是否有一些标准的方法可以做到这一点.我唯一能找到的是 drop_all()，但是它删除了所有的结构，而不仅仅是一个特定的表:

Base.metadata.drop_all(engine) # 删除所有表

<小时>

例如，给出这个非常基本的例子.它包含一个 SQLite 基础架构，带有一个表 my_users，我在其中添加了一些内容.

from sqlalchemy import create_engine, Column, Integer, String, text从 sqlalchemy.orm 导入 sessionmaker从 sqlalchemy.ext.declarative 导入 declarative_baseengine = create_engine('sqlite://', echo=False)Base = declarative_base()类用户(基础):__tablename__ = "my_users"id = 列(整数，primary_key=True)名称 = 列(字符串)def __init__(self, name):self.name = 姓名# 创建数据库中所有的表# 由 Base 的子类定义，例如 UserBase.metadata.create_all(引擎)# 构造一个 sessionmaker 工厂对象session = sessionmaker()# 将 sessionmaker 绑定到引擎session.configure(绑定=引擎)# 生成一个会话来使用s = 会话()# 添加一些内容s.add(用户('我的名字'))s.commit()# 获取数据打印(s.query(User).filter(User.name == 'myname').one().name)

对于这种特定情况，drop_all() 会起作用，但从我开始拥有多个表而我想保留其他表的那一刻起就不方便了.

解决方案

只需对 table 对象调用 drop() 即可.来自 文档:

<块引用>

为此表发出 DROP 语句，使用给定的 Connectable 进行连接.

在你的情况下应该是:

User.__table__.drop()

<小时>

如果您遇到以下异常:

<块引用>

sqlalchemy.exc.UnboundExecutionError:表对象my_users"未绑定到引擎或连接.如果没有要执行的数据库，则无法继续执行

你需要通过引擎:

User.__table__.drop(engine)

I want to delete a table using SQLAlchemy.

Since I am testing over and over again, I want to delete the table my_users so that I can start from scratch every single time.

So far I am using SQLAlchemy to execute raw SQL through the engine.execute() method:

sql = text('DROP TABLE IF EXISTS my_users;')
result = engine.execute(sql)

However, I wonder if there is some standard way to do so. The only one I could find is drop_all(), but it deletes all the structure, not only one specific table:

Base.metadata.drop_all(engine)   # all tables are deleted

---

For example, given this very basic example. It consists on a SQLite infrastructure with a single table my_users in which I add some content.

from sqlalchemy import create_engine, Column, Integer, String, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite://', echo=False)
Base = declarative_base()

class User(Base):
    __tablename__ = "my_users"

    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __init__(self, name):
        self.name = name

# Create all the tables in the database which are
# defined by Base's subclasses such as User
Base.metadata.create_all(engine)

# Construct a sessionmaker factory object
session = sessionmaker()

# Bind the sessionmaker to engine
session.configure(bind=engine)

# Generate a session to work with
s = session()

# Add some content
s.add(User('myname'))
s.commit()

# Fetch the data
print(s.query(User).filter(User.name == 'myname').one().name)

For this specific case, drop_all() would work, but it won't be convenient from the moment I start having more than one table and I want to keep the other ones.

解决方案

Just call drop() against the table object. From the docs:

Issue a DROP statement for this Table, using the given Connectable for connectivity.

In your case it should be:

User.__table__.drop()

---

If you get an exception like:

sqlalchemy.exc.UnboundExecutionError: Table object 'my_users' is not bound to an Engine or Connection. Execution can not proceed without a database to execute against

You need to pass the engine:

User.__table__.drop(engine)

这篇关于如何在 SQLAlchemy 中删除表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！