Flask-sqalchemy和oracle数据库ID不会自动递增 [英] Flask-sqalchemy and oracle database id not autoincrement

问题描述

我想使用带有SQLAlchemy模块的Python和Flask框架在数据库(Oracle 11g，但适用于Ubuntu 16.04的Express Edition)中创建一个新表.它的表和ID的第一个字段是Integer字段，我希望它自动递增，但是Oracle数据库不支持自动递增.我有一个添加新评论的表单，但是当我尝试添加新记录时，它会报告错误:

I want to make a new table in my database (Oracle 11g but the Express Edition for Ubuntu 16.04) using Python and Flask framework with the SQLAlchemy module. The first field of the table it's and ID, is an Integer field and I want it to autoincrement, but Oracle database don't support the autoincrement. I have a form to add a new comment, but when I try to add a new record it reports a error:

sqlalchemy.exc.IntegrityError:(cx_Oracle.IntegrityError)ORA-01400:无法将NULL插入("WIKTOR"."TBL_COMENTARIOS"."ID")[SQL:'将INERT插入tbl_comentarios(usuario，comentario，fecha)值(:usuario，:comentario，:fecha)返回tbl_comentarios.id INTO:ret_0'] [参数:{'usuario':'wiktor'，'comentario':'hola'，'fecha':datetime.datetime(2017，5 ，24，11，23，45，39450)，'ret_0':}]

sqlalchemy.exc.IntegrityError: (cx_Oracle.IntegrityError) ORA-01400: cannot insert NULL into ("WIKTOR"."TBL_COMENTARIOS"."ID") [SQL: 'INSERT INTO tbl_comentarios (usuario, comentario, fecha) VALUES (:usuario, :comentario, :fecha) RETURNING tbl_comentarios.id INTO :ret_0'] [parameters: {'usuario': 'wiktor', 'comentario': 'hola', 'fecha': datetime.datetime(2017, 5, 24, 11, 23, 45, 39450), 'ret_0': }]

基本上，它说ID不能为null，这是因为表单中没有要添加的字段，所以它将id发送为空，然后数据库模型的自动增量不起作用.这是代码:

Basically it says that the ID cannot be null, that happens because there's no field in the form to add it so it sends the id empty and then the autoincrement of the database model don't work. Here's the code:

db = SQLAlchemy()

class Comentarios(db.Model):
    __tablename__ = 'tbl_comentarios'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    usuario = db.Column(db.String(50))
    comentario = db.Column(db.Text())
    fecha = db.Column(db.DateTime, default = datetime.datetime.now)

def __init__(self, usuario, comentario):
    self.usuario = usuario
    self.comentario = comentario

我也尝试过:

id = db.Column(db.Integer, primary_key=True)

该代码在SQL数据库中有效，但在Oracle数据库中均无效.

That code did work in SQL Database but neither works in Oracle Database.

对不起，我的英语，希望有人能帮助我... 谢谢！

Sorry for my english, I hope someone can help me... Thanks!

推荐答案

您可以找到这里讨论此问题的SQLAlchemy文档. Oracle没有自动增量功能，而是依靠顺序来模仿行为.

You can find here the SQLAlchemy documentation that discusses this. Oracle has no auto increment feature and relies on sequence to mimic the behavior.

因此，您的id列应如下所示:

So, your id column should look like this:

id_seq = Sequence('id_seq')
id = db.Column(db.Integer, id_seq,
        server_default=id_seq.next_value(), primary_key=True)

这篇关于Flask-sqalchemy和oracle数据库ID不会自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！