Python Sqlite3 function LOWER UPPER doesnt work

Question

import sqlite3

connection = sqlite3.connect('test.db')
cursor = connection.cursor()
cursor.execute("DROP TABLE `products`")
cursor.execute('''CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY, 
    name TEXT
)''')

cursor.execute("INSERT INTO products (name) VALUES (?)", ('Päärynä',))
cursor.execute("INSERT INTO products (name) VALUES (?)", ('Банан',))
cursor.execute("INSERT INTO products (name) VALUES (?)", ('Banana',))
connection.commit()
cursor.execute(
    "SELECT LOWER(name), UPPER(name), name FROM products WHERE name='Päärynä' OR name='Banana' OR name='Банан'")
result = cursor.fetchall()
connection.close()
print(result)

#[('päärynä', 'PääRYNä', 'Päärynä'), ('Банан', 'Банан', 'Банан'), ('banana', 'BANANA', 'Banana')]

I noticed that not all languages are subject to change. How can this be fixed?

Answer 1

SQLite's UPPER function only applies to ASCII characters, per the docs:

upper(X)

The upper(X) function returns a copy of input string X in which all lower-case ASCII characters are converted to their upper-case equivalent.

This behaviour varies across different database systems; for example in PostgreSQL we get:

test# select upper('Päärynä');
  upper  
═════════
 PÄÄRYNÄ
(1 row)

Answer 2

Unfortunately, sqlite’s UPPER works with ASCII characters only. This is a limitationn of sqlite. And it is often not used in production level databases. However, to answer your question, in the event you MUST use SQLite, here is a solution I verified when copying your code in my local python script:

import sqlite3

# Connect to database
conn = sqlite3.connect('test.db')
c = conn.cursor()

# Create table if not exists
c.execute('''CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY, 
    name TEXT
)''')

# Insert data into table
def to_ascii_equivalent(char):
    return char.encode().decode('utf-8').upper()

product_name = 'Банан'
product_id = c.execute("INSERT INTO products (name) VALUES (?)", (to_ascii_equivalent(product_name),)).lastrowid

# Retrieve data from table
c.execute("SELECT * FROM products WHERE name=?", (to_ascii_equivalent(product_name),))
result = c.fetchone()

# Print result
print(result)

# Close database connection
conn.close()

It’s ugly but if you must use sqlite it works. Notice I focus here specifically on Russian, but the same process works with other languages. Here is the output:

(7, 'БАНАН')