Sqlite3 show tables python

How to list tables in SQLite3 database in Python

You can use this snippet to list all the SQL tables in your SQLite 3.x database in Python:

def tables_in_sqlite_db(conn): cursor = conn.execute("SELECT name FROM sqlite_master WHERE type='table';") tables = [ v[0] for v in cursor.fetchall() if v[0] != "sqlite_sequence" ] cursor.close() return tables

#!/usr/bin/env python3 import sqlite3 # Open database conn = sqlite3.connect('/usr/share/command-not-found/commands.db') # List tables tables = tables_in_sqlite_db(conn) # Your code goes here! # Example: print(tables) # prints ['commands', 'packages']

If this post helped you, please consider buying me a coffee or donating via PayPal to support research & publishing of new posts on TechOverflow

Search

Categories

This website uses cookies to improve your experience. We’ll assume you’re ok with this, but you can opt-out if you wish. Cookie settingsACCEPTPrivacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.

Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.

Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.

Источник

How to get table names using sqlite3 through python?

sqlite3 is a library that provides an interface for working with SQLite databases. It is a popular database for storing structured data in a compact and efficient manner. In Python, it is easy to connect to a SQLite database and perform operations on the tables and data within it. However, sometimes it may be necessary to retrieve information about the tables themselves, such as the names of the tables in a database. This information can be useful in various scenarios, such as when you want to retrieve all data from a specific table or when you want to perform operations on multiple tables. In this article, we will discuss the methods for retrieving the names of tables in a SQLite database using the sqlite3 library in Python.

Method 1: Using SQL Query

To get the table names in SQLite3 using SQL query in Python, you can use the following steps:

conn = sqlite3.connect('database_name.db')

cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")

for table in tables: print(table[0])

import sqlite3 conn = sqlite3.connect('database_name.db') cursor = conn.cursor() cursor.execute("SELECT name FROM sqlite_master WHERE type='table';") tables = cursor.fetchall() for table in tables: print(table[0])

This code will print the names of all the tables in the database. You can modify the SQL query to get more specific results, such as the names of tables that start with a certain prefix.

Method 2: Using sqlite3 Library functions

To get table names using sqlite3 through Python, you can use the following code:

import sqlite3 conn = sqlite3.connect('example.db') cur = conn.cursor() cur.execute("SELECT name FROM sqlite_master WHERE type='table';") tables = cur.fetchall() for table in tables: print(table[0]) cur.close() conn.close()

• First, we import the sqlite3 library to use its functions.
• Then, we create a connection to the database using the connect() function and passing the name of the database as an argument. If the database does not exist, it will be created.
• Next, we create a cursor object using the cursor() function. The cursor is used to execute SQL statements and fetch results.
• To get the table names, we execute a SELECT statement on the sqlite_master table, which contains information about all the tables in the database. We filter the results to only include tables by using the type=’table’ condition.
• We fetch all the results using the fetchall() function and store them in the tables variable.
• Finally, we iterate through the tables variable and print each table name.

Note: This code assumes that the table names are stored in the first column of the result set. If your database schema is different, you may need to modify the code accordingly.

Источник

Python sqlite3 Create Tables

Let’s use Python SQLite3 to create and show tables. SQLite is a lightweight, serverless, embedded database system that runs as part of our script or application. Following are some other features:

1. Developed in C programming language and is open source.
2. Supports SQL language.
3. Writes data directly to a file on the filesystem.
4. Little configuration required.
5. Supports only a small set of datatypes: NULL, INTEGER, REAL, TEXT and BLOB.
6. Ideal for very small datasets.
7. Is not a substitute for a large complex DBMS like Oracle or SQL Server.

Let’s write some code to create a SQLite DB:

import sqlite3 con = sqlite3.connect('data/sample.db') cur = con.cursor() # Create table cur.execute('''CREATE TABLE IF NOT EXISTS Playlist (createdate text, id integer, artist text, songTitle text, albumTitle text, duration text, songprice real)''') #commit changes con.commit() # Close connection but commit changes before you do this con.close() 

