flask 连接mysql相关操作

来源：互联网发布：中央空调故障代码软件编辑：程序博客网时间：2024/06/01 10:18

1 安装flask连接数据库模块flask-sqlalchemy

1
2
3
4
5
6
7
8
[root@django flask]# pip install flask-sqlalchemy
Collecting flask-sqlalchemy
  Downloading Flask-SQLAlchemy-2.0.tar.gz (93kB)
    100% |################################| 94kB 111kB/s
Requirement already satisfied (use --upgrade to upgrade): Flask>=0.10 in /usr/lib/python2.7/site-packages (fromflask-sqlalchemy)
Collecting SQLAlchemy (from flask-sqlalchemy)
  Downloading SQLAlchemy-0.9.9.tar.gz (4.2MB)
    100% |################################| 4.2MB 16kB/s

2 安装mysql-python

[root@django flask]# pip install mysql-python

3 生成mysql数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@django flask]# cat db.py
from flask import Flask
import MySQLdb
from flask.ext.sqlalchemy import SQLAlchemy
  
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] ='mysql://root:123456@localhost/flask'
db = SQLAlchemy(app)
 
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(320), unique=True)
    phone = db.Column(db.String(32), nullable=False)
  
    def __init__(self, username, email, phone):
        self.username = username
        self.email = email
        self.phone= phone
if __name__ == '__main__':
        db.create_all()

primary_key 主键 db.create_all()表示执行mysql语句

python db.py执行完后，查看数据库有没有这个表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> use flask;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> desc user;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| username | varchar(80)  | YES  | UNI | NULL    |                |
| email    | varchar(320) | YES  | UNI | NULL    |                |
| phone    | varchar(32)  | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

以上就是显示说明是成功的，mysql连接简单操作很轻松的

4 插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@django flask]# cat  insert.db
from flask import Flask
import MySQLdb
from flask.ext.sqlalchemy import SQLAlchemy
  
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] ='mysql://root:123456@localhost/flask'
db = SQLAlchemy(app)
 
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(320), unique=True)
    phone = db.Column(db.String(32), nullable=False)
  
    def __init__(self, username, email, phone):
        self.username = username
        self.email = email
        self.phone= phone
inset=User(username='itmin', email='itmin@qq.com', phone='13812345678')
db.session.add(inset)
db.session.commit()

查看下数据库

1
2
3
4
5
6
7
8
9
mysql> select * from User;
ERROR 1146 (42S02): Table 'flask.User' doesn't exist
mysql> select * from user;
+----+----------+--------------+-------------+
| id | username | email        | phone       |
+----+----------+--------------+-------------+
|  1 | itmin    | itmin@qq.com | 13812345678 |
+----+----------+--------------+-------------+
1 row in set (0.00 sec)

5 查询数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[root@django flask]# cat select.db
from flask import Flask
import MySQLdb
from flask.ext.sqlalchemy import SQLAlchemy
  
<span style="color: rgb(255, 0, 0);">from sqlalchemy importand_,not_,or_</span>
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] ='mysql://root:123456@localhost/flask'
db = SQLAlchemy(app)
 
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(320), unique=True)
    phone = db.Column(db.String(32), nullable=False)
  
    def __init__(self, username, email, phone):
        self.username = username
        self.email = email
        self.phone= phone
    def __repr__(self):
        return "<User '{:s}'>".format(self.username)
        # return 'User %r' % self.username
select_=User.query.filter_by(username='itmin').first()
print(select_.id) #精确查询，并查找出ID
 
printUser.query.filter(User.email.endswith('@qq.com')).all()#模糊查询
 
printUser.query.filter(User.phone.endswith('13812345678')).all()
 
print User.query.filter(User.username != 'yoyo').first()#反条件查询，非
 
printUser.query.filter(not_(User.username=='yoyo')).first()

1
#反条件查询，非

1
2
3
4
5
6
7
8
9
print User.query.filter(or_(User.username != 'yoyo', User.email.endswith('@example.com'))).first()   #或查询
print User.query.filter(and_(User.username != 'yoyo', User.email.endswith('@example.com'))).first()  #与查询
print User.query.limit(10).all() #查询返回的数据的数目
 
data_all=User.query.all()
print (data_all)#查询全部
 
for i in range(len(data_all)):
 print data_all[i].username+" "+data_all[i].email+" "+data_all[i].phone

#循环拿出全部数据

结果:

1
2
3
4
5
6
7
8
9
10
11
[root@django flask]# python select.db
1
[<User 'itmin'>, <User 'yoyo'>]
[<User 'itmin'>]
<User 'itmin'>
<User 'itmin'>
<User 'itmin'>
[<User 'itmin'>]
[<User 'itmin'>, <User 'yoyo'>]
itmin itmin@qq.com 13812345678
yoyo yy@qq.com 13812345679

6 更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@django flask]# cat update.py
from flask import Flask
import MySQLdb
from flask.ext.sqlalchemy import SQLAlchemy
  
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] ='mysql://root:123456@localhost/flask'
db = SQLAlchemy(app)
 
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(320), unique=True)
    phone = db.Column(db.String(32), nullable=False)
  
    def __init__(self, username, email, phone):
        self.username = username
        self.email = email
        self.phone= phone
news=User.query.all()
print news
news[1].username='test'
db.session.commit()

7 删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@django flask]# cat delete.py
from flask import Flask
import MySQLdb
from flask.ext.sqlalchemy import SQLAlchemy
  
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] ='mysql://root:123456@localhost/flask'
db = SQLAlchemy(app)
 
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(320), unique=True)
    phone = db.Column(db.String(32), nullable=False)
  
    def __init__(self, username, email, phone):
        self.username = username
        self.email = email
        self.phone= phone
 
name=User.query.filter_by(username = 'bb').first()
db.session.delete(name)
db.session.commit()

1 0