python db读写实践

1 读db

import sqlite3

def ReadDB(path: str):
	conn = sqlite3.connect(path)
	cursor = conn.cursor()
	sqlstr = 'select id, name, age, class from Student;' #填入SQL的查找命令
	cursor.execute(sqlstr)
	
	#请选择其中一种读取方式
	'''
	#读取方法1：一次读取一条记录
	for item in cursor:
		print(item) #item返回一条记录
	'''
	
	'''
	#读取方法2：一次读取所有记录
	rows = cursor.fetchall() #rows返回所有记录，rows是一个二维列表
	'''
	
	conn.commit()
	cursor.close()
	conn.close()
	
	return

path = ''  #填入db文件的路径
ReadDB(path)
print('程序成功执行！')

2 写db

def WriteSqlite(outputInfo: list) -> None:
    if os.path.exists("output.db"):
        os.remove("output.db")	
    
    conn = sqlite3.connect("output.db")
    cursor = conn.cursor()

    #创建一个名为AbsoluteIndex的表，其中包括(id, linkid, ptIndex, LonOffset, LatOffset, LengthOffset)字段
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS AbsoluteIndex (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        linkid INTEGER,
        ptIndex INTEGER,
        LonOffset REAL,
        LatOffset REAL,
        LengthOffset REAL
    )
    """)

    #往AbsoluteIndex表中批量插入数据
    sqlstr = """
    INSERT INTO AbsoluteIndex (id, linkid, ptIndex, LonOffset, LatOffset, LengthOffset)
    VALUES (?, ?, ?, ?, ?, ?)
    """
    cursor.executemany(sqlstr, outputInfo) #outputInfo为列表类型，元素为元组类型，分别对应6个字段值

    conn.commit() #提交连接
    cursor.close() #关闭游标
    conn.close() #关闭连接
    return

3 读取GeomWGS84字段

def geostr2list(GeomWGS84Org: str) -> list:
    geo = []

    i1 = GeomWGS84Org.find('(')
    i2 = GeomWGS84Org.find(')')
    s = GeomWGS84Org[i1+1:i2]  # s = "116.011608 39.659718 25.189173 4326, 116.011654 39.659782 25.07019 4326, 116.0117 39.659846 24.957347 4326, 116.011747 39.65991 24.834431 4326, 116.011792 39.659974 24.758283 4326, 116.011838 39.660037 24.671579 4326, 116.011883 39.6601 24.608741 4326, 116.011929 39.660163 24.539336 4326, 116.011973 39.660224 24.467607 4326, 116.012017 39.660286 24.421583 4326, 116.012061 39.660347 24.365085 4326"
    s_list = s.split(',')
    for i, x in enumerate(s_list):
        # x = "116.011608 39.659718 25.189173 4326"
        x_list = x.split()  # x_list = [116.011608, 39.659718, 25.189173, 4326]
        long = float(x_list[0])
        lati = float(x_list[1])
        geo.append((long, lati))
    
    return geo


def Sqlite2List(dbPath: str, linkids: list) -> map:

    conn = sqlite3.connect(dbPath)
    conn.enable_load_extension(True)
    conn.load_extension('mod_spatialite')
    cursor = conn.cursor()

    map_linkid_Link = collections.defaultdict(Link) #返回值,请注意在大数据面前此字典类型是否会超出其最大容量
    #key为整型表示的linkid,val为Link类型表示的道路

    #遍历每一条chaid，获得Link类的一个实例化对象
    for chaid in linkids:
        #sqlstr = f"SELECT id, AsText(GeomWGS84) FROM BD_CHA where id = {str(chaid)};"
        #sqlstr = f"SELECT id, ST_AsText(GeomWGS84) FROM BD_CHA where id = {str(chaid)};" #注意只保留到小数点后六位
        sqlstr = f"SELECT id, AsEWKT(GeomWGS84) FROM BD_CHA where id = {str(chaid)};" #保留所有小数点
        cursor.execute(sqlstr)
        
        seqnrs = []
        longis = []
        latis = []

        for row in cursor: #cursor中只有1条记录
            chaid = int(row[0])
            GeomWGS84 = str(row[1])
            geos = geostr2list(GeomWGS84) #geos为列表类型，其元素为元组类型，分别为浮点数的经度和纬度

            for i in range(len(geos)):
                seqnrs.append(i)
                longis.append(geos[i][0])
                latis.append(geos[i][1])
        
        #chaid为整型
        linktmp = Link(chaid, seqnrs, longis, latis) #获得Link类的一个实例化对象linktmp

        map_linkid_Link[chaid] = linktmp #将实例化对象linktmp存入字典中

    conn.commit() #提交连接
    cursor.close() #关闭游标
    conn.close() #关闭连接
    return map_linkid_Link