blocks|key|575294|text|这称为数据库迁移(SQLAlchemy不支持开箱即用的迁移)。您可以考虑使用sqlalchemy-migrate在这类情况下提供帮助，或者只需通过所选数据库的命令行实用程序使用ALTER+TABLE，|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|575295|entityMap|0|LINK|mutability|MUTABLE|url|https://opendev.org/x/sqlalchemy-migrate^0|2G|B|12|I|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

This is referred to as database migration (SQLAlchemy doesn't support migration out of the box). You can look at using <a href="https://opendev.org/x/sqlalchemy-migrate" rel="nofollow noreferrer">sqlalchemy-migrate</a> to help in these kinds of situations, or you can just <code>ALTER TABLE</code> through your chosen database's command line utility,

blocks|key|575452|text|我也有同样的问题，一想到只为这些微不足道的事情使用迁移库，我就不由自主地|type|unstyled|depth|inlineStyleRanges|entityRanges|data|575453|颤抖吧。无论如何，这是我到目前为止的尝试：|575454|def+add_column(engine,+table_name,+column):
++++column_name+=+column.compile(dialect=engine.dialect)
++++column_type+=+column.type.compile(engine.dialect)
++++engine.execute('ALTER+TABLE+%25s+ADD+COLUMN+%25s+%25s'+%25+(table_name,+column_name,+column_type))

column+=+Column('new_column_name',+String(100),+primary_key=True)
add_column(engine,+table_name,+column)|code-block|syntax|javascript|575455|尽管如此，我仍然不知道如何将primary_key=True插入到原始SQL请求中。|offset|length|style|CODE|575456|entityMap^0|0|0|0|E|G|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

I have the same problem, and a thought of using migration library only for this trivial thing makes me 
tremble. Anyway, this is my attempt so far:

<pre><code>def add_column(engine, table_name, column):
 column_name = column.compile(dialect=engine.dialect)
 column_type = column.type.compile(engine.dialect)
 engine.execute('ALTER TABLE %s ADD COLUMN %s %s' % (table_name, column_name, column_type))

column = Column('new_column_name', String(100), primary_key=True)
add_column(engine, table_name, column)
</code></pre>

Still, I don't know how to insert <code>primary_key=True</code> into raw SQL request.

blocks|key|791573|text|请参阅SQLAlchemy文档的这一部分：http://docs.sqlalchemy.org/en/latest/core/metadata.html#altering-schemas-through-migrations|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|791574|Alembic是提供此类型功能的最新软件，并且与SQLAlchemy由同一作者开发。|791575|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.sqlalchemy.org/en/latest/core/metadata.html#altering-schemas-through-migrations|1|http://alembic.readthedocs.org/^0|L|2J|0|0|0|7|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

See this section of the SQLAlchemy documentation: <a href="http://docs.sqlalchemy.org/en/latest/core/metadata.html#altering-schemas-through-migrations" rel="noreferrer">http://docs.sqlalchemy.org/en/latest/core/metadata.html#altering-schemas-through-migrations</a>

<a href="http://alembic.readthedocs.org/" rel="noreferrer">Alembic</a> is the latest software to offer this type of functionality and is made by the same author as SQLAlchemy.

blocks|key|575495|text|我有一个用sqlite3构建的名为"ncaaf.db“的数据库和一个名为”游戏“的表。因此，我会在linux命令提示符上进入相同的目录并执行以下操作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|575496|sqlite3+ncaaf.db
alter+table+games+add+column+q4+type+float|code-block|syntax|javascript|575497|这就是所需要的一切！只需确保在sqlalchemy代码中更新定义即可。|575498|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I have a database called "ncaaf.db" built with sqlite3 and a table called "games". So I would CD into the same directory on my linux command prompt and do

<pre><code>sqlite3 ncaaf.db
alter table games add column q4 type float
</code></pre>

and that is all it takes! Just make sure you update your definitions in your sqlalchemy code.

blocks|key|791770|text|我也有同样的问题，我最终只用原始sql编写了我自己的函数。如果您使用的是SQLITE3，这可能会很有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|791771|然后，如果您同时将该列添加到您的类定义中，则似乎可以做到这一点。|791772|import+sqlite3+

def+add_column(database_name,+table_name,+column_name,+data_type):

++connection+=+sqlite3.connect(database_name)
++cursor+=+connection.cursor()

++if+data_type+==+"Integer":
++++data_type_formatted+=+"INTEGER"
++elif+data_type+==+"String":
++++data_type_formatted+=+"VARCHAR(100)"

++base_command+=+("ALTER+TABLE+'{table_name}'+ADD+column+'{column_name}'+'{data_type}'")
++sql_command+=+base_command.format(table_name=table_name,+column_name=column_name,+data_type=data_type_formatted)

++cursor.execute(sql_command)
++connection.commit()
++connection.close()|code-block|syntax|javascript|791773|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I had the same problem, I ended up just writing my own function in raw sql. If you are using SQLITE3 this might be useful. 

Then if you add the column to your class definition at the same time it seems to do the trick. 

<pre><code>import sqlite3 

def add_column(database_name, table_name, column_name, data_type):

 connection = sqlite3.connect(database_name)
 cursor = connection.cursor()

 if data_type == "Integer":
 data_type_formatted = "INTEGER"
 elif data_type == "String":
 data_type_formatted = "VARCHAR(100)"

 base_command = ("ALTER TABLE '{table_name}' ADD column '{column_name}' '{data_type}'")
 sql_command = base_command.format(table_name=table_name, column_name=column_name, data_type=data_type_formatted)

 cursor.execute(sql_command)
 connection.commit()
 connection.close()
</code></pre>

blocks|key|791821|text|from+sqlalchemy+import+create_engine
engine+=+create_engine('sqlite:///db.sqlite3')

engine.execute('alter+table+table_name+add+column+column_name+String')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|791822|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>from sqlalchemy import create_engine
engine = create_engine('sqlite:///db.sqlite3')

engine.execute('alter table table_name add column column_name String')
</code></pre>

blocks|key|791800|text|只是继续查斯马尼提出的简单方法，几乎没有什么改进|type|unstyled|depth|inlineStyleRanges|entityRanges|data|791801|'''
#+simple+migration
#+columns+to+add:+
#+last_status_change+=+Column(BigInteger,+default=None)+
#+last_complete_phase+=+Column(String,+default=None)++
#+complete_percentage+=+Column(DECIMAL,+default=0.0)
'''

import+sqlite3

from+config+import+APP_STATUS_DB
from+sqlalchemy+import+types


def+add_column(database_name:+str,+table_name:+str,+column_name:+str,+data_type:+types,+default=None):
++++ret+=+False

++++if+default+is+not+None:
++++++++try:
++++++++++++float(default)
++++++++++++ddl+=+("ALTER+TABLE+'{table_name}'+ADD+column+'{column_name}'+'{data_type}'+DEFAULT+{default}")
++++++++except:
++++++++++++ddl+=+("ALTER+TABLE+'{table_name}'+ADD+column+'{column_name}'+'{data_type}'+DEFAULT+'{default}'")
++++else:
++++++++ddl+=+("ALTER+TABLE+'{table_name}'+ADD+column+'{column_name}'+'{data_type}'")

++++sql_command+=+ddl.format(table_name=table_name,+column_name=column_name,+data_type=data_type.__name__,
+++++++++++++++++++++++++++++default=default)
++++try:
++++++++connection+=+sqlite3.connect(database_name)
++++++++cursor+=+connection.cursor()
++++++++cursor.execute(sql_command)
++++++++connection.commit()
++++++++connection.close()
++++++++ret+=+True
++++except+Exception+as+e:
++++++++print(e)
++++++++ret+=+False
++++return+ret


add_column(APP_STATUS_DB,+'procedures',+'last_status_change',+types.BigInteger)
add_column(APP_STATUS_DB,+'procedures',+'last_complete_phase',+types.String)
add_column(APP_STATUS_DB,+'procedures',+'complete_percentage',+types.DECIMAL,+0.0)|code-block|syntax|javascript|791802|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Just continuing the simple way proposed by chasmani, little improvement

<pre><code>'''
# simple migration
# columns to add: 
# last_status_change = Column(BigInteger, default=None) 
# last_complete_phase = Column(String, default=None) 
# complete_percentage = Column(DECIMAL, default=0.0)
'''

import sqlite3

from config import APP_STATUS_DB
from sqlalchemy import types


def add_column(database_name: str, table_name: str, column_name: str, data_type: types, default=None):
 ret = False

 if default is not None:
 try:
 float(default)
 ddl = ("ALTER TABLE '{table_name}' ADD column '{column_name}' '{data_type}' DEFAULT {default}")
 except:
 ddl = ("ALTER TABLE '{table_name}' ADD column '{column_name}' '{data_type}' DEFAULT '{default}'")
 else:
 ddl = ("ALTER TABLE '{table_name}' ADD column '{column_name}' '{data_type}'")

 sql_command = ddl.format(table_name=table_name, column_name=column_name, data_type=data_type.__name__,
 default=default)
 try:
 connection = sqlite3.connect(database_name)
 cursor = connection.cursor()
 cursor.execute(sql_command)
 connection.commit()
 connection.close()
 ret = True
 except Exception as e:
 print(e)
 ret = False
 return ret


add_column(APP_STATUS_DB, 'procedures', 'last_status_change', types.BigInteger)
add_column(APP_STATUS_DB, 'procedures', 'last_complete_phase', types.String)
add_column(APP_STATUS_DB, 'procedures', 'complete_percentage', types.DECIMAL, 0.0)
</code></pre>

blocks|key|791919|text|是的，您可以安装sqlalchemy-migrate+(pip+Install+sqlalchemy-migrate)，并在脚本中使用它来调用Table和Column+create()方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|791920|from+sqlalchemy+import+String,+MetaData,+create_engine
from+migrate.versioning.schema+import+Table,+Column

db_engine+=++create_engine(app.config.get('SQLALCHEMY_DATABASE_URI'))
db_meta+=+MetaData(bind=db_engine)

table+=+Table('tabel_name'+,+db_meta)
col+=+Column('new_column_name',+String(20),+default='foo')
++++col.create(table)|code-block|syntax|javascript|791921|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Yes you can
Install sqlalchemy-migrate (pip install sqlalchemy-migrate) and use it in your script to call Table and Column create() method:
<pre><code>from sqlalchemy import String, MetaData, create_engine
from migrate.versioning.schema import Table, Column

db_engine = create_engine(app.config.get('SQLALCHEMY_DATABASE_URI'))
db_meta = MetaData(bind=db_engine)

table = Table('tabel_name' , db_meta)
col = Column('new_column_name', String(20), default='foo')
 col.create(table)
</code></pre>

blocks|key|575661|text|“手动”添加列(不使用python或SQLAlchemy)可能是最简单的？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|575662|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Adding the column "manually" (not using python or SQLAlchemy) is perhaps the easiest?

blocks|key|791737|text|这里也有同样的问题。我要做的是迭代数据库，将每个条目添加到一个新数据库中，并添加额外的列，然后删除旧数据库，并将新数据库重命名为这个数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|791738|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Same problem over here. What I will do is iterating over the db and add each entry to a new database with the extra column, then delete the old db and rename the new to this one.

I made a table using SQLAlchemy and forgot to add a column. I basically want to do this:

<pre><code>users.addColumn('user_id', ForeignKey('users.user_id'))
</code></pre>

What's the syntax for this? I couldn't find it in the docs.

add column to SQLAlchemy Table

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我使用SQLAlchemy制作了一个表，但忘记添加一列。我基本上想这样做：users.addColumn('user_id', ForeignKey('users.user_id'))它的语法是什么？我在文档里找不到。

问将列添加到SQLAlchemy表
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将列添加到SQLAlchemy表EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将列添加到SQLAlchemy表
EN