A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Python Environments Setup.

#PYTHON  - Windows
python -m venv env
env\Scripts\activate or env\Scripts\activate.bat
cd YOUR_PROJECT
pip install -r requirements.txt 


#Unix
$ python -m venv mysite/env
$ source mysite/env/bin/activate



requirements file sample format:requirements.txt
boto==2.49.0
boto3==1.9.74
botocore==1.12.74
click==6.7
docutils==0.14
dominate==2.3.1
Flask==0.12.2
Flask-Bootstrap==3.3.7.1
Flask-Login==0.4.1
Flask-Migrate==2.3.1
Flask-SQLAlchemy==2.3.2
Flask-WTF==0.14.2
itsdangerous==0.24
Jinja2==2.10
jmespath==0.9.3
Mako==1.0.7
MarkupSafe==1.0
psycopg2==2.7.6.1
psycopg2-binary==2.7.6.1


#ANACONDA
conda create -n <environment name>
conda activate <environment name>
conda install pip
pip install -r requirements.txt


 

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