Sorted out some notes of learning SQLAlchemy before, ready to make a series to write, by the way to consolidate their own knowledge, well, no more nonsense, let’s start to learn SQLAlchemy

Before we learn SQLAlchemy, we need to know something called ORM,

Object Relational Mapping (ORM) allows you to manipulate a database as a class instead of writing native SQL statements by Mapping tables into classes, using rows as examples, and using fields as properties. When an ORM performs an object operation, it eventually converts the corresponding operation into a database native statement. Let’s talk about the advantages of using ORM:

  1. Ease of use: using ORM to do database development can effectively reduce the probability of repeated SQL statements, write out the model is more intuitive, clear

  2. Small performance loss: the ORM into the underlying data operation instruction does have some overhead, but from the actual situation, this performance loss rarely (less than 5%), as long as not have strict requirements on performance, comprehensive development efficiency, the readability of the code, is far greater than the benefits of performance losses, and as the project is more and more big effect more apparent

  3. Flexible design: can easily write complex queries

  4. Portability: SQLAlchemy encapsulates the underlying database implementation and supports multiple relational database engines, including the popular MySQL, PostgreSQL and SQLite, making it very easy to switch between databases

Well, that’s all for ORM, so let’s get back to today’s topic

SQLAlchemy: SQLAlchemy is an ORM framework for a database. You can install our SQLAlchemy by PIP install SQLAlchemy

Next up the code

2. Prepare the database connection data # Because the database is different, so each database has its own connection mode. Mysql > select * from 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost', 'localhost DB_NAME = '# database name DB_URI =' mysql + mysqldb: / / % s: % s @ % s: % s / % s? Charset =utf8' % (DB_USERNAME,DB_PASSWORD,DB_HOST,DB_PORT,DB_NAME)

Once the engine is created, we need to test whether it connects to the database successfully, and it will pass

with engine.connect() as con:
    rs = con.execute("select 1")
    print rs.fetchone()

If (1L,) is printed out, it means you have successfully connected to the database. Next we will use the ORM method to create the class. Without further mention, we will use the code

Mysql > create a class UserModel(Base = Engine); / / create a new class UserModel(Base); __tablename__ = 'users' id = Column(Integer,primary_key=True,autoincrement=True) UserName = UserName = UserName = UserName = UserName = UserName = UserName Column(String(100),nullable=False) password = Column(String(100),nullable=False) def __repr__(self): # this is purely to print more attractive return '< UserModel (id = "% s", the username = "% s", "=" % s ") >' % (self. The id, the self. The username, the self. The password) # Submit the User table to table base.metadata. create_all();

This creates a users table in our database

Then I’ll introduce you to the common data types used in SQLAlchemy

  1. Integer: Integer that maps to an int type in the database

  2. String: A character type that maps to a varchar type in the database. When used, a character length is required

  3. Boolean: Map to database bool, pass True/False when used

  4. Text: The Text type that maps to the Text type in the database

  5. Date: Date type, no time. The map to the database is of type Date. When used, pass dateTime.Date () to it

  6. DATETIME: The DateTime type. The map to the database is of the datetime type. When used, pass datetime.datetime() into it

  7. Float: Floating point type

The parameters commonly used for the Column class

  1. Primary_key: Primary keys, True and False

  2. Autoincrement: Whether toincrement automatically, True and False

  3. A. unique B. unique C

  4. Nullable: Nullable. Default is True

  5. Default: The default value

  6. OnUpdate: This is usually used for time updates

Well, today first introduced here, next time to bring you is the ORM add, delete, check and change