sqlalchemy MSSQL+pyodbc schema none

Question

I'm trying to connect to SQL server 2019 via sqlalchemy. I'm using both mssql+pyodbc and msql+pyodbc_mssql, but on both cases it cannot connect, always returns default_schema_name not defined. Already checked database, user schema defined and everything.

Example:

from sqlalchemy import create_engine
import urllib 
from sqlalchemy import create_engine
server = 'server' 
database = 'db' 
username = 'user' 
password = 'pass' 
#cnxn = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password+';Trusted_Connection=yes'
cnxn = 'DSN=SQL Server;SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password+';Trusted_Connection=yes'
params = urllib.parse.quote_plus(cnxn)

engine = create_engine('mssql+pyodbc:///?odbc_connect=%s' % params)
cnxn = engine.connect()

return None, dialect.default_schema_name
AttributeError: 'MSDialect_pyodbc' object has no attribute 'default_schema_name'

TIA.....

Answer 1

Hopefully the following provides enough for a minimum viable sample. I'm using it in a larger script to move 12m rows 3x a day, and for that reason I've included an example of chunking that I pinched from elsewhere.

#Set up enterprise DB connection
# Enterprise DB to be used
DRIVER = "ODBC Driver 17 for SQL Server"
USERNAME = "SQLUsername"
PSSWD = "SQLPassword"
SERVERNAME = "SERVERNAME01"
INSTANCENAME = "\SQL_01"
DB = "DATABASE_Name"
TABLE = "Table_Name"

#Set up SQL database connection variable / path
#I have included this as an example that can be used to chunk data up
conn_executemany = sql.create_engine(
    f"mssql+pyodbc://{USERNAME}:{PSSWD}@{SERVERNAME}{INSTANCENAME}/{DB}?driver={DRIVER}", fast_executemany=True
)




#Used for SQL Loading from Pandas DF
def chunker(seq, size):
    return (seq[pos : pos + size] for pos in range(0, len(seq), size))

#Used for SQL Loading from Pandas DF
def insert_with_progress(df, engine, table="", schema="dbo"):
    con = engine.connect()

    # Replace table
    #engine.execute(f"DROP TABLE IF EXISTS {schema}.{table};") #This only works for SQL Server 2016 or greater
    try:
      engine.execute(f"DROP TABLE Temp_WeatherGrids;")
    except:
      print("Unable to drop temp table")
    
    try:
      engine.execute(f"CREATE TABLE [dbo].[Temp_WeatherGrids]([col_01] [int] NULL,[Location] [int] NULL,[DateTime] [datetime] NULL,[Counts] [real] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];")
    except:
      print("Unable to create temp table")
    

    # Insert with progress
    SQL_SERVER_CHUNK_LIMIT = 250000
    chunksize = math.floor(SQL_SERVER_CHUNK_LIMIT / len(df.columns))

    for chunk in chunker(df, chunksize):
        chunk.to_sql(
            name=table,
            con=con,
            if_exists="append",
            index=False
        )


if __name__ == '__main__':

    # intialise data. Example -  make your own dataframe. DateTime should be pandas datetime objects. 
    data = {'Col_01':[0, 1, 2, 3], 
            'Location':['Bar', 'Pub', 'Brewery', 'Bottleshop'],
            'DateTime':["1/1/2018", "1/1/2019", "1/1/2020", "1/1/2021"],
            'Counts':[1, 2, 3, 4} 
  
    # Create DataFrame 
    df = pd.DataFrame(data) 

    insert_with_progress(df, conn_executemany, table=TABLE)
    del [df]