sqlalchemy OperationalError

Question

我只想创建一个数据库表，并向其中写入一些数据。但是我发现了一些错误，我对解决这些问题一无所知，如果有人能帮助我，我将不胜感激！

    # coding=utf-8
    from sqlalchemy import Column, Integer, String, BigInteger
    from sqlalchemy.ext.declarative import declarative_base
    Base = declarative_base()


    class User(Base):

        __tablename__ = 'users'

        id = Column(Integer(), primary_key=True)
        username = Column(String(30), unique=True, nullable=False)  
        password = Column(String, default='123456', nullable=False) 
        name = Column(String(30), nullable=False)  
        grade = Column(String(30))  
        emp_no = Column(BigInteger, unique=True, nullable=False)

    print User.__table__


    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.ext.declarative import declarative_base


    Base = declarative_base()
    Session = sessionmaker()
    engine = create_engine('sqlite:///:memory:', echo=True)
    Session.configure(bind=engine)
    session = Session()
    Base.metadata.create_all(engine)

    ed_user = User(name='ed', username='jack', password='123', emp_no=1, grade='1')
    #
    session.add(ed_user)
    #
    with session.no_autoflush:
       session.query(User).filter_by(name='ed').first()

以下是结果和错误输出：

    /usr/bin/python2.7 /home/pajamas/PycharmProjects/untitled5/data_generator.py
    users
    2017-07-30 17:56:28,903 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
    2017-07-30 17:56:28,904 INFO sqlalchemy.engine.base.Engine ()
    2017-07-30 17:56:28,904 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
    2017-07-30 17:56:28,904 INFO sqlalchemy.engine.base.Engine ()
    2017-07-30 17:56:28,908 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
    sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: users [SQL: u'SELECT users.id AS users_id, users.username AS users_username, users.password AS users_password, users.name AS users_name, users.grade AS users_grade, users.emp_no AS users_emp_no \nFROM users \nWHERE users.name = ?\n LIMIT ? OFFSET ?'] [parameters: ('ed', 1, 0)]
    2017-07-30 17:56:28,909 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.username AS users_username, users.password AS users_password, users.name AS users_name, users.grade AS users_grade, users.emp_no AS users_emp_no 
    FROM users 
    WHERE users.name = ?
    LIMIT ? OFFSET ?
    2017-07-30 17:56:28,909 INFO sqlalchemy.engine.base.Engine ('ed', 1, 0)

    Process finished with exit code 1

Answer 1

我认为应该重新声明Base，并使create_all方法不会创建您想要的表。

以下代码中的Base是不必要的。

Base = declarative_base()
Session = sessionmaker()
engine = create_engine('sqlite:///:memory:', echo=True)

只需删除第二个Base = declarative_base()

Answer 2

您需要使用声明User表时使用的相同Base，以便在Base.create_all()方法中创建表。在您当前的代码中，您正在创建一个新的声明性基础对象，该对象不能访问User模型。将Base设置为全局变量，该变量是User类和运行create_all的对象的基础