Hướng dẫn sqlite3 python list tables - sqlite3 bảng danh sách python

Xem thảo luận

Cải thiện bài viết

Lưu bài viết

Xem thảo luận

Cải thiện bài viết

Lưu bài viết

Đọc

Bàn luận

Trong bài viết này, chúng tôi sẽ thảo luận về cách liệt kê tất cả các bảng trong cơ sở dữ liệu SQLite bằng Python. Ở đây, chúng tôi sẽ sử dụng bảng cơ sở dữ liệu đã được tạo từ SQLite. Chúng tôi cũng sẽ tìm hiểu xử lý ngoại lệ trong quá trình kết nối với cơ sở dữ liệu của chúng tôi.

Cơ sở dữ liệu được sử dụng:

sqliteConnection = sqlite3.connect('SQLite_Retrieving_data.db')

Các bước để tìm nạp tất cả các bảng bằng SQLite3 trong Python

sql_query = """SELECT name FROM sqlite_master  
  WHERE type='table';"""

1. Tạo một đối tượng kết nối bằng phương thức Connect (),

cursor = sqliteConnection.cursor()

2. Đã tạo một truy vấn SQLite mà chúng tôi sẽ tìm kiếm một danh sách tất cả các bảng có trong cơ sở dữ liệu SQLite3.

cursor.execute(sql_query)

3. Sử dụng đối tượng kết nối, chúng tôi đang tạo một đối tượng con trỏ.

print(cursor.fetchall())

4. Sử dụng các phương thức Execute (), chúng tôi sẽ thực thi truy vấn SQL ở trên.

Python3

5. Cuối cùng, chúng tôi sẽ in một danh sách tất cả các bảng có trong cơ sở dữ liệu SQLite3.

print(cursor.fetchall())

print(cursor.fetchall())

Dưới đây là việc thực hiện

print(cursor.fetchall())

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

print(cursor.fetchall())

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

print(cursor.fetchall())

print(cursor.fetchall())

print(cursor.fetchall())

print(cursor.fetchall())

C:\User\PhoCode>sqlite3 test.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

print(cursor.fetchall())

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

C:\User\PhoCode>sqlite3 test.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

print(cursor.fetchall())

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

import sqlite3 as lite
import sys
import os
con = None

try:
    path = os.path.dirname(__file__) + "\\test.db"
    con = lite.connect(path)
    
    cur = con.cursor()    
    cur.execute('SELECT SQLITE_VERSION()')
    
    data = cur.fetchone()
    
    print ("SQLite version: %s" % data)         
    
except lite.Error as e:
    
    print ("Error %s:" % e.args[0])
    sys.exit(1)
    
finally:
    
    if con:
        con.close()

print(cursor.fetchall())

print(cursor.fetchall())

print(cursor.fetchall())

print(cursor.fetchall())

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

print(cursor.fetchall())

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

import sqlite3 as lite
import sys
import os
con = None

try:
    path = os.path.dirname(__file__) + "\\test.db"
    con = lite.connect(path)
    
    cur = con.cursor()    
    cur.execute('SELECT SQLITE_VERSION()')
    
    data = cur.fetchone()
    
    print ("SQLite version: %s" % data)         
    
except lite.Error as e:
    
    print ("Error %s:" % e.args[0])
    sys.exit(1)
    
finally:
    
    if con:
        con.close()

import sqlite3 as lite

import sqlite3 as lite

print(cursor.fetchall())

print(cursor.fetchall())

C:\User\PhoCode>sqlite3 test.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

print(cursor.fetchall())

C:\User\PhoCode>sqlite3 test.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

import sqlite3 as lite
import sys
import os
con = None

try:
    path = os.path.dirname(__file__) + "\\test.db"
    con = lite.connect(path)
    
    cur = con.cursor()    
    cur.execute('SELECT SQLITE_VERSION()')
    
    data = cur.fetchone()
    
    print ("SQLite version: %s" % data)         
    
except lite.Error as e:
    
    print ("Error %s:" % e.args[0])
    sys.exit(1)
    
finally:
    
    if con:
        con.close()

import sqlite3 as lite
import sys
import os
con = None

try:
    path = os.path.dirname(__file__) + "\\test.db"
    con = lite.connect(path)
    
    cur = con.cursor()    
    cur.execute('SELECT SQLITE_VERSION()')
    
    data = cur.fetchone()
    
    print ("SQLite version: %s" % data)         
    
except lite.Error as e:
    
    print ("Error %s:" % e.args[0])
    sys.exit(1)
    
finally:
    
    if con:
        con.close()

import sqlite3 as lite

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

con = None

C:\User\PhoCode>python
Python 3.5.1 (v3.5.1:37a07cee5969, Dec 6 2015, 01:38:48) [MSC v.1900 32 bit (Intel)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.11'

Output:

print(cursor.fetchall())

import sqlite3 as lite

import sqlite3 as lite