Python: List the tables of given SQLite database file
Write a Python program to list the tables of given SQLite database file.
Sample Solution:
Python Code :
import sqlite3 from sqlite3 import Error def sql_connection(): try: conn = sqlite3.connect('mydatabase.db') return conn except Error: print(Error) def sql_table(conn): cursorObj = conn.cursor() # Create two tables cursorObj.execute("CREATE TABLE agent_master(agent_code char(6),agent_name char(40),working_area char(35),commission decimal(10,2),phone_no char(15) NULL);") cursorObj.execute("CREATE TABLE temp_agent_master(agent_code char(6),agent_name char(40),working_area char(35),commission decimal(10,2),phone_no char(15) NULL);") print("List of tables:") cursorObj.execute("SELECT name FROM sqlite_master WHERE type='table';") print(cursorObj.fetchall()) conn.commit() sqllite_conn = sql_connection() sql_table(sqllite_conn) if (sqllite_conn): sqllite_conn.close() print("\nThe SQLite connection is closed.") List of tables: [('agent_master',), ('temp_agent_master',)] The SQLite connection is closed. Python Code Editor:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource’s quiz.
Follow us on Facebook and Twitter for latest update.
Python: Tips of the Day
Why is �1000000000000000 in range(1000000000000001)� so fast in Python 3?
The Python 3 range() object doesn’t produce numbers immediately; it is a smart sequence object that produces numbers on demand. All it contains is your start, stop and step values, then as you iterate over the object the next integer is calculated each iteration.
The object also implements the object.__contains__ hook, and calculates if your number is part of its range. Calculating is a (near) constant time operation *. There is never a need to scan through all possible integers in the range.
From the range() object documentation:
The advantage of the range type over a regular list or tuple is that a range object will always take the same (small) amount of memory, no matter the size of the range it represents (as it only stores the start, stop and step values, calculating individual items and subranges as needed).
So at a minimum, your range() object would do:
class my_range(object): def __init__(self, start, stop=None, step=1): if stop is None: start, stop = 0, start self.start, self.stop, self.step = start, stop, step if step < 0: lo, hi, step = stop, start, -step else: lo, hi = start, stop self.length = 0 if lo >hi else ((hi - lo - 1) // step) + 1 def __iter__(self): current = self.start if self.step < 0: while current >self.stop: yield current current += self.step else: while current < self.stop: yield current current += self.step def __len__(self): return self.length def __getitem__(self, i): if i < 0: i += self.length if 0 '.format(i)) def __contains__(self, num): if self.step < 0: if not (self.stop < numThis is still missing several things that a real range() supports (such as the .index() or .count() methods, hashing, equality testing, or slicing), but should give you an idea.
I also simplified the __contains__ implementation to only focus on integer tests; if you give a real range() object a non-integer value (including subclasses of int), a slow scan is initiated to see if there is a match, just as if you use a containment test against a list of all the contained values. This was done to continue to support other numeric types that just happen to support equality testing with integers but are not expected to support integer arithmetic as well. See the original Python issue that implemented the containment test.
* Near constant time because Python integers are unbounded and so math operations also grow in time as N grows, making this a O(log N) operation. Since it's all executed in optimised C code and Python stores integer values in 30-bit chunks, you'd run out of memory before you saw any performance impact due to the size of the integers involved here.
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook
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.