Run SQL Query in Python

Question

I need to convert a big SQL code in python. Instead of converting SQL code to python, Is there any way we can run sql query in python Script? Let's suppose there is a stored procedure in my MsSqlServer. How can I call that stored procedure in python?

Answer 1

A way could be the following (you could save the whole query which will of course be more complicated than the one in the example):

# Import sqlalchemy's create_engine() function
from sqlalchemy import create_engine
import pandas as pd

# Create the database engine
engine = create_engine("mssql+pyodbc://user:pass@dsn")

# Create a SQL query to load the entire table
query = """
SELECT * 
  FROM XXX;
"""

# Load with the SQL query
load = pd.read_sql(query, engine)

Answer 2

There are plenty of ways to run SQL commands within a Python script. Check out SqlAlchemy.

Answer 3

You could use SQLAlchemy with the PyODBC driver, which supports Microsoft SQL Servers. You can install both packages directly from PyPi.

from sqlalchemy import create_engine
engine = create_engine("mssql+pyodbc://user:pass@dsn")
engine.execute("SELECT 1;")

Answer 4

If you are working with mysql localhost basically in XAMP then you can include below code in your python file no need to use sql statements SQLALchemy will work fine.

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:@localhost/yourdatabasename'

Answer 5

in addition to other answers, using sqlalchemy (https://www.sqlalchemy.org/), it is possible to execute raw SQL, without using the "pythonic" object way to perform db actions. For example, for SQLITE (which you can use alternative drivers), you can run it this way.

# install with `pip install sqlalchemy`

import sqlalchemy as db

engine = db.create_engine('sqlite:///my-example-db.sqlite')

with engine.connect() as con:

    rs = con.execute('SELECT * FROM myexampletable')

    for row in rs:
        print(row)