我想把我的iPhone应用程序迁移到一个新的数据库版本。由于我没有保存一些版本,我需要检查是否存在某些列名。

这个Stackoverflow条目建议执行选择

SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'

并解析结果。

这是常见的方式吗?选择呢?


当前回答

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

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
    }
}

其他回答

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

ex:

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

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

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;

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

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);
}

在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注入的影响!

     //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 )  
}