Python Pycharm and SQL Server connection

Question

I would like to use data from SQL server in Pycharm using python. I have my database connection set up in Pycharm, but not sure how to access this data within my python code. I would like to query the data within the python code (similar to what I would do in R using the RODBC package).

Any suggestions on what to do or where to look would be much appreciated.

Hi megv! Is is possible for you to share how you connected your database in Pycharm? I am having a lot trouble finding such info and would really really appreciate some guidance. — alwaysaskingquestions, Apr 04 '17 at 00:09
Set up ODBC database connection through control panel. import pandas conn = pyodbc.connect("DSN=connection_name") sql="""SELECT * FROM table] (nolock) where date>'2017-01-01) """ df = pandas.io.sql.read_sql(sql, conn) — megv, Apr 05 '17 at 03:01
thank you so much for the response!!! definitely appreciate it. — alwaysaskingquestions, Apr 05 '17 at 06:58

score 0 · Answer 1 · answered May 21 '16 at 17:07

I have been having issues with this over learning this the last few days. (database / python) For me I am working in flask but it doesn't really seem to matter.

I did get this to work though not exactly what you ask but might get you a start

import MySQLdb

def database():

db = MySQLdb.connect(host="localhost", port=3306, user="root", passwd="admin", db="echo")
cursor = db.cursor()
cursor.execute( "INSERT INTO `post` (`hello`) VALUES (null), ('hello_world')" )
db.commit()
db.close()

I had to just set up my database from the command line. Its not pretty or intuitive but should get you started.

score 0 · Answer 2 · answered May 23 '16 at 00:51

If you want to work with Python objects rather than SQL, I'd use SqlAlchemy and reflection.

from sqlalchemy import MetaData, create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.automap import automap_base

engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)
metadata = MetaData()
metadata.reflect(bind=engine)

session = Session(engine)
Base = automap_base(metadata=metadata)
Base.prepare()

# assuming I have a table named 'users'
Users = Base.classes.users
someUsers = Users.query.filter(Users.name.in_(['Jack', 'Bob', 'YakMan')).all()

score 0 · Answer 3 · edited Jul 14 '19 at 07:10

0

import mysql.connector
connection=mysql.connector.connect(user='root', password='daniela', host='localhost', database='girrafe')
mycursor=connection.cursor()

edited Jul 14 '19 at 07:10

dbc

104,963
20
228
340

answered Jul 14 '19 at 07:07

Lord Dan

1

score 0 · Answer 4 · answered Jul 14 '19 at 07:53

There is a concept called OR(Object Relational) Mapping in python, which can be used for database connections. One of the modules that you need to import for the purpose is SQLAlchemy. First, you will need to install sqlalchemy by:

    pip install sqlalchemy

Now, for database connection, we have an Engine class in the sqlalchemy, which is responsible for the database connectivity. We create an object of the Engine class for establishing connection.

    from sqlalchemy import create_engine,MetaData,select
    engine=create_engine("mysql://user:pwd@localhost/dbname", echo=True)
    connection=engine.connect()

The process of reading the database and creating metadata is called Reflection.

    metadata=MetaData()
    query=select([Student]) #Assuming that my database already has a table named Student 
    result=connection.execute(query)
    row=result.fetchall()  #This works similar to the select* query

In this way, you can manipulate data through other queries too, using sqlalchemy!

Python Pycharm and SQL Server connection

4 Answers4

Linked