Initialization

To start using the library, import the adgsqlserver module and create an instance of the adgsqlserver class by providing the connection string for your SQL Server database.

Download from PyPI:

pip install sqlserver

Initialize the class:

import sqlserver
db = sqlserver.adgsqlserver('yourconnectionstring')

Methods

ExecuteQuery

This method allows you to execute any query without expecting any output. It takes a query string as a parameter and returns True if the query execution is successful or False otherwise.

query = 'SELECT * FROM Customers'
db.ExecuteQuery(query)

GetRecordsAsDict

This method is used for executing SELECT statements or any other query that returns a table as a result. It takes a query string as a parameter and returns the query results as a dictionary.

query = 'SELECT * FROM Customers'
results = db.GetRecordsAsDict(query)

GetRecordsOfColumn

This method is used for executing SELECT statements or any other query that returns a table as a result. It takes a query string and the name of a specific column as parameters, and returns the values of that column as a list.

query = 'SELECT Name FROM Customers'
column_values = db.GetRecordsOfColumn(query, 'Name')

CreateCSVTable

This method creates a SQL table with varchar(max) columns based on the column names in a CSV file. It takes the path to the CSV file as a parameter.

csv_file = 'path/to/file.csv'
db.CreateCSVTable(csv_file)

InsertCSVData

This method inserts the data from a CSV file into an existing SQL table. The CSV file should have the same column names as the target table. It takes the path to the CSV file as a parameter.

csv_file = 'path/to/file.csv'
db.InsertCSVData(csv_file)

InsertXMLSQLTable

This method inserts the data from an XML file into a SQL table. It converts the XML file to a CSV file, creates a table with varchar(max) columns based on the column names in the CSV file, and then inserts the data from the CSV file into the SQL table. It takes the path to the XML file as a parameter.

xml_file = 'path/to/file.xml'
db.InsertXMLSQLTable(xml_file)

CreateTableScript

This method generates the SQL script for creating a table based on a DataFrame object. It takes the DataFrame and the table name as parameters, and returns the SQL script as a string.

df = pd.DataFrame({'Column1': [1, 2, 3], 'Column2': ['A', 'B', 'C']})
table_name = 'TableName'
script = db.CreateTableScript(df, table_name)

InsertScript

This method generates the SQL script for inserting data into a table based on a DataFrame object. It takes the DataFrame, table name, and an optional parameter isNEWID (default value is False) which, if set to True, generates a NEWID() value for the ID column. It returns the SQL script as a string.

 
 
df = pd.DataFrame({'Column1': [1, 2, 3], 'Column2': ['A', 'B', 'C']})
table_name = 'TableName'
script = db.InsertScript(df, table_name)

These methods provide a convenient way to interact with a SQL Server database and perform various operations. Make sure to replace the input file paths and SQL queries with your own data and queries.