0

Ok so here is my use case. I've to make connection to different types of DB(MSSQL, oracl, MYSQL, etc.). I've .sql files for each of these database. As it seems sqlalchemy can't run .sql file so we need to open and execute the statements one by one from the .sql files over the connection.

So guys, I'm having this information and I wanted to connect using SQL Alchemy.

<db type="MSSQL" version="2005" patch_level="SP2" port="1433" id="MSSQLSERVER"/>

here MSSQLServer is the instance. No DB information is provide. so do I need DB name to connect to DB?

this is my command

engine = create_engine('mssql+pyodbc://sa:pass@172.21.153.227/MSSQLSERVER', echo=True)

this is my complete code 

from sqlalchemy.engine import create_engine
engine = create_engine('mssql+pyodbc://sa:pass@172.21.153.227', echo=False)
connection = engine.connect()
connection.execute(
    """
    select @@version
    """
)
connection.close()
Hemant
  • 1,313
  • 17
  • 30

1 Answers1

1

you don't need a db name, you can use this function i wrote: (it works for me on mySQL)

def ConnectToDB(self, server, uid, password):
        """
        this method if for connecting to a db
        @param server: server name
        @param uid: username
        @param password: password
        """

        connection_string = 'mysql://{}:{}@{}'.format(uid, password, server)

        try:
            self.engine = create_engine(connection_string)
            self.connection = self.engine.connect()
        except exc.SQLAlchemyError, e:
            self.engine = None
            return False, e

        return True, None

in your SQL statements you will say the DB and table some thing like this:

INSERT INTO `dbName`.`dbTable`.........
Kobi K
  • 7,743
  • 6
  • 42
  • 86
  • I've edited my question to give a bit of background about what I'm trying to accomplish over here. – Hemant Sep 25 '13 at 10:04
  • I tried without the instance name. it seems not to work for MSSQL. I'm getting the following error - sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') None None – Hemant Sep 25 '13 at 10:06
  • Do you run your sql statment this way: self.connection.execute(SQLStatment) P.S the error you wrote is it on the connection or when you are trying to send a statement? – Kobi K Sep 25 '13 at 10:14
  • i've searched a bit i think you can find your answer here: http://stackoverflow.com/a/15627017/1982962 – Kobi K Sep 25 '13 at 10:50
  • I've looked at the url that you've sent but I'm still not able to connect to the DB. I did some more research and it seems sqlserver odbc driver is not a problem when connecting from windows to windows – Hemant Sep 27 '13 at 09:11