Flutter (dart) Delete by Sqflite
Solution 1
String q = "DELETE FROM Test WHERE id= '$id'";
Solution 2
I've made this simple helper file (like you have done) for a little app that uses sqflite as backend in local. It's complete: it accesses to the local file system to get grant permissions, it opens the file for write , and you can make also Batch updates:
import 'dart:async';
import 'package:sqflite/sqflite.dart';
class DBClient {
final logger = Log.getLogger('\u{1F5AB} DBClient '); // a little logger utility, you can substitute with the simple 'print(...)' function.
static final String dbFilename = 'my_database.db';
final Permission permission;
Database _database;
bool openingDB = false;
DBClient(this.permission) : assert(permission != null);
Batch batch() {
return _database.batch();
}
Future<void> init() async {
if (_database == null && !openingDB) {
String path;
try {
path = await permission.getAbsoluteFileName(dbFilename);
} on Error {
logger.e('Error: cannot open db');
rethrow;
}
openingDB = true;
logger.d('Opening database on path: $path');
_database = await openDatabase(path, version: 1,
onCreate: (Database db, int version) async {
await db.execute("""
CREATE TABLE IF NOT EXISTS ......
""");
await db.execute("""
CREATE TABLE IF NOT EXISTS ....
""");
....
await db.execute("""
CREATE TABLE IF NOT EXISTS ....
""");
});
}
}
Future<int> insert(String tableName, Map<String, dynamic> json) async =>
_database.insert(tableName, json);
Future<List<Map<String, dynamic>>> query(
String tableName, {
String where,
List<dynamic> whereArgs,
}) async {
return _database.query(
tableName,
where: where,
whereArgs: whereArgs,
);
}
Future<int> update(String tableName, Map<String, dynamic> json,
{String where, List<dynamic> whereArgs}) async {
return _database.update(
tableName,
json,
where: where,
whereArgs: whereArgs,
);
}
Future<int> delete(String tableName,
{String where, List<dynamic> whereArgs}) async {
return _database.delete(
tableName,
where: where,
whereArgs: whereArgs,
);
}
}
You can use in this manner:
deleted = await db.delete(Place.tableName(),
where: "name = ?", whereArgs: [name]);
Hint: in the init() method you can use AsyncMemoizer class, it is better to grant that the method is called only once (at the time I wrote the app I didn't do it....)
Hereafter my (very old) dependencies:
dependencies:
date_utils: ^0.1.0+3
equatable: ^1.1.1
logger: ^0.8.3
permission_handler: ^5.0.0+hotfix.3
quiver: ^2.1.3
sqflite: ^1.3.0
path_provider: ^1.6.5
flutter:
sdk: flutter
Phandomas
Updated on December 26, 2022Comments
-
Phandomas over 1 year
I need help :(
I'm trying to delete Data from a Sqflite Database. I can insert and get the data to show, but I cannot delete it if I do a mistake in saving it.
I have my DB:
import 'package:sqflite/sqflite.dart' as sql; import 'package:path/path.dart' as path; import 'package:sqflite/sqflite.dart'; //SQFLITE class DBHelper { static Future<sql.Database> database() async { final dbPath = await sql.getDatabasesPath(); //einmal die Datenbank erstellen in einer Variablen return sql.openDatabase(path.join(dbPath, 'wanderwege.db'), onCreate: (db, version) { return db.execute( 'CREATE TABLE user_places(id TEXT PRIMARY KEY, title TEXT, image TEXT, createTime TEXT, place TEXT, description TEXT)'); // 'CREATE TABLE user_places(id TEXT PRIMARY KEY, title TEXT, image TEXT, loc_lat REAL, loc_lng REAL, address TEXT)'); }, version: 1); } //Da der Eintrag dauern kann bis es in die Daten gespeichert weerden = Future + async static Future<void> insert(String table, Map<String, Object> data) async { final db = await DBHelper.database(); db.insert(table, data, conflictAlgorithm: sql.ConflictAlgorithm.replace); } // Delete data from table // deleteData(table, itemId) async { // final db = await DBHelper.database(); // return await db.rawDelete("DELETE FROM $table WHERE id = $itemId"); // } //Methode um Einträge zu holen static Future<List<Map<String, dynamic>>> getData(String table) async { final db = await DBHelper.database(); return db.query(table); } }
Then I have my Places:
import 'dart:io'; import 'package:flutter/widgets.dart'; import 'package:places/helpers/db_helper.dart'; import 'package:places/models/place.dart'; import 'package:intl/intl.dart'; class GreatPlaces with ChangeNotifier { //To set Creation Time static DateTime actualTime = DateTime.now(); String formattedDate = DateFormat('dd-MM-yyyy').format(actualTime); List<Place> _items = []; List<Place> get items { return [..._items]; } //Information des gesamten places Place findById(String id) { return items.firstWhere((place) => place.id == id); } Future<void> addPlace( String pickedTitle, File pickedImage, String pickedDate, String pickedLocation, String pickedDescription, //PlaceLocation pickedLocation, ) async { //final address = await LocationHelper.getPlaceAddress( // pickedLocation.latitude, pickedLocation.longitude); // final updatedLocation = PlaceLocation( // latitude: pickedLocation.latitude, // longitude: pickedLocation.longitude, // address: address); final newPlace = Place( id: DateTime.now().toString(), image: pickedImage, title: pickedTitle, createTime: pickedDate, place: pickedLocation, description: pickedDescription, ); //location: null); _items.add(newPlace); notifyListeners(); //übergabe 'wanderwege' so wie in db_helper definiert , Data ist von typ map DBHelper.insert('user_places', { 'id': newPlace.id, 'title': newPlace.title, 'image': newPlace.image.path, 'createTime': newPlace.createTime, 'place': newPlace.place, 'description': newPlace.description // 'loc_lat': newPlace.location.latitude, // 'loc_lng': newPlace.location.longitude, // 'address': newPlace.location.address, }); } //Die ganzen places aus der DB holen Future<void> fetchAndSetPlaces() async { final dataList = await DBHelper.getData('user_places'); _items = dataList .map((item) => Place( id: item['id'], title: item['title'], image: File(item['image']), createTime: item['createTime'], place: item['place'], description: item['description'], //location: null // latitude: item['loc_lat'], // longitude: item['loc_lat'], // address: item['address']), )) .toList(); notifyListeners(); } }
and now on the screen where all the Places listed I want to implement a delete function, but I don't get how it works... tried so much from youtube and the documentation from Sqflite, but I don't get it.
I hope someone can help me.
Greetings :)
-
Randal Schwartz over 2 yearsUse a placeholder!
-
Jeremy Caney over 2 yearsCan you offer an explanation on what this does and why this solves the problem? See the previous answer for a good example of the level of detail expected on Stack Overflow.
-
Pranay Dhongade over 2 yearsI don't have to explain anything. It's just the right format of that request. You can google it.