Python — SQLAlchamy — PyMySql
SQLAlchemy supports multiple database clients using dialects/engines
Complete list of dialects supported click here
create_engine accepts data source name (dsn) as parameter
Parts of dsn
scheme://username:password@host/database?params
Since we are using MySQL scheme is mysql
Note :- host is mysql in our case since it was running in docker usually it will be 127.0.0.1 or localhost
engine = create_engine(‘mysql+pymysql://root:password@mysql/stackoverflow_survey_1’)
Obtaining connection
conn = engine.connect()
Converting a sample query to data frame
We can use pandas read_sql function to convert the result into data frame directly It accepts 2 mandatory parameters
- query
- database connection object
query = 'SELECT * FROM responses limit 5;';pd.read_sql(query, conn,index_col='id')
ORM
Object relation mapping is a technique for converting data between incompatible type systems using object-oriented programming languages.
In DBMS it is converting results of query into a Object
SQLAlchemy supports ORM out of box
