python3操作数据库初体验

  |   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()

参考