Submitted by Viandante on Tue, 09/21/2010 - 19:23
Posted in
Hi,
I'm looking for a way to use sqlite databases as datasets to make statistical computations in R.
I've been trying to use the Sqlite data frame package, but it doesn't seem to work. Maybe it's not meant to connect directly to sqlite databases?
I keep receiving 2 errors
1
RPy_RException: Error in eval(expr, envir, enclos) : could not find function "dbDriver"
2
UnicodeEncodeError: 'ascii' codec can't encode character u'\x9b' in position 14: ordinal not in range(128)
If there is no fix for the package, do you have any workaround to use sqlite anyway?
Thanks!
New Database Interface Widget
I placed another database interaction widget in the new version of the rsqlitedataframe package. This should be available using the package manager.
Called SQL Interface.
Thanks
Kyle
OK thanks.
Since you are using linux I assume you are using the SVN to get packages. I'll let you know as soon as I make the changes. Or if you are learning python they you can open the
runSQLiteQuery.py file and go to line 215. There you should change str(d) to d.encode('latin-1')
That should fix the error that you see.
File: runSQLiteQuery.py, line 215 in html_table
Code: s += '
I'm sorry instead, because I can't help you on this. I'm learning python, but not R. Actually, I find R not that easy to get, I still have to grasp its logic.
I will try with RSqlite and, if I manage to do something, I'll post the results.
My linux machine seems to be permanently crashes so i'll have to reinstall linux and all of that. it might take more than a little time.
sqlite doesn't have a requirement for unicode but the function that you are running into does. I'm working on fixing that now.
The sqlite package actually uses two sqlite drivers. One is in python and uses the sqlite3 package in python. The other is in R.
If you know sqlite you may be able to use the executor to query the database using the RSQlite package. It won't be as nice as using red-r for doing the querys but it should work. Or you could make text files from the database and then read them using read files.
Sorry for the inconvenience. I'm working on it as fast as I can.
Kyle
Thanks.
Do you think there is a chance to make it work on linux (Ubuntu/Mint) soon?
Regarding the ascii, I think that characters in the database are unicode, why does it need them to be ascii?
Anyway, do you see other ways to use sqlite? I may use R commands, isn't it? If this is possible, that could be a fast fix, at least to have enough time to fix the problem in linux.
OK I'll take a look at the code. You seem to be using this in linux which isn't fully supported at the moment.
it looks like you have some characters that aren't ascii in the database. The problem is in the section that makes the table specifically in the formatting of the strings into html for viewing. That should be protected from errors like this so I will make those changes and ensure that the data can be sent to downstream widgets.
For the dbDriver issue this might be something that has changed from R 2.9.1 to 2.11 or a problem with the dbi package. All of this should have been handled by your Red-R call to install the RSQLite package.
Red-R is only configured to work on R 2.9.1 (because windows versions of rpy working on 2.11 don't exist).
Hope this helps. I hate to say it but you might have to move to a windows machine.
Kyle
First of all, thanks to answer.
I did the checks you said. First, I think I didn't have DBI. In fact, when I did "install.packages('DBI')" it downloaded the package in: /home/alessio/R/i486-pc-linux-gnu-library/2.11. Which is my home directory.
Then, I tried to make the same operation* and I got the same error with DBI or not (I manually deleted DBI from /home/alessio/R/i486-pc-linux-gnu-library/2.11 for this try).
It's interesting that, reading the error better, the 2 errors I mentioned appear in the same time doing a certain operation*.
*The operation is "Run SQL Query" and the query is a simple "Select * from TableName". I've also tried to display data. This seems to work for the views I've made in Sqlite, but it doesn't for the whole table.
Here the error output:
[quote]
Unhandled exception of type RPy_RException occured at 2010-09-22 10:38:28.685125:
Traceback:
File: widgetSignals.py, line 234 in processSignals
Code: value = oldValue.convertToClass(signal[1])
File: SQLiteTable.py, line 42 in convertToClass
Code: return self._convertToDataFrame()
File: SQLiteTable.py, line 141 in _convertToDataFrame
Code: self.R('m<-dbDriver("SQLite")')
File: RSession.py, line 58 in Rcommand
Code: raise rpy.RPyRException(str(inst))
RPy_RException: Error in eval(expr, envir, enclos) : could not find function "dbDriver"
Unhandled exception of type RPy_RException occured at 2010-09-22 10:38:30.044723:
Traceback:
File: widgetSignals.py, line 234 in processSignals
Code: value = oldValue.convertToClass(signal[1])
File: SQLiteTable.py, line 42 in convertToClass
Code: return self._convertToDataFrame()
File: SQLiteTable.py, line 141 in _convertToDataFrame
Code: self.R('m<-dbDriver("SQLite")')
File: RSession.py, line 58 in Rcommand
Code: raise rpy.RPyRException(str(inst))
RPy_RException: Error in eval(expr, envir, enclos) : could not find function "dbDriver"
Unhandled exception of type UnicodeEncodeError occured at 2010-09-22 10:40:06.411812:
Traceback:
File: runSQLiteQuery.py, line 186 in runStatement
Code: self.updateScan()
File: runSQLiteQuery.py, line 201 in updateScan
Code: txt = self.html_table(colnames,data)
File: runSQLiteQuery.py, line 215 in html_table
Code: s += '
UnicodeEncodeError: 'ascii' codec can't encode character u'\x9b' in position 14: ordinal not in range(128)
Unhandled exception of type UnicodeEncodeError occured at 2010-09-22 10:42:21.213485:
Traceback:
File: runSQLiteQuery.py, line 186 in runStatement
Code: self.updateScan()
File: runSQLiteQuery.py, line 201 in updateScan
Code: txt = self.html_table(colnames,data)
File: runSQLiteQuery.py, line 215 in html_table
Code: s += '
UnicodeEncodeError: 'ascii' codec can't encode character u'\x9b' in position 14: ordinal not in range(128)
[/quote]
Hi Viandante,
I use the sqlitedataframe package quite often to connect data from sqlite to R.
I haven't uploaded the sqlitedataframe package to red-r1.8 yet (at least I don't think so). the dbDriver problem comes from R. the database driver package must not have been installed properly.
I haven't tested the package trying to open a sqlite database that wasn't made using either R or python so error 2 might be a problem with what version of sqlite was used to make your database, that's just a guess.
I'll check the sqlitedatabase package for red-r1.8 and hopefully upload it to the package manager system tomorrow.
the dbDriver for R is here http://bioinformatics.holstegelab.nl/manuals/R/library/DBI/html/dbDriver.html.
you can check if DBI is in the R libraries directory of the R instance beside the red-r directory in program files. if it is, then you can delete it and see if red-r tries to get DBI again when you connect a sqlite database widget to an R widget. If that doesn't happen please let me know and I can check the code to see if there are some problems with this.
Thanks
Kyle