Re: [pysqlite] Exporting Data to .CSV file

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Nuno Ribeiro wrote:
> I'm new to SQLite

One thing you may have missed is that SQLite is a library.  The library
by itself is useless - it needs to be part of a larger process.  There
are two relevant ones.

The first is the SQLite shell - the sqlite3 binary you used.  In
addition to accepting SQL it also has a number of commands to alter how
it formats output and accepts input.

The second is pysqlite.  It wraps the SQLite library so that you can
call it from Python.  You have do output and input yourself expressing
it in Python.

It makes no sense whatsoever to use both at the same time in the same
program.

> I don't see how to IMPORT a .csv file (FROM PYTHON),

You have to write Python code to read the CSV file and ask pysqlite to
update the database with the read values.  Fortunately Python has a csv
module which will do CSV reading and writing for you.

  http://docs.python.org/library/csv.html

Your main loop will look something like this.  Lookup Python iterators
and using bindings with pysqlite.

  for row in csvreader:
    pysqlitecursor.execute("insert into table-xyz values(?,?,?)", row)

> or in the other way, to EXPORT a .csv (from SQLite).

Run ".help" from the shell.  There is a ".mode CSV" command to set how
things are output.  You can use ".output FILENAME" to direct the output
to a file.

> from my python script I can view these results using os.system:
> 
> a = os.system('sqlite3 /Path/To/Database/contacts.db "select * from
> contacts"')

That is a really bad way of doing things.  Either use the SQLite shell
or use pysqlite.  The above gives the worst combination!

> c.execute ( ''' .output file.csv ''')
> c.execute ( ''' .separator , ''' )

The dot commands are part of the SQLite shell and not the library.
Hence they are not available from pysqlite nor would they make any sense.

> But, I want to do all work from Python. Is it possible?

Yes.  Read the entirety of the documentation and look at all the examples.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkvlPcACgkQmOOfHg372QSAegCgsn3RFWJ9FI7MOPwVic6UY/+S
6W0AoMuzNk+9E2GLcij3F3g5ydEYaufT
=8M+t
-----END PGP SIGNATURE-----