why doesn't pandas execute sql query?

Question

import sqlite3
import pandas as pd

# load data
df = pd.read_csv('CurriculumAuditReport.csv')

# strip whitespace from headers
df.columns = df.columns.str.strip()

con = sqlite3.connect("sans.db")

# drop data into database
df.to_sql("MyTable", con, if_exists='replace')

df = pd.read_sql_query('SELECT Count (Department) FROM MyTable WHERE `CompletedTraining` LIKE 'Incomplete' GROUP BY Department', con)
print(df)


con.close()

the query produces the result i want just fine in DB browser for SQLite

the output 
    C:\sans>C:\python34\python test2.py
  File "test2.py", line 15
    df = pd.read_sql_query('SELECT Count (Department) FROM MyTable WHERE 
`CompletedTraining` LIKE 'Incomplete' GROUP BY Department', con)


                                   ^
SyntaxError: invalid syntax

my output should have 11 rows

The first thing to mention is that there is no point using LIKE without a wildcard, just use equals. Or add a wildcard(s) as appropriate. — Andy G
– Andy G, Commented Dec 28, 2017 at 20:42
try using \'Incomplete\' instead in your code, hope that'll work. — Moinuddin Quadri
– Moinuddin Quadri, Commented Dec 28, 2017 at 20:44
\'Incomplete\' may have done the trick, still need more editing! — davidjbeiler
– davidjbeiler, Commented Dec 28, 2017 at 20:44

MaxU - stand with Ukraine · Accepted Answer · 2017-12-28 21:27:16Z

4

You have an issue with quoting - try this:

df = pd.read_sql_query("SELECT Department, count(*) as cnt FROM MyTable WHERE CompletedTraining = 'Incomplete' GROUP BY Department", con)

You can also use the following technique:

qry = """
SELECT department, count(*) as cnt
FROM MyTable
WHERE CompletedTraining = 'Incomplete'
GROUP BY department
"""
df = pd.read_sql_query(qry, con)

edited Dec 28, 2017 at 21:27

answered Dec 28, 2017 at 20:44

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

davidjbeiler Over a year ago

thank you, i was able to get it working, but using \'Incomplete\'

MaxU - stand with Ukraine Over a year ago

@davidjbeiler, this is a little bit unusual way of counting in SQL... What do you want to count?

davidjbeiler Over a year ago

counting all the 'incomplete' rows in my table/csv but i need to count per department and group by department

davidjbeiler Over a year ago

that gives me the total count, not count per department

MaxU - stand with Ukraine Over a year ago

@davidjbeiler, yeah, now it should show you all the departments (where CompletedTraining = 'Incomplete') together with the corresponding number of rows...

|

Collectives™ on Stack Overflow

why doesn't pandas execute sql query?

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related