python with sqlite3

Question

For now I am trying to use python for sqlite3. My question is, I don't know how to read the existed 'abc.db' with python. I mean that I just know the abc.db is a sqlite3 file. But I don't know the structure of it, and I also need to get the information from this abc.db.

I used :

import sqlite3
try: 
 sqlite_conn = sqlite3.connect('abc') 
except sqlite3.Error, e: 
 print 'conntect sqlite database failed.' 
 sqlite_logger.error("conntect sqlite database failed, ret = %s" % e.args[0]) 

So, what can I do next? I need to read the abc, and if it is possible, I want to output the content directly on the terminal. Is it possible? Because I need to analyse the data in this file. Thanks a lot!!!!

• agf: possible duplicate of How do I list the tables in a SQLite database file
• Mayur Patel: RE: "output the content directly to the terminal" : sqlite3 has a ".dump" command which you can use to have it spit out SQL that would recreate the current state of the database. This is essentially a backup mechanism, but allows you to see what is in the database in painful detail. You can pass a ".dump" to the database with a python connection -- you don't necessarily need to use the commandline sqlite3 binary.

Answer 1

In your sqlite_conn object you could run the following command

cur = sqlite_conn.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
 rows = cur.fetchall()
 for row in rows:
 print row[0]

Then you could do a SELECT * from <Tablename> for each of those tables. The sqlite_master is a sqlite metadata here.

Answer 2

Using the command-line sqlite3 client, you can see the schema of an unknown db using:

.schema

then poke around a bit with SQL to get a better idea of the data within.