Error loading from libreoffice base table

[Chaostycoon]

So I’m trying to learn how to manage databases for a fun project, basically how cursed I am when rolling dice in DnD. I’ve created a table within Libre office base with rolls from a few sessions and I think I’ve connected successfully to the database but can’t seen to access the table.

however when I try to load i get the following:

c:/Users/Raymond/python/curseliftersrolls.py
Traceback (most recent call last):
File “C:\Python310\lib\site-packages\sqlalchemy\engine\base.py”, line 1819, in _execute_context
self.dialect.do_execute(
File “C:\Python310\lib\site-packages\sqlalchemy\engine\default.py”, line 732, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: near “DnDRolls”: syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File “c:\Users\Raymond\python\curseliftersrolls.py”, line 11, in
rolls_by_people = pd.read_sql(“DnDRolls”, engine)
File “C:\Python310\lib\site-packages\pandas\io\sql.py”, line 592, in read_sql
return pandas_sql.read_query(
File “C:\Python310\lib\site-packages\pandas\io\sql.py”, line 1557, in read_query
result = self.execute(*args)
File “C:\Python310\lib\site-packages\pandas\io\sql.py”, line 1402, in execute
return self.connectable.execution_options().execute(*args, **kwargs)
File “”, line 2, in execute
File “C:\Python310\lib\site-packages\sqlalchemy\util\deprecations.py”, line 401, in warned
return fn(*args, **kwargs)
File “C:\Python310\lib\site-packages\sqlalchemy\engine\base.py”, line 3176, in execute
return connection.execute(statement, *multiparams, **params)
File “C:\Python310\lib\site-packages\sqlalchemy\engine\base.py”, line 1291, in execute
return self._exec_driver_sql(
File “C:\Python310\lib\site-packages\sqlalchemy\engine\base.py”, line 1595, in _exec_driver_sql
ret = self._execute_context(
File “C:\Python310\lib\site-packages\sqlalchemy\engine\base.py”, line 1862, in _execute_context
self.handle_dbapi_exception(
File “C:\Python310\lib\site-packages\sqlalchemy\engine\base.py”, line 2043, in handle_dbapi_exception
util.raise(
File “C:\Python310\lib\site-packages\sqlalchemy\util\compat.py”, line 208, in raise
raise exception
File “C:\Python310\lib\site-packages\sqlalchemy\engine\base.py”, line 1819, in _execute_context
self.dialect.do_execute(
File “C:\Python310\lib\site-packages\sqlalchemy\engine\default.py”, line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near “DnDRolls”: syntax error
[SQL: DnDRolls]
(Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)

all of that stems from these few lines in a python IDE

#connect with database
engine = create_engine(‘sqlite:///C:\Users\Raymond\Documents\New Database.odb’)

#load from table
rolls_by_people = pd.read_sql(“DnDRolls”, engine) <— this is where it errors

Any Help would be appreciated.

[Ratslinger]

Hello,
With questions regarding Base, please include specific LO version, OS, Database used (seems to be SqLite3) and connector type when appropriate (likely it is ODBC).
Need some clarity.
.
You state:

@Chaostycoon

I’ve created a table within Libre office base with rolls from a few sessions

.
but then state:

@Chaostycoon

I think I’ve connected successfully to the database but can’t seen to access the table.

.
How can you create the table through Base but then not access it? Please explain. Based upon the image there is a table named DnDRolls.
.
Are you getting this error when accessing that table through Base to view it? Or is it because of some code you are running?
.
Please provide additional information.

[Wanderer]

@Chaostycoon

all of that stems from these few lines in a python IDE

#connect with database
engine = create_engine(‘sqlite:///C:\Users\Raymond\Documents\New Database.odb’)

#load from table
rolls_by_people = pd.read_sql(“DnDRolls”, engine) <— this is where it errors

Interpreting your code I assume

• You have created a sqlite-database ?
• You can access this database through Base ? (The type of your database is shown in the status-line at the bottom of your Base-Window in the left half. Attention, If you read someting like hsql-embedded or firebird, you don’t have a sqlite-database! )
• Now you seem to try to get the data from an external python program via a separately installed python 3.10 using the pandas-library
• Your first error is then: pandas and python can “talk” directly to sqlite, no need to go through base. So in the line with create_engine your target should not be the odb-file.
• wich leads to the second error: If you use sqlite via base, the .odb-file doesn’t contain your data. It contains forms, queries and the information where to find your data via odbc or jdbc. The actual data is in a separate file. (Check the properties of your database via Menu, to find your data-file, then name this file in the create_engine command. In this case Base would not be used by pandas, but you can’t use the query in the .odb-file).
• But if you found hsql- or firebird -embedded as type of the database, the the creation of an sqlite-engine will not connect to your data, an you have to search, if pandas can connect to this kind of databases.

J.

[Chaostycoon]

Hi both of you,

After reading what you’ve put down, it seems that I don’t have SQLite installed, I’ve been following datacamp courses and assumed that the line was part of the syntax of the creating the engine. That might be where I’m going wrong here.
@Ratslinger
other bit of information
LO version 7.1
OS windows 10

I can access the table on Base and it brings up what I expect it to bring up.

@Wanderer

This proves i don’t have a SQLite database.

I’ll keep working on it

[Wanderer]

Ok, then some explanations:

For embedded databases the data are also in the .odb. When you open the file in Base the contents are unzipped to a temporary file on disk and packed again, when you close. Great to send little databases by mail to other users of LibreOffice, but can give trouble with data-security an the contents are usually not accessible by other programs.
.
To make data accessible you could export to csv, xls, dbase-format and access this files via pandas/sqlalchemy/python.
.
Otherwise you use an external database like sqlite, mysql, postgres and connect to the database twice: Once for Base, once from your pandas-program in python.

[Ratslinger]

@Chaostycoon
This post may be of interest to you:

Connecting Base to Sqlite in Windows

[flywire]

@Ratslinger

may be of interest

… Documentation/HowTo/Base/Connect to SQLite - The Document Foundation Wiki or Base Guide 7.3 in chapter 2.