[RISOLTO] difficoltà a passare la variabile con una data al codice sql

auato71 · « **il:** Agosto 16, 2020, 18:33 »

Ciao. Non ho modo di risolvere questo problema ... Ho fatto decine di tentativi ma non riesco a passare la variabile d (che contiene una stringa con una data) al codice SQL contenuto nel comando cursor.execute(). Così com'è sotto funziona perfettamente ma se provo a sostituire la data ''14-08-2020'' con ? o %s e in tutte le salse cioè senza apici e con apici o doppi apici e seguito infine dalla variabile d va in tilt

Il problema, credo, possa anche esser dovuto al fatto che non mando direttamente il codice sql al server db E0443888\SQLEXPRESS a cui mi collego ma, rigirandolo tramite statement OPENQUERY ad un'altro linked server (il GATEWAY_DB che è un Oracle), mi smarrisco nella formattazione tra double quotes e single quote.
Potreste darmi una mano per favore?

import pyodbc

d = '14-08-2020'

connection_string = 'DRIVER=ODBC Driver 17 for SQL Server;SERVER=E0443888\SQLEXPRESS;DATABASE=TEMP_DB;Trusted_Connection=yes;UseFMTONLY=Yes;'
connection = pyodbc.connect(connection_string, autocommit=True)

cursor = connection.cursor()

final = cursor.execute("SELECT * FROM OPENQUERY(GATEWAY_DB,'SELECT to_char(starttime, ''dd/mm/yyyy'') as day, TO_CHAR(STARTTIME, ''hh24'') as HOUR, ID_NAME, MAX(USERS) FROM meastable WHERE starttime >= to_date(''14-08-2020'', ''dd-mm-yyyy'') GROUP BY to_char(starttime, ''dd/mm/yyyy''), TO_CHAR(STARTTIME, ''hh24''), ID_NAME ORDER BY to_char(starttime, ''dd/mm/yyyy''), TO_CHAR(STARTTIME, ''hh24''), ID_NAME ')").fetchall()

for row in final:
    print(row)

RicPol · « **Risposta #1 il:** Agosto 17, 2020, 12:39 »

con ogni probabilità pyodcb avrà un adapter predefinito che gli consente di accettare (e restituire) date in formato datetime.datetime, e/o datetime.date. Usa quello, invece che cercare di ficcarci dentro a martellate una stringa di testo.

auato71 · « **Risposta #2 il:** Agosto 17, 2020, 16:20 »

Citazione da: RicPol - Agosto 17, 2020, 12:39

con ogni probabilità pyodcb avrà un adapter predefinito che gli consente di accettare (e restituire) date in formato datetime.datetime, e/o datetime.date. Usa quello, invece che cercare di ficcarci dentro a martellate una stringa di testo.

Innanzitutto grazie @RicPol!
Nulla da fare.... ho provato anche a passargli la variabile in formato datetime.datetime e datetime.date (difatti nel database il campo starttime è in formato DATE) ma l'errore è sempre lo stesso: pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000') Mi sembra di capire che pyodbc non riesca a passare il parametro oppure sql non lo recepisce correttamente o nel giusto formato.

import pyodbc
import datetime

d = datetime.datetime(2020, 8, 16)


connection_string = 'DRIVER=ODBC Driver 17 for SQL Server;SERVER=E0443888\SQLEXPRESS;DATABASE=TEMP_DB;Trusted_Connection=yes;UseFMTONLY=Yes;'
cnxn = pyodbc.connect(connection_string, autocommit=True)
crsr = cnxn.cursor()


final = crsr.execute("""SELECT * FROM OPENQUERY(GATEWAY_DB,
'SELECT *
FROM meastable 
WHERE starttime >= CAST(? as datetime)
')
""", d).fetchall()

for row in final:
    print(row)

RicPol · « **Risposta #3 il:** Agosto 17, 2020, 18:22 »

ma... e non castare nell'sql e lasciare invece che ci pensi il driver python? a lume di buon senso, la funzione di un adapter dovrebbe essere quella di risparmiarti operazioni di basso livello come il cast sql... Poi, uno può sempre andarsi a vedere la documentazione di pyodcb, nel dubbio.

