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 ?

• M Z: 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?
• BoarGules: 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.
• Mike: Oh sorry abou that, but that's not the problem, the code works fine @MZ
• Code-Apprentice: @BoarGules This should be an answer, not a comment.
• Mike: @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

Answer 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.

Answer 2

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')

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