Monday, May 25, 2020

IPython Notebook to connect to SQL Server 2019

I installed Anaconda 3 some time ago, and then I installed SQL Server 2019 without ML libraries for Python. Now I just wanted to connect from an IPython Notebook to this local named instance of SQL Server, directly.

I tried with both pyodbc (which worked from first try) and pymssql which seemed to have some issue. The error message was:

"MSSQLDatabaseException: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (localhost\\SQLSRV2019)\n')"


It looks like pymssql library needs the TCP/IP connection configuration to be enabled, and also needs SQL Server Browser service to run. So, start SQL Server 2019 Configuration Manager and then Enable TCP/IP configuration setting:



And in Windows Services.msc make sure SQL Server Browser runs:



After these changes restart SQL Server 2019 instance (don't forget this step).

The code for pyodbc:
import pyodbc conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=.\SQLSRV2019;DATABASE=db1;UID=sa;PWD=***') cursor = conn.cursor() cursor.execute("select id1, name1 from Table1") rows = cursor.fetchall() print(rows)
The code for pymssql with Pandas DataFrame:
import pymssql import pandas as pd conn = pymssql.connect(user = 'sa', password = '***', host='.\SQLSRV2019', database = 'db1') q = pd.read_sql_query('select name1, id1 from Table1', conn) conn.close() df = pd.DataFrame(q, columns=['name1','id1']) print(df)
Then let's plot the values with matplotlib:
import matplotlib.pyplot as plt
df.plot(kind='bar')
You can try these with either JupyterLab / Jupyter Notebook from Anaconda 3 or even with VS Code extension for Anaconda.

Monday, February 10, 2020

Azure Key Vault—Private endpoints now available in preview

"Establish a private connection between Azure Key Vault and other Azure services by using Azure Private Link, now available in preview for all public regions.
[...]
All traffic to the service can be routed through the private endpoint, so no gateways, NAT devices, ExpressRoute or VPN connections, or public IP addresses are needed. Traffic between your virtual network and the service traverses over the Microsoft backbone network, eliminating exposure from the public Internet."

A question may be why only now? It should have been there from the beginning.

Docs link.

Sunday, November 24, 2019

ContactForm.Sample project on GitHub

I added a new demo project called ContactForm.Sample at github.com/OviCrisan/ContactForm.Sample. It's just a sample solution with 2 projects, one to show a contact form with both C# and JS / AJAX, which then uses HTTP POST to a web API project, which saves data to a Postgres database. The web API (Contactform.Sample.Postgres) is build as a separate infrastructure project because I intend to add some more API projects for other databases, SQL and no-SQL.

Here's a simple diagram of the projects:

And the regular C# web form with Google reCAPTCHA (demo) enabled:


More technical details are added to readme files in the project source code, including details to run it with Docker compose from Docker containers.

By the way, the project Docker images are also available on Docker Hub here and here.

Comments are welcome. Thanks.

Monday, November 18, 2019

Seagate external hard drive

Black Friday came earlier this year for me with a new Seagate 6TB external hard drive, for all videos and books scattered of different computers and laptops I use, at home and at work. All good with it so far but it's formatted with NTFS which doesn't quite work on Mac OS. After a bit of digging I found out that Seagate offers a free version of Paragon NTFS for Mac. Installed and tested. Nice.

Thursday, November 7, 2019

ContactForm github project

It's been a while since I wanted to post a personal project on GitHub, with few features I'm interested in: ASP.NET Core MVC and APIs, Azure DevOps, Azure Functions & AWS Lambda, as well as Docker images. Some other features will be covered in another project I'm working on, but let's keep that as a secret until available.

The project I'm presenting now it's called ContactForm and is available at github.com/OviCrisan/ContactForm (see the link on the right side bar). It's meant for a simple contact form processing page, which works with both HTTP POST form data as well as REST API JSON, on the same endpoint (the root of deployed URL). There is more in the readme.md files from the project, with this diagram trying to explain different components:

The core of it is a .NET Standard 2.0 library deployed to NuGet.org (nuget.org/packages/OviCrisan.ContactForm), which then is used in ContactForm.Web (web app  + web API), ContactForm.AzFunc (Azure Function) and ContactForm.AWSLambda (AWS Lambda function). Some libraries are using older versions just because AWS Lambda uses .NET Core 2.1, and also at the time of writing this Azure Functions 3.0 are in 'preview' mode.

Depending on the settings in the config file or environment variables it enables email notification and/or webhook posting or REST API call. Also, optionally, you can enable Google reCAPTCHA v2 with visible checkbox. For this last option you need to register your own set of keys or use for testing Google provided samples, which you can find in the readme files in the project.

The web application is also deployed as a Docker image at hub.docker.com/r/OviCrisan/ContactFormWeb, read more here.

The core library and web app have some unit testing projects, but very minimal (using XUnit). Also provided Azure DevOps YAML file for core library continuous integration. Some other additions will be added soon.

Comments, suggestions and bugs reporting are welcome. Thanks in advance.

Wednesday, November 6, 2019

PostgreSQL 12 on Windows 10

I used previous versions of PostgreSQL, but I installed them with default Windows installer from the website which came with a Windows Service easy to start / stop. This last version 12 I installed with Chocolatey and I couldn't find any Windows Service to start.

So, back to documentation and googling. First I create environment variable PGDATA pointing to a newly created data folder, like C:\pgdata (easier than specify that folder in parameters). Then from C:\Program Files\PostgreSQL\12\bin I ran:

.\initdb C:\pgdata
.\pg_ctl start
.\createuser --interactive
.\createdb test1

Newly created user 'postgres' has the superuser role.

Additionally, you can create other users with passwords to be used by the applications.

Then, I prefer to use Valentina Studio which integrates nicely with Postgres.

Monday, November 4, 2019

Big Azure update today

There are a lot of announcements today from Microsoft Azure specially for Ignite event, some of most important ones in my opinion: