pyodbc error schema does not exist or you do not have permission

Question

My code:

con = pyodbc.connect("DRIVER={SQL Server};SERVER=WIN-RCNSTVE00SV\SQLEXPRESS;UID=sa;PWD=password;database=master",autocommit=True)
cursor = con.cursor()

cursor.execute("create database test_db")
cursor.execute("create table test_db.tb(id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, random_txt VARCHAR(50), sequence TEXT, status BIT)")

After creating the database, I'm not able to create a table in it. I get this error:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The specified schema name "test_db" either does not exist or you do not have permission to use it. (2760)

I tried executing the grant permission query after the create database but got the same error. I also tried creating a new con(connection) specifying the database but didn't work. Please help.

Answer 1

You are missing schema name. It should be

create table test_db.dbo.tb
(                  --^^^ Missing schema name 
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
random_txt VARCHAR(50), 
sequence TEXT, 
status BIT
)

A fully qualified object name will be in this format

DatabaseName.SchemaName.ObjectName

If you have any other schema created and you want to create the new table in that schema then replace the schema name with dbo. By default the objects will be created in dbo schema. If you want to create the table in dbo schema then you can write like this as well

Create table test_db..tb()

.. implicitly points to the dbo schema