auato71 · « **Risposta #4 il:** Agosto 17, 2020, 23:08 »

Ho fatto un po' di prove e verificato innanzitutto che il mio pyodbc passa bene i paramentri senza lo statement OPENQUERY.
Nel mio codice sql, invece, uso OPENQUERY per estrarre dati non dal server sql locale ma da un server linkato e quindi c'e' un'istruzione SELECT annidata. Se sposto l'istruzione WHERE fuori dall'annidamento, finalemnte funziona.
Ne concludo, almeno credo, che Pyodbc non sia in grado di passare la variabile al codice annidato ma solo al codice di livello piu' superficiale.
Sono quindi passato da:

final = cursor.execute("select * FROM OPENQUERY(GATEWAY_DB, 'SELECT * from meastable where starttime >= ? ')", d).fetchall()

che produce l'errore sopra descritto alla seguente che funziona:

final = cursor.execute("select * FROM OPENQUERY(GATEWAY_DB, 'SELECT * from meastable') where starttime >= ? ", d).fetchall()

Cio' che noto pero' e' la estrema lentezza della soluzione funzionante (150 secondi a fronte dei 3 secondi necessari in normali condizioni). Immagino qual e' la differenza della seconda soluzione: la query annidata interna prova a caricare tutto cio' che puo' ed e' solo la SELECT esterna ad effettuare il filtraggio in un secondo momento. Funziona ma e' impraticabile! Ho provato a guardare nel manuale di pyodbc ma non trovo soluzioni al mio caso.

auato71 · « **Risposta #5 il:** Agosto 18, 2020, 11:41 »

Ultimo aggiornamento, dopodiché possiamo ritenere risolta la questione. Dunque.. è' OPENQUERY che non prevede il passaggio di parametri esterni; tuttavia esiste workaround di Microsoft che ho testato personalmente e risolve il problema.
Il codice python l'ho cambiato leggermente: prevede ora il codice sql in un file txt esterno posto nella stessa cartella.

import pyodbc

d = input("immetti data...[yyyy-mm-dd]> ")
print(d)


connection_string = 'DRIVER=ODBC Driver 17 for SQL Server;SERVER=E0443888\SQLEXPRESS;DATABASE=TEMP_DB;Trusted_Connection=yes;UseFMTONLY=Yes;'
cnxn = pyodbc.connect(connection_string, autocommit=True)
crsr = cnxn.cursor()

sql = open(r'sql.txt', 'r').read()
print("inizio estrazione dati....")
final = crsr.execute(sql, d).fetchall()

for row in final:
    print(row)

Nel file sql.txt c'è poi il seguente codice SQL riadattato per recepire parametri dall'esterno:

DECLARE @TSQL varchar(8000), @d char(19) SELECT @d = ? SELECT @TSQL = 'SELECT * FROM OPENQUERY(GATEWAY_DB, ''SELECT * from meastable where starttime >= ''''' + @d + ''''' '')' EXEC (@TSQL)

Grazie ! A presto

RicPol · « **Risposta #6 il:** Agosto 18, 2020, 18:31 »

No però qui proprio non ci siamo dalle fondamenta, eh?

Allora, cominciamo subito col dire che io adesso non so se davvero pyodcb supporta oppure no il passaggio di parametri con tipi "adattati" python/sql anche all'interno di query annidate con openquery... diciamo solo che mi sembrerebbe pazzescamente strano se non lo facesse. Ma tutto può essere. E' un mondo che onestamente conosco davvero pochino.

Detto questo, resta che quando tu fai


d = input("immetti data...[yyyy-mm-dd]> ")
# ...
final = crsr.execute(sql, d).fetchall()

Quel "d" non è un datetime.datetime o un datetime.date, è una banalissima stringa.
Quindi tutto ciò che poi viene fatto dopo non serve a niente per verificare se pyodcb supporta o non supporta, eccetera eccetera. Non ci siamo, devi lavorare con il tipo python giusto, altrimenti che senso ha?

