It is a problem to create a window program by linking with mysql using Anaconda's spider. It works normally in mysql, but if you run it in the spider, you will get a grammar error. (Long sentence caution)

Asked 1 weeks ago, Updated 1 weeks ago, 1 views

import sys
import pymysql
from PyQt5.QtWidgets import *

def connectDB():
    host = "localhost"
    user = "root"
    pw = "****"
    db = "world"

    conn = pymysql.connect( host= host, user = user, password = pw, db = db)
    return(conn) 

def disconnectDB(conn):
    conn.close()


class MyApp(QWidget):
    def __init__(self):
        super().__init__()
        self.initUI()

    def initUI(self):
        label1 = QLabel('Country name')
        label2 = QLabel('Continent')
        label3 = QLabel('Population')
        label4 = QLabel('GNP')
        label5 = QLabel('Capital city')
        label6 = QLabel('Language')

        self.text_Country_name = QTextEdit()
        self.text_Country_name.setFixedWidth(150)
        self.text_Country_name.setFixedHeight(28)
        btn_1 = QPushButton('Query')
        btn_1.clicked.connect(self.btn_1_clicked)
        self.text_Continent = QTextEdit()
        self.text_Continent.setFixedWidth(150)
        self.text_Continent.setFixedHeight(28)
        self.text_Population = QTextEdit()
        self.text_Population.setFixedWidth(150)
        self.text_Population.setFixedHeight(28)
        self.text_GNP = QTextEdit()
        self.text_GNP.setFixedWidth(150)
        self.text_GNP.setFixedHeight(28)
        self.text_Capital_city = QTextEdit()
        self.text_Capital_city.setFixedWidth(150)
        self.text_Capital_city.setFixedHeight(28)
        self.text_Language = QTextEdit()
        self.text_Language.setFixedWidth(150)
        self.text_Language.setFixedHeight(28)

        gbox = QGridLayout()
        gbox.addWidget(label1, 0, 0)
        gbox.addWidget(self.text_Country_name, 0, 1)
        gbox.addWidget(btn_1, 0, 2)
        gbox.addWidget(label2, 1, 0)
        gbox.addWidget(self.text_Continent, 1, 1)
        gbox.addWidget(label3, 2, 0)
        gbox.addWidget(self.text_Population, 2, 1)
        gbox.addWidget(label4, 3, 0)
        gbox.addWidget(self.text_GNP, 3, 1)
        gbox.addWidget(label5, 4, 0)
        gbox.addWidget(self.text_Capital_city, 4, 1)
        gbox.addWidget(label6, 5, 0)
        gbox.addWidget(self.text_Language, 5, 1)
        self.setLayout(gbox)
        self.setWindowTitle('Country Info')
        self.setGeometry(730,400, 480,250)
        self.show()

    def btn_1_clicked(self):
        Country_name = self.text_Country_name.toPlainText()

        sql = "select * from (select t.continent, t.population, t.gnp, c.Name as capital_city, l.language, l.Percentage, t.name \    from city c, country t, countrylanguage l \    where c.id = t.Capital and c.CountryCode = t.code and t.code = l.CountryCode \    order by l.Percentage desc limit 100000000) o \    group by capital_city \    having name = " + Country_name

        conn = connectDB()
        curs = conn.cursor()
        curs.execute(sql)

        result = curs.fetchone()

        self.text_Continent.setText(result[0])
        self.text_Population.setText(result[1])
        self.text_GNP.setText(result[2])
        self.text_Capital_city.setText(result[3])
        self.text_Language.setText(result[4])

        curs.close()
        disconnectDB(conn)


if (__name__ == '__main__'):
    app = QApplication(sys.argv)
    ex = MyApp()
    sys.exit(app.exec_())

It is a problem to create a window program by linking with mysql using Anaconda's spider.

As shown in the image above, if you enter the country name in the world DB among the basic DBs of mysql, the information of that country is printed.

These are the tables used. Here

In this way, we joined city, country, and country language, and we sorted and grouped them to pick the most frequently used language because only one value was required to show in the Windows program. As you can see, there is no problem with mysql. You can find the name of the country by using having there. But if you run it on Spider, It says that you can't find any grammar errors. I really don't know anything, but I have to solve it, so if you know a better way or an error-solving method, I would appreciate it if you could help me.ㅠ<

python anaconda mysql

2022-09-20 10:24

1 Answers

The only way to be disappointed right now is:

# This is a string, so you have to use quotation marks.
sql = "select ... having name = '" + Country_name + "'"

To get it right: Country_name is passed as an additional factor for the execute() method, and Use binding


2022-09-20 10:24

If you have any answers or tips


© 2022 pinfo. All rights reserved.