mysql - 使用 PyCharm 在远程服务器上运行的 MySQL 语法错误

Question

我对编程相当陌生，我一直在尝试使用 PyCharm 在 Python 中实现这段代码。我正在通过远程服务器运行代码，在本地计算机上使用 PyCharm。它是由一位前同事编写的，自从我们将 MySQL 和 Python 解释器等软件包更新到 3.8 后，它一直存在很多编码问题。MySQL 版本是 8.0，但这是一个更新。那不是编写代码时最初安装的版本。

这是我得到的完整错误：

findBestMatch *** WARNING: FoundCity[i] = {'Country': 'Austria', 'Page': 'Contact', 'Confidence': 10, 'Mentions': 1} WriteToDB 问题 <class 'MySQLdb._exceptions.ProgrammingError '> Traceback（最近一次调用最后）：文件“/remotepath/TextMining/NER/FindLocationStoreSQL.py”，第 399 行，在 WriteToDB(c.title(), cn, idProject, 10, "Contact", "v1", cursor, db, database_country) 文件“/remotepath/TextMining/NER/FindLocationStoreSQL.py”，第 286 行，在 WriteToDB cursor.execute(sql,values) 文件“/home/localhost/.local/lib/python3.8/site -packages/MySQLdb/cursors.py”，第 206 行，在执行 res = self._query(query) 文件“/home/localhost/.local/lib/python3.8/site-packages/MySQLdb/cursors.py”中，第 319 行，在 _query db.query(q) 文件中“/home/localhost/.local/lib/python3.8/site-packages/MySQLdb/connections.py”，第 259 行，在查询 _mysql.connection.query(self, query) MySQLdb._exceptions.ProgrammingError: (1064, “您的 SQL 语法有错误；请查看与您的 MySQL 服务器版本相对应的手册，了解在“挖掘的城市文本，来自数据源的国家/地区”附近使用的正确语法，Confidence='10',FoundWhere=''Contact''在第 1 行") 进程以退出代码 -1 结束',Confidence='10',FoundWhere=''Contact'' at line 1") 进程以退出代码 -1 结束',Confidence='10',FoundWhere=''Contact'' at line 1") 进程以退出代码 -1 结束

它尝试运行的函数的代码示例如下：

def findBestMatch(FoundCity,FoundCountry,database_country):
pair_candidates = []
for i in range(0,5):
    for j in range(0,5):
        if len(FoundCity)>i and len(FoundCountry)>j:
            city_i = FoundCity[i].get('City')
            country_j = FoundCountry[j].get('Country')
            if city_i != None and country_j != None:
                #sql = "SELECT City,Country_CountryName,Longitude,Latitude FROM Semanticon.City where city like '{0}' and Country_CountryName like '{1}' and Population>0 order by Population desc".format(FoundCity[i]['City'].encode('utf-8'),FoundCountry[j]['Country'].encode('utf-8'))
                sql = "SELECT City,Country_CountryName,Longitude,Latitude FROM Semanticon.City where city like '{0}' and Country_CountryName like '{1}' and Population>0 order by Population desc".format(
                    FoundCity[i]['City'], FoundCountry[j]['Country'])
              
                try:
                   cursor.execute(sql)
                except:
                    db = MySQLdb.connect(host, username, password, database, charset='utf8')
                    db.set_character_set("utf8")
                    cursor = db.cursor()
                    cursor.execute(sql)
                resul = cursor.fetchall()
                if len(resul)>0:
                    pair_candidates.append({"City":FoundCity[i]['City'],"Country":FoundCountry[j]['Country'],"Score":(FoundCity[i]["Confidence"]+FoundCountry[j]["Confidence"]+0.5*(FoundCity[i]["Mentions"]+FoundCountry[j]["Mentions"]))})
                    #return FoundCity[i]['City'],FoundCountry[j]['Country'],FoundCity[i]['Confidence']
            else:
                if city_i == None:
                    print("*** WARNING: FoundCity[i] = ", FoundCity[i])
                else:
                    print("*** WARNING: FoundCountry[j] = ", FoundCountry[j])

我不得不去掉编码，因此注释掉了“sql”行。编码导致问题并在要从数据库中读取的字符串中添加了一个额外的“b”。

它抱怨的“WriteToDB”函数如下：

def WriteToDB(City,Country,ProjectId,Confidence,Location,Version,cursor,db,database_country):
sql = None
if database_country!="":
    if database_country == Country:
        if City != "":
            #sql = "UPDATE  ProjectLocation SET City='{0}',DataTrace='{1}',Confidence={2},FoundWhere='{3}' WHERE Projects_idProjects={4} and Country='{5}';".format(City," City text mined, Country from datasource",Confidence,Location,ProjectId,original_database_cntry)
            sql = "UPDATE  ProjectLocation SET City='%s',DataTrace='%s',Confidence='%s',FoundWhere='%s' WHERE Projects_idProjects='%s' and Country='%s';"
            values = (City, " City text mined, Country from datasource", Confidence, Location, ProjectId,
                original_database_cntry)
    if database_country!=Country:
        if Country.encode('utf-8') in database_country:
            #sql = "UPDATE  ProjectLocation SET City='{0}',DataTrace='{1}',Confidence={2},FoundWhere='{3}' WHERE Projects_idProjects={4} and Country='{5}';".format(
               # City, " City text mined, Country from datasource", Confidence, Location, ProjectId,
                #original_database_cntry)

            sql = "UPDATE  ProjectLocation SET City='%s',DataTrace='%s',Confidence='%s',FoundWhere='%s' WHERE Projects_idProjects='%s' and Country='%s';"
            values = (City, " City text mined, Country from datasource", Confidence, Location, ProjectId,
                original_database_cntry)
        else:
            print("Country conflict in project:"+str(ProjectId))
else:
    #sql = "Insert into ProjectLocation (Type,City,Country,Projects_idProjects,Original_idProjects,IsLocationFromDataset,Confidence,FoundWhere,Version,DataTrace)" \
     # "Values ('{0}','{1}','{2}',{3},{4},0,{5},'{6}','{7}','{8}')".format("Main",City,Country,ProjectId,ProjectId,Confidence,Location,Version,"Both minded from text v0.1")

    sql = "Insert into ProjectLocation (Type,City,Country,Projects_idProjects,Original_idProjects,IsLocationFromDataset,Confidence,FoundWhere,Version,DataTrace)" \
          "Values ('%s','%s','%s','%s',0,'%s','%s','%s','%s','%s')"
    values = ("Main", City, Country, ProjectId,ProjectId, Confidence, Location, Version, "Both minded from text v0.1")
if sql!=None:
    cursor.execute(sql,values)
    db.commit()

我注释掉了如图所示的 SQL 查询并尝试绑定它们，因为它给出了很多编码错误。我不确定如何摆脱这个错误并且不会再次出现编码错误。

有人可以帮忙吗？

更新问题。

我恢复了所有的 sql 查询，并使用了现在编码的查询（以前被注释掉了），我在输出中得到了 'b'。

关于如何正确编码这些 SQL 查询以使二进制编码不会在输出中显示为 b 的任何建议？

以下是输出示例：

ProjectID,ProjectName,FoundCity,FoundCountry,DatabaseCity,DatabaseCountry,Confidence,FoundWhere,Website 2542, Migrantour Country,,b'',b'',b'',10,Contact,link 3938,GeoSmartCity,,,b'', b'',10,联系方式,链接

标签： mysqlpython-3.xencodingsyntaxpycharm