Method tutorial for importing txt into mysql in Linux

Preface

Yesterday, when writing a small project, I encountered a requirement: import the data of txt documents into the mysql database. At first, I wanted to import TXT files directly with Mysql Workbench, but finally I found that I did not support TXT import. As a result, I went to transfer TXT to Excel, and found all kinds of garbled code problems when I imported it to Linux.

With the principle that there is nothing a programmer can't do, I handwritten a Python code and directly manipulated the file to import it. As a result, there are about 10,000 files, and the import time is about two minutes.

Here is the specific code:

Mysqlpython.py file: custom class importtxt.py file for connecting to mysql database: read TXT file and insert operation dict.txt file: TXT file to be operated

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 object 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 cursor object and database connection object def close (self): self.cur.close () self.db.close () # execute sql 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 resultif _ _ name__ = "_ _ main__": sqlh = Mysqlpython ("dictionary") sel = "select * from user" r = sqlh.all (sel) print (r)

Importtxt.py file

Import reimport sysfrom mysqlpython import Mysqlpythonsqlh = Mysqlpython ("dictionary") def insert (data): arr = data.split () name = arr [0] description = "" .join (arr [1:]) ins = "insert into words (name,description) values (% 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)

An indef art oneabacus n.frame with beads that slide along parallel rods, used for teaching numbers to children, and (in some countries) for countingabandon v. Go away from (a person or thing or place) not intending to return; forsake; desertabandonment n. Abandoningabase v. ~ oneself/sb lower oneself/sb in dignity; degrade oneself/sb; abash to destroy the self-possession or self-confidence of:disconcertabashed adj. ~ embarrassed; ashamedabate v. Make or become lessabattoir n. = slaughterhouse (slaughter)

You can modify the regular expression for different delimiters. All the code has been posted, and the configuration of the database can be copied and modified directly.

Summary:

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.