SQLAlchemy Tutorial-First chapter-Basic model

Today, 2018-06-29, build a good model first.

Create a data table

We use the way we build databases and tables first. Need to have a MySQL database available first. You can install a MySQL database yourself.
Data model, I still use the instance employee and departmental table that comes with Oracle data.

SQL statements, I have been modified according to MySQL syntax, directly can run.
All the source code for this tutorial is on GitHub. Warehouse address is: Https://github.com/notfresh/sqlalchemy_demo

--Department table--Create tablecreate table DEPT (deptno int (2) not null,dname varchar, loc varchar);--Create/recreate Prima Ry, unique and foreign key Constraintsalter table Deptadd constraint pk_dept primary key (DEPTNO); insert into DEPT (DEPTNO , Dname, loc) VALUES (, ' ACCOUNTING ', ' NEW YORK '), insert INTO dept (DEPTNO, dname, loc) VALUES (+, ' the ' ", ' DALLAS '); INSERT INTO Dept (DEPTNO, dname, loc) VALUES (+, ' SALES ', ' CHICAGO '), insert INTO dept (DEPTNO, dname, loc) VALUES (+, ' OPE Rations ', ' BOSTON ');--Employee Table CREATE TABLE EMP (empno int (4) not null,ename varchar (ten), Job varchar (9), Mgr Int (4), HireDate DAT E,sal Decimal (7,2), Comm Decimal (7,2), Deptno Int (2), DESC2 varchar (+) default 2);--create/recreate primary, unique and fo Reign key Constraintsalter table Empadd constraint pk_emp primary key (EMPNO); ALTER TABLE empadd constraint Fk_deptno fore IGN Key (DEPTNO) references DEPT (DEPTNO); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) value S (7369, ' SMITH ', ' clerk ', 7902, Str_to_date (' 17-12-1980 ', '%d-%m-%y '), 800.00, 1999.00, and null); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate , SAL, COMM, DEPTNO, DESC2) VALUES (7499, ' ALLEN ', ' salesman ', 7698, str_to_date (' 20-02-1981 ', '%d-%m-%y '), 1600.00, 300.00 , +, null); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (7521, ' WARD ', ' salesman ',  7698, Str_to_date (' 22-02-1981 ', '%d-%m-%y '), 1250.00, 500.00, +, NULL); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (7566, ' JONES ', ' MANAGER ', 7839, str_to_date (' 02-04-1981 ', '%d-%m-%y '), 2975.00, NULL, 20 , null); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (7654, ' MARTIN ', ' salesman ', 76 98, str_to_date (' 28-09-1981 ', '%d-%m-%y '), 1250.00, 1400.00, +, null); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate,  SAL, COMM, DEPTNO, DESC2) VALUES (7698, ' BLAKE ', ' MANAGER ', 7839, str_to_date (' 01-05-1981 ', '%d-%m-%y '), 2850.00, NULL, 30, NULL); INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (7782, ' CLARK ', ' MANAGER ', 7839, Str_to_date (' 09-06-1981 ', '%d-%m-%y '), 2450.00, NULL, ten, NULL); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) value  S (7788, ' SCOTT ', ' ANALYST ', 7566, str_to_date (' 19-04-1987 ', '%d-%m-%y '), 3000.00, NULL, (+), null); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (7839, ' KING ', ' president ', NULL, str_to_date (' 17-11-1981 ', '% D-%m-%y '), 5000.00, NULL, ten, NULL); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (78 , ' TURNER ', ' salesman ', 7698, str_to_date (' 08-09-1981 ', '%d-%m-%y '), 1500.00, 0.00, +, NULL); INSERT into EMP (EMPNO, EN AME, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (7876, ' ADAMS ', ' clerk ', 7788, str_to_date (' 23-05-1987 ', '%d-%m-% Y '), 1100.00, NULL, x, NULL); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (7900, ' J AMES ', ' clerk ', 7698, StR_to_date (' 03-12-1981 ', '%d-%m-%y '), 950.00, NULL, +, NULL); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (7902, ' FORD ', ' ANALYST ', 7566, str_to_date (' 03-12-1981 ', '%d-%m-%y '), 3000.00, NULL, and NULL); Inse RT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (7934, ' MILLER ', ' clerk ', 7782, str_to_date  (' 23-01-1982 ', '%d-%m-%y '), 1300.00, NULL, ten, NULL); INSERT into EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2) VALUES (7935, ' KATE ', ' MANAGER ', NULL, NULL, NULL, NULL, NULL, NULL);

Model creation

Use the table inside the SQLAlchemy map database to create the model.

1. There is a preparatory work to be done here.
   Put the code up first.
   db_util.py:

from sqlalchemy.orm import sessionmakerfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_base# 声明一个基类, 所有的模型都需要绑定在这个基类上面, 才能创建到数据库里面去, 而且以只有通过模型才能查询数据库. # 本次我们先创建表, 但是这个基类依然是必不可少的.Base = declarative_base()  engine = create_engine(‘mysql+pymysql://MYSQL_USER:[email protected]:3306/sqlalchemy_demo?charset=utf8‘)   #初学者只需要知道这段是mysql的连接地址Session = sessionmaker(bind=engine)if __name__ == ‘__main__‘:    # 创建表,如果不存在的话    Base.metadata.create_all(engine)

1. Then we create the model.
   We create another file, and again, we paste the code.
   models.py:

from sqlalchemy import Column, Integer, Stringfrom db_util import Base, Sessionsess = Session()class Dept(Base):    __tablename__ = ‘dept‘  # __tablename__指明了数据表实际的名字, 一定要和数据库里的表名字匹配.    deptno = Column(Integer, primary_key=True)  # 第一个字段指明数据类型. 第二个字段表明它是主键.    dname = Column(String(14))    loc = Column(String(13))    def __repr__(self):        return str({            ‘deptno‘: self.deptno,            ‘dname‘: self.dname,            ‘loc‘: self.loc        })

This time, we first create a class called Dept, Mapping the Dept Department table. If you are unfamiliar with fields, go to the top-up SQL script.
Next, we show the results of our work through simple statements.

We enter the Python command-line mode to verify that our code is expected to complete with instant feedback. But I recommend using the Enhanced Python command-line tool, IPython. (Please learn the ipython of the strength of the students themselves)
Create a virtual environment first. (My native development uses UNIX like systems, sometimes with Macs, sometimes with the Linux Ubuntu.) (The necessity of the virtual environment, please understand the students themselves), after creating the virtual environment using PIP to install Ipython.
Enter Ipython into command line mode.

(venv) ?  SQLAlchemy_demo git:(master) ? ipythonIn [1]: from models import *    # 先导入编辑好的代码.In [2]: Dept  # 查看是否导入成功.Out[2]: models.DeptIn [3]: sessOut[3]: <sqlalchemy.orm.session.Session at 0x105f6ee48>In [4]: sess.query(Dept).first()Out[4]: {‘deptno‘: 10, ‘dname‘: ‘ACCOUNTING‘, ‘loc‘: ‘NEW YORK‘}

OK, we have seen a successful query to the first department!!
This time compares Jincu, the explanation is relatively poor. We will continue to learn more about SQLAlchemy later.

SQLAlchemy official website
Http://docs.sqlalchemy.org/en/latest/contents.html

SQLAlchemy Tutorial-First chapter-Basic model