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,
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,
(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.
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,
(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())
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,