如何在Sqlite3数据库上获得列名列表?

小开

PRAGMA table_info(table_name);

会给你一个列有所有列名的列表。

小开

如果你有sqlite数据库，使用sqlite3命令行程序和这些命令:

列出数据库中所有的表。

.tables

要显示给定tablename的模式:

.schema tablename

小开

如果您正在搜索任何特定的列，您可以使用Like语句

例:

SELECT * FROM sqlite_master where sql like('%LAST%')

小开

-(NSMutableDictionary*)tableInfo:(NSString *)table
{
sqlite3_stmt *sqlStatement;
NSMutableDictionary *result = [[NSMutableDictionary alloc] init];
const char *sql = [[NSString stringWithFormat:@"pragma table_info('%s')",[table UTF8String]] UTF8String];
if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)
{
NSLog(@"Problem with prepare statement tableInfo %@",[NSString stringWithUTF8String:(const char *)sqlite3_errmsg(db)]);


}
while (sqlite3_step(sqlStatement)==SQLITE_ROW)
{
[result setObject:@"" forKey:[NSString stringWithUTF8String:(char*)sqlite3_column_text(sqlStatement, 1)]];


}


return result;
}

小开

为了获得列信息，你可以使用下面的代码片段:

String sql = "select * from "+oTablename+" LIMIT 0";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
ResultSetMetaData mrs = rs.getMetaData();
for(int i = 1; i <= mrs.getColumnCount(); i++)
{
Object row[] = new Object[3];
row[0] = mrs.getColumnLabel(i);
row[1] = mrs.getColumnTypeName(i);
row[2] = mrs.getPrecision(i);
}

小开

//JUST little bit modified the answer of giuseppe  which returns array of table columns
+(NSMutableArray*)tableInfo:(NSString *)table{


sqlite3_stmt *sqlStatement;


NSMutableArray *result = [NSMutableArray array];


const char *sql = [[NSString stringWithFormat:@"PRAGMA table_info('%@')",table] UTF8String];


if(sqlite3_prepare(md.database, sql, -1, &sqlStatement, NULL) != SQLITE_OK)


{
NSLog(@"Problem with prepare statement tableInfo %@",
[NSString stringWithUTF8String:(const char *)sqlite3_errmsg(md.database)]);


}


while (sqlite3_step(sqlStatement)==SQLITE_ROW)
{
[result addObject:
[NSString stringWithUTF8String:(char*)sqlite3_column_text(sqlStatement, 1)]];
}


return result;
}

小开

只是为了像我这样的超级菜鸟想知道人们是如何或什么意思

PRAGMA table_info('table_name')

你想使用它作为你的准备语句，如下所示。这样做将选择一个与此类似的表，只是填充了属于您的表的值。

cid         name        type        notnull     dflt_value  pk
----------  ----------  ----------  ----------  ----------  ----------
0           id          integer     99                      1
1           name                    0                       0

其中id和name是列的实际名称。所以要得到这个值，你需要使用以下命令选择列名:

//returns the name
sqlite3_column_text(stmt, 1);
//returns the type
sqlite3_column_text(stmt, 2);

它将返回当前行的列名。为了获取它们或找到你想要的，你需要遍历所有行。最简单的方法是采用下面的方式。

//where rc is an int variable if wondering :/
rc = sqlite3_prepare_v2(dbPointer, "pragma table_info ('your table name goes here')", -1, &stmt, NULL);


if (rc==SQLITE_OK)
{
//will continue to go down the rows (columns in your table) till there are no more
while(sqlite3_step(stmt) == SQLITE_ROW)
{
sprintf(colName, "%s", sqlite3_column_text(stmt, 1));
//do something with colName because it contains the column's name
}
}

小开

我知道已经太迟了，但这会帮助其他人。

要找到表的列名，您应该执行select * from tbl_name，并将得到结果sqlite3_stmt *。并检查列对所获取的总列的迭代。请参考以下代码相同。

// sqlite3_stmt *statement ;
int totalColumn = sqlite3_column_count(statement);
for (int iterator = 0; iterator<totalColumn; iterator++) {
NSLog(@"%s", sqlite3_column_name(statement, iterator));
}

这将打印结果集的所有列名。

小开

< p > . schema 在sqlite控制台中，你在表中在我看来是这样的……< / p >

