"Python" python read file operation MySQL

Cumbersome, when the former is doing the retrieval test, it always requires the business side to operate DB and some other services, which makes the retrieval test highly dependent on the environment and data, and greatly improves the testing cost.

The mock service and MySQL can solve this problem very well, so smoked many did two things:

1 using the company's service framework mock services;

2 Use the Python language to mock MySQL data.

Part 1 only needs to know the company framework to write, this article mainly records the Python operation MySQL section.

An installation environment

Install Python as needed for the MySQLdb module (yum install mysql-python.x86_64), as below, to install successfully.

[[Email protected]118- the~]# Pythonpython2.6.6(r266:84292, the Feb A  -,xx:xx: -) [GCC4.4.7 20120313(Red Hat4.4.7-3)] on Linux2type" Help","Copyright","credits"Or"License"  forMore information.>>>Import MySQLdb>>>

Two implementation of Python operational database

1 The first time I realized is to read the file operations database (commented out for the self-increment key processing) as follows:

#-*-coding:utf-8-*-Import MySQLdb fromitertools Import IsliceTry: Conn= MySQLdb.connect (host='localhost', user='Root', passwd='123456', port=3306, charset='UTF8') #连接mysqldb cur=conn.cursor () conn.select_db ('Test') # Cur.execute ('select Max (ID) from student') #获取mysql中该表的自增键最大值 and add backwards. # Maxid= Cur.fetchall () [0]# Print'Maxid%id'%Maxid# Start= count+1F= File ('Data2.txt') List=[] Line_num=1     forLineinchIslice (F,1, None): #读取txt文件, skip header row STRs= Line.split (",") #文件各字段逗号分隔 Line_num= line_num+1print len (STRs)ifLen (strs)! = -: #缺少字段时, skip the line print'%d line missing field, please check the file'%Line_numContinueData= (strs[0],strs[1],strs[2],strs[3],strs[4],strs[5],strs[6],strs[7],strs[8],strs[9],strs[Ten],strs[ One],strs[ A],strs[ -],strs[ -],strs[ the],strs[ -],strs[ -],strs[ -],strs[ +],strs[ -],strs[ +],strs[ A],strs[ at],strs[ -].replace ("\ n","")) #对strs [24] space to process print data# start= start+1list.append (data) #将data记录到list中, perform an insert operation on the list F.close Cur.executemany ('INSERT into creative_info_test values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,% s,%s)', List) conn.commit () #提交 cur.close () conn.close () print'OK'except Mysqldb.error,e:print"MySQL Error%d:%s"% (e.args[0],e.args[1])

2 The leader said that each record field is too many (multi-table multi-field), let me pin to the script, and then changed separately, so the first version was modified, using the dictionary dict:

#-*-coding:utf-8-*-Import Mysqldbconn= MySQLdb.connect (host='localhost', user='Root', passwd='123456', port=3306, charset='UTF8') #连接mysqldbcur=conn.cursor () conn.select_db ('Test')
#逻辑: One operation is used only once for a test, so the first step is to clear the record, the second step is to insert the data according to the number of bars, and the third to personalize the Insert data (update of some fields).
Try:

        Delete () #数据清除        Producer () #将默认数据插入mysql, note the self-increment primary key        Update () #个性化数据更新        conn.commit () #提交        cur.close ()        conn.close () except mysqldb.error,e:        print ' MySQL Error%d,%s '% (e.args[0],e.args[1])

#具体实现: Inserting data, because of multiple table inserts, the tangent table has the same field as the table, so the uniform setting is based on the number of bars. Def Producer (): Print'Input Numbers:'Num=input () #请多写一条 forIinchRange1, num): table1['user_id']=I table2['user_id']=I...#insert table1 User_info= [user['user_id']...] Cur.execute ('INSERT into user_test values (%s,...)', table1) #insert table2 ... The following similar processingdef Update (): Print'input your SQL statement file name:'filename=raw_input () F=file (filename) forLineinchf.readlines ():operatoryour SQL update #写update语句即可def Delete (): Cur.execute ('Delete from table1') Cur.execute ('Delete from table2')
...#每个表对应一个dict, whose fields are dictionary elements, set default values for each field table1={'user_id':'5185173207809','user_name':
...,}table2={...}

Read three attached Excel files, well, by the way, Python is awesome:

Import CSV   = Open ('creative_info_test.csv','rb'=  Csv.reader (f) for in reader:        print rowf.close

"Python" python read file operation MySQL