Issue creating table in MS SQL Database with Python script

  import pyodbc
  cnxn = pyodbc.connect('DRIVER={SQL Server};' +
                           'SERVER=' + data.dbConnection()[0] + ';' +
                           'DATABASE=' + data.dbConnection()[3] + ';' +
                           'UID=' + data.dbConnection()[1] + ';' +
                           'PWD=' + data.dbConnection()[2])
  cursor = cnxn.cursor()
  cursor.execute(
  """
  CREATE TABLE Persons
  (
  P_Id int,
  LastName varchar(255),
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255)
  )
  """
             )
  cursor.close()
  cnxn.close()

The above code successfully connects to the database. The script also returns no errors when run, however when I go to check if the table was created, there are no tables in the SQL DB at all.

Why is no table created and why is no error returned?

Answer

Each user in MS SQL Server has a default schema associated with it. The configuration details depend on the version of SQL server and it is configured in the Enterprise Manager. I don’t think SQL Management studio has GUI visibility into it.
You can try querying for it using:

select default_schema_name
, type_desc
, name
, create_date
from sys.database_principals
order by default_schema_name
, type_desc
, name

You can also explicitly create tables in a particular schema by prefixing schema name to the table name, i.e.

create myschema.mytable as...