# coding=utf-8
from sqlalchemy import Column, Integer, String, BigInteger,Date, Enum, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
import time
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
import datetime

Base = declarative_base()

class Role(Base):

    __tablename__ = 'roles'

    id = Column(Integer, primary_key=True)

    role_name = Column(String(30), unique=True, nullable=False)


    users = relationship('User', backref='roles')

    def __repr__(self):
        return self.role_name

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)
    role_id = Column(Integer, ForeignKey('roles.id'))


class Scene(Base):

    __tablename__ = 'scenes'

    id = Column(Integer, primary_key=True)
    scene_name = Column(String(30), nullable=False) sqlalchemy.ext.declarative import declarative_base
    life_time = Column(Date, nullable=False,
                          default=datetime.datetime.strptime(
                              time.strftime("%Y-%m-%d", time.localtime(time.time() + (12 * 30 * 24 * 3600))),
                              '%Y-%m-%d').date())  Session = sessionmaker()
    scene_description = Column(String(150), default="")  # 鍦烘櫙鎻忚堪engine = create_engine('sqlite:///:memory:', echo=True)


class Gateway(Base):

    __tablename__ = 'gateways'

    id = Column(Integer, primary_key=True)
    gateway_name = Column(String(30), nullable=False)  # 缃戝叧鐨勫悕
    gateway_api_key = Column(String(100), nullable=False, unique=True)  # 缃戝叧鐨凙PI-Key
    gateway_type = Column(Enum('up', 'down', 'soft', name="gateway_type"), nullable=False)


# ed_user = User(name='ed', username='jack', password='123', emp_no=1, grade='1', role_id=1)
# example_scene = Scene(scene_name='example_1', scene_description='example_description')
# example_gateway = Gateway(gateway_name='example_1',gateway_api_key='11111',gateway_type='up')

# session.add(example_gateway)
# session.commit()


def init_user(flag, number):
    while number >= 1:
        if flag == 1:
            ed_user = User(name='user'+str(number), username='user'+str(number), password='123', emp_no=51610000+number, grade='1')
            pass
        if flag == 2:
            # TODO admin
            pass
        if flag == 3:
            # TODO teacher
            pass
        number -= 1


def init_scene(number):
    while number >= 1:
        number -= 1
        # TODO scene

def init_gateway(api_key, number):
    # TODO gateway
    pass

if __name__ == '__main__':
    engine1 = create_engine('sqlite:///memory:')
    Session = sessionmaker()
    Session.configure(bind=engine1)
    session = Session()
    Base.metadata.create_all(engine1)

    # admin = Role(role_name='Admin')
    admin = session.query(Role).all()
    ed_user = User(name='ed', username='jack', password='123', emp_no=1, grade='1', roles=admin[0])
    session.add(ed_user)
    session.commit()
    # with session.no_autoflush:
    #     c = session.query(User).all()
    # print c[1].name
    # # init_user(1, 1)

/ usr / bin / python2
.7 / home / pajamas / PycharmProjects / untitled5 / data_generator.py
Traceback(most
recent
call
last):
File
"/home/pajamas/PycharmProjects/untitled5/data_generator.py", line
110, in < module >
session.commit()
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line
921, in commit
self.transaction.commit()
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line
461, in commit
self._prepare_impl()
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line
441, in _prepare_impl
self.session.flush()
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line
2213, in flush
self._flush(objects)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line
2333, in _flush
transaction.rollback(_capture_exception=True)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line
66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line
2297, in _flush
flush_context.execute()
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line
389, in execute
rec.execute(self)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line
554, in execute
uow
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line
181, in save_obj
mapper, table, insert)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line
860, in _emit_insert_statements
execute(statement, params)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line
945, in execute
return meth(self, multiparams, params)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line
269, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line
1053, in _execute_clauseelement
compiled_sql, distilled_params
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line
1189, in _execute_context
context)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line
1402, in _handle_dbapi_exception
exc_info
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line
203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line
1182, in _execute_context
context)
File
"/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line
504, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError)
table
users
has
no
column
named
role_id[SQL: u'INSERT INTO users (username, password, name, grade, emp_no, role_id) VALUES (?, ?, ?, ?, ?, ?)'][
parameters: ('jack', '123', 'ed', '1', 1, 1)]

Process
finished
with exit code 1