本文介紹Pycharm安裝可視化插件Datebase查詢數(shù)據(jù),以及Python讀取Mysql可視化效果展示。
Pycharm安裝Datebase插件
- 進(jìn)入File--->seeting--->Plugins,搜索Datebase 找到Datebase Tools and SQL插件進(jìn)行安裝
1.png
-
安裝成功后重啟Pycharm,界面右側(cè)展示Datebase入口
2.png -
打開一個(gè)SQL文件,默認(rèn)需要配置讀取方式
3.png -
進(jìn)入配置界面,選擇MySQL,輸入服務(wù)器信息、表名、端口號(hào)、用戶名和密碼
4.png
備注： 點(diǎn)擊左下角【Test Connection】按鈕，測(cè)試一下是否成功連接 -
連接成功后右側(cè)展示所連接服務(wù)器下數(shù)據(jù)庫信息
5.png -
使用該插件，輸入查詢SQL語句
6.png -
成功查詢到語句
7.png
Python 讀取MySQL
常規(guī)讀取Mysql參考下面代碼及查詢結(jié)果
# -*- coding: utf-8 -*-
# @Time : 2020/3/14 13:33
# @Author : 寒笙
import mysql.connector
"""連接數(shù)據(jù)庫"""
config = {
"host":"172.16.267.238",
"port":3306,
"user":"wftest",
"password":"6658CGWcqp8pk0h22F",
"database":"app_kdz"
}
con = mysql.connector.connect(**config)
"""創(chuàng)建游標(biāo)"""
cursor = con.cursor()
sql = "SELECT a.date, count( DISTINCT a.user_id ) AS 用戶," \
"sum( a.gold_consume ) AS 消費(fèi)金幣," \
"sum( a.gold_gain ) AS 獲得金幣,"\
"sum( a.lottery_gain ) AS 獲得獎(jiǎng)券," \
"sum( a.lottery_gain * 10+ a.gold_gain ) / sum( a.gold_consume ) AS 返獎(jiǎng)率," \
"round( sum( a.duration / 60 ) / count( DISTINCT a.user_id ), 2 ) AS 平均游戲時(shí)長 " \
"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)
"""默認(rèn)"""
"""打印每一條記錄"""
for one in cursor:
print(one)
con.close() #關(guān)閉數(shù)據(jù)庫
8.png
注意：發(fā)現(xiàn)上述代碼SQL語句查詢展示有點(diǎn)不美觀，同時(shí)查詢結(jié)果不直觀
優(yōu)化查詢結(jié)果使其直觀展示
安裝prettytable庫 pip install prettytable，查詢結(jié)果如下圖更直觀漂亮
# -*- coding: utf-8 -*-
# @Time : 2020/3/14 13:33
# @Author : 寒笙
import mysql.connector
from prettytable import from_db_cursor
"""連接數(shù)據(jù)庫"""
config = {
"host":"172.16.267.238",
"port":3306,
"user":"wftest",
"password":"5555CGWcqp8pk0h22F",
"database":"app_kdz"
}
con = mysql.connector.connect(**config)
"""創(chuàng)建游標(biāo)"""
cursor = con.cursor()
sql = "SELECT a.date, count( DISTINCT a.user_id ) AS 用戶," \
"sum( a.gold_consume ) AS 消費(fèi)金幣," \
"sum( a.gold_gain ) AS 獲得金幣,"\
"sum( a.lottery_gain ) AS 獲得獎(jiǎng)券," \
"sum( a.lottery_gain * 10+ a.gold_gain ) / sum( a.gold_consume ) AS 返獎(jiǎng)率," \
"round( sum( a.duration / 60 ) / count( DISTINCT a.user_id ), 2 ) AS 平均游戲時(shí)長 " \
"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)
# """默認(rèn)"""
# """打印每一條記錄"""
# for one in cursor:
# print(one)
"""使用prettytable"""
table = from_db_cursor(cursor)
print(table)
"""打印每一條記錄"""
for one in table:
print(one)
con.close() #關(guān)閉數(shù)據(jù)庫
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()
# 讀取之后關(guān)閉文件
sql.close()
# list 轉(zhuǎn) str
sql = "".join(sqltxt)
# print(sql)
"""連接數(shù)據(jù)庫"""
config = {
"host":"172.16.247.238",
"port":3306,
"user":"wftest",
"password":"8eCGWcqp8pk0h22F",
"database":"app_kdz"
}
con = mysql.connector.connect(**config)
"""創(chuàng)建游標(biāo)"""
cursor = con.cursor()
#連接SQL
cursor.execute(sql)
"""使用prettytable"""
table = from_db_cursor(cursor)
print(table)
# """打印每一條記錄"""
# for one in table:
# print(one)
con.close() #關(guān)閉數(shù)據(jù)庫
10.png
9.png
