Python sqlite3 посмотреть таблицы

Список таблиц, db-схемы, дампа и т.д. С использованием API-интерфейса Python sqlite3

По какой-то причине я не могу найти способ получить эквиваленты интерактивных команд оболочки sqlite:

с использованием API-интерфейса Python sqlite3.

Есть ли что-нибудь подобное?

ОТВЕТЫ

Ответ 1

Вы можете получить список таблиц и схем, запросив таблицу SQLITE_MASTER:

sqlite> .tab job snmptarget t1 t2 t3 sqlite> select name from sqlite_master where type = 'table'; job t1 t2 snmptarget t3 sqlite> .schema job CREATE TABLE job ( id INTEGER PRIMARY KEY, data VARCHAR ); sqlite> select sql from sqlite_master where type = 'table' and name = 'job'; CREATE TABLE job ( id INTEGER PRIMARY KEY, data VARCHAR ) 

Ответ 2

con = sqlite3.connect('database.db') cursor = con.cursor() cursor.execute("SELECT name FROM sqlite_master WHERE type='table';") print(cursor.fetchall()) 

Следите за моим другим ответом . Существует гораздо более быстрый способ использования pandas.

Ответ 3

Самый быстрый способ сделать это в Python — использовать Pandas (версия 0.16 и выше).

db = sqlite3.connect('database.db') table = pd.read_sql_query("SELECT * from table_name", db) table.to_csv(table_name + '.csv', index_label='index') 

import sqlite3 import pandas as pd def to_csv(): db = sqlite3.connect('database.db') cursor = db.cursor() cursor.execute("SELECT name FROM sqlite_master WHERE type='table';") tables = cursor.fetchall() for table_name in tables: table_name = table_name[0] table = pd.read_sql_query("SELECT * from %s" % table_name, db) table.to_csv(table_name + '.csv', index_label='index') cursor.close() db.close() 

Ответ 4

Я не знаком с Python API, но вы всегда можете использовать

SELECT * FROM sqlite_master; 

Ответ 5

По-видимому, версия sqlite3, включенная в Python 2.6, обладает такой способностью: http://docs.python.org/dev/library/sqlite3.html

# Convert file existing_db.db to SQL dump file dump.sql import sqlite3, os con = sqlite3.connect('existing_db.db') with open('dump.sql', 'w') as f: for line in con.iterdump(): f.write('%s\n' % line) 

Ответ 6

Здесь короткая и простая программа на языке Python для распечатки имен таблиц и имен столбцов для этих таблиц (Python 2. Далее следует Python 3).

import sqlite3 db_filename = 'database.sqlite' newline_indent = '\n ' db=sqlite3.connect(db_filename) db.text_factory = str cur = db.cursor() result = cur.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall() table_names = sorted(zip(*result)[0]) print "\ntables are:"+newline_indent+newline_indent.join(table_names) for table_name in table_names: result = cur.execute("PRAGMA table_info('%s')" % table_name).fetchall() column_names = zip(*result)[1] print ("\ncolumn names for %s:" % table_name)+newline_indent+(newline_indent.join(column_names)) db.close() print "\nexiting." 

(РЕДАКТИРОВАТЬ: я получаю периодические голосования по этому вопросу, так что вот версия Python3 для людей, которые находят этот ответ)

import sqlite3 db_filename = 'database.sqlite' newline_indent = '\n ' db=sqlite3.connect(db_filename) db.text_factory = str cur = db.cursor() result = cur.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall() table_names = sorted(list(zip(*result))[0]) print ("\ntables are:"+newline_indent+newline_indent.join(table_names)) for table_name in table_names: result = cur.execute("PRAGMA table_info('%s')" % table_name).fetchall() column_names = list(zip(*result))[1] print (("\ncolumn names for %s:" % table_name) +newline_indent +(newline_indent.join(column_names))) db.close() print ("\nexiting.") 

Ответ 7

После многих попыток я нашел лучший ответ в sqlite docs для перечисления метаданных для таблицы, даже прикрепленных баз данных.

meta = cursor.execute("PRAGMA table_info('Job')") for r in meta: print r 

Ключевой информацией является префикс table_info, а не my_table с именем дескриптора вложения.

Ответ 8

Откроется здесь для дампа. Кажется, в библиотеке sqlite3 есть функция дампа.

Ответ 9

#!/usr/bin/env python # -*- coding: utf-8 -*- if __name__ == "__main__": import sqlite3 dbname = './db/database.db' try: print "INITILIZATION. " con = sqlite3.connect(dbname) cursor = con.cursor() cursor.execute("SELECT name FROM sqlite_master WHERE type='table';") tables = cursor.fetchall() for tbl in tables: print "\n######## "+tbl[0]+" ########" cursor.execute("SELECT * FROM "+tbl[0]+";") rows = cursor.fetchall() for row in rows: print row print(cursor.fetchall()) except KeyboardInterrupt: print "\nClean Exit By user" finally: print "\nFinally" 

Ответ 10

Вы можете использовать этот анализатор определения для анализа таких определений, как код ниже:

$parser = new SqliteTableDefinitionParser; $parser->parseColumnDefinitions('x INTEGER PRIMARY KEY, y DOUBLE, z DATETIME default \'2011-11-10\', name VARCHAR(100)'); 

Источник

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.

Источник

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.

Источник