Basic use of sqlalchemy and sqlalchemy

Basic use of sqlalchemy and sqlalchemy
I. Introduction to ORM

The full name of orm is object relational ing, which is an object ing program. In simple words, we use object-oriented programs like python, but the databases we use are relational, to ensure consistent usage habits, orm is used to establish a ing relationship between the object model of the programming language and the relational model of the database, in this way, you can directly use the object model of the programming language to operate the database, instead of using the SQL language directly.

　　

Advantages of orm:

Disadvantages:

Ii. sqlalchemy

In Python, the most famous ORM framework is SQLAlchemy.

　　

Dialect is used to communicate with data APIs. Different database APIs are called based on different configuration files to perform database operations, for example:

MySQL-Python mysql + mysqldb: // <user >:< password >@< host> [: <port>]/<dbname> pymysql mysql + pymysql: // <username >:< password >@< host>/<dbname> [? <Options>] MySQL-Connector mysql + mysqlconnector: // <user >:< password >@< host> [: <port>]/<dbname> cx_Oracle oracle + cx_oracle: // user: pass @ host: port/dbname [? Key = value & key = value...] # For more information, see: http://docs.sqlalchemy.org/en/latest/dialects/index.html

　　Install sqlalchemy

pip3 install sqlalchemy

　　

III. Basic use of sqlalchemy

3.1 create a table

Create a table using SQL statements:

CREATE TABLE user (    id INTEGER NOT NULL AUTO_INCREMENT,    name VARCHAR(32),    password VARCHAR(64),    PRIMARY KEY (id))

Create with sqlalchemy

#-*-Coding: UTF-8-*-from sqlalchemy import create_enginefrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy. orm import sessionmakerengine = create_engine ("mysql + pymysql: // bigberg: 111111@172.16.200.49: 3306/study", encoding = "UTF-8", echo = True) # connect to the database, echo = True => Print all information out Base = declarative_base () # generate an orm Base class User (Base ): _ tablename _ = "user" # table name id = Column (Integer, primary_key = True) name = Column (String (32) passwd = Column (String (64 )) base. metadata. create_all (engine) # create and define all tables

View the new table:

mysql> desc user;+--------+-------------+------+-----+---------+----------------+| Field  | Type        | Null | Key | Default | Extra          |+--------+-------------+------+-----+---------+----------------+| id     | int(11)     | NO   | PRI | NULL    | auto_increment || name   | varchar(32) | YES  |     | NULL    |                || passwd | varchar(64) | YES  |     | NULL    |                |+--------+-------------+------+-----+---------+----------------+3 rows in set (0.02 sec)

 

3.2 insert data

Use sqlalchemy to insert data

# Create a session class with the database. Note that the class returned to the session is not Session_class = sessionmaker (bind = engine) # generate session instance Session = Session_class () # generate the data object user_obj = User (name = "bigberg", passwd = "twgdh123") You want to create. # No object has been created yet, if you do not believe it, print the id and find it Noneprint (user_obj.name, user_obj.id) # Add the data object to this session and create the Session in a unified manner. add (user_obj) # print (user_obj.name, user_obj.id) is still not created at this time. # submit data in a unified manner to create a data Session. commit ()

mysql> select * from user;+----+---------+----------+| id | name    | passwd   |+----+---------+----------+|  1 | bigberg | twgdh123 |+----+---------+----------+1 row in set (0.00 sec)