Pycharm安装Datebase插件

进入File--->seeting--->Plugins,搜索Datebase 找到Datebase Tools and SQL插件进行安装

1.png

安装成功后重启Pycharm,界面右侧展示Datebase入口

2.png
打开一个SQL文件,默认需要配置读取方式

3.png
进入配置界面,选择MySQL,输入服务器信息、表名、端口号、用户名和密码

4.png
备注：点击左下角【Test Connection】按钮，测试一下是否成功连接
连接成功后右侧展示所连接服务器下数据库信息

5.png
使用该插件，输入查询SQL语句

6.png
成功查询到语句

7.png

Python 读取MySQL

常规读取Mysql参考下面代码及查询结果

# -*- coding: utf-8 -*-
# @Time : 2020/3/14 13:33
# @Author : 寒笙

import mysql.connector


"""连接数据库"""
config = {
    "host":"172.16.267.238",
    "port":3306,
    "user":"wftest",
    "password":"6658CGWcqp8pk0h22F",
    "database":"app_kdz"
}

con = mysql.connector.connect(**config)

"""创建游标"""
cursor = con.cursor()

sql = "SELECT a.date, count( DISTINCT a.user_id ) AS 用户," \
      "sum( a.gold_consume ) AS 消费金币," \
      "sum( a.gold_gain ) AS 获得金币,"\
      "sum( a.lottery_gain ) AS 获得奖券," \
      "sum( a.lottery_gain * 10+ a.gold_gain ) / sum( a.gold_consume ) AS 返奖率," \
      "round( sum( a.duration / 60 ) / count( DISTINCT a.user_id ), 2 ) AS 平均游戏时长 " \
      "FROM (" \
           "SELECT " \
             "date( create_time ) date," \
              "id,grade,user_id,duration,gold_consume,gold_gain,lottery_gain " \
              "from " \
               "log_game_record_202111 " \
                 "where create_time BETWEEN '2021-11-04 19:01:00' AND '2021-11-04 21:00:30' AND gold_consume > 0 )" \
                 " a GROUP BY 1 "


cursor.execute(sql)

"""默认"""
"""打印每一条记录"""
for one in cursor:
    print(one)

con.close() #关闭数据库

8.png

注意：发现上述代码SQL语句查询展示有点不美观，同时查询结果不直观

优化查询结果使其直观展示

安装prettytable库 pip install prettytable，查询结果如下图更直观漂亮

# -*- coding: utf-8 -*-
# @Time : 2020/3/14 13:33
# @Author : 寒笙

import mysql.connector
from prettytable import from_db_cursor


"""连接数据库"""
config = {
    "host":"172.16.267.238",
    "port":3306,
    "user":"wftest",
    "password":"5555CGWcqp8pk0h22F",
    "database":"app_kdz"
}

con = mysql.connector.connect(**config)

"""创建游标"""
cursor = con.cursor()


sql = "SELECT a.date, count( DISTINCT a.user_id ) AS 用户," \
      "sum( a.gold_consume ) AS 消费金币," \
      "sum( a.gold_gain ) AS 获得金币,"\
      "sum( a.lottery_gain ) AS 获得奖券," \
      "sum( a.lottery_gain * 10+ a.gold_gain ) / sum( a.gold_consume ) AS 返奖率," \
      "round( sum( a.duration / 60 ) / count( DISTINCT a.user_id ), 2 ) AS 平均游戏时长 " \
      "FROM (" \
           "SELECT " \
             "date( create_time ) date," \
              "id,grade,user_id,duration,gold_consume,gold_gain,lottery_gain " \
              "from " \
               "log_game_record_202111 " \
                 "where create_time BETWEEN '2021-11-04 19:01:00' AND '2021-11-04 21:00:30' AND gold_consume > 0 )" \
                 " a GROUP BY 1 "


cursor.execute(sql)

# """默认"""
# """打印每一条记录"""
# for one in cursor:
#     print(one)


"""使用prettytable"""
table = from_db_cursor(cursor)
print(table)

"""打印每一条记录"""
for one in table:
    print(one)

con.close() #关闭数据库

9.png

简化代码，Python读取SQL文件，参考代码如下：

# -*- coding: utf-8 -*-
# @Time : 2020/3/14 13:33
# @Author : 寒笙

import mysql.connector
from prettytable import from_db_cursor

sql = open('chaxun.sql','r',encoding='utf-8')
sqltxt = sql.readlines()

# 读取之后关闭文件
sql.close()

# list 转 str
sql = "".join(sqltxt)

# print(sql)

"""连接数据库"""
config = {
    "host":"172.16.247.238",
    "port":3306,
    "user":"wftest",
    "password":"8eCGWcqp8pk0h22F",
    "database":"app_kdz"
}

con = mysql.connector.connect(**config)

"""创建游标"""
cursor = con.cursor()

#连接SQL
cursor.execute(sql)

"""使用prettytable"""
table = from_db_cursor(cursor)
print(table)

# """打印每一条记录"""
# for one in table:
#     print(one)

con.close() #关闭数据库