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?

I would start by querying that created table using the same connection, perhaps inserting something into it. Chances are that will succeed as well. Then the two possibilities are: 1) you don't have automatic transaction commit turned on 2) you are checking in the wrong schema and the table actually exists — MK.
– MK., Commented Oct 2, 2012 at 1:23
You are correct, the table does exist. Can you explain to me how to check the right schema within the Microsoft SQL Management Studio? — iwishiwasacodemonkey
– iwishiwasacodemonkey, Commented Oct 2, 2012 at 1:36
On the left-hand side tree, open the node called Databases. There will be a node in there for System Databases (with several created by default) and separate nodes for each user database you've created. Open the node for whichever database you connected to, find the node called tables, and open that. Now, you should see your newly created table and any others in the database. Right-click on the table name for various options. — Larry Lustig
– Larry Lustig, Commented Oct 2, 2012 at 1:49

Lightsout · Accepted Answer · 2021-02-08 22:08:46Z

6

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

edited Feb 8, 2021 at 22:08

Lightsout

3,7973 gold badges44 silver badges76 bronze badges

answered Jan 31, 2017 at 18:44

cuburt rivera

631 silver badge5 bronze badges

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

1 Comment

jax Over a year ago

I had the same issue and this was the answer to my problem. Unfortunately, in Microsoft documentation example it doesn't say you need to call commit.

4c74356b41 · Accepted Answer · 2019-01-26 15:08:05Z

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

edited Jan 26, 2019 at 15:08

4c74356b41

71.4k7 gold badges118 silver badges155 bronze badges

answered Oct 2, 2012 at 1:42

MK.

34.8k19 gold badges79 silver badges117 bronze badges

1 Comment

iwishiwasacodemonkey Over a year ago

Looking into the problem further, this line was missing. 'code' cursor.commit() #This statement writes the changes to the DB 'code' The reason the tables existed, was because the way I checked I found the temporary table.

David · Accepted Answer · 2018-09-14 20:55:45Z

1

Your statement does not have commit statement before you close the connection. Use cnxn.commit() statement before your cursor.close() and cnxn.close() statements.

edited Sep 14, 2018 at 20:55

David

6,0943 gold badges37 silver badges50 bronze badges

answered Sep 14, 2018 at 19:03

Sudhir Menon

411 bronze badge

Collectives™ on Stack Overflow

Issue creating table in MS SQL Database with Python script

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related