四 .Django (原生mysql操作)

一. Django原生mysql(项目操作增删改查)

1.mysql(语句封装)

 回顾  https://www.cnblogs.com/ugfly/p/7739182.htm 表的三种关系  一对一  一对多  多对多

回顾一下mysql知识https://www.cnblogs.com/dangrui0725/p/9594625.html

使用Python将Excel中的数据导入到MySQL https://www.cnblogs.com/taceywong/p/5428356.html    

https://zzk.cnblogs.com/s/blogpost?Keywords=python%E7%9A%84mysql&pageindex=1 python 原生mysql引用

函数封装

import  pymysql
# 查   所数据
def get_all(sql):
    conn = pymysql.connect(host="localhost", user="root", password="root", database="db6")
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cur.execute(sql)
    res = cur.fetchall()
    cur.close()
    conn.close()
    return  res


# 查 一行数据
def get_one(sql,args):
    conn = pymysql.connect(host="localhost", user="root", password="root", database="db6")
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cur.execute(sql,args)
    res = cur.fetchone()
    cur.close()
    conn.close()
    return  res



# 增 删 改  都要提交 commit
def get_mif(sql,args):
    conn = pymysql.connect(host="localhost", user="root", password="root", database="db6")
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cur.execute(sql,args)
    conn.commit()
    cur.close()
    conn.close()


# 增 删 改  都要提交 commit
# 添加并且带返回值
def get_create(sql,args):
        conn = pymysql.connect(host="localhost", user="root", password="root", database="db6")
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cur.execute(sql,args)
        conn.commit()
        cur.close()
        conn.close()
        return cur.lastrowid
        # python插入记录后取得主键id的方法(cursor.lastrowid和conn.insert_id())



# 增 删 改  都要提交 commit
# 批量加入 以元祖的形式传参数   就是添加几次次提交一次
def mul_mode(sql, args):
        conn = pymysql.connect(host="localhost", user="root", password="root", database="db6")
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        # self.cursor.executemany("insert into user (id,name) values (%s,%s)",[(1,"aaa"),(2,"bbb"),(3,"ccc")])  传参方式
        cur.executemany(sql, args)
        conn.commit()
        cur.close()
        conn.close()

对象封装

import  pymysql
# 注意 args 参数可以传空值[]
class Mysqls(object):
    def __init__(self):
         # 读取配置文件
        self.connect()

    def connect(self):
        self.conn = pymysql.connect(host="localhost", user="root", password="root", database="db6")    #  可以把主机连接等写入配置文件 等
        self.cursor=self.conn.cursor(cursor=pymysql.cursors.DictCursor)

   # 获取所以数据
    def get_all(self,sql,args):
         self.cursor.execute(sql,args)
         res = self.cursor.fetchall()
         return  res

   # 获取一行数据
    def get_one(self,sql,args):
        self.cursor.execute(sql, args)
        res = self.cursor.fetchone()
        return res

   # 添加  就是添加一次提交多次
    def get_mode (self,sql,args):
        self.cursor.execute(sql, args)
        self.conn.commit()

    # 添加并且带返回值
    def get_create(self,sql,args):
        self.cursor.execute(sql,args)
        self.conn.commit()
        return self.cursor.lastrowid
       # python插入记录后取得主键id的方法(cursor.lastrowid和conn.insert_id())

     # 批量加入 以元祖的形式传参数   就是添加一次提交一次
    def mul_mode(self, sql, args):
        # self.cursor.executemany("insert into user (id,name) values (%s,%s)",[(1,"aaa"),(2,"bbb"),(3,"ccc")])  传参方式
        self.cursor.executemany(sql, args)
        self.conn.commit()

    def get_close(self):
           self.cursor.close()
           self.conn.close()