# -*- coding: gbk -*-

import re

import MySQLdb

import time

def select(sqlselect):

    try:

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

        cursor=conn.cursor()

        # 使用execute方法执行SQL语句

        cursor.execute(sqlselect)

        # 使用 fetchone() 方法获取一条数据。

        data = cursor.fetchone()

        #读取所有行

        data = cursor.fetchall()

        return data

    except Exception,e:

        print e

    #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=0

    if rowlimts>0:

        while newline and rows<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时间，如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+=1

            newline=myFile.readline()

    else:

        while newline:

            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()

    return records

def insertItems(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 in records:

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

        try:

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

            _cursor=conn.cursor()

            #批量插入

            _cursor.executemany(sql,values)

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

            return 0

        except Exception,e:

            raise e

            return -1

        finally:

            conn.close()

def insertItem(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","","testdb" )

                _cursor=conn.cursor()

                _cursor.execute(sql)

                conn.commit()

                return 0

            except Exception,e:

                raise e

                return -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条

    print data

    print 'inserting completed.'