A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Python 3 Connection to SQL Server with PYPY

Download pypi and install it on your server with python.

https://pypi.python.org/pypi/pypyodbc/

Once the install is complete, run the following code. It will:
   1. create a table
   2. create a few inserts
   3. select the records 
   4. loop through and print the records

 

#!/usr/bin/python3
import pypyodbc
 
#connect to the database. 
conn = pypyodbc.connect(driver='{SQL Server}', server='myserver', database='mydb', uid='user', pwd='pass') 
cur = conn.cursor()

table_create = """
if not exists(select * from information_schema.tables where table_name = 'Hello_World') 
        Create Table Hello_World (id int identity, val varchar(32))
"""
cur.execute(table_create) #create the table 
cur.execute("insert into hello_world values ('Hello'), ('to'), ('you'), ('too')") #insert some rows
cur.commit() #commit the above sql statements. 

cur.execute("select * from hello_world")
results = cur.fetchone() 

while results:
    print ("values :  id=" +  str(results[0]) + " and val='" + results["val"] + "'")
    results = cur.fetchone() 
conn.close()

Open non valid utf_8 character text file with python. (not unicode but non decodable bytes)

 

I attempted to read and write a CSV using python 3.5 and ran across the following error: 
Traceback (most recent call last):
File "C:\<File Path>.csv, line 14, in <module>
if __name__ == "__main__": main()
File "C:\<File Path>.csv", line 8, in main
lines = file.readlines()
File "C:\Programs\Python\Python35-32\lib\encodings\cp1252.py", line 23, in decode
return codecs.charmap_decode(input,self.errors,decoding_table)[0]
UnicodeDecodeError: 'charmap' codec can't decode byte 0x9d in position 4569: character maps to <undefined>

The original data appeared to have no character information and appeared as : Ã¢Â–¡â–¡â–¡â–¡

I added a couple of params to the open method and no more error : " encoding='ascii', errors='surrogateescape' "

 

#!/usr/bin/python3
def main():
    file =  open(r'\\filepath\filename.csv', 'r', encoding='ascii', errors='surrogateescape')    
    lines = file.readlines()
    
    for line in lines:
        print(line, end = '')
  

if __name__ == "__main__": main()

 

Preparing Visual Studio 2015 For Python Development

Setup a Visual Studio Python Environment in 5 Minutes using PTVS (Python Tools for Visual Studio)

 

You'll need:

 

Download the MSI:
Git "Python Tools for Visual Studio" : https://github.com/Microsoft/PTVS
2012 and 2012: https://github.com/Microsoft/PTVS/releases/v2.1.1
2
015 : https://ptvs.azureedge.net/download/PTVS%202.2.5%20VS%202015.msi

 

Install the tools:
Double click your MSI.

 

Create a new Visual Studio  Python Project:
File >New >Project : Installed >Templates >Python
Choose Python Application. 

PTVS Comes with many different python projects types including:

  • Django Web Projects
  • Flask /Jade Web Projects
  • Python Applications (Console Apps)
  • IronPython Projects

 

Setup the Python environment in VS (if not auto detected):

In most cases PTVS will find your python environment and set the location in VS. if this is not the case, follow the below steps to tell VS where your python environment lives.

Go to  Tools >Options >Python Tools >Environment Options. 
Click add environment, add a name for your environment. ex: "Python 3.4 Annaconda".
Add the path to your python.exe. 

Test your setup:

 In Visual Studio navigate to your PythonApplication1.py file and add the following line of code:
print("hello World")

Press F5 on your keyboard or click start. 

Congratulations! You're now setup for Python development using Visual Studio. Happy Dev'n.