I have a small data analysis problem where I wanted to see a histogram of the data.
I did a quick google search and saw a few things but none of them seemed right to me. So I read the Python docs and decide I could do it with a function defined in Python.
I defined a function named rpad as a normal Python function. Once you have done that you can register that function with sqlite and then you can call the function in SQL. Woooooo!
import sqlite3
db = sqlite3.connect(":memory:")
def rpad(count):
ts = ""
for i in range(count):
ts = ts + "*"
return ts
# register the function with sqlite here
db.create_function("rpad",1,rpad)
cur = db.cursor()
cur.execute("create table grades(grade)")
grades = [1,2,1,3,4,5,5]
for g in grades:
cur.execute("insert into grades values(?)",(g,))
db.commit()
cur.execute("select * from grades")
cur.execute("""
SELECT grade,
COUNT(*) AS 'Count',
rpad(COUNT(*)) AS 'Bar'
FROM grades
GROUP BY grade
ORDER BY COUNT(*)
""")
data = cur.fetchall()
for d in data:
print(d)
db.close()
Top comments (0)