How to read a csv using sql

Question

I would like to know how to read a csv file using sql. I would like to use group by and join other csv files together. How would i go about this in python.

example:

select * from csvfile.csv where name LIKE 'name%'

Answer 1

SQL code is executed by a database engine. Python does not directly understand or execute SQL statements.

While some SQL database store their data in csv-like files, almost all of them use more complicated file structures. Therefore, you're required to import each csv file into a separate table in the SQL database engine. You can then use Python to connect to the SQL engine and send it SQL statements (such as SELECT). The engine will perform the SQL, extract the results from its data files, and return them to your Python program.

The most common lightweight engine is SQLite.

Answer 2

littletable is a Python module I wrote for working with lists of objects as if they were database tables, but using a relational-like API, not actual SQL select statements. Tables in littletable can easily read and write from CSV files. One of the features I especially like is that every query from a littletable Table returns a new Table, so you don't have to learn different interfaces for Table vs. RecordSet, for instance. Tables are iterable like lists, but they can also be selected, indexed, joined, and pivoted - see the opening page of the docs.

# print a particular customer name 
# (unique indexes will return a single item; non-unique
# indexes will return a Table of all matching items)
print(customers.by.id["0030"].name)
print(len(customers.by.zipcode["12345"]))

# print all items sold by the pound
for item in catalog.where(unitofmeas="LB"):
    print(item.sku, item.descr)

# print all items that cost more than 10
for item in catalog.where(lambda o : o.unitprice>10):
   print(item.sku, item.descr, item.unitprice)

# join tables to create queryable wishlists collection
wishlists = customers.join_on("id") + wishitems.join_on("custid") + catalog.join_on("sku")

# print all wishlist items with price > 10
bigticketitems = wishlists().where(lambda ob : ob.unitprice > 10)
for item in bigticketitems:
   print(item)

Columns of Tables are inferred from the attributes of the objects added to the table. namedtuples are good also, as well as a types.SimpleNamespaces. You can insert dicts into a Table, and they will be converted to SimpleNamespaces.

littletable takes a little getting used to, but it sounds like you are already thinking along a similar line.