python使用原生的sql以及ORM操作mysql

在django项目中的setting配置数据库

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'stu_manage',
        'USER': 'root',
        'PASSWORD': '1qaz2wsx',
        'HOST': 'localhost',
        'PORT': 3306
        # python manage.py inspectdb > app01/models.py
        # python manage.py makemigrations ，python manage.py migrate
    }
}

 

封装一个原生sql语句操作数据库的类方法

class SqlHelper(object):
    def __init__(self):
        # 读取配置文件
        self.connect()
    def connect(self):
        self.conn = pymysql.connect(host='127.0.0.1',
                               port=3306,
                               user='root',
                               passwd='1qaz2wsx',
                               db='stu_manage',
                               charset='utf8')
        # 创建游标
        # 游标设置为字典类型
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    def get_list(self, sql, args):
        # 执行SQL，返回值
        self.cursor.execute(sql, args)
        # 获取数据
        result = self.cursor.fetchall()
        return result

    def get_one(self, sql, args):
        # 执行SQL，返回值
        self.cursor.execute(sql, args)
        # 获取数据
        result = self.cursor.fetchone()
        return result

    def modify(self, sql, args):
        # 执行SQL，返回值
        self.cursor.execute(sql, args)
        # 提交数据
        self.conn.commit()

    def multiple_modify(self, sql, args):
        # self.cursor.executemany("insert into d(id name) values(%s, %s)", [(1,'alex'), (2, 'eric')])
        self.cursor.executemany(sql, args)
        self.conn.commit()

    def create(self, sql, args):
        # 执行SQL，返回值
        self.cursor.execute(sql, args)
        # 返回主键id
        last_row_id = self.cursor.lastrowid
        # 提交数据
        self.conn.commit()
        return last_row_id

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

# obj = SqlHelper()
# obj.connect()
# obj.multiple_modify()
# obj.close()

增删改查

# 查
obj = sqlheper.SqlHelper()
obj.get_list("select id, name from teacher", [])
obj.close()

# 改
sqlheper.modify("update student set name=%s, class_id=%s where id=%s",[name,class_id,nid])

# 增
sqlheper.modify('insert into class(title) value(%s)', [title,])

# 删
sqlheper.modify('delete from class where id=%s', [nid])

ORM增删改查

 

# 增
models.Company_Video.objects.create(user_id=_id, video=video)

# 删
e_models.User.objects.filter(elite_id=elite_id).delete()

# 改
e_models.User.objects.filter(elite_id=elite_id).update(resume_name=name, weixin_gender=weixin_gender, resume_age=age)

# 查
h_models.Agent.objects.filter(is_del=0, person_phone=agent_username).values('province_id').first()

region_dict = [model_to_dict(i, fields=['code', 'name', 'parent_code', 'first_char']) for i in region_city]
 
{code: "500000", name: "重庆市", parent_code: "500000", first_char: "C"}

获取字段加1

update_sql = "update hr24_elite_user set credit_rating=credit_rating-1 where elite_id={} and credit_rating > 0".format('"' + elite_id + '"')
db_cursor.execute(update_sql)
# 提交数据
mysql.commit()
# 关闭游标
db_cursor.close()
# 关闭链接
mysql.close()

models.User.objects.filter(elite_id=elite_id).update(credit_rating=F('credit_rating')+1)

获取字段去重

select_auth = {'name': '李四', 'age': 23}
auth_count = e_models.Auth.objects.filter( **select_auth).values(
            'id_card_number').distinct().order_by('id_card_number').count()


select distinct ProvinceSysNo,ProvinceName from Area where ProvinceSysNo is NOT NULL AND CitySysNo is NULL