1. 특정 DB 내의 테이블 목록이 궁금할때.

cur.execute(' select name from sqlite_master where type="table"')

결과테이블리스트: [('doro',), ('buga',), ('juso',), ('jibun',), ('sigudong',) ]

import sqlite3
def getTableName( dbName ):
    con = sqlite3.connect( dbName )
    cur = con.cursor()
    cur.execute('SELECT NAME FROM sqlite_master WHERE TYPE="table"')    
    tableList = [ tb[0] for tb in cur ]
    cur.close() ; con.close()
    return tableList

2. DB 내에 특정테이블이 존재하는지 알아 보기.

cur.execute(' select name from sqlite_master where type="table" and

name="tableName"')

결과: 테이블이 존재하면: [('jibun',)]

존재하지 않으면 : []

def isTableExists( dbName ,tableName ):
    con = sqlite3.connect( dbName )
    cur = con.cursor()
    cur.execute(''' SELECT NAME FROM sqlite_master WHERE
                    TYPE="table" AND name="%s";''' % tableName )
    qd = cur.fetchone()
    cur.close() ; con.close()
    if qd==None:
        return False
    else:
        return True

3. 테이블의 필드(칼럼)이름이 궁금할때.( cursor.description )

cur.execute("SELECT * FROM buga;")

[ fd[0] for fd in cur.description ]

결과필드목록: ['GRNo', 'HJDName', 'SGHBuildName', 'BJUMDName']

def getColumnName( dbName ,tableName):
    tableList = getTableName( dbName )
    tableList = [ tname.upper() for tname in tableList ]
    if tableName.upper() not in tableList:
        return []
    con = sqlite3.connect( dbName )
    cur = con.cursor()
    cur.execute("SELECT * FROM %s LIMIT 1;" % tableName )
    fieldList = [ fd[0] for fd in cur.description ]
    cur.close() ; con.close()
    return fieldList

4. 테이블내의 필드(column) Type 알아내기

cur.execute( 'PRAGMA table_info(tableName)' )

결과: [(0, 'GRNo', 'CHAR(25)', 0, None, 0), (1, 'SiDoName',

'VACHAR(20)', 0, None, 0), ...]

: 튜플 리스트가 나온다. 이중에서 2,3번째의 필드이름과 형식만을 가져와 사용.

def getTypeOfColumn(dbName ,tableName ):
    rtnDict = {}
    con = sqlite3.connect( dbName )
    cur = con.cursor()
    cur.execute( 'PRAGMA table_info(%s)' % tableName )
    qd = cur.fetchall()
    for fd in qd:
        rtnDict[fd[1]] = fd[2]
    cur.close() ; con.close()
    return rtnDict