How to import data from SQL Database?

Question

I connect local database to app then I want to bring these data into app again to be edited. That is my code

import sys
import os
import time

from PyQt5 import QtCore, QtGui, QtWidgets, uic
import mysql.connector
from mysql.connector import errorcode

FORM_CLASS, _ = uic.loadUiType(os.path.join(os.path.dirname(__file__),"mahmoudtarek.ui"))

class Main(QtWidgets.QMainWindow, FORM_CLASS):
    def __init__(self,parent=None):
        super(Main,self).__init__(parent)
        self.setupUi(self)
        self.InitUI()
        self.conn = None

        self.handle_buttons()
        self.handle_db_connections()

    def InitUI(self):
        ## changes in the run time
        pass

    def handle_buttons(self):
          ## all buttons in the app
        self.pushButton.clicked.connect(self.add_mahmoud_friends)
        self.pushButton_3.clicked.connect(self.update_mahmoud_friends)
        self.pushButton_2.clicked.connect(self.delete_mahmoud_friends)
        self.pushButton_6.clicked.connect(self.search_mahmoud_friends)


    def handle_db_connections(self):
        try:
            self.conn = mysql.connector.connect(
                host='127.0.0.1',
                database='mydb',
                user='root',
                password='134668691011',
                use_pure=True)  # use_pure is set to true

            if self.conn.is_connected():
                db_Info = self.conn.get_server_info()
                print("Connected to MySQL database using C extension... MySQL Server version on ", db_Info)
        except mysql.connector.Error as err:
            print("Error while connecting to MySQL using C extension", err)

    def add_mahmoud_friends(self):
        mth_friends = self.lineEdit.text()
        mth_search = self.lineEdit_4.text()
        if self.conn:
            c = self.conn.cursor()
            try:
                c.execute('''INSERT INTO ahmed (mth_friends,mth_search) values (%s,%s)''', (mth_friends,mth_search))
                self.conn.commit()
                self.lineEdit.setText('')
                self.lineEdit_4.setText('')
                self.statusBar.showMessage('ok mahmoud')


            except mysql.connector.Error as err:
                print("Error: ", err)

    def update_mahmoud_friends(self):
        pass

    def delete_mahmoud_friends(self):
        pass
    def search_mahmoud_friends(self):

        sql = '''SELECT * FROM ahmed WHERE mth_search = %s'''
        mth_search = self.lineEdit_3.text()
        self.conn.cursor.execute(sql , [(mth_search)])
        data = self.conn.fetchall()
        for row in data :
            print(row)

    def closeEvent(self, event):
        if self.conn:
            self.conn.close()
        super(Main, self).closeEvent(event)


def main():
    app= QtWidgets.QApplication(sys.argv)
    window =Main()
    window.show()
    app.exec_()

if __name__ == '__main__':
    main()

Error appears here when I import data by printing row in my SQL Database.

Error

Connected to MySQL database using C extension... MySQL Server version on 8.0.12 Traceback (most recent call last): File "/Users/mahmoudtarek/Desktop/mth1/index.py", line 73, in search_mahmoud_friends self.conn.cursor.execute(sql , [(mth_search)]) AttributeError: 'function' object has no attribute 'execute'

so, can it be solved or there is another method?

Possible duplicate of [How do I connect to a MySQL Database in Python?](https://stackoverflow.com/questions/372885/how-do-i-connect-to-a-mysql-database-in-python) — GuyML, Sep 17 '18 at 06:41
iam still beginner in that field , I want someone edit my code above so, I can learn that also — mthafize19, Sep 17 '18 at 06:47
You did it correctly in `add_mahmoud_friends`, why don't you look up the right way from there? — shmee, Sep 17 '18 at 07:05

score 2 · Answer 1 · answered Sep 17 '18 at 07:03

2

Try to change line 73 to

self.conn.cursor().execute(sql , [(mth_search)])

answered Sep 17 '18 at 07:03

GuyML

257
2
10

Further to the explanation, `cursor` is a function that needs to be called to return an object, its not an cursor object itself, hence needing to call it. – danblack Sep 17 '18 at 07:17
You need to `fetchall()` from the cursor and not from the connection. – GuyML Sep 17 '18 at 07:17
I added answer below of my current code but still problem with fetchall() method – mthafize19 Sep 17 '18 at 07:38

score 1 · Answer 2 · answered Sep 17 '18 at 07:30

1

May be you can try something like below

import pymssql
cnxn = pymssql.connect(server='Server_name',user='UID', password='Password', database='Db Name')
cursor = cnxn.cursor()    
cmd = (r"<Your Query>")
cursor.execute(cmd)
query_results = cursor.fetchall()
cursor.close()

Result will be available in query_results

answered Sep 17 '18 at 07:30

Abhishek Bajaj

100
7

I will try but can you modify code in my below answer as I do not want to use 2 modules of sql in my code. I am using (mysql connector ) – mthafize19 Sep 17 '18 at 07:34

score 1 · Answer 3 · answered Sep 17 '18 at 08:00

1

I solved it thank you all

  def search_mahmoud_friends(self):
        if self.conn:
            c = self.conn.cursor()

            try:

                sql = '''SELECT * FROM ahmed WHERE mth_search = %s'''
                mth_search = self.lineEdit_3.text()
                c.execute(sql, [(mth_search)])
                data = c.fetchall()
                for row in data :
                    print(row)

            except mysql.connector.Error as err:
              print("Error: ", err)

answered Sep 17 '18 at 08:00

mthafize19

23
4

This: `[(mth_search)]` should rather read `(mth_search,)` (note the trailing comma). I guess you only made that a list because your DB driver complained about the parameter not being a sequence. That was because if you do `(mth_search)` (without comma) this is treated as literal, not as tuple. Also, please delete your [other answer](https://stackoverflow.com/a/52362661/4134674). As it stands, it is wrong, and you should not add an answer for every iteration of your solution. – shmee Sep 17 '18 at 08:40

How to import data from SQL Database?

3 Answers3