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__)
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__)
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__)
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__)
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__)
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()
|