需求:从SQL文件中 通过文本处理的方法 读取表名
尝试过程中,使用到的函数:
re.match() | re.search() | re.findall() | re.sub() |
---|---|---|---|
从字符串开始匹配,返回match对象 | 从整个文本中匹配,返回match对象 | 匹配并返回一个迭代器 | 去除匹配内容 |
文本内容
DROP TABLE IF EXISTS `cfg_auth_acct_all`;
/*!50001 DROP VIEW IF EXISTS `cfg_auth_acct_all`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `cfg_auth_acct_all` (
`user_id` tinyint NOT NULL,
`ugrp_id` tinyint NOT NULL,
`dgrp_id` tinyint NOT NULL,
`dgrp_name` tinyint NOT NULL,
`dev_id` tinyint NOT NULL,
`dev_name` tinyint NOT NULL,
`dev_domain` tinyint NOT NULL,
`acct_id` tinyint NOT NULL,
`acct_name` tinyint NOT NULL,
`acct_port` tinyint NOT NULL,
`acct_type` tinyint NOT NULL
) ENGINE=MyISAM */;
CREATE TABLE `log_appgenius` (
`id` bigint(20) unsigned NOT NULL DEFAULT 0,
`version` varchar(256) CHARACTER SET utf8 NOT NULL DEFAULT '',
`cpustate` varchar(256) CHARACTER SET utf8 NOT NULL DEFAULT '',
`memstate` varchar(256) CHARACTER SET utf8 NOT NULL DEFAULT '',
`totalmb` varchar(256) CHARACTER SET utf8 NOT NULL DEFAULT '',
`leftmb` varchar(256) CHARACTER SET utf8 NOT NULL DEFAULT '',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`cluste_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Table structure for table `log_approval_account_record`
--
DROP TABLE IF EXISTS `log_approval_account_record`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `log_approval_account_record` (
`id` bigint(20) unsigned NOT NULL DEFAULT 0,
`user_id` int(10) unsigned NOT NULL DEFAULT 0,
`apply_auth_acct` text DEFAULT NULL COMMENT '申请授权的帐号',
`agree_auth_acct` text DEFAULT NULL COMMENT '批准授权的帐号',
`auth_start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '授权开始时间',
`auth_end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '授权截止时间',
`auth_type` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '授权类型',
`work_order_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '工单id',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `userId` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `log_config`
--
DROP TABLE IF EXISTS `log_config`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `log_config` (
`id` bigint(20) unsigned NOT NULL DEFAULT 0,
`node_uuid` bigint(20) unsigned NOT NULL DEFAULT 0,
`action_user_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '操作用户ID',
`action_user_role` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '操作用户角色',
`action_user_name` varchar(256) NOT NULL DEFAULT '' COMMENT '操作用户名',
`action_user_realname` varchar(256) NOT NULL DEFAULT '' COMMENT '操作用户真实姓名',
`action_from` varchar(256) NOT NULL DEFAULT '' COMMENT '操作来源(IP或URL)',
`action_type` varchar(80) NOT NULL DEFAULT '' COMMENT '操作类型',
`action_module` varchar(256) NOT NULL DEFAULT '' COMMENT '配置模块',
`action_use` varchar(256) NOT NULL DEFAULT '',
`web_module` varchar(256) NOT NULL DEFAULT '',
`web_uri` varchar(256) NOT NULL DEFAULT '',
`before_value` text NOT NULL COMMENT '前值',
`before_value_str` text DEFAULT NULL,
`after_value` text NOT NULL COMMENT '后值',
`after_value_str` text DEFAULT NULL,
`action_status` tinyint(4) unsigned zerofill NOT NULL DEFAULT 0000 COMMENT '操作状态 [0: 失败,1:成功]',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`cluste_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`,`node_uuid`),
KEY `action_user_name` (`action_user_name`(255)),
KEY `action_from` (`action_from`(255)),
KEY `action_type` (`action_type`),
KEY `action_module` (`action_module`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `log_config_backup`
--
DROP TABLE IF EXISTS `log_config_backup`;
CREATE TABLE `log_config_backup` (
`id` bigint(20) unsigned NOT NULL DEFAULT 0,
`node_uuid` bigint(20) unsigned NOT NULL DEFAULT 0,
`system_version` varchar(256) NOT NULL DEFAULT '' COMMENT '系统版本',
`backup_desc` text NOT NULL COMMENT '备份说明',
`backup_type` varchar(256) NOT NULL DEFAULT '0' COMMENT '备份位置 0:本地 1:FTP 2:SFTP',
`main_dir` varchar(256) NOT NULL DEFAULT '',
`sub_dir` varchar(256) NOT NULL DEFAULT '',
`file_name` varchar(256) NOT NULL DEFAULT '' COMMENT '文件名',
`file_path` varchar(256) NOT NULL DEFAULT '' COMMENT '完整路径',
`file_size` double(18,0) NOT NULL DEFAULT 0,
`exec_result` varchar(256) NOT NULL DEFAULT '1' COMMENT '执行结果 1:成功 -1:备份失败 -2:上传失败',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`cluste_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`,`node_uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `log_disk_timer_clear_tmp_file`
--
DROP TABLE IF EXISTS `log_disk_timer_clear_tmp_file`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `log_disk_timer_clear_tmp_file` (
`upload_id` varchar(255) NOT NULL COMMENT 'id',
`file_name` text DEFAULT NULL,
`file_size` varchar(255) DEFAULT NULL,
`file_extension` varchar(255) DEFAULT NULL,
`storage_name` varchar(255) DEFAULT NULL,
`state` tinyint(4) NOT NULL DEFAULT 0 COMMENT '0为未处理,2为通过,3为不通过',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `log_login_failed`
--
DROP TABLE IF EXISTS `log_login_failed`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `log_login_failed` (
`id` bigint(20) unsigned NOT NULL DEFAULT 0,
`login_name` varchar(256) NOT NULL DEFAULT 'N/A' COMMENT '登录用户名',
`login_from` varchar(256) NOT NULL DEFAULT '' COMMENT '登录来源',
`login_type` varchar(256) NOT NULL DEFAULT '' COMMENT '登录类型',
`login_mode` varchar(256) NOT NULL DEFAULT 'N/A' COMMENT '登录模式',
`failed_count` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '登录失败计数 冗余字段.暂时不用',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`cluste_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `failed_from` (`login_name`,`login_from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `log_modify_acct_password_acct`
--
DROP TABLE IF EXISTS `log_modify_acct_password_acct`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `log_modify_acct_password_acct` (
`id` bigint(20) unsigned NOT NULL DEFAULT 0,
`log_modify_acct_pwd_record_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '帐号改密计划记录表id',
`acct_id` int(10) unsigned NOT NULL DEFAULT 0,
`dev_id` int(10) unsigned NOT NULL DEFAULT 0,
`execution_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`distribute` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '分发',
`success` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '成功',
`fail` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '失败',
`exception` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '异常回滚',
`acct_old_passwd` text DEFAULT NULL COMMENT '账号旧密码',
`acct_new_passwd` text DEFAULT NULL COMMENT '账号新密码',
`error_code` varchar(4096) NOT NULL DEFAULT '' COMMENT '错误码',
`detail` text DEFAULT NULL COMMENT '详情',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`cluste_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `log_modify_acct_password_plan_record`
--
DROP TABLE IF EXISTS `log_modify_acct_password_plan_record`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `log_modify_acct_password_plan_record` (
`id` bigint(20) unsigned NOT NULL DEFAULT 0,
`plan_name` varchar(255) NOT NULL DEFAULT '' COMMENT '计划名称',
`execution_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '执行时间',
`distribute` tinyint(4) NOT NULL DEFAULT 0 COMMENT '分发',
`result` tinyint(3) unsigned DEFAULT 0 COMMENT '结果',
`create_user_id` tinyint(3) unsigned DEFAULT 0 COMMENT '创建人的id',
`create_user_role` tinyint(3) unsigned NOT NULL DEFAULT 0,
`create_user_depart_id` int(10) unsigned NOT NULL DEFAULT 0,
`plan_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '计划任务id',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`cluste_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- 第一步 读取文件
import re
import string
new = open("new_apom_log.sql","r",encoding ="utf8")
old = open("old_apom_log.sql","r",encoding ="utf8")
newtxt = new.readlines()# 按行读取 , 此时 newtxt 为 list 类型
oldtxt = old.readlines()
new.close()
old.close()
new_log = "".join(newtxt)# list 转 str
old_log = "".join(oldtxt)
- 第二步 匹配列名
最终探索
for line in newtxt: # 循环处理每行数据
table_name = re.findall("CREATE TABLE \S+",line)
for i in iter(table_name):
table_need = re.sub(r"CREATE TABLE ","",i)
print(table_need)
最初是尝试 使用search()
for line in newtxt: # 循环处理每行数据
#print(line)
table_name = re.search("CREATE TABLE \S+",line)
if table_name == None :
pq = table_name
else :
print(table_name)
>>
<re.Match object; span=(0, 30), match='CREATE TABLE `ha_log_sessions`'>
<re.Match object; span=(0, 28), match='CREATE TABLE `log_appgenius`'>
<re.Match object; span=(0, 42), match='CREATE TABLE `log_approval_account_record`'>
<re.Match object; span=(0, 25), match='CREATE TABLE `log_config`'>
<re.Match object; span=(0, 32), match='CREATE TABLE `log_config_backup`'>
<re.Match object; span=(0, 44), match='CREATE TABLE `log_disk_timer_clear_tmp_file`'>
<re.Match object; span=(0, 34), match='CREATE TABLE `log_file_work_order`'>
<re.Match object; span=(0, 27), match='CREATE TABLE `log_function`'>
<re.Match object; span=(0, 24), match='CREATE TABLE `log_login`'>
<re.Match object; span=(0, 31), match='CREATE TABLE `log_login_failed`'>
<re.Match object; span=(0, 44), match='CREATE TABLE `log_modify_acct_password_acct`'>
<re.Match object; span=(0, 51), match='CREATE TABLE `log_modify_acct_password_plan_recor>
<re.Match object; span=(0, 46), match='CREATE TABLE `log_modify_camera_password_acct`'>
<re.Match object; span=(0, 53), match='CREATE TABLE `log_modify_camera_password_plan_rec>
<re.Match object; span=(0, 46), match='CREATE TABLE `log_modify_password_plan_record`'>
<re.Match object; span=(0, 31), match='CREATE TABLE `log_oplog_backup`'>
<re.Match object; span=(0, 30), match='CREATE TABLE `log_oplog_clean`'>
<re.Match object; span=(0, 25), match='CREATE TABLE `log_policy`'>
<re.Match object; span=(0, 26), match='CREATE TABLE `log_reports`'>
<re.Match object; span=(0, 32), match='CREATE TABLE `log_reports_block`'>
<re.Match object; span=(0, 34), match='CREATE TABLE `log_session_details`'>
<re.Match object; span=(0, 32), match='CREATE TABLE `log_session_sport`'>
<re.Match object; span=(0, 27), match='CREATE TABLE `log_sessions`'>
<re.Match object; span=(0, 31), match='CREATE TABLE `log_sessions_end`'>
<re.Match object; span=(0, 32), match='CREATE TABLE `log_user_sessions`'>
<re.Match object; span=(0, 29), match='CREATE TABLE `log_work_order`'>
<re.Match object; span=(0, 49), match='CREATE TABLE `rel_file_work_order_apply_approver`>
<re.Match object; span=(0, 39), match='CREATE TABLE `rel_file_work_order_file`'>
<re.Match object; span=(0, 44), match='CREATE TABLE `rel_work_order_apply_approver`'>
对于match对象有两个方法可以处理:span()和group()
print(table_name.span())
>>
(0, 30)
(0, 28)
(0, 42)
(0, 25)
(0, 32)
(0, 44)
(0, 34)
(0, 27)
(0, 24)
(0, 31)
(0, 44)
(0, 51)
(0, 46)
(0, 53)
(0, 46)
(0, 31)
(0, 30)
(0, 25)
(0, 26)
(0, 32)
(0, 34)
(0, 32)
(0, 27)
(0, 31)
(0, 32)
(0, 29)
(0, 49)
(0, 39)
(0, 44)
print(table_name.group())
>>
CREATE TABLE `ha_log_sessions`
CREATE TABLE `log_appgenius`
CREATE TABLE `log_approval_account_record`
CREATE TABLE `log_config`
CREATE TABLE `log_config_backup`
CREATE TABLE `log_disk_timer_clear_tmp_file`
CREATE TABLE `log_file_work_order`
CREATE TABLE `log_function`
CREATE TABLE `log_login`
CREATE TABLE `log_login_failed`
CREATE TABLE `log_modify_acct_password_acct`
CREATE TABLE `log_modify_acct_password_plan_record`
CREATE TABLE `log_modify_camera_password_acct`
CREATE TABLE `log_modify_camera_password_plan_record`
CREATE TABLE `log_modify_password_plan_record`
CREATE TABLE `log_oplog_backup`
CREATE TABLE `log_oplog_clean`
CREATE TABLE `log_policy`
CREATE TABLE `log_reports`
CREATE TABLE `log_reports_block`
CREATE TABLE `log_session_details`
CREATE TABLE `log_session_sport`
CREATE TABLE `log_sessions`
CREATE TABLE `log_sessions_end`
CREATE TABLE `log_user_sessions`
CREATE TABLE `log_work_order`
CREATE TABLE `rel_file_work_order_apply_approver`
CREATE TABLE `rel_file_work_order_file`
CREATE TABLE `rel_work_order_apply_approver`
刚开始得到返回的match对象后不晓得怎么处理 ,于是将其写入文本文档,再读取去除多余字符
for line in newtxt: # 循环处理每行数据
table_name = re.search("CREATE TABLE \S+",line)
if table_name == None :
pq = table_name
else :
fp = open(r'table.txt','a+')
print(table_name.group(0),file=fp) #将得到的结果 写入文档
fp.close()
tables = open("table.txt","r",encoding ="utf8")
table = tables.readlines()# 按行读取 , 此时 table 为 list 类型
#table_log = "".join(table)# list 转 str
tables.close()
for line in table: # 循环处理每行数据
table_need = re.sub(r"CREATE TABLE ","",line)
print(table_need)
突然有了灵感(基于上述的findall,编写日记的时候突然想到),于是乎··· 虽然得到的类型存在差异
for line in newtxt: # 循环处理每行数据
#print(line)
table_name = re.search("CREATE TABLE \S+",line)
if table_name == None :
pq = table_name
else :
table_need = re.sub(r"CREATE TABLE ","",table_name.group())
print(table_need)