Python3下Pycharm与PyMySQL

一、Django是一个开放源代码的Web应用框架，由Python写成。采用了MVC的框架模式，即模型M，视图V和控制器C。

二、pip 是一个现代的，通用的 Python 包管理工具。提供了对 Python 包的查找、下载、安装、卸载的功能。

Python | 如何用pip安装模块和包 https://jingyan.baidu.com/article/466506583fec5af549e5f825.html

三、linux下环境安装PyMySQL比较方便，直接pip install PyMySQL即可，但是windows下面就比较麻烦一些。

四、安装PyMySQL

1、打开Cmder，输入“pip3 install PyMySQL”

2、测试是否安装成功

成功，在目录下你可以看到这个文件夹

五、pycharm连接PyMySQL数据库

import pymysql
pymysql.install_as_MySQLdb()
#打开数据库连接
db = pymysql.connect("localhost","root","123456")
#使用cursor()方法创建一个游标对象，能获得连接的游标
cu = db.cursor()
#使用execute()方法执行SQL查询
cu.execute("show databases")
#使用fetchall()方法获取数据
data = cu.fetchall()

print(data)
#关闭数据库连接
db.close()

、

六、对数据库进行操作

参考：https://blog.csdn.net/leyounger/article/details/73277209

文件名DBUtil.py

#把对数据库的操作封装成一个类
import pymysql


class DBUtil:
    _db =None
    #在这里配置自己的SQL服务器
    _config = {
        'host':"localhost",
        'port':3306,
        'username':"root",
        'password':'123456',
        'database':"air",
        'charset':"utf-8"
    }

    def __connect(self):
        if(self._db == None):
            self._db = pymysql.connect(
                host = self._config['host'],
                port = self._config['port'],
                user = self._config['username'],
                passwd = self._config['password'],
                db = self._config['database'],
                charset = self._config['charset']
            )
        return self._db

    def __init__(self):
        self.__connect()

    def __del__(self):
        if(self._db is not None):
            self._db.close()

    def query(self,_sql):
        cursor = self.__connect().cursor()
        try:
            cursor.execute(_sql)
            data = cursor.fetchall()
            #提交到数据库执行
            self.__connect().commit()
        except:
            #如果发生错误则回滚
            self.__connect().rollback()
            return False
        return data

    def query_dic(self,_sql_dic):
        if('select' in _sql_dic.keys()):
            sql = "SELECT"+_sql_dic['select']+"FROM"+_sql_dic['from']+self.where(_sql_dic['where'])
            print(sql)
            return self.query(sql)
        elif('insert' in _sql_dic.keys()):
            sql = "INSERT INTO"+_sql_dic['insert']+self.quote(_sql_dic['domain_array'],type_filter=False+"VALUES"+self.quote(_sql_dic['value_array']))
            print(sql)
            return self.query(sql)
        if('delete' in _sql_dic.keys()):
            sql = "DELETE FROM"+_sql_dic['delete']+self.where(_sql_dic['where'])
            print(sql)
            return self.query(sql)

        def where(self,_sql):
            if(isinstance(_sql,dict)==False):
                return "WHERE"+ str(_sql)
            if(isinstance(_sql,dict)):
                _sql_dic = _sql
                s = "WHERE"
                index = 0
                for domain in _sql_dic:
                    if(index == 0):
                        s += domain + "=" + str(_sql_dic[domain]) + " "
                        index += 1
                    else:
                        s += "ADD" + domain + "=" + str(_sql_dic[domain]) + " "
                return s

        #为数组加上外括号，并拼接字符串
        def quote(self,_data_array,type_filter=True):
            s = "("
            index = 0
            if(type_filter):
                for domain in _data_array:
                    if(index == 0):
                        if(isinstance(domain,int)):
                            s += str(domain)
                        elif (isinstance(domain,str)):
                            s += "'" + domain + "'"
                        index += 1
                    else:
                        if(isinstance(domain,int)):
                            s += "," + str(domain)
                        elif(isinstance(domain,str)):
                            s += "," + "'" +domain +"'"
            else:
                for domain in _data_array:
                    if(index == 0):
                        s += str(domain)
                        index+=1
                    else:
                        s += "," + domain
            return s+")"