SQLITE with python : Adding the database name in SELECT statement

Question

I am using sqlite3 with python, and after connecting to the database and creating a table, sqlite3 shows an error when I try to execute a SELECT statment on the table with the name of the databse in it :

con = sqlite3.connect("my_databse")

cur = con.cursor() 

cur.execute('''CREATE TABLE my_table ... ''') 

cur.execute("SELECT * FROM my_database.my_table") # this works fine without the name of the database before the table name

but I get this error from sqlite3 : no such table : my_database.my_table

Is there a way to do a SELECT statment with the name of the database in it ?

is this a typo? your first line uses "my_databse". Also, since you've already connected to that database, why are you trying to use the database name in the select statement? — M Z
– M Z, Commented Jun 10, 2022 at 9:11
Why do you need to do that? That notation is for DBMSs that give you access to several databases with a single connection. But a sqlite file contains only one database. There is no point in prefixing it to the table name. If you are doing this because you plan to migrate your code to a multi-user database at some point, it might be better to do your development on a database that is more like the intended target. Naming the tables will be the least of your problems migrating from sqlite. — BoarGules
– BoarGules, Commented Jun 10, 2022 at 9:14
Oh sorry abou that, but that's not the problem, the code works fine @MZ — Mike
– Mike, Commented Jun 10, 2022 at 9:15
@BoarGules I am working with third party code ( that interacts with my python scripts) that generates SQL statements (prefixed with the database name) that i can not change, i can only work the SQLITE3 database that I create — Mike
– Mike, Commented Jun 10, 2022 at 9:21

Code-Apprentice · Accepted Answer · 2022-06-10 09:17:08Z

1

The short answer is no you can't do this with SQLite. This is because you already specify the database name with sqlite3.connect() and SQLite3 doesn't allow multiple databases in the same file.

answered Jun 10, 2022 at 9:17

Code-Apprentice

84k26 gold badges162 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M Z · Accepted Answer · 2022-06-10 09:22:54Z

0

Make sure of the database is in the same directory with the python script. In order to verify this you can use os library and os.listdir() method. After connecting the database and creating the cursor, you can query with the table name.

cur.execute('SELECT * FROM my_table')

edited Jun 10, 2022 at 9:22

M Z

4,8392 gold badges15 silver badges28 bronze badges

answered Jun 10, 2022 at 9:11

Esad Akçam

365 bronze badges

1 Comment

Mike Over a year ago

The database is in the same directory of the python script, so i don't think that's an issue

Collectives™ on Stack Overflow

SQLITE with python : Adding the database name in SELECT statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related