flask 连接mysql相关操作-白红宇

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

                         
[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 (
from
flask
-
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数据库

                         
[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'
] 
=
''
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执行完后查看数据库有没有这个表

                         
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 插入数据

                         
[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'
] 
=
''
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()
        
      

查看下数据库

                         
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 查询数据库

                         
[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 
import
and_,not_,or_<
/
span>
app 
=
Flask(__name__)
app.config[
'SQLALCHEMY_DATABASE_URI'
] 
=
''
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
print
User.query.
filter
(User.email.endswith(
'@qq.com'
)).
all
()
#模糊查询
print
User.query.
filter
(User.phone.endswith(
'13812345678'
)).
all
()
print
User.query.
filter
(User.username !
=
'yoyo'
).first()
#反条件查询非
print
User.query.
filter
(not_(User.username
=
=
'yoyo'
)).first()
        
      

                         
#反条件查询非

                         
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
        
      

#循环拿出全部数据

结果:

                         
[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 更新数据

                         
[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'
] 
=
''
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 删除数据

                         
[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'
] 
=
''
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()
        
      