Using CSV as a mutable database?

Question

Yes, this is as stupid a situation as it sounds like. Due to some extremely annoying hosting restrictions and unresponsive tech support, I have to use a CSV file as a database.

While I can use MySQL with PHP, I can't use it with the Python backend of my program because of install issues with the host. I can't use SQLite with PHP because of more install issues, but can use it as it's a Python builtin.

Anyways, now, the question: is it possible to update values SQL-style in a CSV database? Or should I keep on calling the help desk?

Answer 1

I'd keep calling help desk. You don't want to use CSV for data if it's relational at all. It's going to be nightmare.

Answer 2

Keep calling on the help desk.

While you can use a CSV as a database, it's generally a bad idea. You would have to implement you own locking, searching, updating, and be very careful with how you write it out to make sure that it isn't erased in case of a power outage or other abnormal shutdown. There will be no transactions, no query language unless you write your own, etc.

Answer 3

Don't walk, run to get a new host immediately. If your host won't even get you the most basic of free databases, it's time for a change. There are many fish in the sea.

At the very least I'd recommend an xml data store rather than a csv. My blog uses an xml data provider and I haven't had any issues with performance at all.

Answer 4

I couldn't imagine this ever being a good idea. The current mess I've inherited writes vital billing information to CSV and updates it after projects are complete. It runs horribly and thousands of dollars are missed a month. For the current restrictions that you have, I'd consider finding better hosting.

Answer 5

Take a look at this: http://www.netpromi.com/kirbybase_python.html

Answer 6

I agree. Tell them that 5 random strangers agree that you being forced into a corner to use CSV is absurd and unacceptable.

Answer 7

You can probably used sqlite3 for more real database. It's hard to imagine hosting that won't allow you to install it as a python module.

Don't even think of using CSV, your data will be corrupted and lost faster than you say "s#&t"

Answer 8

If I understand you correctly: you need to access the same database from both python and php, and you're screwed because you can only use mysql from php, and only sqlite from python?

Could you further explain this? Maybe you could use xml-rpc or plain http requests with xml/json/... to get the php program to communicate with the python program (or the other way around?), so that only one of them directly accesses the db.

If this is not the case, I'm not really sure what the problem.

Answer 9

"Anyways, now, the question: is it possible to update values SQL-style in a CSV database?"

Technically, it's possible. However, it can be hard.

If both PHP and Python are writing the file, you'll need to use OS-level locking to assure that they don't overwrite each other. Each part of your system will have to lock the file, rewrite it from scratch with all the updates, and unlock the file.

This means that PHP and Python must load the entire file into memory before rewriting it.

There are a couple of ways to handle the OS locking.

1. Use the same file and actually use some OS lock module. Both processes have the file open at all times.

2. Write to a temp file and do a rename. This means each program must open and read the file for each transaction. Very safe and reliable. A little slow.

Or.

You can rearchitect it so that only Python writes the file. The front-end reads the file when it changes, and drops off little transaction files to create a work queue for Python. In this case, you don't have multiple writers -- you have one reader and one writer -- and life is much, much simpler.

Answer 10

It's technically possible. For example, Perl has DBD::CSV that provides a driver that runs SQL queries on the CSV file.

That being said, why not run off a SQLite database on your server?

Answer 11

What about postgresql? I've found that quite nice to work with, and python supports it well.

But I really would look for another provider unless it's really not an option.

Answer 12

Disagreeing with the noble colleagues, I often use DBD::CSV from Perl. There are good reasons to do it. Foremost is data update made simple using a spreadsheet. As a bonus, since I am using SQL queries, the application can be easily upgraded to a real database engine. Bear in mind these were extremely small database in a single user application.

So rephrasing the question: Is there a python module equivalent to Perl's DBD:CSV