1. First import our sqlite3 library.
2. sqlite3.connect() opens a connection to the SQLite database file supplied in the parameter. Our parameter is the path to the database file sample.db. The connect() method returns a Connection object which we store as con.
3. con.cursor() returns an instance of class Cursor. This is important because class Cursor has the methods that we will use to execute queries. We store our object in cur.
4. cur.execute() is what executes the SQL Statement. It accepts a SQL statement. In this case we are creating one table called Playlist if it doesn’t already exist.
5. con.commit() commits the current transaction. If this is not called before the connection is closed changes to the data in the table will be lost. On this occasion it is not necessary because we are only creating a table. CREATE TABLE is a DDL statement which means that there is an implicit ‘commit’.
6. con.close() closes the database connection. It is always good practice to close a database connection when it is no longer needed.

The above example will not return any output. If we want to know what tables already exist in the database we can execute the following query:

for row in cur.execute('''SELECT name FROM sqlite_master WHERE type='table' ORDER BY name'''): print(row) #Output #('Playlist',) 

Every SQLite database contains a schema table that stores a record of every created table in the database. This table goes by the name sqlite_master or sqlite_schema table, for backwards compatibility. In the above code we simply query that table to discover what tables already exist in our SQLite database sample.db.

Following is our complete code:

import sqlite3 con = sqlite3.connect('data/sample.db') cur = con.cursor() # Create table cur.execute('''CREATE TABLE IF NOT EXISTS Playlist (createdate text, id integer, artist text, songTitle text, albumTitle text, duration text, songprice real)''') cur.execute('''CREATE TABLE IF NOT EXISTS Playlist2 (createdate text, id integer, artist text, songTitle text, albumTitle text, duration text, songprice real)''') for row in cur.execute('''SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name'''): print(row) #shows two tables # Close connection con.close() con = sqlite3.connect('data/sample.db') cur = con.cursor() # Drop table cur.execute('''DROP TABLE IF EXISTS Playlist2''') for row in cur.execute('''SELECT name FROM sqlite_master WHERE type='table' ORDER BY name'''): print(row) #shows one table # Close connection con.close() #Output #('Playlist',) #('Playlist2',) #('Playlist',) 

So now you know how to use Python SQLite3 to create and show tables. In the next part of this tutorial we will explore how to INSERT and SELECT Rows from our SQLite table. Click HERE for Part 2.

Источник

Retrieve The List Of Tables, Indices And The Associated SQL Statements

Getting the list of tables and indexes present in a SQLite database using Python:

• The sqlite3 is the python database client for SQLite database.
• A database connection to an SQLite database can be created by calling the connect() method of the sqlite3 module.
• Through the connection object a sqlite3.Cursor object can be obtained.
• A SQL statement like a SELECT statement can be executed on the SQLite server by calling the execute() method of the cursor object and passing the SQL statement as parameter.
• The Python example below queries the sqlite_master table and prints the list of tables and indices present in the SQLite main database loaded through the file info.dbs .

Example:

# Import the python module — sqlite3

# Create database connection to a main database which is stored in a file

# Print the tables and indices present in the SQLite main database

cursorObject.execute(«select * from SQLite_master»)

print(«Listing tables and indices from main database:»)

print(«Type of database object: %s»%(table[0]))

print(«Name of the database object: %s»%(table[1]))

print(«SQL statement: %s»%(table[4]))

Output:

Listing tables and indices from main database:

Type of database object: table

Name of the database object: tokens

SQL statement: CREATE TABLE tokens(id int, value varchar(12), weight int)

Type of database object: table

Name of the database object: routes

SQL statement: CREATE TABLE routes(id int, start int, stop int,hops int )

Type of database object: index

Name of the database object: idx_index

SQL statement: CREATE INDEX idx_index on tokens(value)

Источник