Questions tagged [sqlite]
SQLite is an open-source software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.
94,916
questions
3379
votes
12
answers
498k
views
Improve INSERT-per-second performance of SQLite
Optimizing SQLite is tricky. Bulk-insert performance of a C application can vary from 85 inserts per second to over 96,000 inserts per second!
Background: We are using SQLite as part of a desktop ...
1377
votes
19
answers
1.2m
views
How can I list the tables in a SQLite database file that was opened with ATTACH?
What SQL can be used to list the tables, and the rows within those tables in an SQLite database file – once I have attached it with the ATTACH command on the sqlite3 command line tool?
- 50.9k
1113
votes
32
answers
606k
views
How do I check in SQLite whether a table exists?
How do I, reliably, check in SQLite, whether a particular user table exists?
I am not asking for unreliable ways like checking if a "select *" on the table returned an error or not (is this even a ...
- 24.8k
751
votes
9
answers
370k
views
SQLite primary key on multiple columns
What is the syntax for specifying a primary key on more than 1 column in SQLite ?
726
votes
10
answers
239k
views
How can I avoid concurrency problems when using SQLite on Android?
What would be considered the best practices when executing queries on an SQLite database within an Android app?
Is it safe to run inserts, deletes and select queries from an AsyncTask's ...
- 42.8k
667
votes
26
answers
514k
views
How to insert multiple rows in SQLite?
In MySQL you can insert multiple rows like this:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
...
- 233k
625
votes
19
answers
387k
views
UPSERT *not* INSERT or REPLACE
http://en.wikipedia.org/wiki/Upsert
Insert Update stored proc on SQL Server
Is there some clever way to do this in SQLite that I have not thought of?
Basically I want to update three out of four ...
- 8,644
602
votes
25
answers
806k
views
How to get a list of column names on Sqlite3 database?
I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist.
This Stackoverflow entry suggests doing the select
...
- 6,561
544
votes
17
answers
185k
views
What 'additional configuration' is necessary to reference a .NET 2.0 mixed mode assembly in a .NET 4.0 project?
I have a project in which I'd like to use some of the .NET 4.0 features but a core requirement is that I can use the System.Data.SQLite framework which is compiled against 2.X. I see mention of this ...
- 17.3k
501
votes
9
answers
262k
views
Is there an SQLite equivalent to MySQL's DESCRIBE [table]?
I'm just getting started learning SQLite. It would be nice to be able to see the details for a table, like MySQL's DESCRIBE [table]. PRAGMA table_info [table] isn't good enough, as it only has basic ...
- 28.6k
420
votes
7
answers
394k
views
How do I add a new column in between two columns?
I have a table with columns name, qty, rate. I need to add a new column COLNew in between the name and qty columns. How do I add a new column in between two columns?
- 4,575
384
votes
8
answers
290k
views
How to get Top 5 records in SqLite?
I have tried this which did not work.
select top 5 * from [Table_Name]
- 60.1k
384
votes
8
answers
384k
views
Store boolean value in SQLite
What is the type for a BOOL value in SQLite? I want to store in my table TRUE/FALSE values.
I could create a column of INTEGER and store in it values 0 or 1, but it won't be the best way to implement ...
- 53.1k
370
votes
8
answers
240k
views
How to set Sqlite3 to be case insensitive when string comparing?
I want to select records from sqlite3 database by string matching. But if I use '=' in the where clause, I found that sqlite3 is case sensitive. Can anyone tell me how to use string comparing case-...
- 4,141
370
votes
9
answers
211k
views
What are the performance characteristics of sqlite with very large database files? [closed]
2020 update, about 11 years after the question was posted and later closed, preventing newer answers.
Almost everything written here is obsolete. Once upon a time sqlite was limited to the memory ...
- 7,794
360
votes
9
answers
564k
views
INSERT IF NOT EXISTS ELSE UPDATE?
I've found a few "would be" solutions for the classic "How do I insert a new record or update one if it already exists" but I cannot get any of them to work in SQLite.
I have a table defined as ...
- 6,486
356
votes
2
answers
281k
views
Create table in SQLite only if it doesn't exist already
I want to create a table in a SQLite database only if doesn't exist already. Is there any way to do this? I don't want to drop the table if it exists, only create it if it doesn't.
- 3,971
347
votes
23
answers
422k
views
How to delete or add column in SQLITE?
I want to delete or add column in sqlite database
I am using following query to delete column.
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME
But it gives error
System.Data.SQLite.SQLiteException: ...
- 3,661
337
votes
5
answers
226k
views
How to use an existing database with an Android application [duplicate]
I have already created an SQLite database. I want to use this database file with my Android project. I want to bundle this database with my application.
Instead of creating a new database, how can ...
- 11.7k
336
votes
9
answers
316k
views
Java and SQLite [closed]
I'm attracted to the neatness that a single file database provides. What driver/connector library is out there to connect and use SQLite with Java.
I've discovered a wrapper library, http://www.ch-...
- 9,255
335
votes
16
answers
222k
views
How do I rename a column in a SQLite database table?
I would need to rename a few columns in some tables in a SQLite database.
I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of ...
- 9,764
331
votes
38
answers
600k
views
How do I unlock a SQLite database?
When I enter this query:
sqlite> DELETE FROM mails WHERE (id = 71);
SQLite returns this error:
SQL error: database is locked
How do I unlock the database so this query will work?
- 8,277
320
votes
15
answers
138k
views
When does SQLiteOpenHelper onCreate() / onUpgrade() run?
I have created my tables in my SQLiteOpenHelper onCreate() but receive
SQLiteException: no such table
or
SQLiteException: no such column
errors. Why?
NOTE:
(This is the amalgamated summary ...
317
votes
4
answers
95k
views
Core Data vs SQLite 3 [closed]
I am already quite familiar with relational databases and have used SQLite (and other databases) in the past. However, Core Data has a certain allure, so I am considering spending some time to learn ...
- 5,388
313
votes
8
answers
368k
views
How can one see the structure of a table in SQLite? [duplicate]
How can I see the structure of table in SQLite as desc was in Oracle?
- 11.1k
306
votes
5
answers
113k
views
Version of SQLite used in Android?
What is the version of SQLite used in Android?
Reason: I'm wondering how to handle schema migrations. The newer SQLite versions support an "ALTER TABLE" SQL command which would save me having to copy ...
- 10.8k
299
votes
3
answers
319k
views
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied
def insert(array):
connection=sqlite3.connect('images.db')
cursor=connection.cursor()
cnt=0
while cnt != len(array):
img = array[cnt]
print(array[cnt])
...
- 3,375
291
votes
15
answers
79k
views
Any good ORM tools for Android development? [closed]
Anyone working on the Android ('gPhone') have or know of a place where I can find a good ORM tool for it? The code is written in Java, and the database is SQLite. What I would like to find is a tool ...
- 24.2k
271
votes
12
answers
121k
views
Is there a .NET/C# wrapper for SQLite? [closed]
I'd sort of like to use SQLite from within C#.Net, but I can't seem to find an appropriate library. Is there one? An official one? Are there other ways to use SQLite than with a wrapper?
263
votes
19
answers
92k
views
sqlite3-ruby install error on Ubuntu
I have the following error during sqlite3-ruby install:
Building native extensions. This could take a while...
ERROR: Error installing sqlite3-ruby:
ERROR: Failed to build gem native extension.
...
- 7,330
263
votes
49
answers
270k
views
Unable to load DLL 'SQLite.Interop.dll'
Periodically I am getting the following exception:
Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
I am using 1.0.82.0. version,...
- 3,079
262
votes
17
answers
365k
views
Quick easy way to migrate SQLite3 to MySQL? [closed]
Anyone know a quick easy way to migrate a SQLite3 database to MySQL?
- 3,605
260
votes
5
answers
143k
views
Does it matter what extension is used for SQLite database files?
Are there advantages or disadvantages to the file extension used for SQLite databases?
It seems that SQLite itself does not require a naming convention, but there might be other reasons that a ...
- 35.6k
256
votes
9
answers
156k
views
SQLite Concurrent Access
Does SQLite3 safely handle concurrent access by multiple processes
reading/writing from the same DB? Are there any platform exceptions to that?
- 2,569
250
votes
7
answers
170k
views
SQLite: How do I save the result of a query as a CSV file?
Is there a way I can export the results of a query into a CSV file?
- 20.5k
247
votes
5
answers
278k
views
"Insert if not exists" statement in SQLite
I have an SQLite database. I am trying to insert values (users_id, lessoninfo_id) in table bookmarks, only if both do not exist before in a row.
INSERT INTO bookmarks(users_id,lessoninfo_id)
VALUES(
...
- 2,613
247
votes
7
answers
230k
views
Creating stored procedure in SQLite
Is it somehow possible to create a stored procedure when using SQLite?
- 12.5k
246
votes
2
answers
203k
views
How to retrieve inserted id after inserting row in SQLite using Python?
How to retrieve inserted id after inserting row in SQLite using Python? I have table like this:
id INT AUTOINCREMENT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)
I insert a new row with ...
- 2,463
244
votes
9
answers
208k
views
Best way to work with dates in Android SQLite [closed]
I'm having some trouble working with dates on my Android application that uses SQLite.
I have a couple questions:
What type should I use to store dates in SQLite (text, integer, ...)?
Given the best ...
- 3,398
241
votes
3
answers
277k
views
How to concatenate strings with padding in sqlite
I have three columns in an sqlite table:
Column1 Column2 Column3
A 1 1
A 1 2
A 12 2
C 13 2
B ...
- 3,391
241
votes
8
answers
196k
views
Escape single quote character for use in an SQLite query
I wrote the database schema (only one table so far), and the INSERT statements for that table in one file. Then I created the database as follows:
$ sqlite3 newdatabase.db
SQLite version 3.4.0
Enter &...
- 16.7k
240
votes
29
answers
435k
views
No module named _sqlite3
I am trying to run a Django app on my VPS running Debian 5. When I run a demo app, it comes back with this error:
File "/usr/local/lib/python2.5/site-packages/django/utils/importlib.py", line 35, ...
- 2,501
239
votes
8
answers
272k
views
How to create timestamp column with default value 'now'?
How to create a table with a timestamp column that defaults to DATETIME('now')?
Like this:
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
t TIMESTAMP DEFAULT DATETIME('now')
);
...
- 4,073
223
votes
14
answers
265k
views
How to dump the data of some SQLite3 tables?
How do I dump the data, and only the data, not the schema, of some SQLite3 tables of a database (not all the tables)?
The dump should be in SQL format, as it should be easily re-entered into the ...
217
votes
15
answers
249k
views
Mac SQLite editor [closed]
I am aware of CocoaMySQL but I have not seen a Mac GUI for SQLite, is there one?
My Google search didn't turn up any Mac related GUI's which is why I'm asking here rather than Google.
- 110k
208
votes
12
answers
290k
views
List of tables, db schema, dump etc using the Python sqlite3 API
For some reason I can't find a way to get the equivalents of sqlite's interactive shell commands:
.tables
.dump
using the Python sqlite3 API.
Is there anything like that?
- 12.8k
205
votes
5
answers
109k
views
Why do you need to create a cursor when querying a sqlite database?
I'm completely new to Python's sqlite3 module (and SQL in general for that matter), and this just completely stumps me. The abundant lack of descriptions of cursor objects (rather, their necessity) ...
- 2,536
202
votes
5
answers
124k
views
SQLite table constraint - unique on multiple columns
I can find syntax "charts" on this on the SQLite website, but no examples and my code is crashing. I have other tables with unique constraints on a single column, but I want to add a constraint to the ...
- 36.5k
200
votes
20
answers
414k
views
How to delete all records from table in sqlite with Android?
My app has two buttons, the first button is for deleting record on user input and the second button is for deleting all records. But when I want to delete data it shows the message
"Your application ...
- 5,881
199
votes
14
answers
197k
views
How do I add a foreign key to an existing SQLite table?
I have the following table:
CREATE TABLE child(
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT);
How do I add a foreign key constraint on parent_id? Assume foreign keys are ...
- 76.4k