Skip to main content

No project description provided

Project description

fast_to_sql

Introduction

fast_to_sql is an improved way to upload pandas dataframes to Microsoft SQL Server.

fast_to_sql takes advantage of pyodbc rather than SQLAlchemy. This allows for a much lighter weight import for writing pandas dataframes to sql server. It uses pyodbc's executemany method with fast_executemany set to True, resulting in far superior run times when inserting data.

Installation

pip install fast_to_sql

Requirements

  • Written for Python 3.8+
  • Requires pandas, pyodbc

Example

from datetime import datetime

import pandas as pd

import pyodbc
from fast_to_sql import fast_to_sql as fts

# Test Dataframe for insertion
df = pd.DataFrame({
    "Col1": [1, 2, 3],
    "Col2": ["A", "B", "C"],
    "Col3": [True, False, True],
    "Col4": [datetime(2020,1,1),datetime(2020,1,2),datetime(2020,1,3)]
})

# Create a pyodbc connection
conn = pyodbc.connect(
    """
    Driver={ODBC Driver 17 for SQL Server};
    Server=localhost;
    Database=my_database;
    UID=my_user;
    PWD=my_pass;
    """
)

# If a table is created, the generated sql is returned
create_statement = fts.fast_to_sql(df, "my_great_table", conn, if_exists="replace", custom={"Col1":"INT PRIMARY KEY"}, temp=False)

# Commit upload actions and close connection
conn.commit()
conn.close()

Usage

Main function

fts.fast_to_sql(df, name, conn, if_exists="append", custom=None, temp=False)
  • df: pandas DataFrame to upload
  • name: String of desired name for the table in SQL server
  • conn: A valid pyodbc connection object
  • if_exists: Option for what to do if the specified table name already exists in the database. If the table does not exist a new one will be created. By default this option is set to 'append'
    • 'append': Appends the dataframe to the table if it already exists in SQL server.
    • 'fail': Purposely raises a FailError if the table already exists in SQL server.
    • 'replace': Drops the old table with the specified name, and creates a new one. Be careful with this option, it will completely delete a table with the specified name in SQL server.
  • custom: A dictionary object with one or more of the column names being uploaded as the key, and a valid SQL column definition as the value. The value must contain a type (INT, FLOAT, VARCHAR(500), etc.), and can optionally also include constraints (NOT NULL, PRIMARY KEY, etc.)
    • Examples: {'ColumnName':'varchar(1000)'} {'ColumnName2':'int primary key'}
  • temp: Either True if creating a local sql server temporary table for the connection, or False (default) if not.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

fast-to-sql-2.1.12.tar.gz (6.6 kB view hashes)

Uploaded Source

Built Distribution

fast_to_sql-2.1.12-py3-none-any.whl (8.2 kB view hashes)

Uploaded Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page