Tag: references

This is a page recording my frequently used codes.
Which I may never remembered… 🙂

File management with Jupyter Notebook

# upload a zip file onto jupyter 
import zipfile 
zip_ref = zipfile.ZipFile('the_file.zip', 'r') 
zip_ref.extractall() 
zip_ref.close() 

# download folder from jupyter 
!tar chvfz notebook.tar.gz *

Retina Matplotlib setting

%config InlineBackend.figure_format = ‘retina'

Flatten a list of lists (This one I managed to remember after…quite a while)

flat_list = [item for sublist in l for item in sublist]

In time reload imported packages
(when you want to test your .py codes in jupyter notebook, with this code you don’t need to restart kernel to reload updated .py code)

%load_ext autoreload
%autoreload 2

Frequently Used MySQL Code

# Add a column with unique values
ALTER TABLE table_group.table_name ADD UNIQUE (url);

# Add a column with integer value and not null attributes
ALTER TABLE table_group.table_name ADD COLUMN colname bigint unsigned not null;

SET SQL_SAFE_UPDATES = 0; 
SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

UPDATE table_group.table_name B 
JOIN ( 
     SELECT id, CRC32(url) url_crc FROM table_group.table_name 
 ) as A ON A.id = B.id 
 SET B.url_crc = A.url_crc;  

# Remove all content in a table
Truncate table_group.table_name;

# delete a column
ALTER TABLE table_group.table_name DROP COLUMN colname;
 
# create a new table with existing table format
CREATE TABLE new_table_name LIKE existing_table_name;

# Call Stored Procedure as an event
CREATE EVENT `event` 
ON SCHEDULE EVERY 1 DAY 
DO CALL procedure; 

# Stored Procedures 
# Combine the two tables using 'IGNORE', which will drop duplicates automatically. 
CREATE DEFINER=`root`@`%` PROCEDURE `procedure_name`() 
BEGIN 
INSERT IGNORE INTO table_group.table_name1 (col1, col2...) 
SELECT col1, col2... FROM table_group.table_name2; 
TRUNCATE table_group.table_name2;
END

Connect MySQL with Jupyter
Comment:
1. when using ‘INSERT INTO’ command, conn.commit() is important or the content will not be inserted into the table.
2. with mariandb format, use ` instead of ‘ !

import pymysql.cursors

conn = pymysql.connect(host='xxx',
                        user='root',
                        password='xxx',
                        db='table_group',
                        charset='utf8mb4',
                        cursorclass=pymysql.cursors.DictCursor)

cursor = conn.cursor()
cursor.execute("""create table `pytest` (`id` int(11), `name` varchar(20));"”")
cursor.execute("""INSERT INTO table_name` (`url`, `jd`) VALUES (%s, %s)""", ('test', 'test’))
conn.commit()
cursor.close()