Re: How to sql scripts in a file?
John Zhang <[email protected]> Thu, 23 Apr 2020 12:08:54 -0400
| Newsgroups | gmane.comp.gis.postgis,gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <CAKDSG9DJZZJQspT1P6p1Jo2xuytivxOCMR9OQbCJ=4YO8q8+bw@mail.gmail.com> |
--===============4591099700381959017== Content-Type: multipart/alternative; boundary="000000000000ba44c805a3f77740" --000000000000ba44c805a3f77740 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello Giuseppe and S=C3=A1ndor Daku, Thank you for the input. Yes, after conn.commit() is called, it works. Cheers, John On Thu, Apr 23, 2020 at 9:37 AM Giuseppe Broccolo <[email protected]> wrote: > 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 =3D 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 affectin= g the transaction. > > Giuseppe. > > > Il giorno gio 23 apr 2020 alle ore 13:43 John Zhang <[email protected]= m> > 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 =3D r'{}\{}'.format(sFldr_sql,sSQLFile) >> sql_file_contents =3D 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 o= ut. >> >> In the sql file, there are scripts to DROP FUNCTION IF EXISTS and CREAT= E >> 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 > > --=20 Yours sincerely, John Zhang --000000000000ba44c805a3f77740 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Hello=C2=A0Giuseppe and=C2=A0 <span style=3D"color:rgb(13,10,11);font-family:"Open Sans",sans-s= erif;font-size:10.7333px">S=C3=A1ndor Daku,</span><div><span style=3D"color= :rgb(13,10,11);font-family:"Open Sans",sans-serif;font-size:10.73= 33px"><br></span></div><div><span style=3D"color:rgb(13,10,11);font-family:= "Open Sans",sans-serif;font-size:10.7333px">Thank you for the inp= ut. Yes, after conn.commit() is called, it works.=C2=A0</span></div><div><s= pan style=3D"color:rgb(13,10,11);font-family:"Open Sans",sans-ser= if;font-size:10.7333px"><br></span></div><div><span style=3D"color:rgb(13,1= 0,11);font-family:"Open Sans",sans-serif;font-size:10.7333px">Che= ers,</span></div><div><span style=3D"color:rgb(13,10,11);font-family:"= Open Sans",sans-serif;font-size:10.7333px">John</span></div></div><br>= <div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Ap= r 23, 2020 at 9:37 AM Giuseppe Broccolo <<a href=3D"mailto:g.broccolo.7@= gmail.com">[email protected]</a>> wrote:<br></div><blockquote class= =3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg= b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>Hi John,</div><div><= br></div><div>I guess you are using Psycopg2 as driver to PostgreSQL here (= please add further details about your setup).<br></div><div><br></div><div>= Assuming Psycopg2: I guess that is because actions are not committed in you= r snippet. You may be interested to</div><div>set autocommit for the execut= ion of the script, even better to manage properly transactions, see the doc= umentation</div><div>here: <a href=3D"https://www.psycopg.org/docs/usage.ht= ml#transactions-control" target=3D"_blank">https://www.psycopg.org/docs/usa= ge.html#transactions-control</a></div><div><br></div><div>For instance, I&#= 39;d use context managers for connectors and cursors (available from versio= n 2.5):<br><div><pre><span>sqlF =3D r'{}\{}'.format(sFldr_sql,sSQLF= ile)<br>with</span> <span>psycopg2</span><span>.</span><span>connect</span>= <span>(</span><span>...</span><span>)</span> <span>as</span> <span>conn</sp= an><span>:</span> <span>with</span> <span>conn</span><span>.</span><span>cursor</span><sp= an>()</span> <span>as</span> <span>cur</span><span>:</span> <span>curs</span><span>.</span><span>execute</span><span>(open(sqlF= ,'r').read()</span><span></span><span>)<br><br></span></pre><pre><s= pan style=3D"font-family:arial,sans-serif"><span>So that if no exception ha= s been raised, the transaction is committed. In case of exception the trans= action 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>Giuseppe.<br></span></span></pre></div></div></div= ><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">Il gi= orno gio 23 apr 2020 alle ore 13:43 John Zhang <<a href=3D"mailto:johnzh= [email protected]" target=3D"_blank">[email protected]</a>> 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'{}\{}'.f= ormat(sFldr_sql,sSQLFile)<br>sql_file_contents =3D open(sqlF,'r').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> </blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"= class=3D"gmail_signature"><span style=3D"font-family:arial,sans-serif;font= -size:12.5px;border-collapse:collapse;color:rgb(136,136,136)"><div><span st= yle=3D"color:rgb(102,102,102);font-family:Verdana,Arial,Tahoma;line-height:= 20px;border-collapse:separate">Yours sincerely,</span></div><div><br></div>= <div><br></div><div>John Zhang</div><br><div><br></div></span></div> --000000000000ba44c805a3f77740-- --===============4591099700381959017== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 Content-Disposition: inline X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KcG9zdGdpcy11 c2VycyBtYWlsaW5nIGxpc3QKcG9zdGdpcy11c2Vyc0BsaXN0cy5vc2dlby5vcmcKaHR0cHM6Ly9s aXN0cy5vc2dlby5vcmcvbWFpbG1hbi9saXN0aW5mby9wb3N0Z2lzLXVzZXJz --===============4591099700381959017==--