Python 如何删除 SQLAlchemy 中的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35918605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete a table in SQLAlchemy?
提问by fedorqui 'SO stop harming'
I want to delete a table using SQLAlchemy.
我想使用 SQLAlchemy 删除一个表。
Since I am testing over and over again, I want to delete the table my_usersso that I can start from scratch every single time.
由于我一遍又一遍地测试，我想删除该表，my_users以便每次都可以从头开始。
So far I am using SQLAlchemy to execute raw SQL through the engine.execute()method:
到目前为止，我使用 SQLAlchemy 通过engine.execute()方法执行原始 SQL ：
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:
但是，我想知道是否有一些标准的方法可以做到这一点。我能找到的唯一一个是drop_all()，但它删除了所有结构，而不仅仅是一个特定的表：
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_usersin which I add some content.
例如，给出这个非常基本的例子。它由一个带有单个表的 SQLite 基础设施组成my_users，我在其中添加了一些内容。
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.
对于这种特定情况，drop_all()会起作用，但是从我开始拥有不止一张桌子并且我想保留其他桌子的那一刻起就不方便了。
回答by daveoncode
Just call drop()against the table object.
From the docs:
只需调用drop()table 对象。从文档：
Issue a DROP statement for this Table, using the given Connectable for connectivity.
为该表发出 DROP 语句，使用给定的 Connectable 进行连接。
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
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)
回答by WeiHao
Alternative to calling cls.__table__.drop(your_engine), you can try this:
除了 call cls.__table__.drop(your_engine)，你可以试试这个：
Base.metadata.drop_all(bind=your_engine, tables=[User.__table__])
This method as well as the create_all()method accept an optional argument tables, which takes an iterator of sqlalchemy.sql.schema.Tableinstances.
此方法以及该create_all()方法接受一个可选参数tables，该参数采用sqlalchemy.sql.schema.Table实例迭代器。
You can control which tables are to be created or dropped in this way.
您可以通过这种方式控制要创建或删除哪些表。
回答by oscarvalles
Below is example code you can execute in iPython to test the creation and deletion of a table on Postgres
以下是您可以在 iPython 中执行以测试 Postgres 上表的创建和删除的示例代码
from sqlalchemy import * # imports all needed modules from sqlalchemy
engine = create_engine('postgresql://python:[email protected]/production') # connection properties stored
metadata = MetaData() # stores the 'production' database's metadata
users = Table('users', metadata,
Column('user_id', Integer),
Column('first_name', String(150)),
Column('last_name', String(150)),
Column('email', String(255)),
schema='python') # defines the 'users' table structure in the 'python' schema of our connection to the 'production' db
users.create(engine) # creates the users table
users.drop(engine) # drops the users table
You can also preview my article on Wordpress with this same example and screenshots: oscarvalles.wordpress.com (search for SQL Alchemy).
您还可以使用相同的示例和屏幕截图预览我在 Wordpress 上的文章：oscarvalles.wordpress.com（搜索 SQL Alchemy）。
回答by Levon
For the special case when you don't have access to the table class and just need to delete the table by table name then use this code
对于您无权访问表类而只需要按表名删除表的特殊情况，则使用此代码
import logging
from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base
DATABASE = {
'drivername': 'sqlite',
# 'host': 'localhost',
# 'port': '5432',
# 'username': 'YOUR_USERNAME',
# 'password': 'YOUR_PASSWORD',
'database': '/path/to/your_db.sqlite'
}
def drop_table(table_name):
engine = create_engine(URL(**DATABASE))
base = declarative_base()
metadata = MetaData(engine, reflect=True)
table = metadata.tables.get(table_name)
if table is not None:
logging.info(f'Deleting {table_name} table')
base.metadata.drop_all(engine, [table], checkfirst=True)
drop_table('users')
