python mysql bit_用Python操纵MySQL

#-*- coding: gbk -*-

importreimportMySQLdbimporttimedefselect(sqlselect):try:

conn= MySQLdb.connect("localhost","test","123456","testdb")

cursor=conn.cursor()#使用execute方法执行SQL语句

cursor.execute(sqlselect)#使用 fetchone() 方法获取一条数据。

data =cursor.fetchone()#读取所有行

data =cursor.fetchall()returndataexceptException,e:printe#print "Data from mysql: %s %s %s %s" %(data[0],data[1],data[2],data[3])

finally:#关闭数据库连接

conn.close()def loadData(dataFile,rowlimts=0):#dataFile=r'E:\cabspottingdata\new_abboip.txt'

cabid=dataFile[dataFile.index('_')+1:dataFile.index('.')]

myFile=open(dataFile,'r',2048)#2048为缓冲大小

newline=myFile.readline()

records=[]

splitter=re.compile('\\s')#以空白字符作为分隔符

rows=0if rowlimts>0:while newline and rows

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时间，如1213084687，转换成time类型

dtStr=time.strftime('%Y-%m-%d %H:%M:%S',dt)#将time对象，格式化为"2008-06-10 07:58:07"型字符串

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):if len(records)>0:#注意，不论MySQL的表结构中，某个字段为何种类型，Python的SQL语句中都要用%s来表示格式

sql="insert into geoinfo(latitude,longitude,occupancy,time,cabid)values(%s,%s,%s,%s,%s)"values=[]for record inrecords:

values.append((record[0],record[1],record[2],record[3],record[4]))try:

conn= MySQLdb.connect("localhost","test","123456","testdb")

_cursor=conn.cursor()#批量插入

_cursor.executemany(sql,values)

conn.commit()#Insert和update操作必须调用commit方法，操作才能生效

return0exceptException,e:raiseereturn -1

finally:

conn.close()definsertItem(record):if record!=None:#注意，不论MySQL的表结构中，某个字段为何种类型，Python的SQL语句中都要用%s来表示格式

sql="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 -1

finally:

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")#查询前10条

printdataprint 'inserting completed.'