DEV Community

Tony Colston
Tony Colston

Posted on

rpad in sqlite3 with Python

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()
Enter fullscreen mode Exit fullscreen mode

Top comments (0)