python cp6_p128_Save Links To MySQL_pyMySQL_mysql-connector_MySQLdb_sql_Alchemy

#!/usr/bin/python
#encoding:utf-8

"""
@author: LlQ
@contact:LIQINGLIN54951@gmail.com
@file：cp6_p128.py
@time: 4/22/2019 5:57 PM
"""

from urllib.request import urlopen
from bs4 import BeautifulSoup
import re
import pymysql
from random import shuffle

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='LlQ54951', db='mysql', charset='utf8')

cur = conn.cursor()
cur.execute('USE wikipedia')

def insertPageIfNotExists(url):
    #ensures that page records are not duplicated
    cur.execute( 'Select * From pages Where url = %s', (url) )

    # As its name indicates, this function inserts a new page record
    # if it does not exist already.
    if cur.rowcount == 0:
        cur.execute( 'Insert Into pages(url) Values(%s)', (url) )
        conn.commit()
        return cur.lastrowid
    else:
        return cur.fetchone()[0]

def loadPages():
    # This loads all current pages from the database
    cur.execute( 'Select * From pages' )
                               # into a list
    pages = [row[1] for row in cur.fetchall()] #url list
    return pages

def insertLink( fromPageId, toPageId ):
    # It will not create a link if that link already exists
    cur.execute( 'Select * From links Where fromPageId = %s And toPageId = %s', (int(fromPageId), int(toPageId)) )

    if cur.rowcount == 0:
        cur.execute('Insert Into links(fromPageId, toPageId) Values(%s, %s)', (int(fromPageId), int(toPageId)) )
        conn.commit()

def getLinks(pageUrl, recursionLevel, pages):
    if recursionLevel > 4:
        return

    pageId = insertPageIfNotExists( pageUrl )

    html = urlopen( 'https://en.wikipedia.org{}'.format(pageUrl) )
    bs = BeautifulSoup(html, 'html.parser')

    links = bs.findAll('a', href=re.compile('^(/wiki/)((?!:).)*$') )
    links = [link.attrs['href'] for link in links]

    for link in links:
        insertLink(pageId, insertPageIfNotExists(link))

        if link not in pages: # pages is a url list
            # We have encountered a new page, add it and search
            # it for links
            pages.append(link)
            getLinks(link, recursionLevel+1, pages)

getLinks('/wiki/Kevin_Bacon', 0, loadPages())
cur.close()
conn.close()

#######################################################################

@file：01.connectordemo.py

# download and install
# mysql-connector: https://dev.mysql.com/downloads/connector/python/
# MySQLdb: --> Anaconda Prompt: pip install mysqlclient

#check
#mysql-connector:
#from mysql import connector

#MySQLdb:
#import MySQLdb
#MySQLdb.__version__
#show: '1.4.2.post1'

#import sqlalchemy
#sqlalchemy.__version__
#show: '1.1.13'

from mysql import connector  #suggest not

#default mysql
conn = connector.connect(host='127.0.0.1', port=3306, user='root', password='LlQ54951', database='test', charset='utf8')
cursor = conn.cursor()

conn.autocommit = True

#session1
# sqlStatement="insert into users(name, address,email) values('dasheng','beijing','abc@def')"
# cursor.execute(sqlStatement)
# # cursor.connection.commit()
# # AttributeError: 'CMySQLCursor' object has no attribute 'connection'
# cursor.close()
# conn.close()

#session2 Query
# sqlStatement = 'select * from users'
# cursor.execute(sqlStatement)
#
# for row in cursor:
#     print(row)
#
# cursor.close()
# conn.close()

#session3
#easy_install faker

from faker import Factory

#>>> userfaker = Factory.create()  #generator

#>>> userfaker.name()  #return a name
#'Kayla Romero'

#>>> userfaker.address()
#'4785 Lauren River Apt. 778\nEast Bryanview, WV 16121'

#>>> userfaker.email()
#'reneetaylor@welch.com'

#>>> userfaker.paragraph()
#'Two at home according. North mother never treat seek whole. A best dog act. Add poor whom edge.'

#session4 insert
userfaker = Factory.create()

#tupleList
userinfo = [(userfaker.name(), userfaker.address(), userfaker.email()) for i in range(10)]

#print(userinfo)
sql_template = "insert into users(name, address,email) values(%s, %s, %s)"
#error: cursor.execute(sql_template, userinfo)

for tuple in userinfo:
    cursor.execute(sql_template, tuple)
    #print("Name: ",name,"Address: ",address, "Email: ",email)
cursor.close()
conn.close()

###################################################

@file：02.mysqldbdemo.py

import MySQLdb  #suggest: security

conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root',
                       passwd='LlQ54951', db='test',charset='utf8')
cursor = conn.cursor()

conn.autocommit(True)

# #session2 Query
# sqlStatement = 'select * from users'
# cursor.execute(sqlStatement)
#
# for row in cursor.fetchmany(2):
#     print(row)
#
# cursor.close()
# conn.close()

#session4 insert
from faker import Factory
userfaker = Factory.create()

#tupleList
userinfo = [(userfaker.name(), userfaker.address(), userfaker.email()) for i in range(10)]

#print(userinfo)
sql_template = "insert into users(name, address,email) values(%s, %s, %s)"
#error: cursor.execute(sql_template, userinfo)

for tuple in userinfo:
    cursor.execute(sql_template, tuple)
    #print("Name: ",name,"Address: ",address, "Email: ",email)
cursor.close()
conn.close()

###################################################

#!/usr/bin/python
#encoding:utf-8

"""
@author: LlQ
@contact:LIQINGLIN54951@gmail.com
@file：03.sqlalchemydemo.py
@time: 6/2/2019 2:11 AM
"""

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, backref

#1 create connection
# conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root',
#                        passwd='LlQ54951', db='test',charset='utf8')
                             #connector#username#password#host#port #database  #echo: if print SQL to screen
engine = create_engine('mysql+mysqldb://root:LlQ54951@127.0.0.1:3306/testdb?charset=utf8',echo=True)

#2
Base = declarative_base()
class User(Base):   #tablename
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(40))
    #addAttribute         #className
    orders = relationship('Order')

class Product(Base):
    __tablename__ = 'products'

    id = Column(Integer, primary_key=True)
    name = Column(String(40))
    orders = relationship('Order')

class Order(Base):
    __tablename__ = 'orders'

    id = Column(Integer, primary_key=True)
    orderTime = Column(DateTime)
                                      #tableName.primary_key
    uid = Column(Integer, ForeignKey('users.id'))
    pid = Column(Integer, ForeignKey('products.id'))

#3
Base.metadata.create_all(engine)

#4 create session
Session = sessionmaker(engine)
session = Session()

#5      #session1 add objects to table
# liqing = User(name='liqing')
# session.add(liqing)
#
# p1 = Product(name='p1')
# session.add(p1)
#
# session.commit()

       #session2 relationship
liqing = session.query(User).filter(User.name=='liqing').one()
p1 = session.query(Product).filter(Product.name=='p1').one()

o1=Order(uid=liqing.id, pid=p1.id)
session.add(o1)
session.commit()

orders = liqing.orders
for order in orders:
    print(order.id) #output:1