How can I run sql file in python?

How can I run sql file in python? - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: How can I run sql file in python? (/thread-37964.html)

How can I run sql file in python? - korenron - Aug-15-2022

Hello,
I have a sql file with ~ 50 lines of update\delete\set commands
everything is config to work according to the router ID

set @ID = (select rou_id from Company1.router where
rou_serial_no like '%SN123456%'); ## put here the Rou Serial number!

delete FROM israel_v7.history_router
where rou_id=@ID and com_id=@OldCompany;
.
.
.
Update Company1.router set Company1.router.ROU_STATE = @State where Company1.router.ROU_ID = @ID;

can I write a python code where I enter the serial_number --> save it as @ID then run the all sql file according to the Serial I have entered?

Thanks,

RE: How can I run sql file in python? - Axel_Erfurt - Aug-15-2022

https://docs.python.org/3/library/sqlite3.html

RE: How can I run sql file in python? - korenron - Aug-18-2022

As far I can see it's the same as mysql package (maybe I'm missing something )

but I know how to write sql command without any problems .
I don't want to build a new code with all the wanted command -
just run a working sql file and be able to change on it variable and run it on loop

Thanks,

RE: How can I run sql file in python? - Gribouillis - Aug-18-2022

(Aug-18-2022, 06:09 AM)korenron Wrote: As far I can see it's the same as mysql package (maybe I'm missing something )

Before you run the sql file, you need to know for which database it was written, sqlite? mysql? postgresql? etc. Then you need this database installed on your computer. Then you can run the file by running an external command with the subprocess module, such as 'sqlite3' or 'mysql', and send the sql file's content to the command's standard input.

RE: How can I run sql file in python? - korenron - Aug-18-2022

OK
maybe I didn't explain myself correct

I have a mysql database
I have created a sql file (that I'm running using mysql workbanch)
the file is simple
find id or router from serial number , then according to the ID run some commands (delete history and change company)
when I have a list of 10 routers , I'm changing mannauly the @ID parameter then run the sql file
I want to do the same in code - were I will gete the list of routers and for each router run the sql file (after changing the @ID parameter)

how to change paramter in file - I know how to to it and run in a loop

but how do I run the sql file- this is the question

maybe now my questsion is clearer?

Thanks,

RE: How can I run sql file in python? - Gribouillis - Aug-18-2022

(Aug-18-2022, 08:42 AM)korenron Wrote: but how do I run the sql file- this is the question

Try something like

import subprocess
user = ...
sqlfile = ...
dbname = ...
subprocess.run(f"mysql -u {user} -p {dbname} < {sqlfile}", shell=True)

or perhaps

sp = subprocess.Popen(['mysql', '-u', user, '-p', dbname], stdin=subprocess.PIPE)
with open(sqlfile, 'rb') as ifh:
    sp.communicate(ifh.read())

RE: How can I run sql file in python? - korenron - Aug-18-2022

this is whay I was missing

subprocess.run(f"mysql -u {user} -p {dbname} < {sqlfile}", shell=True)

thanks !

do you know how can I get the output? meaning if there was a problem or its OK?
this output for example? (this is what I got from my mysql workbanch)

13:38:45	update routers.router set ROU_IP='6.6.7.8' where (ROU_ID='5213' and com_id ='1')	1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0	0.062 sec

Thanks,

RE: How can I run sql file in python? - Gribouillis - Aug-18-2022

(Aug-18-2022, 10:37 AM)korenron Wrote: do you know how can I get the output?

See the arguments of subprocess.run() and the returned object.