Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

connexion with odbc #14

Open
stonebig opened this issue Aug 21, 2021 · 7 comments
Open

connexion with odbc #14

stonebig opened this issue Aug 21, 2021 · 7 comments

Comments

@stonebig
Copy link
Owner

stonebig commented Aug 21, 2021

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
Copy link
Owner Author

stonebig commented Aug 21, 2021

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
Copy link
Owner Author

over here;: list of tables

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

@stonebig
Copy link
Owner Author

@stonebig
Copy link
Owner Author

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
Copy link
Owner Author

stonebig commented Aug 21, 2021

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
Copy link
Owner Author

stonebig commented Aug 21, 2021

$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
Copy link
Owner Author

apparently:

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant