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.

Answer 3

You can easily query an SQL Database using PHP script. PHP runs serverside, so all your code will have to be on a webserver (the one with the database). You could make a function to connect to the database like this:

$con= mysql_connect($hostname, $username, $password) 
  or die("An error has occured");

Then use the $con to accomplish other tasks such as looping through data and creating a table, or even adding rows and columns to an existing table.

EDIT: I noticed you said .CSV file. You can upload a CSV file into a SQL database and create a table out of it. If you are using a control panel service such as phpMyAdmin, you can simply import a CSV file into your database like this:

If you are looking for a free web host to test your SQL and PHP files on, check out x10 hosting.