connexion with odbc

[stonebig]

using pyodbc or similar

principle:

• using sqlite_bro GUI over an odbc source (or sqlalchemy ?) : . connecting to "odbc" source, a source starting per odbc would be "odbc"
• using sqlite_bro to import from odbc to sqlite
• using sqlite_bro to export from sqlite to odbc.

interest:

• gui on anything in 1 file (if an odbc package)
• keep it simple

limit:

• not designed for performance
• not standard
• could be complex

[stonebig]

example of how it could be https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

#Sample insert query
count = cursor.execute("""
INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) 
VALUES (?,?,?,?,?)""",
'SQL Server Express New 20', 'SQLEXPRESS New 20', 0, 0, CURRENT_TIMESTAMP).rowcount
cnxn.commit()
print('Rows inserted: ' + str(count))

[stonebig]

over here;: list of tables

crsr = cnxn.cursor()
table_names = [x[2] for x in crsr.tables(tableType='TABLE')]

[stonebig]

bit of pyodbc usage https://stackoverflow.com/questions/41430458/load-csv-to-mdb-using-pyodbc-and-pandas

[stonebig]

excel https://www.red-gate.com/simple-talk/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc/ "Driver={Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)};DBQ=MyPath/MyFile"

[stonebig]

import pyodbc
access_string= "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=0;DBQ=C:/tempo/test.xlsx"
con = pyodbc.connect(access_string, autocommit=True)
cursor = con.cursor()
tables = ([x[2] for x in cursor.tables()])
for t in tables:
    for row in cursor.columns(table='%s' % t):
        print('table', t , ' as row ' , row.column_name)
cursor.execute("select * from [Feuil1$]")
print(cursor.description)
print(list(cursor.fetchall()))

table Feuil1$  as row  a
table Feuil1$  as row  b
table Feuil1$  as row  c
table Feuil2$  as row  x
table Feuil2$  as row  yy
table Feuil2$  as row  zzz
(('a', <class 'float'>, None, 53, 53, 0, True), ('b', <class 'float'>, None, 53, 53, 0, True), ('c', <class 'float'>, None, 53, 53, 0, True))
[(2.0, 1.0, 3.0), (54.0, 45.0, 56.0)]

query = "insert into [Feuil1$](a , b, c) values (? ,  ? , ?)"
new = [(12.0, 1.0, 3.0), (254.0, 45.0, 56.0),  (254.0, 45.0, 56.0),  (254.0, 45.0, 56.0)]
for   data in new:
        cursor.execute(query, data)  
cursor.commit()
cursor.execute("select * from [Feuil1$]")
print(cursor.description)
print(list(column[0] for column in cursor.description))
print(list(cursor.fetchall()))
con.close

(('a', <class 'float'>, None, 53, 53, 0, True), ('b', <class 'float'>, None, 53, 53, 0, True), ('c', <class 'float'>, None, 53, 53, 0, True))
['a', 'b', 'c']
[(2.0, 1.0, 3.0), (54.0, 45.0, 56.0), (12.0, 1.0, 3.0), (254.0, 45.0, 56.0), (254.0, 45.0, 56.0), (254.0, 45.0, 56.0), (12.0, 1.0, 3.0), (254.0, 45.0, 56.0), (254.0, 45.0, 56.0), (254.0, 45.0, 56.0)]

[stonebig]

$Connection.ConnectionString = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ='+$ExcelFilePath+'; Extended Properties="Mode=ReadWrite;ReadOnly=false; HDR=YES"' 

import pyodbc
import csv
connection = pyodbc.connect("Driver={SQL Server Native Client 11.0};"                      "Server=Server;"                      "Database=Database;"                      "uid=username;pwd=password")
cursor = connection.cursor()
cursor.execute("select Name, SITE_NAME,ADDRESS from Power_plants")
data=cursor.fetchall()
with open('dataTester.csv', 'w', newline='') as fp: 
   a= csv.writer(fp, delimiter=',')
    for line in data:
        a.writerows(line)
for row in data:
    print (row[0],row[1],row[2])
cursor.close()
connection.close()

[stonebig]

apparently:

# columns in table x
for row in cursor.columns(table='x'):
    print(row.column_name)