[nacholibre]

Hello all.,

1) I am trying to list table names in a database using python,
2) also need help specifically calling a table and getting whats in it.
if you dont know python thats fine, i just need help in the syntax of sqlite queries

Here is my code for getting table names,
i don't understand the format it results. what does " u' "in front of the names mean. why is there a comma after the names? yes i am new to databases...

dbconn = sqlite3.connect(unicode('data.db'))
cursor = dbconn.cursor()
cursor.execute( "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
data = cursor.fetchall()
print data

it gives the following.

~$ python sqltest.py
[(u'table1',), (u'table2',)]

i was expecting something like table1 table2

How do i list the stuff in specific table? lets say table2
this did not work for some reason.

c.execute('select * from table2')
data2 = cursor.fetchall()
print data2

I really appreciate all your help

This post has been edited by nacholibre: 29 November 2010 - 02:09 PM

[nacholibre]

from http://www.sqlite.org/sqlite.html

i found the following

Quote

For example, to see a list of the tables in the database, you can enter ".tables".

sqlite> .tables
tbl1
tbl2
sqlite>

The ".tables" command is similar to setting list mode then executing the following query:

SELECT name FROM sqlite_master
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
WHERE type IN ('table','view')
ORDER BY 1

so naturally i put the second set of commands in python expecting it to give me table names....
it gave the following...

<sqlite3.Cursor object at 0xb77248f0>

maybe its because i am not setting list mode - any idea how to do that?
i know in terminal ' > .mode list 'works

any help on this?

This post has been edited by nacholibre: 29 November 2010 - 03:08 PM

[JackOfAllTrades]

The us mean it's a Unicode-encoded string. For your reading pleasure.

[(u'table1',), (u'table2',)]

That's a list of the tables. Reading the manual helps here:

Quote

Cursor.fetchall()
Fetches all (remaining) rows of a query result, returning a list. Note that the cursor’s arraysize attribute can affect the performance of this operation. An empty list is returned when no rows are available.

EDIT: BTW, I moved this to Python, as this is really more of a Python question than a database question.

This post has been edited by JackOfAllTrades: 29 November 2010 - 04:27 PM

[baavgai]

Here's a test script:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("select name from sqlite_master where type='table'").fetchall()
conn.execute('create table tab1(id integer primary key, name text unique)')
conn.execute('create table tab2(id integer primary key, name text unique)')
conn.execute("select name from sqlite_master where type='table'").fetchall()

for row in conn.execute("select name from sqlite_master where type='table'").fetchall():
	print "TableName: ", row[0]

for name in ['alpha','bravo','charlie','delta','echo']:
	conn.execute('insert into tab1(name) values(?)',(name,))

conn.execute("select * from tab1").fetchall()

for row in conn.execute("select id, name from tab1").fetchall():
	print "ID: ", row[0]
	print "Name: ", row[1]
	print

Here's what it looks like when I run it in the python interpreter:

>>> import sqlite3
>>> 
>>> conn = sqlite3.connect(":memory:")
>>> conn.execute("select name from sqlite_master where type='table'").fetchall()
[]
>>> conn.execute('create table tab1(id integer primary key, name text unique)')
<sqlite3.Cursor object at 0x8631380>
>>> conn.execute('create table tab2(id integer primary key, name text unique)')
<sqlite3.Cursor object at 0x86383e0>
>>> conn.execute("select name from sqlite_master where type='table'").fetchall()
[(u'tab1',), (u'tab2',)]
>>> 
>>> for row in conn.execute("select name from sqlite_master where type='table'").fetchall():
...     print "TableName: ", row[0]
... 
TableName:  tab1
TableName:  tab2
>>> for name in ['alpha','bravo','charlie','delta','echo']:
...     conn.execute('insert into tab1(name) values(?)',(name,))
... 
<sqlite3.Cursor object at 0x8631380>
<sqlite3.Cursor object at 0x86383b0>
<sqlite3.Cursor object at 0x8631380>
<sqlite3.Cursor object at 0x86383b0>
<sqlite3.Cursor object at 0x8631380>
>>> conn.execute("select * from tab1").fetchall()
[(1, u'alpha'), (2, u'bravo'), (3, u'charlie'), (4, u'delta'), (5, u'echo')]
>>> 
>>> for row in conn.execute("select id, name from tab1").fetchall():
...     print "ID: ", row[0]
...     print "Name: ", row[1]
...     print
... 
ID:  1
Name:  alpha

ID:  2
Name:  bravo

ID:  3
Name:  charlie

ID:  4
Name:  delta

ID:  5
Name:  echo

>>> 

Essentially, your output is just a python list from the way you're calling it. There are a few types of results python can offer you up.

The little u just tells you the data is in unicode format.