Android数据库提取查询(Android database fetch query)
private static final String TAG = "DBAdapter"; private static final String DATABASE_NAME = "jobDiagnosis"; private static final int DATABASE_VERSION = 2; Cursor cursor = null; private static final String TABLE_DATA = "create table Job_Saved (" + "Id text not null," + "Title text not null," + "Location text not null," + "State text not null," + "Company text not null," + "Description text not null," + "Status text not null," + "Username text not null," + "Password text not null)"; private final Context context; private DatabaseHelper dbHelper; private SQLiteDatabase db; private static DBAdapter instance; private DBAdapter(Context c) { System.out.println("Constructor of DB Adapter..."); this.context = c; System.out.println("Creating the object of db helper class.."); try { dbHelper = new DatabaseHelper(context); dbHelper.getWritableDatabase(); } catch (Exception e) { // TODO: handle exception Log.d("err", ""+e); } } public static DBAdapter getInstance(Context C) { if (null == instance) { instance = new DBAdapter(C); } return instance; } public void openReadableDatabase() throws SQLException { db = dbHelper.getReadableDatabase(); } public void openWritableDatabase() throws SQLException { db = dbHelper.getWritableDatabase(); } public void close() { dbHelper.close(); } public long DeleteLocation(String name) { return db.delete("Job_Saved", null, null); } public long insertlocation(String Id,String Title,String Location,String State,String Company,String Description,String value, String Username,String Password) { //DatabaseHelper d=new DatabaseHelper(DBAdapter.this); ContentValues initialValues = new ContentValues(); initialValues.put("Id", Id); initialValues.put("Title", Title); initialValues.put("Location", Location); initialValues.put("State", State); initialValues.put("Company",Company); initialValues.put("Description", Description); initialValues.put("Status", value); initialValues.put("Username", Username); initialValues.put("Password", Password); return db.insert("Job_Saved", null, initialValues); } public ArrayList<Data> getAllData(){ ArrayList<Data> arr = new ArrayList<Data>(); Cursor c = db.query("Job_Saved", null, null, null, null, null, null); if(c.getCount()>0){ while(c.moveToNext()){ Data f = new Data(); f.setid(c.getString(0)); f.setbusinessname(c.getString(1)); f.setcityname(c.getString(2)); f.setstatename(c.getString(3)); f.setcompanyname(c.getString(4)); f.setDesc(c.getString(5)); f.setStatus(c.getString(6)); f.setUser(c.getString(7)); //f.setCharging(c.getString(8)); arr.add(f); } } c.close(); return arr; } public void updateDownload(String id , String status) { ContentValues initialValues = new ContentValues(); initialValues.put("status", status); db.update("Likes", initialValues, "id=?", new String[] {id}); } public String getIdStatus(String id){ String a=""; Cursor c = db.query("Likes", null, "id='" + id + "'", null, null, null, null); if(c.getCount()>0){ while(c.moveToNext()) { a=c.getString(1); } } return a; } private class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); System.out.println("Constructor of DB Helper Class..."); } @Override public void onCreate(SQLiteDatabase db) { System.out.println("on create of database helper class.."); try { // Creating Database........................................... db.execSQL(TABLE_DATA); } catch (Exception e) { e.printStackTrace(); } } @Override public void onOpen(SQLiteDatabase db) { // DBAdapter.this.db=db; } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // Log.w(TAG, "Upgrading database from version " + oldVersion // + " to " // + newVersion + ", which will destroy all old data"); // db.execSQL("DROP TABLE IF EXISTS users"); // onCreate(db); } } public String getFolderkeywordname(String string) { String index = ""; Cursor c = db.query("folders", new String[] { "keyword" }, "name='" + string + "'", null, null, null, null); if(c.getCount()>0){ while(c.moveToNext()){ System.out.println("folder id is : " + index); index = c.getString(0); } } c.close(); return index; }
}
这是我的数据库适配器类。 我还定义了一个getter和setter类,但我不知道从数据库中获取值所以请帮助我。
我也想使用fetch和delete查询
任何帮助表示赞赏。 先谢谢你。
private static final String TAG = "DBAdapter"; private static final String DATABASE_NAME = "jobDiagnosis"; private static final int DATABASE_VERSION = 2; Cursor cursor = null; private static final String TABLE_DATA = "create table Job_Saved (" + "Id text not null," + "Title text not null," + "Location text not null," + "State text not null," + "Company text not null," + "Description text not null," + "Status text not null," + "Username text not null," + "Password text not null)"; private final Context context; private DatabaseHelper dbHelper; private SQLiteDatabase db; private static DBAdapter instance; private DBAdapter(Context c) { System.out.println("Constructor of DB Adapter..."); this.context = c; System.out.println("Creating the object of db helper class.."); try { dbHelper = new DatabaseHelper(context); dbHelper.getWritableDatabase(); } catch (Exception e) { // TODO: handle exception Log.d("err", ""+e); } } public static DBAdapter getInstance(Context C) { if (null == instance) { instance = new DBAdapter(C); } return instance; } public void openReadableDatabase() throws SQLException { db = dbHelper.getReadableDatabase(); } public void openWritableDatabase() throws SQLException { db = dbHelper.getWritableDatabase(); } public void close() { dbHelper.close(); } public long DeleteLocation(String name) { return db.delete("Job_Saved", null, null); } public long insertlocation(String Id,String Title,String Location,String State,String Company,String Description,String value, String Username,String Password) { //DatabaseHelper d=new DatabaseHelper(DBAdapter.this); ContentValues initialValues = new ContentValues(); initialValues.put("Id", Id); initialValues.put("Title", Title); initialValues.put("Location", Location); initialValues.put("State", State); initialValues.put("Company",Company); initialValues.put("Description", Description); initialValues.put("Status", value); initialValues.put("Username", Username); initialValues.put("Password", Password); return db.insert("Job_Saved", null, initialValues); } public ArrayList<Data> getAllData(){ ArrayList<Data> arr = new ArrayList<Data>(); Cursor c = db.query("Job_Saved", null, null, null, null, null, null); if(c.getCount()>0){ while(c.moveToNext()){ Data f = new Data(); f.setid(c.getString(0)); f.setbusinessname(c.getString(1)); f.setcityname(c.getString(2)); f.setstatename(c.getString(3)); f.setcompanyname(c.getString(4)); f.setDesc(c.getString(5)); f.setStatus(c.getString(6)); f.setUser(c.getString(7)); //f.setCharging(c.getString(8)); arr.add(f); } } c.close(); return arr; } public void updateDownload(String id , String status) { ContentValues initialValues = new ContentValues(); initialValues.put("status", status); db.update("Likes", initialValues, "id=?", new String[] {id}); } public String getIdStatus(String id){ String a=""; Cursor c = db.query("Likes", null, "id='" + id + "'", null, null, null, null); if(c.getCount()>0){ while(c.moveToNext()) { a=c.getString(1); } } return a; } private class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); System.out.println("Constructor of DB Helper Class..."); } @Override public void onCreate(SQLiteDatabase db) { System.out.println("on create of database helper class.."); try { // Creating Database........................................... db.execSQL(TABLE_DATA); } catch (Exception e) { e.printStackTrace(); } } @Override public void onOpen(SQLiteDatabase db) { // DBAdapter.this.db=db; } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // Log.w(TAG, "Upgrading database from version " + oldVersion // + " to " // + newVersion + ", which will destroy all old data"); // db.execSQL("DROP TABLE IF EXISTS users"); // onCreate(db); } } public String getFolderkeywordname(String string) { String index = ""; Cursor c = db.query("folders", new String[] { "keyword" }, "name='" + string + "'", null, null, null, null); if(c.getCount()>0){ while(c.moveToNext()){ System.out.println("folder id is : " + index); index = c.getString(0); } } c.close(); return index; }
}
This is my database adapter class. I have also defined a getter And setter class but i don't have any idea to fetch value from database So please help me.
I also want to use fetch and delete query
Any help is appreciated. Thank you in advance.
原文:https://stackoverflow.com/questions/19922694
最满意答案
您可以按
values
将df转换为numpy数组并reshape
。 然后你可以通过列表理解用范围设置新的列名:print resultsDf 0 0 100 1 -2800 2 -2800 3 -2800 0 -2800 1 -2800 2 -2900 3 -3000 0 -3000 1 -3000 2 -3000 3 -3000 0 -3000 1 -3000 2 -3000 3 -3000 df = pd.DataFrame((resultsDf.values).reshape((4, (resultsDf.values).shape[0]/4))) df.columns = ['C' + str(i) for i in range(1, len(df.columns) + 1) ] print df C1 C2 C3 C4 0 100 -2800 -2800 -2800 1 -2800 -2800 -2900 -3000 2 -3000 -3000 -3000 -3000 3 -3000 -3000 -3000 -3000
如果缺少最后一行(不将索引重复为其他行):
print resultsDf 0 0 100 1 -2800 2 -2800 3 -2800 4 -2800 5 -2800 6 -2900 7 -3000 8 -3000 9 -3000 10 -3000 11 -3000 12 -3000 13 -3000 14 -3000 15 -3000 0 100 1 -2800 2 -2800 3 -2800 4 -2800 5 -2800 6 -2900 7 -3000 8 -3000 9 -3000 10 -3000 11 -3000 12 -3000 13 -3000 14 -3000 15 -3000 0 -3100 1 25500
#use all df without last two rows - resultsDf[:-2] df = pd.DataFrame((resultsDf[:-2].values).reshape(16, resultsDf[:-2].values.shape[0]/16)) #append last two rows to new df df = pd.concat([df, resultsDf[-2:]], axis=1) df.columns = ['C' + str(i) for i in range(1, len(df.columns) + 1) ] print df C1 C2 C3 0 100 -2800 -3100 1 -2800 -2800 25500 2 -2800 -2800 NaN 3 -2900 -3000 NaN 4 -3000 -3000 NaN 5 -3000 -3000 NaN 6 -3000 -3000 NaN 7 -3000 -3000 NaN 8 100 -2800 NaN 9 -2800 -2800 NaN 10 -2800 -2800 NaN 11 -2900 -3000 NaN 12 -3000 -3000 NaN 13 -3000 -3000 NaN 14 -3000 -3000 NaN 15 -3000 -3000 NaN
You can convert df to numpy array by
values
andreshape
. then you can set new columns names by list comprehension with range:print resultsDf 0 0 100 1 -2800 2 -2800 3 -2800 0 -2800 1 -2800 2 -2900 3 -3000 0 -3000 1 -3000 2 -3000 3 -3000 0 -3000 1 -3000 2 -3000 3 -3000 df = pd.DataFrame((resultsDf.values).reshape((4, (resultsDf.values).shape[0]/4))) df.columns = ['C' + str(i) for i in range(1, len(df.columns) + 1) ] print df C1 C2 C3 C4 0 100 -2800 -2800 -2800 1 -2800 -2800 -2900 -3000 2 -3000 -3000 -3000 -3000 3 -3000 -3000 -3000 -3000
If you have last rows missing (not repeating indices as other rows):
print resultsDf 0 0 100 1 -2800 2 -2800 3 -2800 4 -2800 5 -2800 6 -2900 7 -3000 8 -3000 9 -3000 10 -3000 11 -3000 12 -3000 13 -3000 14 -3000 15 -3000 0 100 1 -2800 2 -2800 3 -2800 4 -2800 5 -2800 6 -2900 7 -3000 8 -3000 9 -3000 10 -3000 11 -3000 12 -3000 13 -3000 14 -3000 15 -3000 0 -3100 1 25500
#use all df without last two rows - resultsDf[:-2] df = pd.DataFrame((resultsDf[:-2].values).reshape(16, resultsDf[:-2].values.shape[0]/16)) #append last two rows to new df df = pd.concat([df, resultsDf[-2:]], axis=1) df.columns = ['C' + str(i) for i in range(1, len(df.columns) + 1) ] print df C1 C2 C3 0 100 -2800 -3100 1 -2800 -2800 25500 2 -2800 -2800 NaN 3 -2900 -3000 NaN 4 -3000 -3000 NaN 5 -3000 -3000 NaN 6 -3000 -3000 NaN 7 -3000 -3000 NaN 8 100 -2800 NaN 9 -2800 -2800 NaN 10 -2800 -2800 NaN 11 -2900 -3000 NaN 12 -3000 -3000 NaN 13 -3000 -3000 NaN 14 -3000 -3000 NaN 15 -3000 -3000 NaN
相关问答
更多-
TCP/IP模型是一个________。[2023-10-02]
a -
下列中不属于面向对象的编程语言的是?[2022-05-30]
a -
为方便起见,我建议使用pd.date_range生成索引,然后将.loc与Timestamp或datetime对象一起使用。 from datetime import datetime import pandas as pd start = datetime(2015, 10, 1, 0, 0, 0) end = datetime(2015, 10, 1, 4, 0, 0) dates = pd.date_range(start, end, freq='H') df = pd.DataFrame(ind ...
-
只是为了在这里得到答案并希望更多地理解这个问题,这里有一种方法可以删除没有9的行,并使用您的样本数据删除前9个之前的所有列。 # make data into pd.DataFrame df = pd.DataFrame.from_dict(data) # make all columns strings df.columns = df.columns.astype(str) # sort the columns df.sort_index(axis = 1, inplace = True) # dr ...
-
您可以使用DataFrame.mask: (2 ** dfA).mask(dfB, 2 ** (dfA+1)) Out: 0 1 2 0 4 4 8 1 16 64 64 这将检查dfB,如果为True,则从原始DataFrame( (2 ** dfA) )获取值,如果为false,则从另一个中获取值( 2 ** (dfA+1) )。 这类似于numpy的功能: np.where(dfB, 2 ** (dfA+1), 2 ** (dfA)) Out: array([[ ...
-
您可以使用indexer_between_time进行indexer_between_time : In [11]: data.index.indexer_between_time(start='01:15', end='02:00') Out[11]: array([5, 6, 7, 8]) In [12]: data.iloc[data.index.indexer_between_time(start='1:15', end='02:00')] Out[12]: ...
-
根据索引选择固定的数据帧范围并附加到新的数据帧(selecting a fixed dataframe range based upon index and appending to a new dataframe)[2021-09-22]
您可以按values将df转换为numpy数组并reshape 。 然后你可以通过列表理解用范围设置新的列名: print resultsDf 0 0 100 1 -2800 2 -2800 3 -2800 0 -2800 1 -2800 2 -2900 3 -3000 0 -3000 1 -3000 2 -3000 3 -3000 0 -3000 1 -3000 2 -3000 3 -3000 df = pd.DataFrame((resultsDf.values).reshape(( ... -
根据熊猫数据帧的索引从稀疏矩阵中选择行(Selecting rows from a sparse matrix based on the index of a panda dataframe)[2022-02-14]
不知何故找到了解决这个问题的更快方法。 首先创建catalogue索引=> collection索引的字典。 index_dict = dict(zip( catalogue.index.values.tolist(), range(collection.shape[0]))) 然后我的collection_filter_row变成了 def collection_filter_row(catalogue_filtered, index_dict, collection): ret ... -
从某个索引值开始选择pandas数据帧中的行(selecting rows in a pandas dataframe starting with a certain index value)[2023-02-21]
我的快速回答 s = df.Date.searchsorted(pd.to_datetime('2017-2-28'))[0] df.iloc[s:s + 10] 演示 df = pd.DataFrame(dict( Date=pd.date_range('2017-01-31', periods=90, freq='B'), ClosingPrice=np.random.rand(90) )).iloc[:, ::-1] date = pd.to_datetim ... -
使用Pandas Dataframe创建相对于booelan索引的范围(Creating a range relative to a booelan index using Pandas Dataframe)[2022-07-09]
您可以将bfill (向后填充)方法与limit参数一起使用: df.shift().where(df.diff() != 0, pd.np.nan).bfill(limit=2).fillna(0).astype(bool) You might use the bfill (backward fill) method with a limit parameter: df.shift().where(df.diff() != 0, pd.np.nan).bfill(limit=2).fillna(0).a ...