Using SQLAlchemy to connect sqlserver2012 databases under MacOS

Connect to SQL Server database using SQLAlchemy under MacOS Preface

Recently requested, to convert the database to a giant hard home SQL Server 2012 because on the internet struggling to find SQLAlchemy to configure the specific Chinese steps to connect SQL Server, turn over the official documents and organize the steps

Pre-preparation

The SQLAlchemy has been installed.

Introduction to the operating environment

System: MacOS 10.13.1 Beta Python Environment: 3.6.0 sqlalchemy 1.1.4 pycode 4.0.19 SQL Server 2012

Implementation steps

In SQLAlchemy, the original address for connecting to SQL Server is described http://docs.sqlalchemy.org/en/latest/core/engines.html#sqlalchemy.create_engine

Microsoft SQL Server

The SQL Server dialect uses PYODBC as the default Dbapi. Pymssql is also available:

Pyodbc

Engine = Create_engine (' mssql+pyodbc://scott:[email protected] ')

Pymssql

Engine = Create_engine (' mssql+pymssql://scott:[email protected]:p ort/dbname ')

So you want to use SQLAlchemy first configure PYODBC normal use

Installing PYODBC

If you feel too slow, you can use the add-I parameter to select domestic sources such as watercress

install pyodbc

Pip Install Pyodbc

Configure PYODBC

Under Mac OS, PYODBC is not just for installation, but it also requires some configuration to be able to see the official documentation for PYODBC Https://github.com/mkleehammer/pyodbc/wiki

Macos Configuration document See Https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX

The following is a rough translation of the steps in the document

Installing install FreeTDS and UnixODBC using the command line Software Installation tool on your Mac

brew updatebrew install unixodbcbrew install freetds --with-unixodbc

Encounter the Pit

During the installation process, it is possible that the downloaded package is incomplete due to network reasons, and it is possible to report an error similar to SHA256 Checksum mismatch , as long as the incomplete package is removed from the download path as prompted.

Treetds Configuration

Use commands to get the freetds.conf location

tsql -C

Then append your own SQL Server configuration to the back of this file

For example:

[MYMSSQL]host = mssqlhost.xyz.comport = 1433tds version = 7.3

For a different database with the TDS version look below http://www.freetds.org/userguide/choosingtdsprotocol.htm through the above configuration The Mac can be connected to the SQL Server servers, and you can use the following command to connect to the command line state

tsql -S MYMSSQL -U myuser -P mypassword

Mymssql the name that always gives a database connection in the config file

MyUser User for connection

MyPassword User-corresponding password

Status after a successful connection

"en_US.UTF-8"locale charset is "UTF-8"using default charset "UTF-8"1>

You can use a SQL statement to test for example "SELECT @ @VERSION"

Note When you finish entering the SQL statement, enter go in the next line (enter) to execute

Edit Odbcinst.ini and Odbc.ini configuration files

To get the file location of Odbcinst.ini and Odbc.ini by command

odbcinst -j

Write the following to Odbcinst.ini

[FreeTDS]Description=FreeTDS Driver for Linux & MSSQLDriver=/usr/local/lib/libtdsodbc.soSetup=/usr/local/lib/libtdsodbc.soUsageCount=1

Write the following to Odbc.ini

[MYMSSQL]Description         = Test to SQLServerDriver              = FreeTDSServername          = MYMSSQL

Note that the Servername here is the name configured in freetds.conf.

then enter

isql MYMSSQL myuser mypassword

You can enter some SQL statements to test

Sample code to connect to the database via PYODBC

import pyodbc# the DSN value should be the name of the entry in odbc.ini, not freetds.conf#将myuser 和mypassword 自己环境中的conn = pyodbc.connect(‘DSN=MYMSSQL;UID=myuser;PWD=mypassword‘)crsr = conn.cursor()rows = crsr.execute("select @@VERSION").fetchall()print(rows)crsr.close()conn.close()

If the results show that the connection is normal,

Manipulating databases through SQLAlchemy for example, creating a table

#!/usr/bin/env Python3Import Pyodbc,sqlalchemyFrom Sqlalchemy.ext.declarativeImport Declarative_baseFrom SQLAlchemyImport Column,integer,string,create_engineFrom Sqlalchemy.ormImport SessionmakerFrom Sqlalchemy.sqlImport SqlTypesENGINE = Create_engine (' Mssql+pyodbc://admin:[Email protected]Mymssql ')Base = Declarative_base ()ClassUserinfo (Base): __tablename__ = ' userinfo ' id =Column (Integer,primary_key=True,Autoincrement=True) account_id =Column (string (255), nullable=False) Username = Span class= "Hljs-type" >column (string (255), nullable=false) Encrypted_password = column (sqltypes.varbinary (+), nullable=False) token = column (string (255)) def init_db ():  Base.metadata.create_all (engine) def drop_db ():  Base.metadata.drop_all (engine) def session (): CLS = Sessionmaker (engine) return CLS () #drop_db () init_db ()       

Use SQLAlchemy to connect sqlserver2012 databases under MacOS