how to run .sql (mssql or sql server) file in python?

Question

Is there any ways to run .sql(sql server) file in python?

Python Code:

import pyodbc,tempfile,os
server_name = "localhost"
db_name = "abc"
password = "1234"
local_path = tempfile.gettempdir()
sqlfile = "test.sql"
filepath = os.path.join(local_path,sqlfile)
Connection_string = 'Driver={ODBC Driver 17 for SQL Server};Server='+server_name+';Database='+db_name+';UID=sa;PWD='+password+';'
cnxn = pyodbc.connect(Connection_string)
cursor1 = cnxn.cursor()
with open(filepath, 'r') as sql_file:
        cursor1.execute(sql_file.read())

Below contain is available in test.sql
This sql script is running manually successfully.

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'area' AND type = 'U') DROP TABLE area;
CREATE TABLE area (
  areaid int NOT NULL default '0',
  mapid int NOT NULL default '0',
  areaname varchar(50) default NULL,
  x1 int NOT NULL default '0',
  y1 int NOT NULL default '0',
  x2 int NOT NULL default '0',
  y2 int NOT NULL default '0',
  flag int NOT NULL default '0',
  restart int NOT NULL default '0',
  PRIMARY KEY  (areaid)
)

FYI a .sql file isn't a "SQL Server" file. A .sql file is just a text file that contains SQL (Structured Query Language). That SQL might be a specific dialect (T-SQL in this case) but a .sql file generated by MySQL, Oracle, etc, would still be a .sql file, but it most certainly wouldn't be a "SQL Server file". — Thom A, Jan 11 '23 at 09:27
@Larnu , Ok I understood but there is any ways to run .sql or txt file which contains sql schema or command. — microset, Jan 11 '23 at 09:29
Have you check [this]??:https://stackoverflow.com/questions/19472922/reading-external-sql-script-in-python Can it be your answer.. — Manvi, Jan 11 '23 at 09:31

ljmc · Answer 1 · 2023-01-11T10:07:23.680

0

You're not pointing to the right location.

Your filepath evaluates to something like C:\TEMP\test.sql (see tempfile.gettempdir docs)

If you set it to D:\test\test.sql (from your comment), then it should work.

import pyodbc

server_name = "localhost"
db_name = "abc"
password = "1234"

connection_string = 'Driver={ODBC Driver 17 for SQL Server};Server='+server_name+';Database='+db_name+';UID=sa;PWD='+password+';'

filepath = r"D:\test\test.sql"  # raw string (r"") to avoid double blackslash

cnxn = pyodbc.connect(connection_string)

cursor1 = cnxn.cursor()

with open(filepath, 'rt') as sql_file:  # note also mode is "rt" to read text
    cursor1.execute(sql_file.read())

edited Jan 11 '23 at 10:07

answered Jan 11 '23 at 09:59

ljmc

4,830
2
7
26

Thanks @ljmc , Script is running but unable to create table in sql server, – microset Jan 11 '23 at 10:09
What error are you seeing ? – ljmc Jan 11 '23 at 10:16
there is no any error getting but table is not creating into database. – microset Jan 11 '23 at 10:36
Alright, in another script please put `SELECT 1`, change `filepath` to point to that script and add a `print(cursor1.fetchone())` at the end of my code above, see if this prints a `1`. – ljmc Jan 11 '23 at 13:21
select 1 is giving result as (1, ) as query , but I cannot get result while run whole script in python, in cursor1.fetchone() is shows "No results. Previous SQL was not a query.". – microset Jan 12 '23 at 05:08
Yes, sorry it's going to give the `(1,)` tuple. Anyway, still with the `SELECT 1` script, try to assign the `sql_file.read()` to a variable, print that variable, then if you want you can still execute with that variable and print the results, but this should not change from previous trial. – ljmc Jan 12 '23 at 09:18
select 1 is working properly with script and added into single variable but table is not creating with script. – microset Jan 12 '23 at 11:19

how to run .sql (mssql or sql server) file in python?

1 Answers1