pyodbc/sql – Newly created tables are going to Master database and not specified database

I am working on a project for class, where I am using Python to create a database and tables in my 2016 SQL Server. I have installed pyodbc and was able to establish a connection between Python and my SQL Server. However, when I run my code, I am able to create the ‘mydatabase’ database…but the subsequent tables that I want to create are going to the ‘Tables’ folder in the ‘Master’ database.

I have tried it both with creating the database and the tables in the same Python file, as well as only adding the tables. The result is always the same – it goes to the Master database, and not the ‘mydatabase’ database. I’ve done quite a bit of Googling, and it seems like I have the code written correctly; so, I am not sure what to look at now.

Here is the code for creating the database and tables in one fair swoop…

import pyodbc

mydb = pyodbc.connect('Driver={SQL Server};'
                      'Server=DESKTOP-5I015MMSQLEXPRESS;'
                      'Trusted_Connection=yes;')

mydb.autocommit = True

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

Q1 = "CREATE TABLE Contact(id INT IDENTITY(1,1) PRIMARY KEY, 
        username varchar(255) NOT NULL, password varchar(255) NOT NULL, 
        firstName varchar(255) NOT NULL, middleName varchar(255) NOT NULL, 
        lastName varchar(255) NOT NULL, address varchar(255) NOT NULL, 
        officePhone varchar(15) NOT NULL, cellPhone varchar(15) NOT NULL, 
        email varchar(255) NOT NULL)"
mycursor.execute(Q1)

Q2 = "CREATE TABLE Manufacturer(name varchar(255) PRIMARY KEY, 
        registeredCountry varchar(255) NOT NULL, 
        contactPerson INT NOT NULL, 
        FOREIGN KEY(contactPerson) REFERENCES Contact(id))"
mycursor.execute(Q2)

Q3 = "CREATE TABLE TestLab(name varchar(255) PRIMARY KEY, 
        address varchar(255) NOT NULL, 
        contactPerson INT NOT NULL, 
        FOREIGN KEY(contactPerson) REFERENCES Contact(id))"
mycursor.execute(Q3)

Q4 = "CREATE TABLE Product(modelNumber varchar(255) PRIMARY KEY, 
        manufacturer varchar(255) NOT NULL, 
        maufacturingDate date NOT NULL, 
        prodLength decimal(5,2) NOT NULL, 
        prodWidth decimal(5,2) NOT NULL, 
        cellTechnology varchar(255) NOT NULL, 
        cableType varchar(255) NOT NULL, 
        maximumSystemVoltage varchar(255) NOT NULL, 
        ratedVoc decimal(3,1) NOT NULL, 
        ratedIsc decimal(3,2) NOT NULL, 
        ratedVmp decimal(3,1) NOT NULL, 
        ratedImp decimal(3,2) NOT NULL, 
        ratedPmp decimal(4,1) NOT NULL, 
        ratedFf decimal(3,1) NOT NULL, 
        FOREIGN KEY(manufacturer) REFERENCES Manufacturer(name))"
mycursor.execute(Q4)

Q5 = "CREATE TABLE TestResults(dataSource varchar(255) NOT NULL, 
        modelNumber varchar(255) NOT NULL, 
        reportingCondition varchar(255) NOT NULL, 
        testSequence varchar(255) NOT NULL, 
        testDate date NOT NULL, 
        isc decimal(3,2) NOT NULL, 
        voc decimal(3,1) NOT NULL, 
        imp decimal(3,2) NOT NULL, 
        vmp decimal(3,1) NOT NULL, 
        pmp decimal(4,1) NOT NULL, 
        ff decimal(3,1) NOT NULL, 
        noct decimal(3,1) NOT NULL, 
        PRIMARY KEY(dataSource, modelNumber), 
        FOREIGN KEY(dataSource) REFERENCES TestLab(name), 
        FOREIGN KEY(modelNumber) REFERENCES Product(modelNumber))"
mycursor.execute(Q5)

And here is the code for just creating the tables in the ‘mydatabase’ database, if the database already existed in SQL…

import pyodbc

mydb = pyodbc.connect('Driver={SQL Server};'
                      'Server=DESKTOP-5I015MMSQLEXPRESS;'
                      'Database=mydatabase;'
                      'Trusted_Connection=yes;')

mydb.autocommit = True

mycursor = mydb.cursor()

Q1 = "CREATE TABLE Contact(id INT IDENTITY(1,1) PRIMARY KEY, 
        username varchar(255) NOT NULL, password varchar(255) NOT NULL, 
        firstName varchar(255) NOT NULL, middleName varchar(255) NOT NULL, 
        lastName varchar(255) NOT NULL, address varchar(255) NOT NULL, 
        officePhone varchar(15) NOT NULL, cellPhone varchar(15) NOT NULL, 
        email varchar(255) NOT NULL)"
mycursor.execute(Q1)

Q2 = "CREATE TABLE Manufacturer(name varchar(255) PRIMARY KEY, 
        registeredCountry varchar(255) NOT NULL, 
        contactPerson INT NOT NULL, 
        FOREIGN KEY(contactPerson) REFERENCES Contact(id))"
mycursor.execute(Q2)

Q3 = "CREATE TABLE TestLab(name varchar(255) PRIMARY KEY, 
        address varchar(255) NOT NULL, 
        contactPerson INT NOT NULL, 
        FOREIGN KEY(contactPerson) REFERENCES Contact(id))"
mycursor.execute(Q3)

Q4 = "CREATE TABLE Product(modelNumber varchar(255) PRIMARY KEY, 
        manufacturer varchar(255) NOT NULL, 
        maufacturingDate date NOT NULL, 
        prodLength decimal(5,2) NOT NULL, 
        prodWidth decimal(5,2) NOT NULL, 
        cellTechnology varchar(255) NOT NULL, 
        cableType varchar(255) NOT NULL, 
        maximumSystemVoltage varchar(255) NOT NULL, 
        ratedVoc decimal(3,1) NOT NULL, 
        ratedIsc decimal(3,2) NOT NULL, 
        ratedVmp decimal(3,1) NOT NULL, 
        ratedImp decimal(3,2) NOT NULL, 
        ratedPmp decimal(4,1) NOT NULL, 
        ratedFf decimal(3,1) NOT NULL, 
        FOREIGN KEY(manufacturer) REFERENCES Manufacturer(name))"
mycursor.execute(Q4)

Q5 = "CREATE TABLE TestResults(dataSource varchar(255) NOT NULL, 
        modelNumber varchar(255) NOT NULL, 
        reportingCondition varchar(255) NOT NULL, 
        testSequence varchar(255) NOT NULL, 
        testDate date NOT NULL, 
        isc decimal(3,2) NOT NULL, 
        voc decimal(3,1) NOT NULL, 
        imp decimal(3,2) NOT NULL, 
        vmp decimal(3,1) NOT NULL, 
        pmp decimal(4,1) NOT NULL, 
        ff decimal(3,1) NOT NULL, 
        noct decimal(3,1) NOT NULL, 
        PRIMARY KEY(dataSource, modelNumber), 
        FOREIGN KEY(dataSource) REFERENCES TestLab(name), 
        FOREIGN KEY(modelNumber) REFERENCES Product(modelNumber))"
mycursor.execute(Q5)

Answer

After the row Mycursor.execute("CREATE DATABASE mydatabase") Add mycursor.execute("USE mydatabase")

Advertisement

Answer