Python's MySQL and SQLAlchemy operations summary

I. Summary of MYSQL commands 1. Create a library

create database test1;

2. Authorizing a user

grant all privileges on *.* to ‘feng‘@‘%‘ identified by ‘[email protected]‘;

3. Create a table

create table Teacher(teaId int not null,teaname varchar(100),age int,sex enum(‘M‘, ‘F‘),phone int);

4. Enquiry

select * from tabel_name where 条件1 and  条件2

5. Increase

insert into table_name (id, name, age, sex, grander) values (1, ‘feng‘, 30, ‘M‘, 99), (2, ‘ajing‘, 45, ‘F‘, 88);

6. Modifications

update table_name set id=10 where 条件判断

7. Delete

delete from table_name  where 条件判断drop table table_name

8. Joint Queries

select a.id, b.name from A a join B b on a.id=b.tid

9. Create an index

create index idx_库名_表名_列名1_列名2 (列名1， 列名2)

10. See if SQL goes through the index

explain select * from student where name=‘ling‘

11. Link Database

Python2 is using MYSQLDB.
Python3 using the pymysql PIP installation

(1) Creating links and cursors
Note: In MySQL connection, try to use a connection to make sure that the number of MySQL concurrency

conn = pymysql.connect(host=‘‘, port=, user=‘‘, passwd=‘‘, db=‘‘)cus = conn.curse()

(2) Execute SQL

"select * from Student;"cus.execute(sql)cus.fetchone()  获取单个  返回值  tuplecus.fetchall()  获取多个  返回值  list（单个元素是tuple）cus.fetchmany(size=n)  获取多个

(3) Closing cursors and connections

cus.close()conn.close()

Note the use of combined try exception finally

Ii. Summary of sqlalchemy operation 1. Creating an engine

engine = create_engine(‘mysql+pymysql://username:[email protected]:port/db‘)

2. Create Session

DBsession = sessionmaker(bind=engine)session = DBsession()

3. Create a table

a. 获得engineb. metadata = MetaData(engine)c. student = Table(‘表名‘, metadata, Colume(‘id‘, Integer, primary_key=True), Colume(‘name‘, String(50))d. metadata.create_all()

4. Increase

(1) to have a model first

Base = Declarative_base (0Class Student (base) :< Span class= "Hljs-class" >    __tablename__ =  ' student '      ID = column (integer, primary _key=true )     name = column (string (< Span class= "Hljs-class" >100primary_key= True            

(2) Import the model class, instantiate the class,

sutdent1 = Student(1, ‘ling‘)c. session.add(单实例)      session.add_all([实例1, 实例2])

5. Enquiry

filter和filter_by的区别filter：可以使用>  < 等，但是列必须是： 表.列，   filter的等于号是==session.query(Student).filter(Student.id>100)filter 不支持组合查询session.query(Student).filter(Studnet.id>100).filter(name==‘ling‘)filter_by： 可以直接写列，不支持< >  filter_by 等于是==session.query(Student).filter_by(id==10)filter_by 可以支持组合查询session.query(Student).filter_by(name==‘ling‘ and id==‘342‘)select * from student where name like ‘%ling%‘;模糊查询含有ling的关键字

Fuzzy query

session.query(Student).filter(Student.name like(‘%ling%‘))

There are two ways to get data:
One () tuple
All () list (single element is tuple)
If one () is not written in the query, or all () is the SQL statement

6. Update

(1) Check it out first.
(2) The property value corresponding to the new class is OK
(3) Session.commit ()

student1 = session.query(Student).filter(Student.id==1001)student1.name = "test"session.commit()

7. Delete

(1) Check it out first.
(2) Call the Delete () method directly to
(3) Submit

8. Statistics, grouping, sorting

Statistics: COUNT ()
Only need to replace one or all with count () after checking out
How many of them are counted?

Group: group_by
After checking out, replace one or all with group_by (attribute)

Python's MySQL and SQLAlchemy operations summary