Questions tagged [sqlite3]

0

votes
0

answer
9

Views

dplyr & gracefully dealing with SQL NULL values

I'm trying to use dplyr to work with SQL databases using R and I'd like to gracefully handle SQL's NULL values--either by simply filtering them out or treating them as zeros when the come up, depending on the scenario--without making any changes to the underlying database itself. (In other words, I'...
Steve S
1

votes
1

answer
2.9k

Views

SQLite INNER JOIN in python: sqlite3.OperationalError: no such column:

I'm having an issue with SQLite in python. The following code doesn't appear to work due to the error sqlite3.OperationalError: no such column: Company I'm trying to gather data from both of the tables and display them back to the user using tabulate but cannot proceed and I cannot figure out how t...
B. Anderson
0

votes
0

answer
4

Views

Why the time for queries oscilates in a sqlite3 database?

I was testing the difference in time between querying a database, which is stored in an external hard drive, using sqlalchemy and raw SQLite3 in order to see which would give me the best times and found the results to be oscillating in time: The code used for each step is: def time_attack_alchemy():...
Chicrala
1

votes
3

answer
3.8k

Views

sqlite3 can't import .csv file

I'm very new to DQL and sqlite. I've created a very basic database and am trying to import data from a .csv file. I watched a tutorial and have entered the following code: .import countries.csv countries This gives the following error: Error: cannot open "countries.csv" I'm sure im over looking some...
Charles
1

votes
2

answer
426

Views

Convert SQLite 3 database to CSV

I am looking for a way to convert my databases into CSV files. I use SQLite 3 through the terminal as well as from SQLiteStudio. I have not come across a simple way to export or save as a CSV, which makes me think I may not be able to do it in one step.
Ceej
1

votes
1

answer
309

Views

Script for Deleting old data from sqlite3 databases

Currently I'm deleting manually old data (old more than 90 days) from sqlite databases, so below are the steps currently following for that. so guys Is that possible to do this job using Bash Script ?? 1. cd /opt/db (my database location) 2. ls -lSh | head -n30 (sorting fr...
lfreez
1

votes
1

answer
278

Views

How can I write SQL statements for psycopg2 for PostreSQL and sqlite3?

I use the psycopg2 library for PostgreSQL in python for my production environment but I want to test my code with the sqlite3 library. The problem is that psycopg2 uses %s as a placeholder for parameter substitution, whereas sqlite3 uses ?. I am not happy with the code I got now, it looks like this:...
Jelle Besseling
1

votes
0

answer
7

Views

SQLite AUTOINCREMENT non-primary key column

I have the following SQLite table: CREATE TABLE podcast_search ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, search TEXT NOT NULL UNIQUE ) Whenever a user inserts/updates a row in the table, I want to sort that row at the end of the table. Thus, if I insert the following values: _id | search |...
Heath Borders
1

votes
1

answer
27

Views

Set default value for WHERE clause for specific columns

Suppose I've created a table like this: CREATE TABLE stuff( a1 TEXT, a2 TEXT, . . . an TEXT, data TEXT ); I need to make SELECT queries like this: SELECT data FROM stuff WHERE a1="..." and a2="..." and . . . and an="..." selecting a specific value of data, with all a values specified. There are a lo...
SU3
0

votes
0

answer
6

Views

Python SQLite3 function not printing any data

I have created a function that is supposed to send all the items, with a stock level of less than 10, in my database to a text file. But i am not receiving any data when I press the reorder button. def Database(): global conn, cursor conn = sqlite3.connect("main_storage.db") cursor = conn.cursor() c...
R. Place
1

votes
2

answer
18

Views

Finding the Max of multiple elements in the same column

Table A is given with A.i, A.j, and A.val. A makes a 3x3 matrix. I need to find the Max(A.val) from A.val where its ij, i+1j, ij+1,i-1j, ij-1 if it exists. However, I can't find any solution to finding the max value of specific elements of the same column. Please help. Sample: i | j | val 0...
Steven Choi
0

votes
0

answer
12

Views

How to write multiple queries, where each query depends on results of a previous query(sqlite)

