我有一个关于如何向数据库添加新的但唯一的项的问题。
app = Flask(__name__, template_folder='templates')
app.config.from_object('config')
db = SQLAlchemy(app)
类看起来像这样的多对多实现:
connections = db.Table('connections', db.metadata,
db.Column('book_id', db.Integer, db.ForeignKey('books.id')),
db.Column('author_id', db.Integer, db.ForeignKey('authors.id'))
)
class Author(db.Model):
__tablename__ = 'authors'
__searchable__ = ['a_name']
__table_args__ = {'sqlite_autoincrement': True,}
id = db.Column(db.Integer, primary_key=True)
a_name = db.Column(db.String(80), unique=True)
def __repr__(self):
return unicode(self.a_name)
class Book(db.Model):
__tablename__ = 'books'
__searchable__ = ['b_name']
__table_args__ = {'sqlite_autoincrement': True,}
id = db.Column(db.Integer, primary_key=True)
b_name = db.Column(db.String(80), unique=True)
authors = db.relationship('Author', secondary=lambda: connections,
backref=db.backref('books'))
def __repr__(self):
return unicode(self.b_name)
我只想添加唯一的数据库项。如果我会写这个代码:
author = Author(a_name = "Author1")
book = Book(b_name = "Book1")
author.books.append(book)
db.session.add(author)
db.session.add(book)
db.session.commit()
我们的数据库中已经有一个名称为"Author1"的作者,将出现错误排除。
IntegrityError: (IntegrityError) column a_name is not unique u'INSERT INTO authors (a_name) VALUES (?)' (u'u0410u0432u0442u043eu04402',)
我需要检查这种插入的唯一性吗?如何检查?或者还有其他最佳解决方案?
ORM不支持"合并"方法(例如"插入或替换"等),而且这些方法也有很多复杂之处。我使用独特的对象配方或其变体。当我进行大型数据合并时,我经常会提前将某个特定区块要处理的整个对象集加载到字典中,所以只有一个SELECT语句来加载它们,然后在进行时只需检查该字典。
事实上,我发现了一个不容易的解决方案和丑陋的解决方案:),但它是有效的。当然,当我使用一个大数据库时,我会使用@zzzeek介绍的这个独特的对象配方。
new_author = Author(a_name = request.form['author'])
new_book = Book(b_name = request.form['book'])
author_in_db = Author.query.filter_by(a_name=unicode(new_author)).first()
book_in_db = Book.query.filter_by(b_name=unicode(new_book)).first()
# both author and book are new and unique
if unicode(new_author) != unicode(author_in_db) and
unicode(new_book) != unicode(book_in_db):
new_author.books.append(new_book)
db.session.add(new_author)
db.session.add(new_book)
db.session.commit()
# just book is not unique
elif unicode(new_author) != unicode(author_in_db):
new_author.books.append(book_in_db)
db.session.add(new_author)
db.session.commit()
# just author is not unique
elif unicode(new_book) != unicode(book_in_db):
author_in_db.books.append(new_book)
db.session.add(new_book)
db.session.commit()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String, unique=True)
fullname = Column(String)
password = Column(String)
insert_command = User.__table__.insert(
prefixes=['OR IGNORE'],
values=dict(name='MyName', fullname='Slim Shady', password='******')
)
session.execute(insert_command)
session.commit()
更换作品以及
prefixes=['OR REPLACE']