Quello che tu devi provare è, per prima cosa, se con un database LOCALE riesci a fare una cosa banale come


import datetime
d = datetime.datetime(2020, 6, 15)
# ...
cursor.execute("select * from foo where starttime >= ? ", d).fetchall()

o qualcosa del genere. Ora questo dovresti riuscire a farlo sicuramente, se "starttime" è una colonna dichiarata SQL_TYPE_TIMESTAMP nel database (e ti consiglio di controllare... sempre, sempre coprire le basi prima).

Se riesci a fare questo, allora puoi passare a vedere se la stessa cosa funziona anche con un database remoto attraverso openquery:


import datetime
d = datetime.datetime(2020, 6, 15)
# ...
cursor.execute("select * from openquery(remotedb, 'select * from foo where starttime >= ? ')", d).fetchall()

(posto che ovviamente tirar giù tutti i dati dalla tabella remota e poi fare il "where" localmente funziona ma, come hai già notato, è lento per forza).
Sempre facendo attenzione che "starttime" sia SQL_TYPE_TIMESTAMP (coprire le basi).

Se effettivamente questo non funziona, allora puoi prendertela con pyodcb (ovvero, chiedere lumi direttamente lì...

Documentazione di pyodcb (va a sapere, magari è utile da leggere): https://github.com/mkleehammer/pyodbc/wiki
Bug tracker di pyodcb (da cercare "openquery" o qualcosa del genere per vedere se qualcuno ha avuto problemi con queste cose): https://github.com/mkleehammer/pyodbc/issues

auato71 · « **Risposta #7 il:** Agosto 18, 2020, 20:21 »

Citazione da: RicPol - Agosto 18, 2020, 18:31

Allora, cominciamo subito col dire che io adesso non so se davvero pyodcb supporta oppure no il passaggio di parametri con tipi "adattati" python/sql anche all'interno di query annidate con openquery... diciamo solo che mi sembrerebbe pazzescamente strano se non lo facesse. Ma tutto può essere. E' un mondo che onestamente conosco davvero pochino.

Pazzesco, vero! Pyodbc come sqllite3, SQLalchemy ecc.... supportano tutti il passaggio di parametri ma le loro documentazioni non affrontano proprio se è consentito farlo verso parti di codice sql di tipo "nested" o verso linked-server. Mi sembra di capire che non è un problema loro... non di queste librerie. Eppure è una settimana che impazzisco e fino a poco fa non riuscivo a capire il perché. Poi nei remarks della documentazione di OPENQUERY (https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15)mi accorgo che "OPENQUERY does not accept variables for its arguments." Allora la mia strategia cambia e smetto di impazzire con Python per iniziare il rompicapo con OPENQUERY e trovo le soluzioni di Microsoft (https://support.microsoft.com/it-it/help/314520/how-to-pass-a-variable-to-a-linked-server-query):ho adottato la prima, cioè "Pass Basic Values".

Citazione da: RicPol - Agosto 18, 2020, 18:31

Detto questo, resta che quando tu fai
d = input("immetti data...[yyyy-mm-dd]> ")
# ...
final = crsr.execute(sql, d).fetchall()
Quel "d" non è un datetime.datetime o un datetime.date, è una banalissima stringa.
Quindi tutto ciò che poi viene fatto dopo non serve a niente per verificare se pyodcb supporta o non supporta, eccetera eccetera. Non ci siamo, devi lavorare con il tipo python giusto, altrimenti che senso ha?

Sì... in qualche post più su sbattevo la testa ad usare datetime.datetime ma il problema esisteva sempre: se pero' spostavo la variabile ? fuori da OPENQUERY, funzionava tutto alla perfezione sia con datetime che con stringa semplice. Quindi ho capito che non era il tipo di Python a darmi problemi ma, piuttosto, dove posizionavo la variabile ? con il parametro.

Citazione da: RicPol - Agosto 18, 2020, 18:31

Quello che tu devi provare è, per prima cosa, se con un database LOCALE riesci a fare una cosa banale come
import datetime
d = datetime.datetime(2020, 6, 15)
# ...
cursor.execute("select * from foo where starttime >= ? ", d).fetchall()
o qualcosa del genere. Ora questo dovresti riuscire a farlo sicuramente, se "starttime" è una colonna dichiarata SQL_TYPE_TIMESTAMP nel database (e ti consiglio di controllare... sempre, sempre coprire le basi prima).

Se riesci a fare questo, allora puoi passare a vedere se la stessa cosa funziona anche con un database remoto attraverso openquery:
import datetime
d = datetime.datetime(2020, 6, 15)
# ...
cursor.execute("select * from openquery(remotedb, 'select * from foo where starttime >= ? ')", d).fetchall()
(posto che ovviamente tirar giù tutti i dati dalla tabella remota e poi fare il "where" localmente funziona ma, come hai già notato, è lento per forza).
Sempre facendo attenzione che "starttime" sia SQL_TYPE_TIMESTAMP (coprire le basi).

Se effettivamente questo non funziona, allora puoi prendertela con pyodcb (ovvero, chiedere lumi direttamente lì...

Documentazione di pyodcb (va a sapere, magari è utile da leggere): https://github.com/mkleehammer/pyodbc/wiki
Bug tracker di pyodcb (da cercare "openquery" o qualcosa del genere per vedere se qualcuno ha avuto problemi con queste cose): https://github.com/mkleehammer/pyodbc/issues

Queste prove rientrano tra quelle già fatte e, infatti, con codici semplici che estraevano dati da tabelle del db locale non avevo nessun problema. Il problema compare solo e sempre se provo a passare il parametro dentro OPENQUERY. Quindi mi sembra di capire che non c'è da presndersela con pyodbc che fa bene il suo lavoro e passa correttamente anche dentro SELECT annidate, purché non di OPENQUERY, OPENDATASOURCE, OPENROWSET e tutto ciò che ha a che fare con i server linkati .

Grazie per il supporto!

RicPol · « **Risposta #8 il:** Agosto 19, 2020, 19:55 »

Ragazzi, che panorama squallido... ok, quindi non c'entra pyodcb ma è proprio una faccenda di openquery. E già questo...
Ora, per prima cosa ti direi se non puoi avere quel server in un modo più... come dire... maneggevole... che non come linked server.

In subordine, pare che la soluzione debba essere quella di comporre "a mano" la stringa sql senza ricorrere a variabili.
Ora, quello che non capisco comunque è perché devi ricorrere a quell'accrocchio tutto aggrovigliato quando potresti semplicemente comporre la stringa in python...
voglio dire, se la tua variabile fosse una semplice stringa, dovresti probabilmente fare qualcosa come


var = 'hello'
...execute('select * from foo where bar = "%s";') % var # invece di '... where bar = ?'), var)

che è assolutamente orribile da vedere, e che se vedessi fare da qualunque principiante lo striglierei per un'ora... ma in questo caso...
Anche qui, dovresti prima provare con una stringa banale, e mettere a posto gli eventuali problemi di escaping delle virgolette (che sicuramente ti beccherai).
Quando sei sicuro che funziona, puoi provare a farlo componendo le date. Anche qui, devi verificare il tipo sql della colonna che ti interessa. Se fosse un timestamp, è probabile che il formato potrebbe essere quello canonico "yyyy-mm-dd hh:mm:ss"... se componi la stringa esattamente così, potrebbe funzionare.

Topic: [RISOLTO] difficoltà a passare la variabile con una data al codice sql (Letto 411 volte)

auato71

RicPol

Re:difficoltà a passare la variabile con una data al codice sql

auato71

Re:difficoltà a passare la variabile con una data al codice sql

RicPol

Re:difficoltà a passare la variabile con una data al codice sql

auato71

Re:difficoltà a passare la variabile con una data al codice sql

auato71

Re:difficoltà a passare la variabile con una data al codice sql

RicPol

Re:difficoltà a passare la variabile con una data al codice sql

auato71

[RISOLTO] difficoltà a passare la variabile con una data al codice sql

RicPol

Re:[RISOLTO] difficoltà a passare la variabile con una data al codice sql