sqlite

“Insert if not exists” statement in SQLite

by

If you never want to have duplicates, you should declare this as a table constraint: CREATE TABLE bookmarks( users_id INTEGER, lessoninfo_id INTEGER, UNIQUE(users_id, lessoninfo_id) ); (A primary key over both columns would have the same effect.) It is then possible to tell the database that you want to silently ignore records that would violate such … Read more

How to retrieve inserted id after inserting row in SQLite using Python?

by

You could use cursor.lastrowid (see “Optional DB API Extensions”): connection=sqlite3.connect(‘:memory:’) cursor=connection.cursor() cursor.execute(”’CREATE TABLE foo (id integer primary key autoincrement , username varchar(50), password varchar(50))”’) cursor.execute(‘INSERT INTO foo (username,password) VALUES (?,?)’, (‘test’,’test’)) print(cursor.lastrowid) # 1 If two people are inserting at the same time, as long as they are using different cursors, cursor.lastrowid will return the … Read more

Creating stored procedure and SQLite?

by

SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth Source : Appropriate Uses For SQLite

SQLite Concurrent Access

by

If most of those concurrent accesses are reads (e.g. SELECT), SQLite can handle them very well. But if you start writing concurrently, lock contention could become an issue. A lot would then depend on how fast your filesystem is, since the SQLite engine itself is extremely fast and has many clever optimizations to minimize contention. … Read more

How to concatenate strings with padding in sqlite

by

The || operator is “concatenate” – it joins together the two strings of its operands. From http://www.sqlite.org/lang_expr.html For padding, the seemingly-cheater way I’ve used is to start with your target string, say ‘0000’, concatenate ‘0000423’, then substr(result, -4, 4) for ‘0423’. Update: Looks like there is no native implementation of “lpad” or “rpad” in SQLite, … Read more

Best way to work with dates in Android SQLite [closed]

by

The best way is to store the dates as a number, received by using the Calendar command. //Building the table includes: StringBuilder query=new StringBuilder(); query.append(“CREATE TABLE “+TABLE_NAME+ ” (“); query.append(COLUMN_ID+”int primary key autoincrement,”); query.append(COLUMN_DATETIME+” int)”); //And inserting the data includes this: values.put(COLUMN_DATETIME, System.currentTimeMillis()); Why do this? First of all, getting values from a date range … Read more

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)