Problem: I have 4 tables (Items, Users, Reviews and Comments). I need to get reviews based on the item name, this query is simple SELECT * FROM Reviews WHERE item_id =(SELECT id FROM Items WHERE itemname=${itemname}) But then, based on Reviews.id, Reviews.author I need to all pull info from Users (...
Bradley M
0

votes
2

answer
28

Views

scraping weather information

i have scraped some weather information onto my website and now want to put them into sqlite3 each time the weather page on my website refreshes. the problem im having is inserting the information into the database as the weather is already displaying on my website. hope someone can help me thanks d...
Isaac Bajada
0

votes
0

answer
4

Views

get average value from sqlite database using nodejs and sqlite 3

I have the following code, where I am trying to insert a record into SQLite database and trying to read from it. let stmt = db.prepare('INSERT INTO ratings(post_id, rating) VALUES (?, ?)') stmt.run(1,4); stmt.finalize() // code above works succefully let stmt2 = db.prepare(`SELECT AVG(rating) as a...
Shobi
1

votes
2

answer
20

Views

If Query Didn't Find Any Match

I have a webpage that creates a cookie in client's computer named "token" with a random value inside when he is logging in. That cookie is created from a separate login page and not included here as the cookie is already getting created without problems. Then I match the cookie token's value from th...
haZh
0

votes
2

answer
20

Views

How to fix syntax error in SQLite in Python

I've finished making some SQLite tables and am executing the instructions. When executing the instructions, the following error has come up: sqlite3.OperationalError: near "Category": syntax error Most of my tables use the same sort of format, below is an example of one such table. CategoryTableSQL...
Umar Ali Khan
0

votes
1

answer
21

Views

How to declare the datatype of date and year in SQLite?

I'm creating a SQLite database on python and am on the stage of creating tables. Within the table below I'm storing a date to an ID and need help with the datatype on the date. I'm not sure if there's an inbuilt one or if I do text and then format the date when inputting the data. I also have a simi...
Umar Khan
0

votes
0

answer
4

Views

How to populate a django manytomany database from excel file

I'm trying to transfer data from a excel file into a manytomany table in my sqlite3 database. model.py from django.db import models class Major(models.Model): name = models.CharField(max_length=30, db_index=True) class School(models.Model): name = models.CharField(max_length=50, db_index=True) major...
david yeritsyan
3

votes
1

answer
24

Views

How can I improve DELETE performance from an SQLite table with foreign keys?

Deleting from a table in SQLite with foreign keys enabled seems very slow. Is there anything that can be done to improve the performance (without disabling the foreign keys)? import time import sqlite3 def fk_check(state): conn = sqlite3.connect(":memory:") c = conn.cursor() c.execute("CREATE TABLE...
Lex Berezhny
1

votes
2

answer
73

Views

How to use Android NDK SQLite3 extension json1

I am sorry that I lack English first. I compiled it because I needed SQLite3 extension JSON1 in Android NDK gcc -Os -I. -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_JSON1 \ -DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_USE_ALLOCA \ -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 -DSQLITE_LIK...
SeongJae Lee
1

votes
2

answer
37

Views

Trying to introduce a simple new condition to a JOIN query

I am using SQLite and I had someone help me construct this JOIN query which works quite well, but now I need to add another condition but I am having trouble introducing it to the query without it breaking. In both tables used in the JOIN there is a column called EventId and I want to introduce the...
AzzyDude
0

votes
0

answer
4

Views

fix error KeyError: 'id' in import_export django package while importing excel file

i am trying to import excel file into sqlite 3 database using import_export package in django. excel file consiste of 2 columns : id:(empty values) name:(contains values) its not working and display the below error: Errors Line number: 1 - 'id' None, smugling Traceback (most recent call last): File...
Django Dg
17

votes
5

answer
4.1k

Views

can't activate sqlite3 (~> 1.3.6), already activated sqlite3-1.4.0

I’m using Ubuntu and run into to a problem when using db:migrate for ruby project. rails aborted! LoadError: Error loading the 'sqlite3' Active Record adapter. Missing a gem it depends on? can't activate sqlite3 (~> 1.3.6), already activated sqlite3-1.4.0. Make sure all dependencies are added to...
Juan Rodriguez
0

votes
0

answer
3

Views

sqlite3 - does BLOB and TEXT data need to be in quotes?

I cannot find a resource on this. I am using the C library. When inserting TEXT and BLOB data by constructing a basic char query, ie "INSERT INTO table (a,b,c) values (1,sometext,someblob);" do sometext and someblob need to be in quotes?
peer_2_peer_2
1

votes
2

answer
75

Views

Passing python variable to sql

I have a python script that I want to extract data to a list from a DB, and then print it out. Here is the code: sql = "SELECT * FROM example WHERE column1 == 'string_value' AND column2 = float_value AND column3 == 'string_value'" # Assemble list. query = [] for row in c.execute(sql): query.append(r...
Oscar Dulzaides
1

votes
1

answer
455

Views

SQL table with “list” entry vs SQL table with a row for each entry

I have a Sqlite table where each row is the form of: value, "a,b,c,d,e,f,g,h,i,j", value3, value4 Where my queries look for a pairing of value, %b% using LIKE. Is it more efficient in terms of query time to instead have a table where each row is a permutation: value, a, value3, value4 ... value, j,...
user2967087
1

votes
1

answer
1.7k

Views

How to install SQLite in Windows (when there's no sqlite-shell and sqlite3.exe)?

Most of the tutorial I found online mention sqlite-shell to install sqlite in Windows, but I could not find it in SQLite download page nor through searching online anywhere else. When I went to the download page, the ones under Windows are only: Precompiled Binaries for Windows sqlite-dll-win32-x86-...
justanobody
1

votes
3

answer
720

Views

Tkinter, curselection()[0], IndexError: tuple index out of range

I am connecting frontend to backend of a database application using Tkinter and sqlite3. Need help finding potential reasons that resulted in this error: Exception in Tkinter callback Traceback (most recent call last):   File "C:\Users\Peng\AppData\Local\Programs\Python\Python36\lib\tkinte...
Hongrun Zhou
0

votes
0

answer
2

Views

Create a view in SQLite3 that lists all unique paths through tree-like database structure

I'm using SQLite3 (and python, but that's not important for the question, I don't think) to build a database of parts in a hierarchical assembly with multiple nesting levels, where every part in the assembly can have multiple variants. The database can include entries from multiple assemblies which...
Beezum
1

votes
1

answer
44

Views

How to drop a table (sqlite3) in Django==2.1?

I created the following models in my app events : from django.db import models from django.utils import timezone from django.urls import reverse class EventType(models.Model): type_of_event = models.CharField(max_length=100, unique=True) def __str__(self): return self.type_of_event class Event(model...
marcos
1

votes
2

answer
2k

Views

Android SQLite created database but table can't be found

So I'm trying to fetch data from one of my database tables, could you please check this out for me and see if you can spot the error? SQLiteDatabase db = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.OPEN_READONLY); Cursor cursor = db.query(TABLE_CLASSES, new String[] { TABLE_C_DAY, TABL...
Daniel Wardin
1

votes
2

answer
7.3k

Views

Setting Password for Android Sqlite Database

I am designing one Custom library, in that library to store the confidential data. For storing the data i have 2 options. 1) shared preferences. 2) Sqlite3 database. If , i use SharedPreferences with MODE_PRIVATE . It can be accessed by the application developers those who are use my library. Second...
Naveen
1

votes
1

answer
3.7k

Views

Getting data to SQLite database in Django

I'm new to Python and Django and I have ran into problems, I couldnt find answer to. I'm using Django 1.7 with SQLite3 and Python 3.4. I'm trying to make a sports league table, by getting data from a third party website and posting it to my own. So far I can get the needed data from the web. I have...
Jagulari
1

votes
1

answer
916

Views

syncing sqlite3 for Django tutorial

I'm starting the Django tutorials and have virtual environments and django installed. I'm working on a Mac 10.6.8, which has sqlite3 already installed. I'm working inside a virtual environment. I changed the settings.py file to: ENGINE -- 'django.db.backends.sqlite3' I left the NAME -- in the set...
user1552159
1

votes
1

answer
53

Views

Trying to modify an sqlite3 insert to return the PK. Wasn't sure how to do it with sqlite3 specifically

I was trying to insert into a table some data, but I wasn't sure what flag allows me to return the primary key. I believe I recall MSSQL using RETURNING, and some others tack on RETURNS at the end. could someone help out with the appending of it? I'm trying to return TABLEA.a, and my query and desig...
Fallenreaper
1

votes
2

answer
751

Views

Get SQLite free space %

Is there a way to get the amount of free space available to an application's SQLite DB? I see that SQLiteDatabase.getMaximumSize() returns the maximum size of a DB, but is there a way to get the current amount of bytes taken up by the database?
Stealth Rabbi
1

votes
1

answer
3.5k

Views

How do I display a d3.js circle pack graph with RoR?

I am trying to set up a graph like this http://bl.ocks.org/4063269#index.html with d3.js: I need to do this by putting methods in a controller and the js in a .html.haml file. I have made the controllers, but have absolutely no idea how to write the methods. The methods need to take values from a s...
areke
1

votes
2

answer
994

Views

Android - deleted my database manually, app won't start anymore?

I deleted my database file manually (using *adb shell* and sqlite3). Ever since then, my app doesn't recreates the database file (and throws an exception upon starting). I'm using ORMLite. I tried wiping the data before starting the emulator ("Wipe data"). But it didn't help. Any suggestions?
Tool
1

votes
1

answer
42

Views

Creates new database when there is no file instead of raising error

Im using SQLite and PDO in PHP for the first time. I wrote simple connect procedure: try { $link = new PDO("sqlite:".$file); $link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo 'SQLite error: ' . $e->getMessage(); } I moved my database file, to test er...
Kamil
1

votes
1

answer
448

Views

Comparing strings that contain accents in SQLite for Java doesn't work

I'm reading some bowling players' names in a file and then I need to find those names in a SQLite database to calculate their average. Everything works perfectly, except for names that contain accents (such as "É"). I've used PRAGMA Encoding in SQLite Manager and the database is encoded in UTF-8. I...
Adam Smith

View additional questions