FAQ Database Discussion Community


Consolidating row data from DB into a list of dicts

python,sqlite,python-3.x,data-structures,sqlite3
I'm reading data from a SELECT statement of SQLite. Date comes in the following form: ID|Phone|Email|Status|Role Multiple rows may be returned for the same ID, Phone, or Email. And for a given row, either Phone or Email can be empty/NULL. However, for the same ID, it's always the same value...

Retrieve Image from sqlite3 database and directly display on kivy window

android,python,database,sqlite3,kivy
What I am getting from this code? Image is retrieved from the database, then created in the same folder from this code with open(self.filename, 'wb') as output_file: output_file.write(self.ablob) then only I can get access to that image. I cannot directly get the image from the database and display it. What...

Having problems with sqlite3, getting syntax error?

python,database,python-3.x,sqlite3
When using sqlite3's database, I get an error like so: File '..\database.py', line 131, in load_player_abilities (player.id, ability.id)): sqlite3.OperationalError: near '=': syntax error The error I'm getting is coming from the following piece of code: for ability in player.abilities: for level in cursor.execute( "SELECT level FROM abilities" "WHERE player_id=? AND...

Why I can't join partial tables into one in Sqlite3?

database,database-design,sqlite3
I decomposed a large table into five tables based on BCNF and I tried to join them all together to check if I lost something or not. I used this sql statement in Sqlite3 Select spart.Sno, spart.Sname, cpart.Cno, CtoT.Cname, CtoT.Tno,tpart.Tname, scorepart.Degree from spart, cpart, CtoT, tpart, scorepart where cpart.Cname...

string formatting a sql query in sqlite3

python,sqlite3
I've been playing around with sqlite3, and I get an sqlite3.OperationalError: near "sweet": syntax error for this line of my code query_cursor.execute("INSERT INTO mcdonalds_menu VALUES(%d, %s, %f, %s, %d)" % (ids[num],names[num], price[num], descriptions[num], calories[num])) When I put in the values in 3 separate queries the code seems to work, but...

In a left join, select row with value A, if not select row with value B

sql,join,sqlite3
This must be simple, but I think I'm lost. I have a table A: name id Tom 1 Barbara 2 Gregory 3 ...and table B: id nickname preferred 1 Spiderman 0 1 Batman 1 2 Powerpuff 0 3 Donald Duck 0 3 Hulk 1 How do I query the table...

update part of the the field in SQLite

sql,sqlite,sqlite3
UPDATE record SET recordId = replace(recordId, substr(recordId, 6, 3), '000'), WHERE recordId LIKE '%001' Errors It seems that recordId = replace(recordId, substr(recordId, 6, 3), '000') is not allowed in UPDATE. How to do this?...

Can I include hardcoded data in a SQL SELECT result set?

sql,json,sqlite3
I have the following code: (pseudo code) Select id, name, code, address from users I json encode the data from the database like so: {"id":"228","name":"Jimmy","code":"12345","address":"abc street"}, {"id":"229","name":"John","code":"22345","address":"10 1st street"} But I actually need the data to look like this: {"id":"228","name":"Jimmy","code":"12345","address":"abc street", "DT_RowId": "row_228"}, {"id":"229","name":"John","code":"22345","address":"10 1st street","DT_RowId": "row_229"} where DT_RowId is...

sqlite3 python ATTACH DATABASE copy table .schema

python,database,sqlite3
System Python 2.7 SQLite3 Code I want to restore a backup of a database by copying a table from the _bak.db to the being used .db. conn = sqlite3.connect(os.path.join("data", "db", "Kanji-story.db")) c = conn.cursor() c.execute("DROP TABLE IF EXISTS current") c.execute("ATTACH DATABASE ? AS db2", (os.path.join('data', 'db', 'Kanji-story_bak.db'),)) # TODO Code...

Sequel Sinatra Post Database params

ruby,sqlite3,sinatra,sequel
I am trying to build just a very simple form for registering attendees for my wedding. This is somewhat a draft because I don't know if I'll be using Sinatra or Rails. I am going to be setting up scheduled emails to notify guests and myself of the wedding specifics....

SQLite3 Unable to write to database file on Amazon AWS but only in certain instances

php,amazon-ec2,sqlite3,ubuntu-14.04
I have setup and EC2 instance on AWS and as part of this instance I am using a SQLite3 database to handle certain data. All of the database operations are routed through a single PHP file, with a single connection: function dataQuery($query) { // establish database connection try { $dbh...

temp store full when iterating over large amount sqlite3 records

python,sqlite,python-3.x,sqlite3
I have a large SQLite db where I am joining a 3.5M-row table onto itself. I use SQLite since it is the serialization format of my python3 application and the flatfile format is important in my workflow. When iterating over the rows of this join (around 55M rows) using: cursor.execute('SELECT...

Syntax error in rails database query

mysql,sql,ruby-on-rails,sqlite3
I have a one to many relationship between two tables (User name: string group: integer) and Relationship (source: integer, target: integer,value: integer). They are related so that userid=relatioship.source. I am trying to get some json out of it so I have written: def self.including_relationships User.joins("INNER JOIN relationships ON users.id =...

erroneous with SQLite3 query in Java

java,hibernate,jdbc,sqlite3
I am parsing data out of a metrolink database. When I query the db from the command line I get the following out put : Stop Name Latitude Longitude ---------------------------------------- --------------- CLAYTON METROLINK STATION 38.645657 -90.339223 SUNNEN METROLINK STATION 38.607509 -90.330296 MAPLEWOOD METROLINK STATION 38.614023 -90.331314 BRENTWOOD METROLINK STATION 38.627851...

Go SQLite3 database connection using sql.Open

database,go,sqlite3
I am new to golang and I am a having a hard time connecting to an SQLite3 database hosted on the local machine. I have the SQLite3 database created and have worked through a few tutorials I have fond but they are not working. currently my code is based on...

Trouble storing numpy array in sqlite3 with python

python,sqlite,numpy,sqlite3
I'm trying to follow the example shown as the top answer here: Python insert numpy array into sqlite3 database At first I thought it was my code, but I've tried copying and pasting the answer code exactly and I'm still getting the same error: OSError: Failed to interpret file <_io.BytesIO...

flask/sqlalchemy - OperationalError: (sqlite3.OperationalError) no such table

python,sqlite3,flask,sqlalchemy
I get the below error sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: s_amodel [SQL: 'INSERT INTO s_amodel My files forms.py from flask_wtf import Form class SAForm(Form): .... models.py from app.app_and_db import db class SAmodel(db.Model): id = db.Column(db.Integer(), primary_key=True) views.py from app.app_and_db import app, db from app.sa.forms import SAForm from app.sa.models import SAmodel...

SQL: count the number of values two distinct entries have

sql,sqlite,sqlite3
Given the following SQLite table: Name | Lived_in ---------------- John | Canada John | UK James | Canada James | UK Mary | UK Mike | USA Is there any way of listing the pairs formed by the distinct values in col_1 (Names), alongside the number of values they share...

Is there a SQLite3 query generator?

ruby-on-rails,ruby,sqlite,sqlite3
I am having a database in Ruby on Rails application using SQLite3. Knowing my table, is there an open source project which can provide me an easy way of creating sqlite queries? For example, based on the table names and order, I have a the available joins, and then I...

how to see core data table in iPhone [duplicate]

ios,iphone,sqlite3
This question already has an answer here: IOS 8 Store sqlite File Location Core Data 8 answers I have create core data and I want to see that data. i used to see that data Path like this: ~/Library/Application Support/iPhone Simulator/User/Applications/ but I am getting after this again Library:...

sqlite3 error: no such table: when checking if table exists

c++,sql,sqlite,sqlite3
I have read several posts but still cannot figure out what is wrong here. I have a c++ wrapper to call the sqlite. I want to test if the table exists before it is created, by reading check-in-sqlite-whether-a-table-exists, I use the following sql statement to check a table exists "SELECT...

SQLite3 C++ Query near “CASE”: syntax error

c++,sql,sqlite3
Using SQLite3 C++ implementation though I believe this error is more general. I'm simply trying to do a statement that says: "If the column 'lastPlayed' does not exist in the table 'Players', ALTER the table and add it to said table" Here's my query: query = string("CASE WHEN COL_LENGTH('Players', 'lastPlayed')...

Python sqlite3 extra character in queries result

python,sqlite,sqlite3
So I am trying to run a simple query in python using sqlite to try it out . I get the result of the query right, but I get an extra "u" at the beginning of each text field and I am not sure why. Here is the code: import...

Comparing dates from from two different sources

python,sqlite,python-2.7,sqlite3
I am using Tweepy API and storing tweets in a sqlite3 database. I am able to get the date and time using a function in Tweepy. it stores the value in the database as 2015-06-06 23:06:19 I then need to determine if the age of this date is greater than...

Ipython : Installation error, unable to find _sqlite.so

python,sqlite,python-3.x,sqlite3,ipython
On trying to install ipython for Python3.4 I get a message that it is already installed. pip3 install ipython Requirement already satisfied (use --upgrade to upgrade): ipython in /usr/local/lib/python3.4/site-packages But when I try to run it with ipython3 notebook I get all this: Traceback (most recent call last): File "/usr/local/bin/ipython3",...

SQLiteDatabase.rawQuery(java.lang.String, java.lang.String[])' on a null object reference

java,android,sqlite,sqlite3,android-sqlite
When accessing the database a NullPointerException is thrown. Here is the code that creates the db: public class DatabaseHelper extends SQLiteOpenHelper { public static final String KEY_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS {0} ({1})"; public static final String KEY_DROP_TABLE = "DROP TABLE IF EXISTS {0}"; private Context context; Activity...

Perform SQL query and then join

sql,sqlite3,left-join
Lets say I have two tables: ticket with columns [id,date, userid] userid is a foreign key that references user.id user with columns [id,name] Owing to really large tables I would like to first filter the tickets table by date SELECT id FROM ticket WHERE date >= 'some date' then I...

c++ store special characters (“ç”, “á”,..etc) on SQLite db

c++,qt,sqlite,visual-studio-2012,sqlite3
My program uses SQLite3 lib to open and execute some querys to a local DB. This is the function I use to execute a query (with error displaying): void exec(string query, string place_error){ if( sqlite3_exec(sqldb,query.c_str(),0,0,&db_err) != SQLITE_OK) sql_error(place_error+" - "+(string)db_err); } Now, if I try to execute something like: exec("INSERT...

SQLite - select row by year only?

sql,sqlite,sqlite3
How can I select rows from a table by year only? SELECT * FROM speckdata AS s WHERE DATE_FORMAT(s.localdate, '%Y') = '2014') I could get the result in MySQL Now on sqlite I get this error, SQLiteManager: Likely SQL syntax error: SELECT* FROM speckdata AS s WHERE DATE_FORMAT(s.localdate, '%Y') =...

Working on many files with Python

python,linux,file,sqlite3,subprocess
The task: I am working with 4 TB of data/files, stored on an external usb disk: images, html, videos, executables and so on. I want to index all those files in a sqlite3 database with the following schema: path TEXT, mimetype TEXT, filetype TEXT, size INT So far: I os.walk...

Checking if SQLite3 database contains any data

ios,sqlite3
Setting up an SQLITE3 database as a permanent store in an app. I need to check if a database object exists populated with tables and execute an SQL DDL statement if no tables are present (i.e. on first launch of the APP)wondering if the best way to approach this is...

update table based on another tables value in sqLite

sqlite,join,sqlite3,sql-update
We have two tables table1 table2 In table1 Itemnumber SalesCode 123 213UB 142 132NB 1458 256GD In table2 Itemnumber ProductGroupCode 123 142 1458 After Update table2 We need Like this Itemnumber ProductGroupCode 123 213UB 142 132NB 1458 256GD We tried like this tx.executeSql('UPDATE table2 JOIN table1 ON (table2.ItemNumber=table1.ItemNumber) SET table2.CustomerPriceGroup...

How do i query SQlite using python and then compare the query result?

python,sqlite,sqlite3,python-3.4
The aim of this code is to validate a password that is entered using the passwords and usernames stored in a database. The table contains username(nick) and password, passwords are encrypted using: My code at the moment consists of: def check_login(db, username, password): """returns True if password matches stored""" cursor...

sqlite3 table into QTableWidget, sqlite3, PyQt5

python-3.x,sqlite3,pyqt5
I have created a program that manages tables in a database, now i'm trying to make a gui for it, and i figured the easiest way would be to do it with Qt Designer and then convert it to python with PyQt5, and so i did it, i know how...

The last inserted row id in database is successfully inserted but to get that id is 0?

ios,objective-c,iphone,sqlite3,xcode6
This is the last row inserted method that return me the last id. Code: -(int)LastId { int noteID; NSString * sqlStr; sqlStr = [NSString stringWithFormat:@"select * from notes"]; sqlite3_stmt *ReturnStatement = (sqlite3_stmt *) [self getStatement: sqlStr]; while(sqlite3_step(ReturnStatement) == SQLITE_ROW){ @try{ noteID=[[NSString stringWithUTF8String:(char *)sqlite3_column_text(ReturnStatement, 0)] intValue]; } @catch (NSException *ept) {...

sqlite3 compound index usage for LIKE and GROUP BY

sql,sqlite3,group-by,sql-like
I can't seem to get sqlite3 to not use a TEMP B-TREE with a compound index for a query with a LIKE clause and a GROUP BY clause. Seems to only use the index for the WHERE clause. I've tried making the index with all combinations with COLLATE NOCASE in...

android sqlite query with offset doesn't return desired rows

java,android,sqlite,sqlite3,android-sqlite
I'm using the following code to get a list of notifications (total rows are 21): List<Notification> list = new ArrayList<Notification>(); Cursor c = _db.query(TABLE_NAME, COL_ALL, null, null, null, null, order, get_limitStr(offset)); if(c != null && c.moveToFirst()) { while(!c.isAfterLast()) { Notification model = cursorToModel(c); if(model != null) { list.add(model); } c.moveToNext();...

How to get parsed Date from Sqlite3 databse with Java?

java,jsp,sqlite3
I am getting data from sqlite3 DB, by creating a model for my method with POJO. While retrieving data in a arraylist I am getting parse error for date. The date is defined as timestamp in the sqlite3 DB and while compiling it is throwing : Unparseable date: "2015-05-21 12:41:56"...

Sqlite order by, AaBbCc

sqlite,sqlite3,order
I have been trying to get sqlite to order a set of entries by name in the following order AaBbCcDd......Zz but the closest I've gotten is using COLLATE NOCASE to get a case insensitive sort. The entries I have are Addy|2000-01-22T16:06:14.155Z alice|2000-01-22T16:06:20.514Z billyRay|2000-01-22T16:06:36.175Z Bobby|2000-01-22T16:06:26.868Z claus|2000-01-22T16:06:52.531Z CoffinLLC|2000-01-22T15:33:11.235Z Default|2000-01-22T15:55:12.168Z I have tried...

Undefined symbols for architecture i386: “_sqlite3_open”, referenced from:

ios,objective-c,sqlite3,xcode6
i have sqlite3.dylib also in my application, but it give me that upper error i don't know that how to fix this error. please someone help me i am waiting? Undefined symbols for architecture i386: "_sqlite3_open", referenced from: -[MyDataBase openOrCreateDatabase:] in MyDataBase.o "_sqlite3_exec", referenced from: -[MyDataBase createTable:] in MyDataBase.o -[MyDataBase...

How to eliminate multiple similar sql queries with one resultset handler?

c++,database,qt,c++11,sqlite3
I have some similar sql queries, all of the selected fields are same, just the condition is different. So, the resultset handlers are same also. I use sqlite3 and sqlite_modern_cpp. Here are two of the similar functions(there are several others): QVariantMap getItemById(int id) { QVariantMap map; database db(dbfile); db <<...

How to update the value of a specific column of every row in DB with the output of a function that was passed in the original value.

python-2.7,sqlite3
For example: if you wanted to replace in each row the value of a column with the value after being run through a hash function. In my case I have saved many URLs in a column that have 302 redirects and I want to save where they redirect to after...

Getting NULL value back in UITableView from SQLite database

ios,objective-c,cocoa-touch,sqlite3
Program is running without issue I can see the database being opened When I add an item to the app, it shows up as NULL in the UITableView I have unit tests that test each action: create, update, delete, and count. These all pass Here are my unit tests: -...

Consecutive and sequence insert in node.js sqlite

node.js,asynchronous,sqlite3,event-handling
my code is: var json = [1,2,3,4,5,6,7,8,9]; async.forEach(json, function (that, callback) { sqlitedb.run("INSERT INTO mytable (name) VALUES ("+that+")" , function () { console.log(that); callback(); }); }); The callback() placed on after insert item. but when i execute, my json items inserted on no squence and result is like: 7 8...

Rails + Angular + sqlite3 Deployment

ruby-on-rails,ruby,angularjs,heroku,sqlite3
I'm a beginner for this and currently finding a free web host and has a couple of questions. Will Heroku be able to handle all of this: AngularJS, Ruby on Rails, and sqlite3? What are other good free web hosts? This is just for a personal portfolio/blog. I'm seeing several...

SQLite returns incorrect arthimetic value

sqlite,sqlite3
I'm working on a query that adds the following numbers and it's returning a puzzling result. >SELECT ((36.7300 - 20.4300) - 16.3) AS Amount; >-3.5527136788005e-15 I've tried everything I can think of like casting the values to different data types, but I'm still getting the same result. When I separate...

SQLite3 — Return Affected Rows

jdbc,sqlite3,transactions
Supposing I have a schema with a name field and an int field. I want to get one random entry where the int value is 1, set it to 0, and return the name of the entry that was changed in a transactional manner. What's the best way to do...

Selecting entries from a table based on another table

mysql,sql,database,sqlite,sqlite3
I am working on a forum with a SQLITE3 backend. My goal is to find the last 20 topics that has the most recent posts beneath them. I tried this: SELECT * FROM Topics INNER JOIN Posts ON Topics.ID = Posts.TopicID ORDER BY Posts.Modified DESC LIMIT 20 This is not...

DELETE from a Join in SQLite / syntax for IN clause on tuples?

sqlite3
I have a schema which uses a compound primary key to store users and associated data, like so: CREATE TABLE users ( domain integer, userid integer, ... PRIMARY KEY (domain, userid) ); CREATE TABLE userdata ( domain integer, userid integer, key integer, ... PRIMARY KEY (domain, userid, key), FOREIGN KEY...

Manage sqlite3 db not using the official sqlite3 library

c++,stl,sqlite3
I need to simply manipulate a Sqlite3 database (just read some column) and being this just a small part of my program,I don't feel to add 160k line for just few instruction. There is a way to do this STL or another library that isn't so much memory expensive? I...

If I install the app in another device, is it still possible to log in with the pre-made ID?

android,database,sqlite,sqlite3,android-sqlite
For my best knowledge so far, the biggest difference between SQLite and MySQL is that it does not require a server to run, as SQLite stores data in a database file in each device. The question rising from this part is.. Let's say I created an Android app which simply...

Questions about create table and transaction in sqlite3 of python

python,sqlite,sqlite3
I'm learning to use sqlite in python by sqlite3. The SQL operations like insert, update support support transaction and the commit() should be called before close the connection, or nothing would change in the database. However, the 'create table' do not support transaction, or the create table is auto committed....

select's fields function of korma does not reject colums?

clojure,sqlite3,korma,sqlkorma
I play around with clojure and its korma library using an sqlite3 database on windows. I follow an example of the 7web book. It introduces the select* function and its friends. But using the fields function adds fields instead of restricting. ;; models.clj (defentity issue (entity-fields :id :project_id :title :description...

LIKE query not working as expected

sql,ruby-on-rails,sqlite,sqlite3
I have an SQL Query that looks like: SELECT "medical_interventions"."id" AS t0_r0, "medical_interventions"."refcode" AS t0_r1, "medical_interventions"."intervention_tid" AS t0_r2, "medical_interventions"."medical_intervention_category_id" AS t0_r3, "medical_interventions"."created_at" AS t0_r4, "medical_interventions"."updated_at" AS t0_r5, "translations"."id" AS t1_r0, "translations"."lang" AS t1_r1, "translations"."text" AS t1_r2, "translations"."created_at" AS t1_r3, "translations"."updated_at" AS t1_r4 FROM "medical_interventions"...

sqlite3 error when compiling with cl.exe from MVS 2013 with OMIT opition

c++,sqlite3
When I try to compile the sqlite3.c and shell.c file to create a library, I write cl.exe /DSQLITE_OMIT_"one.of.the.option." sqlite3.c shell.c It returns an error: sqlite3.c sqlite3.c(155868) : error C2129: static function 'void sqlite3"name of the option choosen"(Parse *,Expr *,Expr *,Expr *)' declared but not defined sqlite3.c(13360) : see declaration of...

sqlite3 fails to import db from sql file

sql,sqlite3
I'm trying to install PolicyD v2, but when creating sqlite3 database from sql file it throw errors: SQLite version 3.7.17 2013-05-20 00:56:22 sqlite> .read /usr/doc/policyd2-2.0.14/policyd2.sqlite Error: near line 1: unrecognized token: "#" Error: near line 43: no such table: policies Error: near line 44: no such table: policies Error: near...

Allow multiple async calls to finish before calling termination function in Node.js

node.js,sqlite3
I'm using node.js to search through a sqlite table of books and returning only the books that are not checked out. My question is if the second function that I am passing into stmt.each() will wait for isCheckedOut() to finish before executing? At the moment it seems to be waiting...

Transform query that works in SQLite into something that works in Postgres

ruby-on-rails,postgresql,heroku,sqlite3
Message.order("created_at DESC").where(user_id: current_user.id).group(:sender_id, :receiver_id).count Works with my dev environment SQLite3 for a Rails 3.2 app but fails when pushed to Heroku using Postgres with this error: PG::GroupingError: ERROR: column "messages.created_at" must appear in the GROUP BY clause or be used in an aggregate function It seems it wants :created_at in...

SQL - trigger as log of update

sql,triggers,sqlite3
My tables are: -stormtrooper -squad -platoon Stormtrooper has 'ID' as primary key and 'squadID' as foreign, squad has 'ID' as primary key and 'platoonID' as foreign and platoon has 'ID' as primary key. Using trigger I want to display everything that connects to stormtrooper which I update. CREATE TRIGGER log...

convert a query from Sqlite3 to mysql

mysql,sql,sqlite,sqlite3
I have converted my database from Sqlite3 to mysql, but when I try to execute a this query: SELECT * from main WHERE case_id IN ( SELECT DISTINCT case_id FROM main WHERE subject LIKE '%word%' AND court = 'other' ORDER BY RANDOM() LIMIT 100 ) ORDER BY case_id, date; that...

db.sqlite3, ,settings.py for heroku

python,django,heroku,sqlite3
DATABASES['default'] = dj_database_url.config() NameError: name 'DATABASES' is not defined I'm getting this error while doing heroku run python manage.py syncdb command. initially I wrote it like this DATABASES = { 'default': dj_database_url.config() } but then I was getting this error File "/app/.heroku/python/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 20, in <module> raise ImproperlyConfigured("Error loading psycopg2...

Inserting row into SQLite table

python,sqlite3,python-3.4
I'm trying to add a row (post submitted by user) into a SQLite table (posts). I need to add the new post with the timestamp being the current time and date and then i need to return the id for this new post. My code so far (incomplete as im...

Can I use WAL mode in SQLite3 if I use an additional mutex for multiple writers?

c++,sqlite3,mutex,wal
http://sqlite.org/wal.html WAL mode is specified for N readers and one writer. It the writer's identity interchangible? For example, I have N writers and an additional mutex. Each writer has to obtain this mutex to be able to write, so there are never two writers writing at the same time. Is...

An error occurs when signing up a second user

android,database,sqlite,sqlite3
I wrote two java files - on about signup, and the other about storing data. Those are as follows. signup part String entered_email = email.getText().toString(); String entered_password = password.getText().toString(); String repassword = rePassword.getText().toString(); String entered_firstname = firstname.getText().toString(); String entered_lastname = lastname.getText().toString(); String entered_dob = dob.toString(); if(entered_email.equals("") || entered_password.equals("") || repassword.equals("")...

Why does SQLite give Cannot create instead of trigger on table

triggers,sqlite3,android-sqlite
With this table create table FOLDER ( _ID integer primary key autoincrement, NAME text not null, PARENT integer not null, DELETED integer, constraint VALUEOF_folder_deleted check (DELETED == 1 or DELETED isnull) on conflict abort, unique (NAME, PARENT) on conflict abort ); I wanted to replace an insert where the NAME...

Cannot find the SQLite db file on my device

android,database,sqlite,sqlite3,android-sqlite
I created a database in the following code. public final class MembershipDatabaseHelper extends SQLiteOpenHelper { private static final String DB_NAME = "members.db"; private static final String DB_TABLE = "members"; private static final int DB_VERSION = 1; private static final String COLUMN_ID = "_id"; private static final String COLUMN_EMAIL = "email";...

How to get list of column names of a sqlite db file [duplicate]

python,database,sqlite3
This question already has an answer here: Is there a way to get a list of column names in sqlite? 4 answers Sorry for the rookie question. I have a sqlite file and I need to get table column names. How can I get them?...

How to retrieving specific records from SQlite (Need Query)

android,sqlite,sqlite3,android-sqlite
I am working on apps in which i need retrieve specific records from one position to another. i have table Track_Detail having 20 records. How to retrieved records from 1 to 10 in one list and 11 to 20 in another list. here my source code: retrieve record from Track...

Releasing SQLite resources in F#

sqlite,sqlite3,f#,system.data.sqlite,fsi
Consider the following F# script, which creates a simple SQLite database and table, and then should delete it. However, the SQLite obejct doesn't seem to be disposed of properly and isn't releasing its lock on the file. #r @"C:\Path\To\System.Data.SQLite.dll" open System.Data.SQLite let createTable() = use db = new SQLiteConnection(@"Data Source=test.sqlite")...

Select all if blah.. else select all where blah

ios,objective-c,sqlite3
I have an enum like: typedef NS_ENUM(int, name) { kAll, kFirstType, kSecondType }; then I do: if (type == kAll) { query = [NSString stringWithFormat:@"SELECT * FROM TABLE WHERE ID = %ld", itemID]; } else { query = [NSString stringWithFormat:@"SELECT * FROM TABLE WHERE ID = %ld AND type =...

Update not working sqlite in objective c

ios,objective-c,sqlite,sqlite3
I am trying to update row in a loop and its not working. I have explained the scenario below I have got set of values from web services stored in arrays. Now i have to update those values in DB by looping through the array. Below is the code am...

Group by regex count

regex,sqlite,sqlite3
id 1001 2001 2002 3001 3002 3003 How to group the id column by its first digit 1, 2, 3? I mean something like below: select count(*) from my_tbl group by expr_id_1st_digit; count(*) 1 2 3 ...

SQLITE_MISUSE in prepared statement

c++,sqlite3
I'm getting a SQLITE_MISUSE error on the following code, and I am wondering if it might be caused by having the table name be a bound parameter? What are some different causes of SQLITE_MISUE? const char sqlNeuralStateInsert[] = "INSERT INTO ?1(LAYER_ID, NEURON_ID, INPUT_ID, VALUE)" "VALUES(?2, ?3, ?4, ?5);"; sqlite3_stmt* stmt1;...

sqlite3 “OperationalError: near ”(“: syntax error” python

python,sqlite3
simply put i am trying to make a sql database table and input data into it. I have it working in a simpler way, but when I put it into my script it results in this error. I'm hoping its something simple I missed. Any help/advice would be greatly appreciated....

Python sqlite3 assertion error

python,python-3.x,sqlite3,pycharm,bottle
I am pretty sure the code I am doing is logical and makes sense but when I run it against my test the result comes back quite unusual, I am trying find a session from a cookie, and then use it to retrieve the user from the sessions table. I...

Database field length is not enforced

python,sqlite3,web2py
I am using web2py (python) with sqlite3 database (test flowers database :) ). Here is the declaration of the table: db.define_table('flower', Field('code', type='string', length=4, required=True, unique=True), Field('name', type='string', length=100, required=True), Field('description', type='string', length=250, required=False), Field('price', type='float', required=True), Field('photo', 'upload')); Which translates into correct SQL in sql.log: CREATE TABLE flower( id...

Improve code that periodically query SQLite database

php,sqlite,sqlite3
I have an sqlite database that I query from PHP periodically. The query is always the same and it returns me a string. Once the string changes in the database the loop ends. The following code is working, but I am pretty sure this is not the optimal way to...

Should I make new table for storing user's address in Rails

ruby-on-rails,database,sqlite3,rails-activerecord,normalization
I have created a rails app and my "users" table goes like this create_table "users", force: :cascade do |t| t.string "name" t.string "email" t.string "password_hash" t.string "password_salt" t.integer "country_id" t.integer "state_id" t.integer "pincode" t.string "address" t.string "phone_no" t.float "latitude" t.float "longitude" Other tables states , country , posts Associativity: user...

C# sqlite Unable to load sqlite3.dll

c#,sqlite,sqlite3,nuget
I have used nuget to install sqlite-net and System.Data.SQLite (x86/x64). I have rebuilt and I have restarted and I still get the following error: Unable to load DLL 'sqlite3': The specified module could not be found. (Exception from HRESULT: 0x8007007E). This is the line that triggers it: db = new...

How to enable DELETE MODE in iOS coredata sqlite using swift

ios,swift,core-data,sqlite3
I am learning iOS database creation using CoreData. I created sample application to understand the CoreData.I extracted the Sqlite file from default database creation directory to check the data using SQLite browser.But DBBrowser showing nothing.But success message showing and data retrieving from the database. Then i found this about CoreData...

Sqlite3 : Unable to Authenticate

python,sqlite3,flask
I'm creating an application in flask to learn python and sqlite3. What I'm facing problem is my login authentication is not checking database entries and allowing every user to log in. And user data is been stored in database at the time of User signUp so database and tables are...

Check if a row exists in sqlite3?

python,sqlite,python-3.x,sqlite3
How do I properly check if a row exists in my database table using Python 3 and sqlite3? Here's my original code: cursor.execute("SELECT name, age FROM people WHERE pin=?", (pin, )) name, age = cursor.fetchone() or ('', 0) So currently I'm setting them a default value with the or ('',...

django - can't assign a foreign key

python,mysql,django,sqlite3
For unknown reasons, I cannot assign a foreign key instance of Item_rarity table into Detailed_item table. Django throws an error: Cannot assign "u'Basic'": "Detailed_item.rarity" must be a "Item_rarity" instance. ... But in Item_rarity dictionary "Basic" record exists - I can choose it from admin panel and create Detailed_item record manually....

Python SQLite3 syntax error when creating a table

python,sqlite3,foreign-keys
I'm trying to create a table with the following command: 'CREATE TABLE users_profile(user_id INTEGER UNIQUE, FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,\ firstname TEXT, lastname TEXT, email TEXT, website TEXT,\ picture TEXT, mobile TEXT, skype TEXT, age INTEGER, residence TEXT, gender TEXT,\ signature TEXT, avatar TEXT)' I get the error:...

How to select columns in a table which have values not null in another table

sql,sqlite,sqlite3
I have two SQL tables (I am using SQLite). Table1 (code TEXT) Table2 (code TEXT, codeTable1 TEXT) How can I fetch all the table1's content which has at least one row in the table 2 with the codeTable1 not null?...

Replace the following query in sqlite

c#,sql-server,sqlite,if-statement,sqlite3
I have a sql server query as follows in a store procedure with parameters @firstId and @secondId IF @firstId = '' begin SELECT DISTINCT x, y = FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id AND t2.id = @secondId ORDER BY t1.somecolumn END ELSE BEGIN SELECT DISTINCT x,...

SQLite FTS4: A query containing a single unary not expression?

sql,sqlite,sqlite3,full-text-search
The SQLite3 FTS4 documentation regarding the standard query unary - operator states: An FTS query may not consist entirely of terms or term-prefix queries with unary "-" operators attached to them. Is there any way around this? Specifically, I want to retrieve all the rows in my table that do...

How to resolve db:migrate error SQLite3::SQLException: no such table:

ruby-on-rails,sqlite3
I am trying to use the social network gem inkwell. I am following the simple instructions here . I have created a Post, User, Category and Community model using rails generate model I then configure the models and run $ rake inkwell:install:migrations $ rake db:migrate but end up with the...

sqlite.net PCL throws TypeInitalizationException

c#,sqlite3,sqlite-net
We changed from sqlite-net to sqlite.net PCL edition. Our project worked fine with the old one - with the PCL edition, we now have to explicitely declare our platform by using. var conn = new SQLiteConnection(new SQLitePlatformWin32(), Path.Combine(folderPath, dbName)); But now, when I call this line, we get a A...

Not able to create staging instance on heroku [duplicate]

ruby-on-rails,git,heroku,sqlite3
This question already has an answer here: Errors of pushing rails app to Heroku error occurred while installing sqlite3, and Bundler cannot continue 3 answers I am trying to create a staging instance of my app on heroku. While I am doing git push staging master I am getting...

Populate/insert values in query

c,string,replace,sqlite3,str-replace
I'm new with C, i'm trying to make simple program with sqlite3 db. I have query like this: sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS) " \ "VALUES (1, 'Paul', 32, 'California' ); " \ I have values in char* variables, is it possible to use %s like on printf for...

SQLite how to refer to column name by a query

sqlite,sqlite3,relational-database
What I want to do basically is SELECT (SELECT fields FROM tableB WHERE interest=yes) FROM tableA Why I can not do this? What could be the workaround? Example: I got tablea like this time|c1|c2|c3 --------------- 0.0 |1 |11|111 1.0 |2 |22|222 2.0 |3 |33|333 tableb like this field| interes --------------...

Convert AWK command to sqlite query

sql,awk,sqlite3
I have to parse very big (abt 40Gb) text files(logs) very often. Usually AWK/grep is enough for my needs, but logs are growing and now I'm curious whether sqlite3 will allow me to do the same things more efficiently. I chosen sqlite for my tests as it installed out-of-the-box (SQLite...

Ubuntu 14.04 - An error occurred while installing pg (0.18.2), and Bundler cannot continue

ruby-on-rails,postgresql,ubuntu,heroku,sqlite3
This issue doesn't let me go ahead and I don't know whether it's possible for me to deploy my Rails App ever on Heroku. When I try bundle install by having gem 'pg' in my Gemfile it gives following error. An error occurred while installing pg (0.18.2), and Bundler cannot...

Rename true false boolean

python,sqlite,python-2.7,sqlite3,boolean
Was wondering if there is a way to change a boolean from true / false to in / out? My code below is a boolean switch but I want it to print in or out not 0 or 1 while True: Tag = raw_input("Please Scan your Tag: ") q =...

Python Sqlite3: Create a schema without having to use a second database

python,database,sqlite,sqlite3
I would like to create a schema for a sqlite3 database in python, but sqlite does not appear to support CREATE SCHEMA (sqlite docs). I've looked into ATTACH, and it seems like it would do the job by using a second database but I only need one database that has...

SQLITE_BUSY error in iOS application

ios,database,swift,sqlite3
I am writing an iOS application to allow medical doctors to select diagnoses codes from a database and bill patients that are also in the sqlite3 database. The main page is a form that asks for the patient name and date of birth. Also on the main page is a...

Insert and select from other table and contant value

sqlite,sqlite3
I'm looking to "mark" entries of my SQLite database. This following command will add new entries from the other_table: INSERT OR IGNORE INTO landing_table (idx) SELECT gidx FROM other_table However, I want to update only the entries that don't exist in landing_table that do exist in other_table to have the...

ruby sqlite3 insert with autoincrement

ruby,sqlite3
I have a trouble inserting data into a sqlite3 database with an id autoincrement. Here is what I have tried so far: begin db = SQLite3::Database.open db_name db.execute "CREATE TABLE IF NOT EXISTS Audit( id INTEGER PRIMARY KEY AUTOINCREMENT, module TEXT, hostname TEXT, criticity TEXT, raw_data TEXT );" rescue SQLite3::Exception...

Python - Insert filename into database with sqlite3

python,sqlite3
I've created this table in my database: cur.execute("""CREATE TABLE VIDEO(ID VARCHAR(128) NOT NULL PRIMARY KEY)""") and what my script is supposed to do is to read all the files in a folder and put the filename into the VIDEO table (the filename is the ID and primary key): def AddVideo(video):...