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()

 

C# Dictionary from Key Value Pair String (URL)

I was searching for an elegant way to break a url into key value pairs and add them to a dictionary. 

Most of the code I came across appeared to be overkill so I wrote the function (getKeyValuePairs) below.

I return the dictionary from a function/method call because there seems to be some confusion in a few places on the web about being able to achieve this.

    class Program
    {
        static void Main(string[] args)
        {
            string message = "EventType=Information&EventDateGMT=2016-05-19 12:00:00&Source=Calling and Source Apps&Severity=99&Message=Hello World.!.";
            Dictionary<string, string> dict = getKeyValuePairs(message, '&', '=');
            foreach (var i in dict)  Console.WriteLine("Key: {0}, Value: {1}", i.Key, i.Value);

            Console.WriteLine("hit any key when ready."); Console.ReadKey();
        }

        private static Dictionary<string, string> getKeyValuePairs(string data, char setDelimiter, char keyValueDelimiter)
        {
            return data.Split(setDelimiter).ToDictionary(x => x.Split(keyValueDelimiter)[0], x => x.Split(keyValueDelimiter)[1]);
        }     

    }