sqlalchemy操作mysql

#coding=utf-8
from sqlalchemy import Column, String, create_engine,  ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('mysql+pymysql://root:@192.168.43.136:3306/uic',
    echo=False)
conn = engine.connect()
result = conn.execute("select * from user;")
# print result.fetchall()

Base = declarative_base()
class Muser(Base):
    __tablename__ = "user3"
    id = Column(String(20),primary_key=True)
    name = Column(String(20))

class Student(Base):
    __tablename__ = 'student'
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    books = relationship('Book')
class Book(Base):
    __tablename__ = 'book'
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    user_id = Column(String(20), ForeignKey('student.id'))

Base.metadata.create_all(engine) 

DBSession = sessionmaker(bind=engine)
session = DBSession()
# new_user = Muser(id='8', name='bob')
# session.add(new_user)
# session.commit()
# session.close()

user = session.query(Muser).filter(Muser.id=='5').one()
print user.id,user.name

联结查询

MariaDB [uic]> select student.name "学生姓名",book.name "书名" from student right join book on student.id=book.user_id;
+--------------+--------+
| 学生姓名     | 书名   |
+--------------+--------+
| sudent1      | book1  |
| student2     | book2  |
| student2     | book3  |
| student2     | book4  |
| student2     | book5  |
| student2     | book2  |
+--------------+--------+
6 rows in set (0.00 sec)

MariaDB [uic]> select student.name "学生姓名",book.name "书名" from student right join book on student.id=book.user_id and student.id=2;
+--------------+--------+
| 学生姓名     | 书名   |
+--------------+--------+
| NULL         | book1  |
| student2     | book2  |
| student2     | book3  |
| student2     | book4  |
| student2     | book5  |
| student2     | book2  |
+--------------+--------+
6 rows in set (0.00 sec)

MariaDB [uic]> select student.name "学生姓名",book.name "书名" from student left join book on student.id=book.user_id and student.id=2;
+--------------+--------+
| 学生姓名     | 书名   |
+--------------+--------+
| sudent1      | NULL   |
| student2     | book2  |
| student2     | book3  |
| student2     | book4  |
| student2     | book5  |
| student2     | book2  |
| student3     | NULL   |
| bob          | NULL   |
+--------------+--------+
8 rows in set (0.00 sec)

MariaDB [uic]> select student.name "学生姓名",book.name "书名" from student join book on student.id=book.user_id and student.id=2;
+--------------+--------+
| 学生姓名     | 书名   |
+--------------+--------+
| student2     | book2  |
| student2     | book3  |
| student2     | book4  |
| student2     | book5  |
| student2     | book2  |
+--------------+--------+
5 rows in set (0.00 sec)

MariaDB [uic]> select student.name "学生姓名",book.name "书名" from student inner join book on student.id=book.user_id and student.id=2;
+--------------+--------+
| 学生姓名     | 书名   |
+--------------+--------+
| student2     | book2  |
| student2     | book3  |
| student2     | book4  |
| student2     | book5  |
| student2     | book2  |
+--------------+--------+
5 rows in set (0.00 sec)

MariaDB [uic]> select student.name "学生姓名",book.name "书名" from student outer join book on student.id=book.user_id and student.id=2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'outer join book on student.id=book.user_id and student.id=2' at line 1
MariaDB [uic]> select student.name "学生姓名",book.name "书名" from student left outer join book on student.id=book.user_id and student.id=2;
+--------------+--------+
| 学生姓名     | 书名   |
+--------------+--------+
| sudent1      | NULL   |
| student2     | book2  |
| student2     | book3  |
| student2     | book4  |
| student2     | book5  |
| student2     | book2  |
| student3     | NULL   |
| bob          | NULL   |
+--------------+--------+
8 rows in set (0.00 sec)

MariaDB [uic]> select student.name "学生姓名",book.name "书名" from student right outer join book on student.id=book.user_id and student.id=2;
+--------------+--------+
| 学生姓名     | 书名   |
+--------------+--------+
| NULL         | book1  |
| student2     | book2  |
| student2     | book3  |
| student2     | book4  |
| student2     | book5  |
| student2     | book2  |
+--------------+--------+
6 rows in set (0.00 sec)

https://blog.csdn.net/u011573853/article/details/51355113?utm_source=blogxgwz0

出处：https://zm10.sm-tc.cn/

openfalcon alarm-db-schema.sql

CREATE DATABASE alarms
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;
USE alarms;
SET NAMES utf8;

/*
* 建立告警归档资料表, 主要存储各个告警的最后触发状况
*/
DROP TABLE IF EXISTS event_cases;
CREATE TABLE IF NOT EXISTS event_cases(
                id VARCHAR(50),
                endpoint VARCHAR(100) NOT NULL,
                metric VARCHAR(200) NOT NULL,
                func VARCHAR(50),
                cond VARCHAR(200) NOT NULL,
                note VARCHAR(500),
                max_step int(10) unsigned,
                current_step int(10) unsigned,
                priority INT(6) NOT NULL,
                status VARCHAR(20) NOT NULL,
                timestamp Timestamp NOT NULL,
                update_at Timestamp NULL DEFAULT NULL,
                closed_at Timestamp NULL DEFAULT NULL,
                closed_note VARCHAR(250),
                user_modified int(10) unsigned,
                tpl_creator VARCHAR(64),
                expression_id int(10) unsigned,
                strategy_id int(10) unsigned,
                template_id int(10) unsigned,
                process_note MEDIUMINT,
                process_status VARCHAR(20) DEFAULT 'unresolved',
                PRIMARY KEY (id),
                INDEX (endpoint, strategy_id, template_id)
)
        ENGINE =InnoDB
        DEFAULT CHARSET =utf8;


/*
* 建立告警归档资料表, 存储各个告警触发状况的历史状态
*/
DROP TABLE IF EXISTS events;
CREATE TABLE IF NOT EXISTS events (
                id int(10) NOT NULL AUTO_INCREMENT,
                event_caseId VARCHAR(50),
                step int(10) unsigned,
                cond VARCHAR(200) NOT NULL,
                status int(3) unsigned DEFAULT 0,
                timestamp Timestamp,
                PRIMARY KEY (id),
                INDEX(event_caseId),
                FOREIGN KEY (event_caseId) REFERENCES event_cases(id)
                        ON DELETE CASCADE
                        ON UPDATE CASCADE
)
        ENGINE =InnoDB
        DEFAULT CHARSET =utf8;

/*
* 告警留言表
*/
CREATE TABLE IF NOT EXISTS event_note (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  event_caseId VARCHAR(50),
  note    VARCHAR(300),
  case_id VARCHAR(20),
  status VARCHAR(15),
  timestamp Timestamp,
  user_id int(10) unsigned,
  PRIMARY KEY (id),
  INDEX (event_caseId),
  FOREIGN KEY (event_caseId) REFERENCES event_cases(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (user_id) REFERENCES uic.user(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);