Flask-sqlalchemy Installation and Setup

Flask-sqlalchemy Installation and Setup

• SQLALchemy is actually an abstraction of the database, so that developers do not have to deal with SQL statements directly, but through the Python object to manipulate the database, while discarding some performance overhead, in exchange for the development efficiency of a large increase
• SQLAlchemy is a relational database framework that provides high-level ORM and the operation of the underlying native database. Flask-sqlalchemy is a flask extension that simplifies the sqlalchemy operation.
• Document Address: Http://docs.jinkan.org/docs/flask-sqlalchemy

Installation

• Installing Flask-sqlalchemy

  Pip Install Flask-sqlalchemy

• If you are connecting to a MySQL database, you need to install MySQLdb

  Pip Install Flask-mysqldb

Database connection Settings

• In Flask-sqlalchemy, the database is specified with a URL, and the database used by the program must be saved to the Sqlalchemy_database_uri key of the Flask configuration object

  app.config[' sqlalchemy_database_uri ' = ' mysql://root:[email protected]:3306/test '

• Other settings:

  # Dynamic tracking modifies settings, such as not set only prompts warning app.config[' sqlalchemy_track_modifications ' = true# query when the original SQL statement is displayed app.config[' Sqlalchemy_echo '] = True

• Configuration complete need to go to MySQL to create the database used by the project

  $ mysql-uroot-pmysql$ CREATE DATABASE test charset UTF8;

• Other configurations

name	Notes
Sqlalchemy_database_uri	The database URI used for the connection. Example: Sqlite:////tmp/test.dbmysql://username:[email protected]/db
Sqlalchemy_binds	A dictionary that maps binds to the connection URI. For more binds information see working with binds for multiple databases .
Sqlalchemy_echo	If set to Ture, SQLAlchemy records all statements sent to stderr, which is useful for debugging. (Print SQL statement)
Sqlalchemy_record_queries	Can be used to explicitly disable or enable query logging. Query logging is automatically enabled in debug or test mode. See Get_debug_queries () for more information.
Sqlalchemy_native_unicode	Can be used to explicitly disable native Unicode support. This is necessary for some database adapters (for example, some versions of PostgreSQL on Ubuntu) when using an inappropriate specified non-encoded database default value.
Sqlalchemy_pool_size	The size of the database connection pool. Default is engine default (usually 5)
Sqlalchemy_pool_timeout	Sets the connection time-out period for the connection pool. The default is 10.
Sqlalchemy_pool_recycle	The number of seconds after which the connection is automatically recycled. This is necessary for MySQL, which by default removes connections that are idle for more than 8 hours. Note If MySQL is used, Flask-sqlalchemy automatically sets this value to 2 hours.

Connecting to other databases

For a list of full connection URIs, go to the document below SQLAlchemy (supported Databases). Some common connection strings are given here.

• Postgres:

  Postgresql://scott:[email Protected]/mydatabase

• Mysql:

  Mysql://scott:[email Protected]/mydatabase

• Oracle:

  -Oracle://scott:[email Protected]:1521/sidname

• SQLite (note the first four slashes):

  Sqlite:////absolute/path/to/foo.db

Common Types of SQLAlchemy fields

type name	types in Python	Description
Integer	Int	Ordinary integers, typically 32-bit
Smallinteger	Int	An integer with a small range of values, typically 16 bits
BigInteger	int or Long	Integer with no limit to precision
Float	Float	Floating point number
Numeric	Decimal. Decimal	Ordinary integers, typically 32-bit
String	Str	Variable length string
Text	Str	Variable length string, optimized for longer or unlimited lengths of strings
Unicode	Unicode	Variable-length Unicode string
UnicodeText	Unicode	Variable-length Unicode string optimized for longer or unlimited length strings
Boolean	bool	Boolean value
Date	Datetime.date	Time
Time	Datetime.datetime	Date and time
Largebinary	Str	binary files

Common sqlalchemy Column Options

Option Name	Description
Primary_key	If True, represents the primary key for the table
Unique	If true, this column does not allow duplicate values
Index	If true, create an index for this column to improve query efficiency
Nullable	If true, a null value is allowed, and if False, a null value is not allowed
Default	Define default values for this column

Common SQLAlchemy Relationship Options

Option Name	Description
Backref	Add a reverse reference to another model of a relationship
Primary Join	Explicitly specify the junction conditions used between the two models
Uselist	If False, the list is not used, and a scalar value is used
Order_by	Specify how records are sorted in a relationship
Secondary	Specify the name of the relationship table in a many-to-many relationship
Secondary Join	Specify a two-level join condition in a many-to-many relationship when the SQLAlchemy cannot be determined at its own discretion

Flask-sqlalchemy Installation and Setup