Shulou(Shulou.com)06/03 Report--

I. background

There are four tables, and each table inserts multiple pieces of test data. If there is the same requirement, a script is written to add data.

II. Code

#-coding:utf8--import pymysqlclass InsertTestData (object): STUDENT_FILE = 'Student.txt' COURSE_FILE =' Course.txt' TEACHER_FILE = 'Teacher.txt' SCORE_FILE =' Score.txt' def _ init__ (self): self.connect = pymysql.Connect (host = 'localhost', port = 3306, user =' root', # passwd ='' Charset = 'utf8') self.database =' execersise_test' def read_lines (self Filename): dict = {} file_name = filename.split ('.') [0] dict ['file_name'] = file_name with open (filename) as f: lines = f.readlines () dict [' file_content'] = lines return dict def connect_mysql (self): cursor = self.connect.cursor () return cursor def close _ mysql (self): self.connect.close () def close_curser (self): self.connect_mysql () .close () def add_test_datas (self File_obj): file = file_obj file_name = file ['file_name'] title = file [' file_content'] [0] .strip () datas = file ['file_content'] [1:] content_len = len (datas) data =' 'counter = 1 for tmpdata in datas: if counter = = content_len: Data + ='('+ tmpdata.strip () +') 'else: data + =' ('+ tmpdata.strip () +') 'counter + = 1 sql =' insert into'+ self.database +'.'+ file_name +'('+ title +') values'+ data try: # self.connect_mysql (). Executemany (sql) self.connect_mysql (). Execute (sql) except Exception ase: print ('add_' + file_name +' error:' E) self.connect.commit () self.close_curser () if _ _ name__ ='_ _ main__': testdata = InsertTestData () testdata.add_test_datas (testdata.read_lines (InsertTestData.STUDENT_FILE)) testdata.add_test_datas (testdata.read_lines (InsertTestData.TEACHER_FILE)) testdata.add_test_datas (testdata.read_lines (InsertTestData.COURSE_FILE)) testdata.add _ test_datas (testdata.read_lines (InsertTestData.SCORE_FILE))

In the main function, you only need to specify the file to read to quickly insert the test data.

To facilitate the construction of sql statements, the defined data file format is as follows:

`SID`, `CID`, `Degree` '103',' 3-245', '86mm' 105','3-245', '75mm' 109','3-245', '68' 103',' 3-105', '92mm' 105','3-105','88''109',' 3-105', '76' 101',' 3-105', '64'' '3-105','91-108','3-105','78-101','6-166', '85'-107',' 6-166','79''108',' 6-166', '81'

III. Problems encountered

UnicodeDecodeError: 'gbk' codec can't decode byte 0xbf in position 2: illegal multibyte sequence

Reason: file encoding is not ANSI

Solution: open the file with notepad, save it, and set the encoding to ANSI format.

The Chinese information entered into the database is garbled and displayed as'?'

Reason: charset was not set to utf8 when creating the database

Solution: rebuild the database, rebuild the table, set the charset of the database and the table to utf8

How to construct the values data of sql into the format of'(), (), ();'

Solution: increase the counter, ending with'; 'when reading to the last line

How to read a file and get the table name, column name and values to be operated at the same time

Solution:

The file name of the data file, defined as the table name

The titile of the data file, that is, the contents of the first row, defines the columns of the table

The contents of the data file, that is, the contents after the first row, define the data as a table.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.