SQLAlchemy操纵MySQL数据库

SQLAlchemy操纵MySQL数据库

环境

Python 3.7.4
pymysql 0.9.3
sqlalchemy 1.3.23
MySQL Server version: 5.7.18-20170830-log 20170531
PyCharm 2020.2.4 (Professional Edition)

实现代码

数据库URL

• [dialect] : 数据库方言，如mysql，posgrel等
• [driver] : 数据库驱动，如pymysql，psycopg2等
• [user] : 用户名
• [password] : 密码
• [hostname] : 数据库主机名，本地为localhost
• [port] : 数据库端口号，默认为3306
• [database] : 数据库名

The string form of the URL is
dialect[+driver]://user:password@host/dbname[?key=value..], where
dialect is a database name such as mysql, oracle,
postgresql, etc., and driver the name of a DBAPI, such as
psycopg2, pyodbc, cx_oracle, etc. Alternatively,
the URL can be an instance of :class:~sqlalchemy.engine.url.URL.

main.py

from user_alchemy import UserAlchemy, User

if __name__ == '__main__':
    url = "[dialect]+[driver]://[user]:[password]@[hostname]:[port]/[database]"
    userAlchemy = UserAlchemy(url)
    user = User(username="Tony", password="123456")
    userAlchemy.insert(user)
    user = User(username="Charles", password="123456")
    userAlchemy.insert(user)
    user = User(username="Tom", password="123456")
    userAlchemy.insert(user)
    userAlchemy.commit()
    users = userAlchemy.query_all()
    for user in users:
        print(user)

user_alchemy.py

import sqlalchemy
from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    username = Column(String(128))
    password = Column(String(128))

    def __repr__(self):
        return "<User(username='%s', password='%s')>" % (self.username, self.password)


class UserAlchemy:
    def __init__(self, url):
        self.session = None
        try:
            # ``dialect[+driver]://user:password@host/dbname[?key=value..]``
            engine = sqlalchemy.create_engine(url, encoding="utf-8")

            Base.metadata.create_all(engine)

            Session = sqlalchemy.orm.sessionmaker()

            Session.configure(bind=engine)

            self.session = Session()

        except Exception as e:
            print(e)

    def insert(self, user):
        self.session.add(user)

    def query_all(self):
        return self.session.query(User)

    def commit(self):
        self.session.commit()

测试结果

<User(username='Tony', password='123456')>
<User(username='Charles', password='123456')>
<User(username='Tom', password='123456')>

参考

SQLAlchemy

最后

• 由于博主水平有限，不免有疏漏之处，欢迎读者随时批评指正，以免造成不必要的误解！