sqlite3

atomic read from sqlite database while it is being written to

Is it possible to read from a sqlite database while it is being written to? How would one go about accomplishing this? Thanks! ...

how do i store the output of my cursor object to a text file?

I have accessed a database and have the result in a cursor object. when I try to save it to a text file, python says TypeError: argument 1 must be string or read-only character buffer, not sqlite3.Cursor can someone tell me what I should do here? curobject.execute('select * from device_auth') for row in curobject: print ...

Extract numeric values from Char in SQLite

Hi, I need to sort a column in database based on numeric characters in the string value.For eg one of the database column contains value a11,a12,ab01,ab02 etc.I need to sort like this ab01,ab02,a11,a12 etc,that is i need to extract only numeric values and then sort the result .Is it possible to do this using some query .I am using SQLi...

SQLite3 storing String which contains only numbers.

I have to run a very simple query like this in SQLite3. INSERT OR REPLACE INTO tblPhoneList(Phone, PeopleId, StorageType) VALUES('+91912345789', '1','1'); But whatever I do, the number is stored as 91912345789. The + prefix is ignored. The field Phone is a String. Why does it ignore the + prefix? Is there anyway to avoid this? P.S. T...

Continue loading after IntegrityError

In python, I am populating a SQLITE data base using the importmany, so I can import tens of thousands of rows of data at once. My data is contained as a list of tuples. I had my database set up with the primary keys where I wanted them. Problem I ran into was primary key errors would throw up an IntegrityError. If I handle the except...

Escaping chars in Python and sqlite

Hi, I have a python script that reads raw movie text files into an sqlite database. I use re.escape(title) to add escape chars into the strings to make them db safe before executing the inserts. Why does this not work: In [16]: c.execute("UPDATE movies SET rating = '8.7' WHERE name='\'Allo\ \'Allo!\"\ (1982)'") --------------...

Using SQLite3 on iPhone to get midnight (this morning) as a UNIX timestamp

Okay, I really know this has GOT to be the long way around doing this... however, what I want is relatively simple one would think. I have a database with a timestamp column. I am using iPhone SDK with SQLite3. I want to have SQLite3 get all records for today (where timestamp >= midnight this morning) .. What i have come up with (th...

Flushing and SQLite database on android

I have an android application using an SQLite database. I open the database when the application starts, but never close it as it is in constant use. What is the best way to tell the database to flush all its changes to permanent storage? Do I need to just close it and re-open or is there a more efficient way? My problem is that when t...

Count on the same table

Hi, i have got a problem with my SQL Statement: Table structure: CREATE TABLE "tags" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "page_id" INTEGER NOT NULL, "title" TEXT NOT NULL ); Now i want to select all the tags from one page and the frequency of this tag in the whole database. I have the following SQL statement: S...

storing the output of a cursor object in csv format

i've accessed a database and have the result in a cursor object. but i couldn't save it :( cur_deviceauth.execute('select * from device_auth') for row in cur_deviceauth: print row writer = csv.writer(open("out.csv", "w")) writer.writerows(cur_deviceauth) i don't get an error msg and i couldn't write it. how do i make it?...

Best way for search word in 150k words dictionnary in a iphone app ?

Hi, I m developping a simple dictionnary word app in french with 150k words and definitions. i m looking for the best way to do this. First i use a sqlite bdd with 150k words. i use the LIKE command for word search but it is very slow ex : SELECT * FROM words WHERE word LIKE '%avoi%' LIMIT 0,50; for searching word who contain 'avoi' li...

NoMethodError (set_result) in SQLite3 when running Geocommons Geocoder using irb on Mac OS X 10.5.8

Has anyone here ever encountered the following error after installing and attempting to run the Geocommons geocoder on Mac OS X 10.5.8? This is my exact output from the Terminal window from the point at which I started irb: $ irb >> require 'geocoder/us' => true >> db = Geocoder::US::Database.new("/opt/tiger/orangeca.db") => #<Geocoder...

SQLite short file names 8.3

Hello I am attempting to compile SQLite for an operating system that does not support long file names. The max file name is 8 chars long with an extension of 3 chars (8.3). Currently a "-journal" is created while using SQLite this breaks the file name rule and stops SQLite with "Disk I/O Error" I have tried to disable the journal f...

Simple SQL Lite table/import question

Hello, I have a simple SQL question. I want to make a 3 column database and I have the following code: sqlite3 meshdb.db "create table t1 (t1key INTEGER PRIMARY KEY, prideID, pubmedID);" When I try to import a simple csv file with two columns (prideID and pubmedID), I get a "expected 3 columns of data but found 2" error. I want the t...

Backing up sqlite3 database in Python 2.5

I'm working on a project that needs to support both Python 2.5 and 2.6, and uses sqlite3. I'd like to be able to backup the database from within the program. It seems to be that there are two ways to do this: create a new database in sqlite3 and move all the data, or simply copy the database file on the drive. My instinct (and part ...

Can I achieve scalable multi-threaded access to an in-memory SQLite database

I have a multi-threaded Linux C++ application that needs a high performance reference data lookup facility. I have been looking at using an in-memory SQLite database for this but can't see a way to get this to scale in my multi-threaded environment. The default threading mode (serialized) seems to suffer from a single coarse grained loc...

import on csv to sqlite3 crashes

Hello, I have a csv file that is comma delimited. I have no problem with the importing aspect of it except for when the import reaches a line like this: 1,2,3, Canada's,5, 6,7 The import crashes when it reaches the single quote. Does anyone know of a program I could use to import my csv file in to sqlite database without having to get...

Populating a SQLite3 database from a .txt file with Python

Dear Python Experts, I am trying to setup a website in django which allows the user to send queries to a database containing information about their representatives in the European Parliament. I have the data in a comma seperated .txt file with the following format: Parliament, Name, Country, Party_Group, National_Party, Position ...

How to dump sqlite database on windows?

I have a sqlite database.What is the command on windows using .dump to export to a file? And,how to import it back? Can I just dump the schema? thanks ...

use CASE with SQLITE for matching fields

I have an SQLite database with student names. Each student has different classes. I have a table with student grades and classes and another table that defines class by type. I'd like to run a query (that I'll be saving as a CSV to use in OpenOffice as an Excel document for a mail merge). I need to list the student's Math grade, History ...