flask 连接mysql相关操作

于 2016-03-09 16:55:06 发布
阅读量1w 收藏 8
点赞数 6
分类专栏： flask 文章标签： python flask mysql 数据库

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

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  import and_,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
 
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()
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()
狼王不乖s
关注
  • 6
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值