The problem, as it turns out, is that the PDO SQLite driver requires that if you are going to do a write operation (INSERT,UPDATE,DELETE,DROP, etc), then the folder the database resides in must have write permissions, as well as the actual database file. I found this information in a comment at the very bottom of … Read more
As of version 3.1.0 you can use CURRENT_TIMESTAMP with the DEFAULT clause: If the default value of a column is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used in the new row is a text representation of the current UTC date and/or time. For CURRENT_TIME, the format of the value is “HH:MM:SS”. For CURRENT_DATE, “YYYY-MM-DD”. … Read more
The insert method returns the id of row just inserted or -1 if there was an error during insertion. long id = db.insert(…); where db is SQLiteDatabase.
I found on the sqlite documentation (https://www.sqlite.org/lang_datefunc.html) this text: Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone. SELECT datetime(1092941466, ‘unixepoch’, ‘localtime’); That didn’t look like it fit my needs, so I tried changing the “datetime” function around a bit, and wound up with this: select datetime(timestamp, ‘localtime’) … Read more
Using LIKE: SELECT * FROM TABLE WHERE column LIKE ‘%cats%’ –case-insensitive
There can be several reasons for this error message: Several processes have the database open at the same time (see the FAQ). There is a plugin to compress and encrypt the database. It doesn’t allow to modify the DB. Lastly, another FAQ says: “Make sure that the directory containing the database file is also writable … Read more
From the SQLite docs INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. Since long is 8 byte and INTEGER can also save values of 8 bytes, you can use INTEGER.
import csv, sqlite3 con = sqlite3.connect(“:memory:”) # change to ‘sqlite:///your_filename.db’ cur = con.cursor() cur.execute(“CREATE TABLE t (col1, col2);”) # use your column names here with open(‘data.csv’,’r’) as fin: # `with` statement available in 2.5+ # csv.DictReader uses first line in file for column headings by default dr = csv.DictReader(fin) # comma is default delimiter to_db … Read more
Try this: delete from your_table; delete from sqlite_sequence where name=”your_table”; SQLite Autoincrement SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the SQLITE_SEQUENCE table … Read more
What also is being said in the comments, SQLite sees your input as 1, 25, 62, 7. I also had a problem with , and in my case it was solved by changing “separator ,” into “.mode csv”. So you could try: sqlite> create table foo(a, b); sqlite> .mode csv sqlite> .import test.csv foo The … Read more