sqlite>.schema
CREATE TABLE players(
id integer primary key,
Name varchar(255),
Number INT,
Team varchar(255)

小开

如果有的话

.headers ON

你会得到你想要的结果。

小开

function getDetails(){
var data = [];
dBase.executeSql("PRAGMA table_info('table_name') ", [], function(rsp){
if(rsp.rows.length > 0){
for(var i=0; i<rsp.rows.length; i++){
var o = {
name: rsp.rows.item(i).name,
type: rsp.rows.item(i).type
}
data.push(o);
}
}
alert(rsp.rows.item(0).name);


},function(error){
alert(JSON.stringify(error));
});
}

小开

当你运行sqlite3命令行，输入:

sqlite3 -header

也会给你想要的结果吗

小开

如果你想让你的查询输出包含列名，并正确对齐为列，使用这些命令在sqlite3:

.headers on
.mode column

你会得到如下输出:

sqlite> .headers on
sqlite> .mode column
sqlite> select * from mytable;
id          foo         bar
----------  ----------  ----------
1           val1        val2
2           val3        val4

小开

获得这里没有提到的列名列表的另一种方法是，从PRAGMA_TABLE_INFO()表值函数中进行选择，这种方法是跨平台的，不依赖于sqlite3.exe shell。

SELECT name FROM PRAGMA_TABLE_INFO('your_table');
name
tbl_name
rootpage
sql

您可以通过查询来检查某个列是否存在:

SELECT 1 FROM PRAGMA_TABLE_INFO('your_table') WHERE name='column1';
1

如果你不想解析select sql from sqlite_master或pragma table_info的结果，你可以使用它。

注意此特性是实验性的，在SQLite 3.16.0(2017-01-02)版本中添加。

参考:

https://www.sqlite.org/pragma.html#pragfunc

小开

要获得列的列表，您可以简单地使用:

.schema tablename

小开

我知道这是一个旧的帖子，但最近我需要同样的帖子，并找到了一个简洁的方法:

SELECT c.name FROM pragma_table_info('your_table_name') c;

小开

# EYZ0

这将从数据库中列出表的列名。

希望这能有所帮助!!

小开

     //Called when application is started. It works on Droidscript, it is tested
function OnStart()
{
//Create a layout with objects vertically centered.
lay = app.CreateLayout( "linear", "VCenter,FillXY" );


//Create a text label and add it to layout.
txt = app.CreateText( "", 0.9, 0.4, "multiline" )
lay.AddChild( txt );
app.AddLayout(lay);


db = app.OpenDatabase( "MyData" )
  

//Create a table (if it does not exist already).
db.ExecuteSql( "drop table if exists test_table" )
db.ExecuteSql( "CREATE TABLE IF NOT EXISTS test_table " +
"(id integer primary key, data text, num integer)",[],null, OnError )
db.ExecuteSql( "insert into test_table values (1,'data10',100),
(2,'data20',200),(3,'data30',300)")
//Get all the table rows.
DisplayAllRows("SELECT * FROM test_table");
DisplayAllRows("select *, id+100 as idplus, 'hahaha' as blabla from
test_table order by id desc;")
}


//function to display all records
function DisplayAllRows(sqlstring)  // <-- can you use for any table not need to
//  know column names, just use a *
// example:
{
//Use all rows what is in ExecuteSql  (try any, it will works fine)
db.ExecuteSql( sqlstring, [], OnResult, OnError )
}
//Callback to show query results in debug.
function OnResult( res )
{
var len = res.rows.length;
var s = txt.GetText();
// ***********************************************************************
// This is the answer how to read column names from table:
for(var ColumnNames in res.rows.item(0)) s += " [ "+ ColumnNames +" ] "; // "[" & "]" optional, i use only in this demo
// ***********************************************************************
//app.Alert("Here is all Column names what Select from your table:\n"+s);
s+="\n";
for(var i = 0; i < len; i++ )
{
var rows = res.rows.item(i)
for (var item in rows)
{
s += "    " + rows[item] + "   ";
}
s+="\n\n";
}
//app.Alert(s);
txt.SetText( s )
}
//Callback to show errors.
function OnError( msg )
{
app.Alert( "Error: " + msg )
}

小开

如果您正在使用SQLite3，则不支持INFORMATION_SCHEMA。使用PRAGMA table_info代替。这将返回关于表的6行信息。要获取列名(row2)，使用如下所示的for循环

cur.execute("PRAGMA table_info(table_name)")  # fetches the 6 rows of data
records = cur.fetchall()
print(records)
for row in records:
print("Columns: ", row[1])

小开

我能够通过使用一个sql查询检索具有相应列的表名，但列输出是逗号分隔的。我希望它能帮助到一些人

SELECT tbl_name, (SELECT GROUP_CONCAT(name, ',') FROM PRAGMA_TABLE_INFO(tbl_name)) as columns FROM sqlite_schema WHERE type = 'table';

小开

在Python中使用sqlite3

上面的回答 PRAGMA table_info()返回一个元组列表，它可能不适合进一步处理，例如:

[(0, 'id', 'INTEGER', 0, None, 0),
(1, 'name', 'TEXT', 0, None, 0),
(2, 'age', 'INTEGER', 0, None, 0),
(3, 'profession', 'TEXT', 0, None, 0)]

在Python中使用sqlite3时，只需在最后添加列表理解以过滤掉不需要的信息。

import sqlite3 as sq


def col_names(t_name):
with sq.connect('file:{}.sqlite?mode=ro'.format(t_name),uri=True) as conn:
cursor = conn.cursor()
cursor.execute("PRAGMA table_info({}) ".format(t_name))
data = cursor.fetchall()
return [i[1] for i in data]


col_names("your_table_name")

结果

["id","name","age","profession"]

免责声明:请勿在生产环境中使用，因为此代码段可能会受到SQL注入的影响!

小开

这是一个老问题，但这里有一个替代答案，检索SQLite数据库中的所有列，并为每列提供关联表的名称:

WITH tables AS (SELECT name tableName, sql
FROM sqlite_master WHERE type = 'table' AND tableName NOT LIKE 'sqlite_%')
SELECT fields.name, fields.type, tableName
FROM tables CROSS JOIN pragma_table_info(tables.tableName) fields

返回这样的结果:

{
"name": "id",
"type": "integer",
"tableName": "examples"
}, {
"name": "content",
"type": "text",
"tableName": "examples"
}

对于包含标识符和字符串内容的简单表。

小开

获取一个表和列的列表作为视图:

CREATE VIEW Table_Columns AS
SELECT m.tbl_name AS TableView_Name, m.type AS TableView, cid+1 AS Column, p.*
FROM sqlite_master m, Pragma_Table_Info(m.tbl_name) p
WHERE m.type IN ('table', 'view') AND
( m.tbl_name = 'mypeople' OR m.tbl_name LIKE 'US_%')   -- filter tables
ORDER BY m.tbl_name;

小开

下面的命令设置列名:

.header on

然后，它是这样的:

sqlite> select * from user;
id|first_name|last_name|age
1|Steve|Jobs|56
2|Bill|Gates|66
3|Mark|Zuckerberg|38

下面的命令将取消列名:

.header off

然后，它是这样的:

sqlite> select * from user;
1|Steve|Jobs|56
2|Bill|Gates|66
3|Mark|Zuckerberg|38

这些命令显示命令“。header":

.help .header

或者:

.help header

然后，它是这样的:

sqlite> .help .header
.headers on|off          Turn display of headers on or off

此外，下面的命令设置输出模式“box":

.mode box

然后，它是这样的:

sqlite> select * from user;
┌────┬────────────┬────────────┬─────┐
│ id │ first_name │ last_name  │ age │
├────┼────────────┼────────────┼─────┤
│ 1  │ Steve      │ Jobs       │ 56  │
│ 2  │ Bill       │ Gates      │ 66  │
│ 3  │ Mark       │ Zuckerberg │ 38  │
└────┴────────────┴────────────┴─────┘

下面的命令设置输出模式“表”;:

.mode table

然后，它是这样的:

sqlite> select * from user;
+----+------------+------------+-----+
| id | first_name | last_name  | age |
+----+------------+------------+-----+
| 1  | Steve      | Jobs       | 56  |
| 2  | Bill       | Gates      | 66  |
| 3  | Mark       | Zuckerberg | 38  |
+----+------------+------------+-----+

这些命令显示命令“。mode”的详细信息;:

.help .mode

或者:

.help mode

然后，它是这样的:

sqlite> .help .mode
.import FILE TABLE       Import data from FILE into TABLE
Options:
--ascii               Use \037 and \036 as column and row separators
--csv                 Use , and \n as column and row separators
--skip N              Skip the first N rows of input
--schema S            Target table to be S.TABLE
-v                    "Verbose" - increase auxiliary output
Notes:
*  If TABLE does not exist, it is created.  The first row of input
determines the column names.
*  If neither --csv or --ascii are used, the input mode is derived
from the ".mode" output mode
*  If FILE begins with "|" then it is a command that generates the
input text.
.mode MODE ?OPTIONS?     Set output mode
MODE is one of:
ascii       Columns/rows delimited by 0x1F and 0x1E
box         Tables using unicode box-drawing characters
csv         Comma-separated values
column      Output in columns.  (See .width)
html        HTML <table> code
insert      SQL insert statements for TABLE
json        Results in a JSON array
line        One value per line
list        Values delimited by "|"
markdown    Markdown table format
qbox        Shorthand for "box --width 60 --quote"
quote       Escape answers as for SQL
table       ASCII-art table
tabs        Tab-separated values
tcl         TCL list elements
OPTIONS: (for columnar modes or insert mode):
--wrap N       Wrap output lines to no longer than N characters
--wordwrap B   Wrap or not at word boundaries per B (on/off)
--ww           Shorthand for "--wordwrap 1"
--quote        Quote output text as SQL literals
--noquote      Do not quote output text
TABLE          The name of SQL table used for "insert" mode