Issue creating table in MS SQL Database with Python script

Question

  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 1

I think you need to add cnxn.commit() command just before the cnxn.close(). Thats how you save the changes made in the program to the database

Answer 2

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