7/25/24, 4:29 PM ex02-Query Table Information.
ipynb - Colab
keyboard_arrow_down ex02-Query Table Information
This notebook will show how to query the number of tables and a table'columns given a database using the powerful SELECT.
%load_ext sql
keyboard_arrow_down Connet to the given database of demo.db3
%sql sqlite:///data/demo.db3
u'Connected: @data/demo.db3'
keyboard_arrow_down List tables in a database
Table and index names can be list by doing a SELECT on a special table named "SQLITE_MASTER". Every SQLite database has an
SQLITE_MASTER table that defines the schema for the database. For tables, the type field will always be 'table' and the name field will be the
name of the table. So to get a list of all tables in the database, use the following SELECT command:
See more at [Link]
%sql SELECT name FROM sqlite_master WHERE type='table'
* sqlite:///data/demo.db3
Done.
name
rch
hru
sub
sed
watershed_daily
watershed_monthly
watershed_yearly
channel_dimension
hru_info
sub_info
rch_info
ave_plant
ave_annual_hru
ave_monthly_basin
ave_annual_basin
keyboard_arrow_down List columns in a table
We take the table of rch as an example
%sql select * from rch where 1=0
* sqlite:///data/demo.db3
Done.
RCH YR MO FLOW_INcms FLOW_OUTcms EVAPcms TLOSScms SED_INtons SED_OUTtons SEDCONCmg_kg ORGN_INkg ORGN_OUTkg ORGP_INkg ORGP
keyboard_arrow_down :) We get the table information just using SELECT * with key work of LIMIT
You can try to change the limit number to 1, 2 to check the results.
%sql select * from rch LIMIT 0
* sqlite:///data/demo.db3
Done.
RCH YR MO FLOW_INcms FLOW_OUTcms EVAPcms TLOSScms SED_INtons SED_OUTtons SEDCONCmg_kg ORGN_INkg ORGN_OUTkg ORGP_INkg ORGP
[Link] 1/2
7/25/24, 4:29 PM ex02-Query Table [Link] - Colab
keyboard_arrow_down Or directly use selet *
Warning! This table contains too many records. It'd better not query all of them.
%sql select * from rch
Start coding or generate with AI.
[Link] 2/2
