“GIS DICTIONARY A-Z” 查詢頁面開發（2）——INSERT INTO資料庫

今日工作：資料庫連接、數據寫入

一、資料庫連接：使用了pymysql庫

from G2_dataClean import defList
import pymysql
db = pymysql.connect(db='gisdictionary',charset='UTF8')
cursor = db.cursor()

二、數據寫入

countId = 1 #ID欄位
for i in range(0,len(defList)):
        alpha = defList[i][1][0:1].lower() #alpha即letter欄位，為單詞首字母。這一步截取出首字母並.lower()小寫。
        if not(alpha.isalpha()):    #判斷首字母是否為a-z字母，如果不是a-z，則分配'#'給它。
            alpha = '#'
        sql = '''INSERT INTO GISAZ(ID,WORDS,MEANING,LETTER)VALUES(%s,'%s',"%s",'%s');'''%(countId,pymysql.escape_string(defList[i][1]),pymysql.escape_string(defList[i][0]),alpha)
        countId += 1
        cursor.execute(sql)
try:
        db.commit()
except Exception:
        print("發生錯誤",Exception) #不會捕獲mysql的warning警告，Try...Except只捕獲Error錯誤。
        db.rollback()    #回滾事務
finally:
        db.close()

代碼部分結束，雖然今天的代碼看起來又少又容易，但sql語句可真是廢了不少力氣。

#核心語句
#sql = '''INSERT INTO GISAZ(ID,WORDS,MEANING,LETTER)VALUES(%s,'%s',"%s",'%s');'''
#%(countId,pymysql.escape_string(defList[0][1]),pymysql.escape_string(defList[0][0]),alpha)
#cursor.execute()
#db.commit()
#db.close()

#0、核心語句調試了很久，最終使用#三層引號、#%s、#pymysql.escape_string()的方式集合完成了sql語句的撰寫；
#pymysql.escape_string()會將數據值string中的 單引號等有意義的符號 通過 多層轉義 的方式取消對語句的影響；
#能成功的兩種相同效果的無%s語句:(不必深究，下次要用時再根據這次的經驗嘗試即可)
#（一）'''INSERT INTO GISAZ(ID,WORDS,MEANING,LETTER)VALUES(1,'3D feature',"A representation of a three-dimensional, 
#real-world object in a map or scene, with elevation values (z-values) stored within the feature\\'s geometry. Besides geometry, 
#3D features may have attributes stored in a feature table. In applications such as CAD, 3D features are often referred to 
#as 3D models.",'3');'''
#（二）'INSERT INTO GISAZ(ID,WORDS,MEANING,LETTER)VALUES(1,\'3D feature\',"A representation of a three-dimensional, 
#real-world object in a map or scene, with elevation values (z-values) stored within the feature\\\'s geometry. Besides geometry, 
#3D features may have attributes stored in a feature table. In applications such as CAD, 3D features are often referred to 
#as 3D models.",\'3\');'
#參考文章：https://blog.csdn.net/qq_36622490/article/details/87455903
#1、下麵這句SQL刪除了相同id的行數據，但是沒能保留住其中id最小的那一條行數據;網上方法嘗試數次未能成功，報sql語法錯誤:delete from cqssc where id in (select id from (select id from cqssc where expect in (select expect from cqssc group by expect having count(expect)>1) and id not in (select min(id) from cqssc group by expect having count(expect)>1)) as tmpresult)
#delete from gisaz where id in (select id from (select id from gisaz where ID in (select ID from gisaz group by ID having count(ID)>1)) as tmpresult)
#參考文章：https://www.cnblogs.com/jdbeyond/p/8157224.html
#2、已在Letter列將所有首字母小寫
#3、初次插入數據我選擇分3次進行，犯下了重置countId的錯誤；要註意countId是ID欄位，不能重置進而引起ID重覆。
#4、暫時在數據表中空置了一列other，打算用其放置圖片，或是為"See xxxxxx"等meaning創造通往其他詞條的超鏈接（資料庫內大概有類似功能）。
#5、（無傷大雅，meaning的資料庫類型設置了為text並且沒有設置長度）在phpadmin中暫時沒有找到完成顯示meaning數據的方法，現在都是兩條如"In ArcGIS, a message from a replica to its relativ..."和"原始長度207"的顯示；
#6、（Warning!）部分Words的長度超過了預設的varchar(20)，造成部分詞語名稱不全。只得修改表結構，重新寫入數據。varchar(40)也不夠，應該去寫一個找出defList[n][1]最大值的程式。
#未來再做資料庫還是得先確定最大長度再建表；通過簡單的程式'max=len(defList[0][1]);for i in range(0,1729):{if(len(defList[i][1])>max):{max=len(defList[i][1])}}'得到最大值為50，故設定varchar(60)。
#7、考慮是否要把後119條數據按照首字母重新洗入前面的數據中。

 最終資料庫成果，總共1729條數據：