Fedora Linux Support Community & Resources Center
  #1  
Old 11th April 2009, 10:38 AM
premudriy Offline
Registered User
 
Join Date: Dec 2006
Posts: 313
Python+SQLite: how do I search the whole table(all columns)?

Hi,

I need an expert advice for searching SQLite databases:

This python script is intended to be used for multiple databases, so I don't know beforehand what tables db has or what columns each table contains. I need to be able to search somehow all columns of a given table without knowing their names.

So far I'm able to retrieve the table names and then iterate through them. I've tried to search each table as:


dbCursor.execute('SELECT * FROM '+givenTableVar+' WHERE * LIKE '%textString%')



that doesn't work, however, because I can't use widcard "*" for the name of the column after the WHERE clause. Pysqlite want's me to specifically tell it the column name that I'm searching, while I need to search all columns at once.

Are there any clever way to search the whole table ( or even the whole db) for a given string that I can use?


Thank you!
Reply With Quote
  #2  
Old 11th April 2009, 12:37 PM
daverj Offline
Registered User
 
Join Date: Jan 2006
Location: Denver, CO USA
Posts: 670
using a sql orm mapper like sqlalchemy might come in handy for something like this. It has the ability to extract the schema (table and column names) from an existing database. The schema could then be queried to find what you are looking for. I don't have any examples handy since it's been a while since I've use sqlalchemy. The sqlalchemy site has excellent documentation and O'Reilly even has a book on it.

Generally speaking, if you plan on doing a fair amount of database programming, a sql toolkit/orm mapper is the way to go. It will save you lots of time.

davidj
Reply With Quote
  #3  
Old 11th April 2009, 03:16 PM
sideways Offline
Retired User
 
Join Date: Oct 2004
Location: London, UK
Posts: 4,999
you're trying to do something with SQL which isn't supported, either specify each column in the WHERE clause and use OR, or select the whole table and pipe to the shell grep command to get specific lines, then parse the strings and reuse what is needed back in further SQL commands
Reply With Quote
  #4  
Old 12th April 2009, 07:46 AM
premudriy Offline
Registered User
 
Join Date: Dec 2006
Posts: 313
Thanks, guys! I ended up doing it with couple of loops by, first, pulling the array of table names from sqlite_master table and then another loop to cycle through each column name, which I pull out of dbCursor.description.
Reply With Quote
Reply

Tags
columns, python, search, sqlite, tableall

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQLite: Insert unique records into a table WITHOUT primary key? premudriy Programming & Packaging 6 15th February 2009 11:55 PM


Current GMT-time: 23:02 (Friday, 25-07-2014)

TopSubscribe to XML RSS for all Threads in all ForumsFedoraForumDotOrg Archive
logo

All trademarks, and forum posts in this site are property of their respective owner(s).
FedoraForum.org is privately owned and is not directly sponsored by the Fedora Project or Red Hat, Inc.

Privacy Policy | Term of Use | Posting Guidelines | Archive | Contact Us | Founding Members

Powered by vBulletin® Copyright ©2000 - 2012, vBulletin Solutions, Inc.

FedoraForum is Powered by RedHat