python - 如何在SqlAlchemy orm中设置主键自增

Question

我厌倦了使用 SqlAlchemy orm 构建 api 以将上传的 Excel 文件中的值插入数据库。当我测试代码时，它一直显示错误:

TypeError: __init__() missing 1 required positional argument: 'id'

我已将本地 MySql 数据库中的 id 键更新为主键、自动递增、唯一且无符号。我相信系统无法自动插入主键，因为如果我手动将值分配给 id ，它就可以工作

transaction_obj = Transaction(id=1, name="David", date="2018-03-03", 
                product="fruit", quantity=20, amount=12.55)

这是模型.py

from sqlalchemy import Table, MetaData, Column, Integer, String, DATE, DECIMAL,ForeignKey, DateTime
from sqlalchemy.orm import mapper

metadata = MetaData()


customers = Table('customers', metadata,
                  Column('id', Integer, primary_key=True),
                  Column('name', String(20)),
                  Column('phone', String(20)),
                  Column('address', String(45)),
                  Column('source_from', String(45))
                  )


class Customers(object):
    def __init__(self, name, phone, address, source_from):
        self.name = name
        self.phone = phone
        self.address = address
        self.source_from = source_from

    def __repr__(self):
        return "<Customer(name='%s', phone='%s', address='%s', " \
               "source_from='%s')" % (self.name, self.phone, self.address,
                                      self.source_from)


mapper(Customers, customers)



transaction = Table('transaction', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('name', String(20)),
                    Column('date', DateTime),
                    Column('product', String(20)),
                    Column('quantity', Integer),
                    Column('amount',DECIMAL(2))
                    )


class Transaction(object):
    def __index__(self, name, date, product, quantity, amount):
        self.name = name
        self.date = date
        self.product = product
        self.quantity = quantity
        self.amount = amount

    def __repr__(self):
        return "<Transaction(name='%s', date='%s', product='%s'," \
               "quantity='%s', amount='%s')>" % (self.name, self.date,
                                                 self.product, self.quantity,
                                                 self.amount)


mapper(Transaction, transaction)

这是我的测试代码:test.py

import json
import os
import os
import json
from sqlalchemy import create_engine
import config
import pandas as pd

conn = config.conn_str

def tran_test():
    engine = create_engine(conn)
    Session_class = sessionmaker(bind=engine) 
    Session = Session_class  
    # generate the object for the data we would like to insert

    transaction_obj = Transaction(name="David", date="2018-03-03", 
                product="fruit", quantity=20, amount=12.55)
    Session.add(transaction_obj)

    Session.commit()

def test_uploaded_file(file):
    df = pd.read_excel(file)
    return df.info()


if __name__ == '__main__':
    # set_env_by_setting('prod')
    # conn_str = os.environ.get('ConnectionString')
    # print(conn_str)
    # test_uploaded_file("-1.xlsx")
    tran_test()

我正在使用 SQLAlchemy==1.2.10，PyMySQL==0.9.2。我怀疑我是否在 model.py 中使用了错误的格式。请指教。谢谢。

Answer 1

虽然我不确定您正在使用的模式(手动映射到您的表类)，但我认为您会更轻松地使用为您执行此操作的 declarative_base 。

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

然后确保您的模型继承Base

from sqlalchemy import (
    Column,
    Integer,
    String
)


    class Customers(Base):
        __tablename__ = 'customer'
        id = Column(Integer, primary_key=True) # Auto-increment should be default
        name = Column(String(20))
        # Etc.

        def __repr__(self):
            return "<Customer(name='%s', phone='%s', address='%s', " \
                   "source_from='%s')" % (self.name, self.phone, self.address,
                                          self.source_from)

最后使用 Base 创建表:

Base.metadata.create_all(engine)

这里是基本声明性用例的一个很好的引用。它会变得有点复杂，具体取决于您如何构建应用程序，但它是一个很好的起点: http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/basic_use.html