Flas-sqlachemy database operations Using learning notes

Flas-sqlachemy database operations Using learning notes

1. Load the Flask-sqlalchemy extension code example:1.1 for your Flask app from flask import Flask f rom flask.ext.sqlalchemy import SQLAlchemy app = Flask(__name__) app.config[‘SQLALCHEMY_DATABASE_URI‘] = ‘sqlite:////tmp/test.db‘ db = SQLAlchemy(app) #这个就是你以后操作数据库的对象实例了Sqlalchemy_database_uri Format instance:

postgresql://scott:[email protected]/mydatabasemysql://scott:[email protected]/mydatabaseoracle://scott:[email protected]127.0.0.1:1521/sidnamesqlite:////absolute/path/to/foo.db #注意：有3个斜杠+路径

2. Building the database model and initializing the database

To create a database model:

Code Example:

Import HashlibFrom appImport db#在数据库模型文件中导入上面建立的db对象Class User(Db. Model):Id= db. Column (db. Integer, Primary_key=True)# ID Username= db. Column (db. String (), Unique=True) Email= db. Column (db. String (), Unique=True) password= db. Column (db. String (), Nullable=False)Def __init__(Selfself.username = username self.email = email self.password< Span class= "OP" >= hashlib.md5 (password)  #呵呵 so that the insertion of data automatically hashes the password! def __repr__ ( Self): return  "<user ' {: s} ' >".  Format (self.username)            

Initializing the database is also very simple, just call the Db.create_all () function.

Code Example:

if __name__ == ‘__main__‘: db.create_all()

Third, insert data
Code Example:

= User(username=‘peter‘, email=‘[email protected]‘, password=‘123456‘)db.session.add(u) #插入数据db.session.commit() #只有提交事务了，才可以获取(u.id)数据的ID值。

Iv. Querying data
Get the data with the primary key:
Code Example:

User.query.get(1)<User u‘admin‘>

To counter-check with an exact parameter:

= User.query.filter_by(username=‘peter‘).first()  #注意：精确查询函数query.filter_by()，是通过传递参数进行查询；其他增强型查询函数是query.filter()，通过传递表达式进行查询。print(peter.id) #如果数据不存在则返回None

Fuzzy query:

User.query.filter(User.email.endswith(‘@example.com‘)).all()[<User u‘admin‘>, <User u‘guest‘>]

Logic not 1:

= User.query.filter(User.username != ‘peter‘).first()print(peter.id)

Logic not 2:

from sqlalchemy import not_peter = User.query.filter(not_(User.username==‘peter‘)).first()print(peter.id)

Logic with:

from sqlalchemy import and_peter = User.query.filter(and_(User.username==‘peter‘, User.email.endswith(‘@example.com‘))).first()print(peter.id)

Logical OR:

from sqlalchemy import or_peter = User.query.filter(or_(User.username != ‘peter‘, User.email.endswith(‘@example.com‘))).first()print(peter.id)

Six, query data processing

The sort and limit functions can be followed by query or filter.
Sort:

User.query.order_by(User.username)  #嘿嘿，你用哪个字段作为排序参考呢？[<User u‘admin‘>, <User u‘guest‘>, <User u‘peter‘>]

Limit the number of returns:

User.query.limit(1).all()[<User u‘admin‘>]

Vi. Query Data return

Returns the first object queried to:

= User.query.first()print(r)

Returns all queries to the object:

= User.query.all()print(r)

Vii. deletion of data

= User.query.first()db.session.delete(u)  #删除数据和插入数据一样简单，但必须是通过查询返回的对象。db.session.commit()

Viii. Updating data

= User.query.first()u.username = ‘guest‘  #更新数据和变量赋值那么简单，但必须是通过查询返回的对象。db.session.commit()

Flas-sqlachemy database operations Using learning notes