Linux imports txt into mysql's method tutorial

preface

Yesterday when I was writing a small project, I met one requirement: I imported the data of txt document into the mysql database. At the beginning, I intended to import TXT file directly with Mysql Workbench, but finally I found that TXT import was not supported. As a result, I went to transfer TXT to Excel, and when I got it to Linux, I found a variety of random code problems.

Holding the principle that nothing can't be done by programmers, I handwrote 1 Python code to directly operate the file for import. The result is about 10,000 files, which take about two minutes to import.

Here's the code:

mysqlpython.py file: Custom class that connects to the mysql database importtxt. py file: Read the TXT file and insert it dict. txt file: the TXT file to operate on

mysqlpython py file

from pymysql import *

class Mysqlpython:
 def __init__(self,database,host="localhost",
     user="root",password="123456",
     charset="utf8",port=3306):
  self.database = database
  self.host = host
  self.user = user
  self.password = password
  self.charset = charset
  self.port = port
  

 #  Create data connection and cursor objects 
 def open(self):
  self.db = connect(host=self.host,
     user=self.user,
     password=self.password,
     port=self.port,
     database=self.database,
     charset=self.charset)
  self.cur = self.db.cursor()

 #  Close the cursor object and the database connection object 
 def close(self):
  self.cur.close()
  self.db.close()

 #  perform sql The command 
 def zhixing(self,sql,L=[]):
  self.open()

  self.cur.execute(sql,L)
  self.db.commit()

  self.close()

 #  Query function 
 def all(self,sql,L=[]):
  self.open()
  self.cur.execute(sql,L)
  result = self.cur.fetchall()
  return result


if __name__ == "__main__":
 sqlh = Mysqlpython("dictionary") 
 sel = "select * from user"
 r = sqlh.all(sel)
 print(r)

importtxt py file

import re
import sys
from mysqlpython import Mysqlpython
sqlh = Mysqlpython("dictionary")

def insert(data):
 arr = data.split()
 name = arr[0]
 description = " ".join(arr[1:])
 ins = "insert into words(name,description) values(%s,%s)"
 sqlh.zhixing(ins,[name,description])

def get_addr():
 f = open('./dict.txt')
 lines=f.readlines()
 for line in lines:
  insert(line)
 f.close()
 return ''


if __name__ =='__main__':
 print(get_addr())

dict.py file (I copied several files)

a    indef art one
abacus   n.frame with beads that slide along parallel rods, used for teaching numbers to children, and (in some countries) for counting
abandon   v. go away from (a person or thing or place) not intending to return; forsake; desert
abandonment  n. abandoning
abase   v. ~ oneself/sb lower oneself/sb in dignity; degrade oneself/sb ;
abash   to destroy the self-possession or self-confidence of:disconcert
abashed   adj. ~ embarrassed; ashamed
abate   v. make or become less
abattoir   n. = slaughterhouse (slaughter)

Modify 1 regular expression for different delimiters. All the code is pasted, directly copy and modify the database configuration is ready to run.

Conclusion: