sqlite3 database won't accept fonted reshaped arabic data - python

OS: ubuntu 20.04
IDE: pyCharm community Build #PC-223.8214.51, built on December 20, 2022
Language: python 3.10.6

I'm using kivy to develop a simple app that queries an sqlite3 database for data written in Arabic. Now, because kivy doesn't natively support Arabic, I had to use both BIDI and arabic-reshaper so I can allow the user to type in proper Arabic.

The database is created from a .csv file, which in turn created by the LibreOffice calc also on Ubuntu 20.04. I have used pandas on terminal to create my database as follows:

users = pd.read_csv('numberDataBaseCSV.csv')
users.to_sql('users', conn, if_exists='replace', index = False) 

First, I had to override the TextInput.insert_text as follows:

class Ar_text(TextInput):
    max_chars = NumericProperty(20)  # maximum character allowed
    str = StringProperty()

    def __init__(self, **kwargs):
        super(Ar_text, self).__init__(**kwargs)
        self.text = bidi.algorithm.get_display(arabic_reshaper.reshape("اطبع شيئاً"))

    def insert_text(self, substring, from_undo=False):
        if not from_undo and (len(self.text) + len(substring) > self.max_chars):
            return

        self.str = self.str+substring
        self.text = bidi.algorithm.get_display(arabic_reshaper.reshape(self.str))
        substring = ""
        super(Ar_text, self).insert_text(substring, from_undo)

    def do_backspace(self, from_undo=False, mode='bkspc'):
        self.str = self.str[0:len(self.str)-1]
        self.text = bidi.algorithm.get_display(arabic_reshaper.reshape(self.str))

Then I built the app as usual:

class TestApp(kivy.app.App):
    def build(self):
        def create_connection(db_file):
            """ create a database connection to the SQLite database
                specified by the db_file
            :param db_file: database file
            :return: Connection object or None
            """
            conn = None
            try:
                conn = sqlite3.connect(db_file)
            except sqlite3.Error as e:
                print(e)

            return conn

        self.dbConnect = create_connection("numberData.db")
        self.number_cursor = self.dbConnect.cursor()

        reshaped_text = arabic_reshaper.reshape("بحث")
        bidi_text = bidi.algorithm.get_display(reshaped_text)


        self.circuit_number_label = kivy.uix.label.Label(text=bidi_text, font_name="janna-lt-bold/Janna LT Bold/Janna LT Bold.ttf")
        self.nameTextField = Ar_text(text=bidi_text, font_name="janna-lt-bold/Janna LT Bold/Janna LT Bold.ttf")
        self.searchButton = kivy.uix.button.Button(text=bidi_text, font_name="janna-lt-bold/Janna LT Bold/Janna LT Bold.ttf")
        self.searchButton.bind(on_press = self.search_number_by_name)


        boxLayout = kivy.uix.boxlayout.BoxLayout(orientation="vertical")
        boxLayout.add_widget(self.circuit_number_label)
        boxLayout.add_widget(self.nameTextField)
        boxLayout.add_widget(self.searchButton)

        return boxLayout

ِAnd this is the callback that's triggered when I click the button:

    def search_number_by_name(self, event):
       reshaped_text = bidi.algorithm.get_display(self.nameTextField.text)
       print(reshaped_text)
       print("select الرقم,الدارة from numbers_table where الاسم = '" + reshaped_text.strip() + "'")
       self.number_cursor.execute("select الرقم,الدارة from numbers_table where الاسم = '" + reshaped_text.strip() + "'")
       rows =  self.number_cursor.fetchall()
       for row in rows:
           print(row)

And then of course running the app:

testApp = TestApp()
testApp.run()

Even though the data is correct and I should get the queried data from the database, nothing seems to be happening. Not even an error message.

when I hard code the data in the where clause:

quereied_data = "خالد"

and then build the query:

self.number_cursor.execute("select الرقم,الدارة from numbers_table where الاسم = '" + queried_data + "'")

I get the expected results from the database, only when I retrieve the data from the TextInput I get nothing.

I've tried to "undo" the reshaping process with the code snippet:

reshaped_text = bidi.algorithm.get_display(self.nameTextField.text) 

But that also didn't work, I'm not sure where the problem is since I can't get any error messages. I'd appreciate the help.

As it turns out, there's a property in the TextInput class: str, so in order to retrieve the data and use in a query you need to do the following:

reshaped_text = self.nameTextField.str

Then use it as usual in the built query:

self.number_cursor.execute("select الرقم,الدارة from numbers_table where الاسم = '" + reshaped_text.strip() + "'")
   rows = self.number_cursor.fetchall()
   for row in rows:
       print(row)