Getting Started with Flask Web Development (vi) Access database __ Database

We introduce two ways to access the MySQL database, one is to use the mysql.connector Direct connection, the other is to use the SQLAlchemy ORM Framework. Mysql.connector Direct connection mode

# Get database connection
def get_connection ():
    try:
        conn = Mysql.connector.connect (**config)
        return  Conn
    except Exception as E:
        logger.debug (' Exception is%s '% E) return to
        None


# Get user by user_id
def Get_user (user_id):
    try:
        conn = get_connection ()
        If conn:
            cursor = conn.cursor ()
            Cursor.execute (User_sql, (user_id,)) Result
            = Cursor.fetchall ()
            logger.debug (' conn is%s '% conn)
            return result
        else:
            logger.debug (' conn is%s '% conn) return to
            None
        logger.debug ("Conn is%s"% conn) C23/>return none
    except Exception as E:
        logger.debug (' Exception is%s '% E) return
        none
    finally:< C28/>cursor.close ()
        conn.close ()

where config is defined as follows:

Config = {
' Host ': ' 127.0.0.1 ',
' User ': ' User ',
' Password ': ' Password ',
' Port ': 3306,
' Database ': ' DB ',
' CharSet ': ' UTF8 '
}

User_sql = ' SELECT * from db.test where user_id =%s Limit 1 ' SQLAlchemy ORM Framework

Defining a connection string

Orm_url = ' mysql+mysqlconnector://user:password@ip:port/db '

Get user objects

def get_user_engine (user_id):
    try:
        engine = Create_engine (orm_url) Session
        = Sessionmaker ()
        Session.configure (bind=engine)
        Base.metadata.create_all (engine)
        S = Session ()
        ret = S.query (User). Filter_by (username=user_id). A () A return
        ret
    except Exception as E:
        logger.debug (' Exception is%s '% E ) Return
        None

Where the user class is defined as follows:

From SQLAlchemy import Column, Integer, String from sqlalchemy.ext.declarative import declarative_base base = declarative

    _base () # http://docs.sqlalchemy.org/en/latest/orm/mapping_columns.html class User (base): __tablename__ = ' T_user '  id = column (' id ', Integer, primary_key=true) Username = column (' user_id ', String (128)) email = column (' Email ', String (128)) Password = column (' Pwd_hash ', string (128)) create_time = column (' Create_time ', string (128)) UPDA Te_time = Column (' Update_time ', String (128)) def __init__ (self, id, username, email, password, create_time, Update_ti
        Me): self.id = id self.username = Username self.email = Email Self.password = password  Self.create_time = Create_time Self.update_time = Update_time def __repr__ (self): return ' <id Is%s, username was%s, password is%s, email was%s, create time was%s, update time is%s> '% (self.id, SE Lf.username, Self.passwoRd, Self.email, Self.create_time, Self.update_time)
 

Call Method:

         user = Flask_db.get_user_engine (username)
         logger.debug (' DB User ID '%s, detail is%s '% (user.username, user))

Http://docs.sqlalchemy.org/en/latest/orm/mapping_columns.html

Source reference:Https://github.com/ypmc/flask-sqlalchemy-web