python3操作数据库初体验

2020-10-27 | 0 评论 | 0 浏览

背景

python3操作数据库的方式有哪些呢？

初体验

pymysql

def connect(database):
      #创建数据库连接，设置ip、端口、用户名、密码、数据库等，可写在配置文件中
    connection = pymysql.connect(host='localhost',
                                 user='root',
                                 password='1234',
                                 db=database,
                                 charset='utf8mb4')
    return connection
def commit(connection, movie):
          # 创建游标, 操作数据库, 指定游标返回内容为字典类型
        with connection.cursor() as cursor:
            sql = """
            INSERT INTO `movie` (`name`, `director`, `actor`, `type`, `country`,    `language`, `time`,`introduction` ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            """
            movie = movie._asdict()
              #执行存储过程, 获取返回值
            cursor.execute(sql, list(movie.values()))
            connection.commit()

ORM - sqlalchemy

ORM：Object-Relational Mapping，把关系数据库的表结构映射到对象上
使用sqlalchemy框架实现mysql数据库操作：

import requests
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('mysql+mysqlconnector://root:@localhost:3306/zhihu')
Base = declarative_base()


class Live(Base):

    __tablename__ = 'live'
    id = Column(String(20), primary_key=True)
    name = Column(String(20))


Base.metadata.create_all(engine)


if __name__ == '__main__':
    new_live = Live(id='1', name='翻译组如何改变了电影和游戏')
    DBSession = sessionmaker(bind=engine)
    session = DBSession()
    session.add(new_live)
    session.commit()
    session.close()

参考

python3数据库操作