Open Text File and Save as SQL file using Python

Question

By using the .replace function, I was able to open a csv file and replace some text within the string, and re save the csv file as a txt file using python. I would now like to save this new txt file (or at leas the string that is in the txt file) as a .sql file.

Code: # Read in the file with open('Region.txt', 'r') as file : filedata = file.read()

# Replace the target strings
filedata = filedata.replace("America", "Europe")
filedata= filedata.replace("South America", "East Europe")

# Write the file out again
with open('MyDebt.txt', 'w') as file:
  file.write(filedata)
file.close()

Text file: (Region.txt)

select country_name from abcd.d_organizations where region = 'America' and region2 = 'South America'
union all
select 'South America' country_name from dual
order by country_name asc

The SQL file (you mean DDL I think) is a text file with syntax that the DBMS that you are dealing with recognizes. So you can take valid SQL from your Region.txt and save it to a file, say Region.SQL and use that with a DBMS. You might need to add some other DDL to that file to make it do what you want. — Shawn Mehan
– Shawn Mehan, Commented Oct 4, 2017 at 15:27
@ShawnMehan Okay I understand what you are saying now. Yes, I would like to save the text from my Region.txt (as this is the language my database recongnizes) as a .sql file ( e.g- Region.sql). But how do i do this??? I will need run some separate python code that opens the connection to the DB, runs Regions.sql and writes the output to a dataframe. — spacedinosaur10
– spacedinosaur10, Commented Oct 4, 2017 at 19:49

Sharim09 · Accepted Answer · 2022-08-29 16:36:26Z

2

I figured out what I was doing wrong.. pretty simple. Once i make the changes to the .txt file, I need to save immediately save the changes to the text (filedata)to a .sql file instead of to the same .txt file.

# Read in the file with open('Region.txt', 'r') as file : filedata = file.read()

# Replace the target strings
filedata = filedata.replace("America", "Europe")
filedata= filedata.replace("South America", "East Europe")

# Write the file out again
with open('Region.sql', 'w') as file:
  file.write(filedata)

edited Aug 29, 2022 at 16:36

Sharim09

6,2162 gold badges9 silver badges31 bronze badges

answered Oct 4, 2017 at 20:29

spacedinosaur10

7354 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shawn Mehan Over a year ago

Yep. As I wrote, the sql file is really just a txt file with valid SQL in it. BTW, your last file.close() is redundant. You don't need it since you appropriately used a context handler to open the file, so you can safely delete that line.

Collectives™ on Stack Overflow

Open Text File and Save as SQL file using Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related