Create a schema in SQL Server using pyodbc

Question

I am using pyodbc to read from a SQL Server database and create analogous copies of the same structure in a different database somewhere else.

Essentially:

  for db in source_dbs:
    Execute('create database [%s]' % db)  # THIS WORKS.
    for schema in db:
      # The following result in an error starting with:
      #   [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]

      Execute('create schema [%s].[%s]' % (db, schema)
      # Incorrect syntax near '.'

      Execute('use [%s]; create schema [%s]' %(db, schema)
      # CREATE SCHEMA' must be the first statement in a query batch.

In this example, you can assume that Execute creates a cursor using pyodbc and executes the argument SQL string.

I'm able to create the empty databases, but I can't figure out how to create the schemas within them.

Is there a solution, or is this a limitation of using pyodbc with MS SQL Server?

EDIT: FWIW - I also tried to pass the database name to Execute, so I could try to set the database name in the connection string. This doesn't work either - it seems to ignore the database name completely.

Gord Thompson · Accepted Answer · 2015-09-29 01:34:17Z

5

Python database connections usually default to having transactions enabled (autocommit == False) and SQL Server tends to dislike certain DDL commands being executed in a transaction.

I just tried the following and it worked for me:

import pyodbc
connStr = (
    r"Driver={SQL Server Native Client 10.0};"
    r"Server=(local)\SQLEXPRESS;"
    r"Trusted_connection=yes;"
)
cnxn = pyodbc.connect(connStr, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("CREATE DATABASE pyodbctest")
crsr.execute("USE pyodbctest")
crsr.execute("CREATE SCHEMA myschema")
crsr.close()
cnxn.close()

answered Sep 29, 2015 at 1:34

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

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

Collectives™ on Stack Overflow

Create a schema in SQL Server using pyodbc

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related