Creating connection sring for mysql DB

Question

I tried the exact same code as found here... https://github.com/TomMalkin/SimQLe

I am not sure how to connect to mysql database.

from simqle import ConnectionManager
cm = ConnectionManager("connections.yaml")

sql = "SELECT name, age FROM people WHERE category = :category"
params = {"category": 5}

result = cm.recordset(con_name="my-database", sql=sql, params=params)

Getting an error:

UnknownConnectionError: Unknown connection my-database

This is how I can connect to mysql database from command prompt.

mysql -h 172.31.84.39 -udba -pXXXX -P 3392

How do I write the connection string?

Don't know what's wrong with your approach, but I found mysql.connector the simplest way to connect to mysql. Give it a try. — Keivan Ipchi Hagh
– Keivan Ipchi Hagh, Commented Jul 25, 2021 at 6:01

shiratori3 · Accepted Answer · 2021-07-25 06:47:37Z

2

I usually use sqlalchemy to connect mysql database. I have readed the document of SimQLe which you are using. In SimQLe document,

cm = ConnectionManager("connections.yaml")

is the way to connect to database and you should put your login param in this yaml file called connections.yaml.

Here is the offical document simple: https://github.com/TomMalkin/SimQLe#the-connectionsyaml-file

connections:
 
    # The name of the connection - this is what will be used in your project
    # to reference this connection.
  - name: my-sql-server-database
    driver: mssql+pyodbc:///?odbc_connect=
    connection: DRIVER={SQL Server};UID=<username>;PWD=<password>;SERVER=<my-server>
 
    # some odbc connections require urls to be escaped, this is managed by
    # setting url_escaped = true:
    url_escape: true

    # File based databases like sqlite are slightly different - the driver
    # is very simple.
  - name: my-sqlite-database
    driver: sqlite:///
    # put a leading '/' before the connection for an absolute path, or omit
    # if it's relative to the project path
    connection: databases/my-database.db
    #  This connection will be used if no name is given if the default 
    # parameter is used:
    default: true

Maybe you should change some params in here:

    driver: mssql+pyodbc:///?odbc_connect=
    connection: DRIVER={SQL Server};UID=<username>;PWD=<password>;SERVER=<my-server>

And from the document, it says that SimQle is built on SQLAlchemy,

SimQLe is built on top of the fantastic SQLAlchemy library.

maybe you can use the SQLAlchemy's login params to connect the database in SimQLe. Such like this:

mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

Changed to:

    driver: mysql+pymysql://
    connection: <username>:<password>@<host>/<dbname>[?<options>]

Offical documents:

https://simqle.readthedocs.io/en/latest/ https://docs.sqlalchemy.org/en/14/dialects/mysql.html#module-sqlalchemy.dialects.mysql.pymysql https://docs.sqlalchemy.org/en/14/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc

edited Jul 25, 2021 at 6:47

answered Jul 25, 2021 at 6:27

shiratori3

1461 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shantanuo Over a year ago

Do you think there is any advantage in using connectionManager class?

shiratori3 Over a year ago

@shantanuo, I haven't use this package and can't say whether it's an advantage to save your login params in a separate file. But if you have this demand to do this, it maybe better for you. And usually I think it's better to use the popular package to build your project, since it's more stable and the document can be more clearly.

Tom Malkin Over a year ago

@shantanuo @shiratori3 Author the package here, thanks for trying it out! The default is with a dot at the start: .connections.yaml, kind of treating it like a .env file in that it shouldn't be committed to a repo for security reasons. In answer to "is there an advantage to using a class" - I find it helps me because all the connection details to all the databases you have to deal with are stored in the class and you just supply which connection, the sql and params to easily get at your data :). There are other reasons which are outlined simqle.readthedocs.io/en/latest/explanation

Collectives™ on Stack Overflow

Creating connection sring for mysql DB

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related