1、pip install sqlacodegen
pip install pymysql
在/usr/lib/python/site-packages/sqlacodegen/main.py中添加:
import pymysql
pymysql.install_as_MySQLdb()
2、创建review_models.sh文件,在文件中添加:
#!/usr/bin/env bash
sqlacodegen --noviews --noconstraints --outfile=models.py mysql://iips:iips@192.168.1.200:3306/iips
3、执行上面shell文件,将在当前目录下输出models.py,数据库iips中的表结构将转换成为sqlalchemy可操作的类对象,如下:
# coding: utf-8 from sqlalchemy import Column, DateTime, Integer, String from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() metadata = Base.metadata class TBuilding(Base): __tablename__ = 't_building' id = Column(Integer, primary_key=True) building_code = Column(String(20), nullable=False, unique=True) land_code = Column(String(20), nullable=False, index=True) building_name = Column(String(40)) building_area = Column(Integer) rent_area = Column(Integer) one_floor_area = Column(Integer, nullable=False) total_floors = Column(Integer, nullable=False) floor_hight = Column(Integer, nullable=False) loadbearing = Column(Integer, nullable=False) status = Column(Integer, nullable=False) structure = Column(String(40))
python 从数据库表生成model
python 从数据库表生成model
找了很久才找到这个,我是新手...
现在已有建好的数据库,需要基于原有数据做数据分析的web应用,我选择python+Tornado ,由于不想写SQL语句,就想偷个懒
1、安装工具
1 ningjian@freegodly:~/code/py/django/logcloud$ sudo pip install sqlacodegen 2 Downloading/unpacking sqlacodegen 3 Downloading sqlacodegen-1.1.6-py2.py3-none-any.whl 4 Downloading/unpacking inflect>=0.2.0 (from sqlacodegen) 5 Downloading inflect-0.2.5-py2.py3-none-any.whl (58kB): 58kB downloaded 6 Requirement already satisfied (use --upgrade to upgrade): SQLAlchemy>=0.6.0 in /usr/local/lib/python2.7/dist-packages (from sqlacodegen) 7 Installing collected packages: sqlacodegen, inflect 8 Successfully installed sqlacodegen inflect 9 Cleaning up...
2、转换
ningjian@freegodly:~/code/py/django/logcloud$ sqlacodegen mssql+pymssql://name:password@ip/LogColudDB --outfile logcloude_model.py ningjian@freegodly:~/code/py/django/logcloud$
3、查看,哈哈
ningjian@freegodly:~/code/py/django/logcloud$ cat logcloude_model.py # coding: utf-8 from sqlalchemy import BigInteger, Column, DateTime, Float, ForeignKey, Integer, LargeBinary, T able, Unicode, text from sqlalchemy.dialects.mssql.base import BIT from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() metadata = Base.metadata class AuthorityInfo(Base): __tablename__ = 'AuthorityInfo' ID = Column(BigInteger, primary_key=True) Description = Column(Unicode('max'), nullable=False) IsDelete = Column(BIT, nullable=False) AuthorityIndex = Column(Integer, nullable=False) AuthorityName = Column(Unicode('max'), nullable=False) class CPKInfoHistory(Base): __tablename__ = 'CPKInfoHistory' PO = Column(Unicode(10), primary_key=True) ProcessName = Column(Unicode(50), nullable=False) Result = Column(Unicode('max'), nullable=False) LastLogID = Column(BigInteger, nullable=False) class ComputerState(Base): __tablename__ = 'ComputerState' Name = Column(Unicode(50), primary_key=True) Ip = Column(Unicode(50), nullable=False) IsDelete = Column(BIT, nullable=False) LastDate = Column(DateTime, nullable=False) IsProceted = Column(BIT, nullable=False) class DeviceInfo(Base): __tablename__ = 'DeviceInfo' ID = Column(BigInteger, primary_key=True) ClientName = Column(Unicode(50), nullable=False) Description = Column(Unicode(1024)) IsDelete = Column(BIT, nullable=False) LinesInfo_ID = Column(ForeignKey(u'LinesInfo.ID'), nullable=False, index=True) LinesInfo = relationship(u'LinesInfo') class DisposeErrorCodeInfo(Base): __tablename__ = 'DisposeErrorCodeInfo' ID = Column(BigInteger, primary_key=True) NewOperation = Column(Unicode('max'), nullable=False) Status = Column(Unicode('max'), nullable=False) DisposeInfo_ID = Column(ForeignKey(u'DisposeInfo.ID'), nullable=False, index=True) ErrorCode_Info_ID = Column(ForeignKey(u'ErrorCode_Info.ID'), nullable=False, index=True) ErrorCode_OperationID = Column(BigInteger, nullable=False) WeightValue = Column(Integer, nullable=False) DisposeInfo = relationship(u'DisposeInfo') ErrorCode_Info = relationship(u'ErrorCodeInfo') class DisposeErrorCodeInfoHistory(Base): __tablename__ = 'DisposeErrorCodeInfoHistory' ID = Column(BigInteger, primary_key=True) OperateTime = Column(DateTime, nullable=False) OldStatus = Column(Unicode('max'), nullable=False) NewStatus = Column(Unicode('max'), nullable=False) IsDelete = Column(BIT, nullable=False) WeightValue = Column(Integer, nullable=False) UserInfo_ID = Column(ForeignKey(u'UserInfo.ID'), nullable=False, index=True) DisposeErrorCodeInfo_ID = Column(ForeignKey(u'DisposeErrorCodeInfo.ID'), nullable=False, in dex=True) DisposeErrorCodeInfo = relationship(u'DisposeErrorCodeInfo') UserInfo = relationship(u'UserInfo') class DisposeInfo(Base): __tablename__ = 'DisposeInfo' ID = Column(BigInteger, primary_key=True) Operation = Column(Unicode('max'), nullable=False) IsOK = Column(BIT, nullable=False) Description = Column(Unicode(1024)) TouchTime = Column(DateTime, nullable=False) NoticeInfo_ID = Column(ForeignKey(u'NoticeInfo.ID'), nullable=False, index=True) UserTask_ID = Column(ForeignKey(u'UserTask.ID'), nullable=False, index=True) NoticeInfo = relationship(u'NoticeInfo') UserTask = relationship(u'UserTask') class ErrorCodeInfo(Base): __tablename__ = 'ErrorCode_Info' ID = Column(BigInteger, primary_key=True) ErrorCode = Column(Unicode(10), nullable=False) Description = Column(Unicode(1024)) IsDelete = Column(BIT, nullable=False) class ErrorCodeOperation(Base): __tablename__ = 'ErrorCode_Operation' ID = Column(BigInteger, primary_key=True) Operation = Column(Unicode('max'), nullable=False) WeightValue = Column(Integer, nullable=False) IsEnable = Column(BIT, nullable=False) ErrorCode_Info_ID = Column(ForeignKey(u'ErrorCode_Info.ID'), nullable=False, index=True) ErrorCode_Info = relationship(u'ErrorCodeInfo') class FilesManage(Base): __tablename__ = 'FilesManage' ID = Column(BigInteger, primary_key=True) ClassName = Column(Unicode(50), nullable=False, index=True) Md5 = Column(Unicode(32), nullable=False) Data = Column(LargeBinary, nullable=False) Ver = Column(Integer, nullable=False) DateCreated = Column(Unicode(50), nullable=False) UpLoadUserName = Column(Unicode(50), nullable=False) Remarks = Column(Unicode('max')) Catagory = Column(Unicode(50), nullable=False) LocalFileName = Column(Unicode(50)) class LOGInfo(Base): __tablename__ = 'LOG_Info' ID = Column(BigInteger, primary_key=True) Po = Column(Unicode(10), nullable=False, index=True) ProcessName = Column(Unicode(10), nullable=False, index=True) User = Column(Unicode(10), nullable=False) ErrorCode = Column(Unicode(10)) Log = Column(Unicode('max'), nullable=False) Barcode = Column(Unicode(50)) Isn = Column(Unicode(50)) Shift = Column(Unicode(10), nullable=False) TestResult = Column(Unicode(10), nullable=False, index=True) LastDate = Column(DateTime, nullable=False) ClientName = Column(Unicode(50), nullable=False, index=True) class LinesInfo(Base): __tablename__ = 'LinesInfo' ID = Column(BigInteger, primary_key=True) Name = Column(Unicode(50), nullable=False) Description = Column(Unicode(1024)) IsDelete = Column(BIT, nullable=False) class NoticeInfo(Base): __tablename__ = 'NoticeInfo' ID = Column(BigInteger, primary_key=True) Top1_ErrorCodeID = Column(BigInteger, nullable=False) Top2_ErrorCodeID = Column(BigInteger) Top3_ErrorCodeID = Column(BigInteger) TouchTime = Column(DateTime, nullable=False) IsDispose = Column(BIT, nullable=False) TaskLavel = Column(Integer, nullable=False) Responsibility_ID = Column(ForeignKey(u'Responsibility.ID'), nullable=False, index=True) UserTask_ID = Column(ForeignKey(u'UserTask.ID'), nullable=False, index=True) DeviceInfo_ID = Column(ForeignKey(u'DeviceInfo.ID'), nullable=False, index=True) StatisticsInfo_ID = Column(BigInteger, nullable=False) DeviceInfo = relationship(u'DeviceInfo') Responsibility = relationship(u'Responsibility') UserTask = relationship(u'UserTask') class POInfo(Base): __tablename__ = 'POInfo' ID = Column(BigInteger, primary_key=True) Po = Column(Unicode(10), nullable=False) Plm = Column(Unicode(20)) ProductName = Column(Unicode(50)) Description = Column(Unicode(1024)) IsDelete = Column(BIT, nullable=False) Customer = Column(Unicode(50)) class Responsibility(Base): __tablename__ = 'Responsibility' ID = Column(BigInteger, primary_key=True) ProcessName = Column(Unicode(10), nullable=False) BaseNumber = Column(Integer, nullable=False) ErrorRate = Column(Float(53), nullable=False) Description = Column(Unicode(1024)) OverTime = Column(Integer, nullable=False) POInfo_ID = Column(ForeignKey(u'POInfo.ID'), nullable=False, index=True) UserInfo_ID = Column(ForeignKey(u'UserInfo.ID'), nullable=False, index=True) POInfo = relationship(u'POInfo') UserInfo = relationship(u'UserInfo') class RoleAuthority(Base): __tablename__ = 'RoleAuthority' ID = Column(BigInteger, primary_key=True) IsDelete = Column(BIT, nullable=False) RoleInfo_ID = Column(ForeignKey(u'RoleInfo.ID'), nullable=False, index=True) AuthorityInfo_ID = Column(ForeignKey(u'AuthorityInfo.ID'), nullable=False, index=True) AuthorityInfo = relationship(u'AuthorityInfo') RoleInfo = relationship(u'RoleInfo') class RoleInfo(Base): __tablename__ = 'RoleInfo' ID = Column(BigInteger, primary_key=True) RoleName = Column(Unicode(256), nullable=False) Description = Column(Unicode(1024)) IsDelete = Column(BIT, nullable=False) RoleLevel = Column(Integer, nullable=False) class SettingInfo(Base): __tablename__ = 'SettingInfo' ID = Column(BigInteger, primary_key=True, nullable=False) Key = Column(Unicode(50), primary_key=True, nullable=False) Value = Column(Unicode('max'), nullable=False) Description = Column(Unicode(1024)) class StatisticsInfo(Base): __tablename__ = 'StatisticsInfo' ID = Column(BigInteger, primary_key=True) ProcessName = Column(Unicode(10), nullable=False) BeginTime = Column(DateTime, nullable=False) NowErrorRate = Column(Float(53), nullable=False) Times = Column(Integer, nullable=False) IsOutmoded = Column(BIT, nullable=False) POInfo_ID = Column(ForeignKey(u'POInfo.ID'), nullable=False, index=True) DeviceInfo_ID = Column(ForeignKey(u'DeviceInfo.ID'), nullable=False, index=True) DeviceInfo = relationship(u'DeviceInfo') POInfo = relationship(u'POInfo') class UserInfo(Base): __tablename__ = 'UserInfo' ID = Column(BigInteger, primary_key=True) Name = Column(Unicode(10), nullable=False) JobNumber = Column(Unicode(10), nullable=False) Phone = Column(Unicode(20)) Emil = Column(Unicode(30), nullable=False) Department = Column(Unicode(20)) Duties = Column(Unicode(20)) Description = Column(Unicode(1024)) Group = Column(Unicode(20), nullable=False) IsDelete = Column(BIT, nullable=False) Password = Column(Unicode(32)) CreateDateTime = Column(DateTime, nullable=False) class UserProfile(Base): __tablename__ = 'UserProfile' UserId = Column(Integer, primary_key=True) UserName = Column(Unicode(56), nullable=False, unique=True) class UserRoleInfo(Base): __tablename__ = 'UserRoleInfo' ID = Column(BigInteger, primary_key=True) IsDelete = Column(BIT, nullable=False) UserInfo_ID = Column(ForeignKey(u'UserInfo.ID'), nullable=False, index=True) RoleInfo_ID = Column(ForeignKey(u'RoleInfo.ID'), nullable=False, index=True) RoleInfo = relationship(u'RoleInfo') UserInfo = relationship(u'UserInfo') class UserTask(Base): __tablename__ = 'UserTask' ID = Column(BigInteger, primary_key=True) TaskLavel = Column(Integer, nullable=False) Shift = Column(Unicode(10), nullable=False) Description = Column(Unicode(1024)) IsDelete = Column(BIT, nullable=False) UserInfo_ID = Column(ForeignKey(u'UserInfo.ID'), nullable=False, index=True) LinesInfo_ID = Column(ForeignKey(u'LinesInfo.ID'), nullable=False, index=True) LinesInfo = relationship(u'LinesInfo') UserInfo = relationship(u'UserInfo') t_view_LinesState = Table( 'view_LinesState', metadata, Column('ID', BigInteger, nullable=False), Column('ClientName', Unicode(50), nullable=False), Column('LinesInfo_ID', BigInteger, nullable=False), Column('count', Integer) ) t_view_LogState = Table( 'view_LogState', metadata, Column('Po', Unicode(10), nullable=False), Column('ProcessName', Unicode(10), nullable=False), Column('ErrorCode', Unicode(10)), Column('ClientName', Unicode(50), nullable=False), Column('ID', BigInteger), Column('count', Integer) ) t_view_NoticeInfo = Table( 'view_NoticeInfo', metadata, Column('ID', BigInteger, nullable=False), Column('TouchTime', DateTime, nullable=False), Column('Top1_ErrorCodeID', BigInteger, nullable=False), Column('Top2_ErrorCodeID', BigInteger), Column('Top3_ErrorCodeID', BigInteger), Column('IsDispose', BIT, nullable=False), Column('TaskLavel', Integer, nullable=False), Column('ClientName', Unicode(50)), Column('ProcessName', Unicode(10)), Column('BeginTime', DateTime), Column('NowErrorRate', Float(53)), Column('Times', Integer), Column('IsOutmoded', BIT), Column('Po', Unicode(10)) ) t_view_Top1_error = Table( 'view_Top1_error', metadata, Column('Top1_ErrorCodeID', BigInteger, nullable=False), Column('count', Integer), Column('ID', BigInteger, nullable=False), Column('ErrorCode', Unicode(10), nullable=False), Column('Description', Unicode(1024)) ) class WebpagesMembership(Base): __tablename__ = 'webpages_Membership' UserId = Column(Integer, primary_key=True) CreateDate = Column(DateTime) ConfirmationToken = Column(Unicode(128)) IsConfirmed = Column(BIT, server_default=text("((0))")) LastPasswordFailureDate = Column(DateTime) PasswordFailuresSinceLastSuccess = Column(Integer, nullable=False, server_default=text("((0 ))")) Password = Column(Unicode(128), nullable=False) PasswordChangedDate = Column(DateTime) PasswordSalt = Column(Unicode(128), nullable=False) PasswordVerificationToken = Column(Unicode(128)) PasswordVerificationTokenExpirationDate = Column(DateTime) class WebpagesOAuthMembership(Base): __tablename__ = 'webpages_OAuthMembership' Provider = Column(Unicode(30), primary_key=True, nullable=False) ProviderUserId = Column(Unicode(100), primary_key=True, nullable=False) UserId = Column(Integer, nullable=False) class WebpagesRole(Base): __tablename__ = 'webpages_Roles' RoleId = Column(Integer, primary_key=True) RoleName = Column(Unicode(256), nullable=False, unique=True) UserProfile = relationship(u'UserProfile', secondary='webpages_UsersInRoles') t_webpages_UsersInRoles = Table( 'webpages_UsersInRoles', metadata, Column('UserId', ForeignKey(u'UserProfile.UserId'), primary_key=True, nullable=False), Column('RoleId', ForeignKey(u'webpages_Roles.RoleId'), primary_key=True, nullable=False) ) ningjian@freegodly:~/code/py/django/logcloud$
作者:BosyJ
轉載:https://www.cnblogs.com/pejsidney/
本站使用「署名 4.0 国际」创作共享协议,转载请在文章明显位置注明作者及出处。