|
import pymysql |
|
from collections import defaultdict |
|
|
|
""" |
|
This script is used to generate Navicat connections list to save your time adding many MySQL connection info to Navicat GUI. |
|
|
|
output: (in current directory) |
|
- connections.ncx: import it to navicat |
|
- vgroup.xml: move it to navicat Profile location, usually C:\Users\ecuser\Documents\Navicat\Premium\profiles |
|
|
|
db instances login user and password shall be provided by your self. |
|
|
|
ablog.mysql.info: |
|
CREATE TABLE `mysql_info` ( |
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
|
`environment` varchar(20) DEFAULT NULL, |
|
`dbid` varchar(30) DEFAULT NULL, |
|
`dbhost` varchar(100) DEFAULT NULL, |
|
`dbport` int(11) DEFAULT '3306', |
|
`dbgroup` varchar(20) DEFAULT NULL, |
|
`dbcluster` varchar(20) DEFAULT NULL, |
|
`conn_name` varchar(60) DEFAULT NULL, |
|
`dbuser` varchar(30) DEFAULT NULL, |
|
`dbpass` varchar(120) DEFAULT NULL, |
|
`hostname` varchar(60) DEFAULT NULL, |
|
`description` varchar(120) DEFAULT NULL, |
|
PRIMARY KEY (`id`) |
|
) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8mb4; |
|
|
|
""" |
|
|
|
dbconn = pymysql.Connect(host='192.168.1.125', port=3307, user='xx', password='xxx', charset='utf8', database='ablog') |
|
|
|
sql = """ |
|
SELECT * FROM mysql_info |
|
""" |
|
|
|
cur = dbconn.cursor() |
|
cur.execute(sql) |
|
instances = cur.fetchall() |
|
|
|
navicat_connections = [] |
|
navicat_vgroup = defaultdict(list) |
|
tmpl_connection = """ |
|
<Connection ConnectionName="{0}" ConnType="MYSQL" OraConnType="BASIC" |
|
Host="{1}" Port="{2}" Database="" OraServiceNameType="SERVICENAME" TNS="" MSSQLAuthenMode="SQLSERVER" DatabaseFileName="" |
|
UserName="{3}" Password="{4}" SavePassword="true" SettingsSavePath="C:\Users\Administrator\Documents\Navicat\MySQL\servers\{0}" |
|
Encoding="65001" Keepalive="false" KeepaliveInterval="240" MySQLCharacterSet="true" Compression="false" AutoConnect="false" NamedPipe="false" NamedPipeSocket="/tmp/mysql.sock" OraRole="DEFAULT" OraOSAuthen="false" SQLiteEncryption="false" MSSQLEncryption="false" UseAdvanced="false" SSL="false" SSL_Authen="false" SSL_PGSSLMode="REQUIRE" SSL_ClientKey="" SSL_ClientCert="" SSL_CACert="" SSL_Clpher="" SSL_PGSSLCRL="" SSH="false" SSH_Host="" SSH_Port="22" SSH_UserName="" SSH_AuthenMethod="PASSWORD" SSH_Password="" SSH_SavePassword="false" SSH_PrivateKey="" HTTP="false" HTTP_URL="" HTTP_EQ="false" HTTP_PA="false" HTTP_PA_UserName="" HTTP_PA_Password="" HTTP_PA_SavePassword="false" HTTP_CA="false" HTTP_CA_ClientKey="" HTTP_CA_ClientCert="" HTTP_CA_CACert="" HTTP_CA_Passphrase="" HTTP_Proxy="false" HTTP_Proxy_Host="" HTTP_Proxy_Port="0" HTTP_Proxy_UserName="" HTTP_Proxy_Password="" HTTP_Proxy_SavePassword="false"/> |
|
""" |
|
|
|
for row in instances: |
|
conn_name = row[7] |
|
db_host = row[3] |
|
db_port = row[4] |
|
db_user = row[8] |
|
db_pass_x = row[9] |
|
navicat_vgroup[row[1]].append(conn_name) |
|
navicat_connections.append(tmpl_connection.format(conn_name, db_host, db_port, db_user, db_pass_x)) |
|
|
|
|
|
|
|
tmpl_vgroup = '\n<Category Name="{0}" Type="Connection">' |
|
tmpl_vgroup_item = '<Item Name="{0}" Type="Connection" ServerType="ctMYSQL"/>' |
|
navicat_group = [] |
|
for conn_group, conn_names in navicat_vgroup.items(): |
|
navicat_group.append(tmpl_vgroup.format(conn_group)) |
|
for conn_name in conn_names: |
|
navicat_group.append(tmpl_vgroup_item.format(conn_name)) |
|
navicat_group.append('</Category>') |
|
|
|
|
|
with open("connections.ncx", 'w') as f1, open('vgroup.xml', 'w') as f2: |
|
f1.write('<?xml version="1.0" encoding="UTF-8"?>\n<Connections Ver="1.1">\n') |
|
f1.write('\n'.join(navicat_connections)) |
|
f1.write('\n</Connections>') |
|
|
|
|
|
f2.write('<?xml version="1.0" encoding="UTF-8"?>\n<VGroup Ver="1.1">\n') |
|
f2.write(''.join(navicat_group)) |
|
f2.write('\n</VGroup>') |
