Using Python SQLite3 to find latin alphabets in column

user_14170649 0
Viewed 106

I have a column in sqlite3 table containing all the chemical elements. There is a column called “Symbol”, where it contains symbols of chemicals. For example, H, Be, Mg.

I would like to pull out all characters from A-Z that does not appear in the column (not case sensitive). Below is what i have currently

conn = sqlite3.connect('chemicals.sqlite')
cur = conn.cursor()
cur.execute('select Symbol from chemicalElements')
rows = cur.fetchall()
for row in rows:
    print(row)

Example of table below

code Symbol Name
1 H Hydrogen
2 He Helium
3 N Nitrogen
4 Mg Magnesium

In this case, the final output should be alphabets other than H,E,N,M,G

sqlite python
2 Answers

You can use a group concat in your query to get all the symbols in one string, then use a set to hold only the unique letters.
Create a set of the letters of the alphabet and do a symmetric difference operation on them.

import string

conn = sqlite3.connect('chemicals.sqlite')
cur = conn.cursor()
cur.execute("select upper(group_concat(symbol,'')) from chemicalElements")
row = cur.fetchone()
symbols = set(row[0])
alphabet = set(string.ascii_uppercase)
other_symbols = alphabet.symmetric_difference(symbols)
print(other_symbols)
user_13530110

Build up a set of all the characters you do find, subtract that from the set of all letters.

conn = sqlite3.connect('chemicals.sqlite')
cur = conn.cursor()
cur.execute('select Symbol from chemicalElements')
rows = cur.fetchall()
found = set()
for row in rows:
    found = found.union(list(row[0].upper()))
missing = set(string.ascii_uppercase) - found
print(missing)
user_18833160
Related