Python3 using SQLAlchemy to connect to a database

Installation Configuration mariadb

Yum install mariadb mariadb-server mariadb-develcreate database first_sqlalchemy charset utf8;grant all privileges on Firs t_sqlalchemy.* to ' Heboan ' @ ' percent ' identified by ' 123456 '; flush privileges; Info: Data address: 192.168.88.1:3306 database: First_ SQLAlchemy User: Heboan Password: 123456

Python3 Installing Pymysql and SQLAlchemy

# Pymysql:pymysql is a package that Python uses to manipulate MySQL, so installing pip from pip install pymysql# Sqlalchemy:sqlalchemy is an ORM framework for data, installing PIP via pip Install SQLAlchemy

Connect to a database using SQLAlchemy

From SQLAlchemy Import create_engine# database configuration information host = ' 192.168.88.1 ' PORT = ' 3306 ' db = ' first_sqlalchemy ' USERNAME = ' He Boan ' PASSWORD = ' 123456 ' # Connection database fixed format: Dialect+driver://username:[email protected]:p Ort/databasedb_uri = "mysql+ Pymysql://{username}:{password}@{host}:{port}/{db}?charset-utf8 ". Format (username=username,                                                                                        password= PASSWORD,                                                                                        host=host,                                                                                        port=port,                                                                                        db=database) #创建一个引擎engine = Create_engine (Db_uri) # Call this engine's Connect method to get an object conn = Engine.connect () #通过这个对象就可以对数据进行操作result = Conn.execute (' Select version () ') print ( Result.fetchone ())

ORM Introduction

As the project grows larger and more SQL is present in the code using native SQL, the problem arises:

　　1, the SQL statement repetition utilization is not high, the more complex SQL statement conditions, the longer the code, there will be a lot of similar SQL statements

2, many SQL statements are spelled out in the business logic, if the data need to change, it is necessary to modify these logic, more error-prone

3, write SQL is easy to ignore the Web security issues, the future is a hidden danger, such as SQL injection

ORM, full Name object relationship Mapping, Chinese called object-relational mapping, through ORM We can use the way the class to manipulate the database, instead of writing the native SQL statements. The table is mapped into classes, the rows are instantiated, and the fields are used as attributes. The ORM will eventually convert the corresponding operation into a database native statement when performing object operations. There are many advantages to using ORM:

1, ease of use: using ORM to do database development can effectively reduce the probability of repeating SQL statements, the model is more intuitive, clear

2, the performance loss is small: ORM conversion to the underlying database operation instructions there is actually some overhead, but from the actual situation, this performance loss less than 5%

3, Design flexibility: can easily write complex queries

4, Portability: SQLAlchemy package The underlying database implementation, support multiple relational database engine, can easily switch the database

Python3 using SQLAlchemy to connect to the database