manipulating MySQL with Python

This example uses Python to manipulate MySQL, read the data from the specified file, process the data, and then bulk insert MySQL after processing.

Paste the code:

#-*-CODING:GBK-*-ImportReImportMySQLdbImport TimedefSelect (sqlselect):Try: Conn= MySQLdb.connect ("localhost","Test","123456","TestDB") Cursor=conn.cursor ()#executing SQL statements using the Execute methodCursor.execute (sqlselect)#Use the Fetchone () method to get a single piece of data. data =Cursor.fetchone ()#Read all Rowsdata =Cursor.fetchall ()returnDataexceptexception,e:Printe#print "Data from MySQL:%s%s%s%s"% (Data[0],data[1],data[2],data[3])    finally:        #To close a database connectionconn.close ()defLoadData (datafile,rowlimts=0):#datafile=r ' E:\cabspottingdata\new_abboip.txt 'Cabid=datafile[datafile.index ('_') +1:datafile.index ('.')] MyFile=open (DataFile,'R', 2048)#2048 for buffer sizeNewline=myfile.readline () records=[] Splitter=re.compile ('\\s')#use blank characters as separatorsRows=0ifRowlimts>0: whileNewLine androws<rowlimts:content=Splitter.split (newline) record=[] record.append (float (content[0))) Record.append (float (content[1])) record.append (int (content[2]))#int型也可插入到数据库的bit型字段DT=time.gmtime (int (content[3])#将unix时间, such as 1213084687, convert to Time type dtstr=time.strftime ('%y-%m-%d%h:%m:%s', DT) #将time对象, formatted as "2008-06-10 07:58:07" type string record.append (dtstr) record.append (cabid ) Records.append (record) rows+=1NewLine=Myfile.readline ()Else:         whilenewline:content=Splitter.split (newline) record=[] record.append (float (content[0))) Record.append (float (content[1])) record.append (int (content[2])) DT=time.gmtime (int (content[3])) Dtstr=time.strftime ('%y-%m-%d%h:%m:%s', DT) record.append (DTSTR) record.append (cabid) records.append (record) NewLine=myfile.readline () myfile.close ( )returnRecordsdefInsertitems (Records):ifLen (Records) >0:#Note that regardless of the type of a field in the MySQL table structure, the Python SQL statement uses%s to represent the formatSql="INSERT INTO Geoinfo (latitude,longitude,occupancy,time,cabid) VALUES (%s,%s,%s,%s,%s)"Values=[]         forRecordinchrecords:values.append ((record[0],record[1],record[2],record[3],record[4]))        Try: Conn= MySQLdb.connect ("localhost","Test","123456","TestDB") _cursor=conn.cursor ()#BULK Insert_cursor.executemany (sql,values) conn.commit ()#INSERT and UPDATE operations must call the Commit method for the operation to take effect            return0exceptexception,e:Raiseereturn-1finally: Conn.close ()defInsertItem (record):ifrecord!=None:#Note that regardless of the type of a field in the MySQL table structure, the Python SQL statement uses%s to represent the formatSql="INSERT INTO Geoinfo (latitude,longitude,occupancy,time,cabid) VALUES (%s,%s,%s,%s,%s)"SQL=sql% (record[0],record[1],record[2],record[3],record[4])            Try: Conn= MySQLdb.connect ("localhost","Test","123456","TestDB") _cursor=conn.cursor () _cursor.execute (SQL) Conn.commit ()return0exceptexception,e:Raiseereturn-1finally: Conn.close ()if __name__=='__main__': DataFile=r'E:\cabspottingdata\new_abboip.txt'Records=LoadData (datafile)Print 'inserting%d records'%Len (Records) Insertitems (records) data=select ("Select Objectid from Geoinfo limit 0,10")#Query the first 10 articles    PrintDataPrint 'inserting completed.'

Precautions:

1) When BULK INSERT, Cursor.execute () is executed in the For loop, which is far less efficient than the Cursor.executemany () method.

2) Regardless of the MySQL table structure, the type of a field, such as the float,int,bit,datetime type, the Python SQL statement will use%s to represent the format, otherwise, for non-string type fields, the insertion may be error-based.

For example, when I insert a geoinfo table (structure as follows, Objectid field is self-growing), when the INSERT statement is written sql="insert INTO Geoinfo (Latitude,longitude,occupancy,time, Cabid) VALUES (%f,%f,%d,%s,%s)" will error:

"Float argument required, not str"

Later changed to sql="insert INTO Geoinfo (latitude,longitude,occupancy,time,cabid) VALUES (%s,%s,%s,%s,%s)" is right.

Also, for decimal types such as 37.75134 and 122.39488, if the field is designed as float, MySQL automatically truncates it and rounds it when it is inserted.

I thought the length is not enough, so the length is 50, when inserted is automatically truncated to an integer, and then changed to double type.

manipulating MySQL with Python