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.

Answer 1

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

Answer 2

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!

Answer 3

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()

Answer 4

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.