- 1 year ago
Run postgres and sql requests from one file
To run PostgreSQL and SQL requests from a single file, you can use a tool like psql
or a scripting language that supports database connectivity, such as Python with the psycopg2
library. Here's an example of how you can achieve this using Python:
Install the
psycopg2
library by runningpip install psycopg2
in your command prompt.Create a file called
sql_script.sql
and populate it with your PostgreSQL and SQL queries. For example:
sql-- sql_script.sql
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
INSERT INTO customers (name, email) VALUES ('John Doe', 'johndoe@example.com');
INSERT INTO customers (name, email) VALUES ('Jane Smith', 'janesmith@example.com');
SELECT * FROM customers;
- Create a Python script, e.g.,
run_sql_script.py
, and add the following code:
pythonimport psycopg2
# Establish a connection to the PostgreSQL database
connection = psycopg2.connect(
host="your_host",
port="your_port",
database="your_database",
user="your_username",
password="your_password"
)
# Open the SQL script file and read the queries
with open("sql_script.sql", "r") as file:
sql_script = file.read()
# Execute the SQL script
with connection.cursor() as cursor:
cursor.execute(sql_script)
result = cursor.fetchall()
for row in result:
print(row)
# Close the database connection
connection.close()
Make sure to replace the placeholder values in the psycopg2.connect
call with your actual PostgreSQL connection details (host, port, database, username, and password).
- Save the Python script and run it using
python run_sql_script.py
. It will connect to the PostgreSQL database, execute the SQL queries from thesql_script.sql
file, and print the results.
This approach allows you to combine your PostgreSQL and SQL commands in a single file and execute them using Python's database connectivity capabilities.