How to sql scripts in a file?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

How to sql scripts in a file?

Blue Sky
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


Reply | Threaded
Open this post in threaded view
|

Fwd: How to sql scripts in a file?

Sándor Daku
On Thu, 23 Apr 2020 at 14:43, John Zhang <[hidden email]> wrote:
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.

Hi John,

Psycopg2 I guess. 
You have to commit the changes with calling commit() on the connection(not on the cursor!).

Or - 2.5 and above - you can use cursors and connections as context managers and it will be taken care of automatically.


Regards,
Sándor


Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] How to sql scripts in a file?

Giuseppe Broccolo-2
In reply to this post by Blue Sky
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

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 <[hidden email]> 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] How to sql scripts in a file?

Blue Sky
Hello Giuseppe and  Sándor 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 <[hidden email]> 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

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 <[hidden email]> 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


--
Yours sincerely,


John Zhang