Re: How to sql scripts in a file?

--===============5497630565688034373==
Content-Type: multipart/alternative; boundary="000000000000ffb7ec05a3f55905"

--000000000000ffb7ec05a3f55905
Content-Type: text/plain; charset="UTF-8"

Hi John,

I guess you are using Psycopg2 as driver to PostgreSQL here (please add
further details about your setup).

Assuming Psycopg2: I guess that is because actions are not committed in
your snippet. You may be interested to
set autocommit for the execution of the script, even better to manage
properly transactions, see the documentation
here: https://www.psycopg.org/docs/usage.html#transactions-control

For instance, I'd use context managers for connectors and cursors
(available from version 2.5):

sqlF = r'{}\{}'.format(sFldr_sql,sSQLFile)
with psycopg2.connect(...) as conn:
    with conn.cursor() as cur:
        curs.execute(open(sqlF,'r').read())

So that if no exception has been raised, the transaction is committed.
In case of exception the transaction
is rolled back. Also the cursor is then properly closed, without
affecting the transaction.

Giuseppe.


Il giorno gio 23 apr 2020 alle ore 13:43 John Zhang <[email protected]>
ha scritto:

> Hello all,
>
> I have a need to execute sql scripts in a file with comments in Python
> 2.7.
> here is m code snippet:
>
> sqlF = r'{}\{}'.format(sFldr_sql,sSQLFile)
> sql_file_contents = open(sqlF,'r').read()
> cur.execute(sql_file_contents)
>
> the file can be executed from pgAdmin successfully. However, the code
> above runs quietly without any issues raised but NO results was worked out.
>
> In the sql file, there are scripts to DROP FUNCTION  IF EXISTS and CREATE
> OR REPLACE FUNCTION.
>
> Your input to shed light on it would be much appreciated.
>
> Thank you
>
> --
> Yours sincerely,
>
>
> John Zhang
>
>
> _______________________________________________
> postgis-users mailing list
> [email protected]
> https://lists.osgeo.org/mailman/listinfo/postgis-users

--000000000000ffb7ec05a3f55905
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi John,</div><div><br></div><div>I guess you are usi=
ng Psycopg2 as driver to PostgreSQL here (please add further details about =
your setup).<br></div><div><br></div><div>Assuming Psycopg2: I guess that i=
s because actions are not committed in your snippet. You may be interested =
to</div><div>set autocommit for the execution of the script, even better to=
 manage properly transactions, see the documentation</div><div>here: <a hre=
f=3D"https://www.psycopg.org/docs/usage.html#transactions-control">https://=
www.psycopg.org/docs/usage.html#transactions-control</a></div><div><br></di=
v><div>For instance, I&#39;d use context managers for connectors and cursor=
s (available from version 2.5):<br><div><pre><span class=3D"gmail-k">sqlF =
=3D r&#39;{}\{}&#39;.format(sFldr_sql,sSQLFile)<br>with</span> <span class=
=3D"gmail-n">psycopg2</span><span class=3D"gmail-o">.</span><span class=3D"=
gmail-n">connect</span><span class=3D"gmail-p">(</span><span class=3D"gmail=
-n">...</span><span class=3D"gmail-p">)</span> <span class=3D"gmail-k">as</=
span> <span class=3D"gmail-n">conn</span><span class=3D"gmail-p">:</span>
    <span class=3D"gmail-k">with</span> <span class=3D"gmail-n">conn</span>=
<span class=3D"gmail-o">.</span><span class=3D"gmail-n">cursor</span><span =
class=3D"gmail-p">()</span> <span class=3D"gmail-k">as</span> <span class=
=3D"gmail-n">cur</span><span class=3D"gmail-p">:</span>
        <span class=3D"gmail-n">curs</span><span class=3D"gmail-o">.</span>=
<span class=3D"gmail-n">execute</span><span class=3D"gmail-p">(open(sqlF,&#=
39;r&#39;).read()</span><span class=3D"gmail-n"></span><span class=3D"gmail=
-p">)<br><br></span></pre><pre><span style=3D"font-family:arial,sans-serif"=
><span class=3D"gmail-p">So that if no exception has been raised, the trans=
action is committed. In case of exception the transaction
is rolled back. Also the cursor is then properly closed, without affecting =
the transaction.<br><br></span></span></pre><pre><span style=3D"font-family=
:arial,sans-serif"><span class=3D"gmail-p">Giuseppe.<br></span></span></pre=
></div></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D=
"gmail_attr">Il giorno gio 23 apr 2020 alle ore 13:43 John Zhang &lt;<a hre=
f=3D"mailto:[email protected]">[email protected]</a>&gt; ha scritto=
:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"lt=
r"><div dir=3D"ltr">Hello all,<div><br></div><div>I have a need to execute =
sql scripts in a file with comments in Python 2.7.=C2=A0</div><div>here is =
m code snippet:</div><div dir=3D"ltr"><br></div>sqlF =3D r&#39;{}\{}&#39;.f=
ormat(sFldr_sql,sSQLFile)<br>sql_file_contents =3D open(sqlF,&#39;r&#39;).r=
ead()<br><div>cur.execute(sql_file_contents)</div><div>=C2=A0</div><div>the=
 file can be executed from pgAdmin successfully. However, the code above ru=
ns quietly without any issues raised but NO results was worked out.</div><d=
iv><br></div><div>In the sql file, there are scripts to DROP=20

FUNCTION=C2=A0 IF EXISTS and CREATE OR REPLACE FUNCTION.</div><div><br></di=
v><div>Your input to shed light on it would be much appreciated.</div><div>=
<br></div><div>Thank you</div><div><br></div></div>-- <br><div dir=3D"ltr">=
<span style=3D"font-family:arial,sans-serif;font-size:12.5px;border-collaps=
e:collapse;color:rgb(136,136,136)"><div><span style=3D"color:rgb(102,102,10=
2);font-family:Verdana,Arial,Tahoma;line-height:20px;border-collapse:separa=
te">Yours sincerely,</span></div><div><br></div><div><br></div><div>John Zh=
ang</div><br><div><br></div></span></div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href=3D"mailto:[email protected]" target=3D"_blank">postgis-=
[email protected]</a><br>
<a href=3D"https://lists.osgeo.org/mailman/listinfo/postgis-users" rel=3D"n=
oreferrer" target=3D"_blank">https://lists.osgeo.org/mailman/listinfo/postg=
is-users</a></blockquote></div>

--000000000000ffb7ec05a3f55905--

--===============5497630565688034373==
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: base64
Content-Disposition: inline

X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KcG9zdGdpcy11
c2VycyBtYWlsaW5nIGxpc3QKcG9zdGdpcy11c2Vyc0BsaXN0cy5vc2dlby5vcmcKaHR0cHM6Ly9s
aXN0cy5vc2dlby5vcmcvbWFpbG1hbi9saXN0aW5mby9wb3N0Z2lzLXVzZXJz

--===============5497630565688034373==--