借助Python将txt文本内容导入到数据库

安装数据库并创建admin账号

#Create mariadb user
CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON hosts_info.* TO 'admin'@'%';
FLUSH PRIVILEGES;

创建库并创建数据表

#创建库
CREATE DATABASE hosts_info;
#创建表
CREATE TABLE host_tmp (
	ip VARCHAR(50) NOT NULL,
    username VARCHAR(15) NOT NULL,
    password VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

借助Python工具将txt文本内容导入到数据库

# coding=utf-8
#导入所需模块
import paramiko
import mysql.connector
#连接数据库
db_connection = mysql.connector.connect(
  user="admin",
  password="password",
  host="192.168.168.168",
  database="hosts_info"
)
#
# 打开文件phosts.txt
with open('phosts.txt', 'r') as f:
# 遍历文件中的每一行数据
 for line in f:
# 分割每行数据
  data = line.strip().split(' ')
  print(f"{data[0]}")
#
  cursor = db_connection.cursor()
  sql = "INSERT INTO host_tmp (ip,username,password) VALUES (%s, %s, %s)"
  val=(data[0],data[1],data[2])
  cursor.execute(sql, val)
  cursor = db_connection.cursor()
# 提交事务
db_connection.commit()
# 关闭游标和连接
cursor.close()
db_connection.close()