218f960e-2f76-429e-aa73-367d2e11b48e

Committed 07 Aug 2024 10:16AM UTC coverage: 85.87% (+0.1%) from 85.767%

Build # 218f960e-2f76-429e-aa73-367d2e11b48e

Build Type

Pull #235

circleci

Committed by

web-flow

Commit Message

Use urlquote to allow passwords with special characters.

Pull Request Pull Request #235: Use urlquote to allow passwords with special characters.

Run Details

1 of 3 new or added lines in 1 file covered. (33.33%)

3470 of 4041 relevant lines covered (85.87%)

6.77 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

85.15

/scrapydweb/utils/setup_database.py

# coding: utf-8
import os
import re
import sys
from urllib.parse import quote as urlquote


DB_APSCHEDULER = 'scrapydweb_apscheduler'
DB_TIMERTASKS = 'scrapydweb_timertasks'
DB_METADATA = 'scrapydweb_metadata'
DB_JOBS = 'scrapydweb_jobs'
DBS = [DB_APSCHEDULER, DB_TIMERTASKS, DB_METADATA, DB_JOBS]

PATTERN_MYSQL = re.compile(r'mysql://(.+?)(?::(.+?))?@(.+?):(\d+)')
PATTERN_POSTGRESQL = re.compile(r'(?:postgres|postgresql)://(.+?)(?::(.+?))?@(.+?):(\d+)')
PATTERN_SQLITE = re.compile(r'sqlite:///(.+)$')

SCRAPYDWEB_TESTMODE = os.environ.get('SCRAPYDWEB_TESTMODE', 'False').lower() == 'true'


def test_database_url_pattern(database_url):
    m_mysql = PATTERN_MYSQL.match(database_url)
    m_postgres = PATTERN_POSTGRESQL.match(database_url)
    m_sqlite = PATTERN_SQLITE.match(database_url)
    return m_mysql, m_postgres, m_sqlite


def setup_database(database_url, database_path):
    database_url = re.sub(r'\\', '/', database_url)
    database_url = re.sub(r'/$', '', database_url)
    database_path = re.sub(r'\\', '/', database_path)
    database_path = re.sub(r'/$', '', database_path)

    m_mysql, m_postgres, m_sqlite = test_database_url_pattern(database_url)
    if m_mysql:
        setup_mysql(*m_mysql.groups())
    elif m_postgres:
        setup_postgresql(*m_postgres.groups())
    else:
        database_path = m_sqlite.group(1) if m_sqlite else database_path
        database_path = os.path.abspath(database_path)
        database_path = re.sub(r'\\', '/', database_path)
        database_path = re.sub(r'/$', '', database_path)
        if not os.path.isdir(database_path):
            os.mkdir(database_path)

    if m_mysql or m_postgres:
        APSCHEDULER_DATABASE_URI = '/'.join([database_url, DB_APSCHEDULER])
        SQLALCHEMY_DATABASE_URI = '/'.join([database_url, DB_TIMERTASKS])
        SQLALCHEMY_BINDS = {
            'metadata': '/'.join([database_url, DB_METADATA]),
            'jobs': '/'.join([database_url, DB_JOBS])
        }
    else:
        # db names for backward compatibility
        APSCHEDULER_DATABASE_URI = 'sqlite:///' + '/'.join([database_path, 'apscheduler.db'])
        # http://flask-sqlalchemy.pocoo.org/2.3/binds/#binds
        SQLALCHEMY_DATABASE_URI = 'sqlite:///' + '/'.join([database_path, 'timer_tasks.db'])
        SQLALCHEMY_BINDS = {
            'metadata': 'sqlite:///' + '/'.join([database_path, 'metadata.db']),
            'jobs': 'sqlite:///' + '/'.join([database_path, 'jobs.db'])
        }

    if SCRAPYDWEB_TESTMODE:
        print("DATABASE_PATH: %s" % database_path)
        print("APSCHEDULER_DATABASE_URI: %s" % APSCHEDULER_DATABASE_URI)
        print("SQLALCHEMY_DATABASE_URI: %s" % SQLALCHEMY_DATABASE_URI)
        print("SQLALCHEMY_BINDS: %s" % SQLALCHEMY_BINDS)
    return APSCHEDULER_DATABASE_URI, SQLALCHEMY_DATABASE_URI, SQLALCHEMY_BINDS, database_path


def drop_database(cur, dbname):
    sql = "DROP DATABASE %s" % dbname
    print(sql)
    try:
        cur.execute(sql)
    except Exception as err:
        print(err)


def setup_mysql(username, password, host, port):
    """
    ModuleNotFoundError: No module named 'MySQLdb'
    pip install mysqlclient
    Python 2: pip install mysqlclient -> MySQLdb/_mysql.c(29) :
    fatal error C1083: Cannot open include file: 'mysql.h': No such file or directory
    https://stackoverflow.com/questions/51294268/pip-install-mysqlclient-returns-fatal-error-c1083-cannot-open-file-mysql-h
    https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient
    pip install "path to the downloaded mysqlclient.whl file"
    """
    require_version = '0.9.3'  # Dec 18, 2018
    install_command = "pip install --upgrade pymysql"
    try:
        import pymysql
        assert pymysql.__version__ >= require_version, install_command
    except (ImportError, AssertionError):
        sys.exit("Run command: %s" % install_command)
    else:
        # Run scrapydweb: ModuleNotFoundError: No module named 'MySQLdb'
        pymysql.install_as_MySQLdb()

    conn = pymysql.connect(host=host, port=int(port), user=username, password=urlquote(password),
                           charset='utf8', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    for dbname in DBS:
        if SCRAPYDWEB_TESTMODE:
            drop_database(cur, dbname)
        # pymysql.err.ProgrammingError: (1007, "Can't create database 'scrapydweb_apscheduler'; database exists")
        # cur.execute("CREATE DATABASE IF NOT EXISTS %s CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'" % dbname)
        try:
            cur.execute("CREATE DATABASE %s CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'" % dbname)
        except Exception as err:
            if 'exists' in str(err):
                pass
            else:
                raise
    cur.close()
    conn.close()


def setup_postgresql(username, password, host, port):
    """
    https://github.com/my8100/notes/blob/master/back_end/the-flask-mega-tutorial.md
    When working with database servers such as MySQL and PostgreSQL,
    you have to create the database in the database server before running upgrade.
    """
    require_version = '2.7.7'  # Jan 23, 2019
    install_command = "pip install --upgrade psycopg2"
    try:
        import psycopg2
        assert psycopg2.__version__ >= require_version, install_command
    except (ImportError, AssertionError):
        sys.exit("Run command: %s" % install_command)

    conn = psycopg2.connect(host=host, port=int(port), user=username, password=urlquote(password))
    conn.set_isolation_level(0)  # https://wiki.postgresql.org/wiki/Psycopg2_Tutorial
    cur = conn.cursor()
    for dbname in DBS:
        if SCRAPYDWEB_TESTMODE:
            # database "scrapydweb_apscheduler" is being accessed by other users
            # DETAIL:  There is 1 other session using the database.
            # To restart postgres server on Windonws -> win+R: services.msc
            drop_database(cur, dbname)

        # https://www.postgresql.org/docs/9.0/sql-createdatabase.html
        # https://stackoverflow.com/questions/9961795/
        # utf8-postgresql-create-database-like-mysql-including-character-set-encoding-a

        # psycopg2.ProgrammingError: invalid locale name: "en_US.UTF-8"
        # https://stackoverflow.com/questions/40673339/
        # creating-utf-8-database-in-postgresql-on-windows10

        # cur.execute("CREATE DATABASE %s ENCODING 'UTF8' LC_COLLATE 'en-US' LC_CTYPE 'en-US'" % dbname)
        # psycopg2.DataError: new collation (en-US) is incompatible with the collation of the template database
        # (Chinese (Simplified)_People's Republic of China.936)
        # HINT:  Use the same collation as in the template database, or use template0 as template.
        try:
            cur.execute("CREATE DATABASE %s ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8'" % dbname)
        except:
            try:
                cur.execute("CREATE DATABASE %s" % dbname)
            except Exception as err:
                # psycopg2.ProgrammingError: database "scrapydweb_apscheduler" already exists
                if 'exists' in str(err):
                    pass
                else:
                    raise
    cur.close()
    conn.close()

1	# coding: utf-8
2	import os	8✔
3	import re	8✔
4	import sys	8✔
5	from urllib.parse import quote as urlquote	8✔
6
7
8	DB_APSCHEDULER = 'scrapydweb_apscheduler'	8✔
9	DB_TIMERTASKS = 'scrapydweb_timertasks'	8✔
10	DB_METADATA = 'scrapydweb_metadata'	8✔
11	DB_JOBS = 'scrapydweb_jobs'	8✔
12	DBS = [DB_APSCHEDULER, DB_TIMERTASKS, DB_METADATA, DB_JOBS]	8✔
13
14	PATTERN_MYSQL = re.compile(r'mysql://(.+?)(?::(.+?))?@(.+?):(\d+)')	8✔
15	PATTERN_POSTGRESQL = re.compile(r'(?:postgres\|postgresql)://(.+?)(?::(.+?))?@(.+?):(\d+)')	8✔
16	PATTERN_SQLITE = re.compile(r'sqlite:///(.+)$')	8✔
17
18	SCRAPYDWEB_TESTMODE = os.environ.get('SCRAPYDWEB_TESTMODE', 'False').lower() == 'true'	8✔
19
20
21	def test_database_url_pattern(database_url):	8✔
22	m_mysql = PATTERN_MYSQL.match(database_url)	8✔
23	m_postgres = PATTERN_POSTGRESQL.match(database_url)	8✔
24	m_sqlite = PATTERN_SQLITE.match(database_url)	8✔
25	return m_mysql, m_postgres, m_sqlite	8✔
26
27
28	def setup_database(database_url, database_path):	8✔
29	database_url = re.sub(r'\\', '/', database_url)	8✔
30	database_url = re.sub(r'/$', '', database_url)	8✔
31	database_path = re.sub(r'\\', '/', database_path)	8✔
32	database_path = re.sub(r'/$', '', database_path)	8✔
33
34	m_mysql, m_postgres, m_sqlite = test_database_url_pattern(database_url)	8✔
35	if m_mysql:	8✔
36	setup_mysql(*m_mysql.groups())	1✔
37	elif m_postgres:	7✔
38	setup_postgresql(*m_postgres.groups())	1✔
39	else:
40	database_path = m_sqlite.group(1) if m_sqlite else database_path	6✔
41	database_path = os.path.abspath(database_path)	6✔
42	database_path = re.sub(r'\\', '/', database_path)	6✔
43	database_path = re.sub(r'/$', '', database_path)	6✔
44	if not os.path.isdir(database_path):	6✔
45	os.mkdir(database_path)	1✔
46
47	if m_mysql or m_postgres:	8✔
48	APSCHEDULER_DATABASE_URI = '/'.join([database_url, DB_APSCHEDULER])	2✔
49	SQLALCHEMY_DATABASE_URI = '/'.join([database_url, DB_TIMERTASKS])	2✔
50	SQLALCHEMY_BINDS = {	2✔
51	'metadata': '/'.join([database_url, DB_METADATA]),
52	'jobs': '/'.join([database_url, DB_JOBS])
53	}
54	else:
55	# db names for backward compatibility
56	APSCHEDULER_DATABASE_URI = 'sqlite:///' + '/'.join([database_path, 'apscheduler.db'])	6✔
57	# http://flask-sqlalchemy.pocoo.org/2.3/binds/#binds
58	SQLALCHEMY_DATABASE_URI = 'sqlite:///' + '/'.join([database_path, 'timer_tasks.db'])	6✔
59	SQLALCHEMY_BINDS = {	6✔
60	'metadata': 'sqlite:///' + '/'.join([database_path, 'metadata.db']),
61	'jobs': 'sqlite:///' + '/'.join([database_path, 'jobs.db'])
62	}
63
64	if SCRAPYDWEB_TESTMODE:	8✔
65	print("DATABASE_PATH: %s" % database_path)	8✔
66	print("APSCHEDULER_DATABASE_URI: %s" % APSCHEDULER_DATABASE_URI)	8✔
67	print("SQLALCHEMY_DATABASE_URI: %s" % SQLALCHEMY_DATABASE_URI)	8✔
68	print("SQLALCHEMY_BINDS: %s" % SQLALCHEMY_BINDS)	8✔
69	return APSCHEDULER_DATABASE_URI, SQLALCHEMY_DATABASE_URI, SQLALCHEMY_BINDS, database_path	8✔
70
71
72	def drop_database(cur, dbname):	8✔
73	sql = "DROP DATABASE %s" % dbname	2✔
74	print(sql)	2✔
75	try:	2✔
76	cur.execute(sql)	2✔
77	except Exception as err:	2✔
78	print(err)	2✔
79
80
81	def setup_mysql(username, password, host, port):	8✔
82	"""
83	ModuleNotFoundError: No module named 'MySQLdb'
84	pip install mysqlclient
85	Python 2: pip install mysqlclient -> MySQLdb/_mysql.c(29) :
86	fatal error C1083: Cannot open include file: 'mysql.h': No such file or directory
87	https://stackoverflow.com/questions/51294268/pip-install-mysqlclient-returns-fatal-error-c1083-cannot-open-file-mysql-h
88	https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient
89	pip install "path to the downloaded mysqlclient.whl file"
90	"""
91	require_version = '0.9.3' # Dec 18, 2018	1✔
92	install_command = "pip install --upgrade pymysql"	1✔
93	try:	1✔
94	import pymysql	1✔
95	assert pymysql.__version__ >= require_version, install_command	1✔
96	except (ImportError, AssertionError):	×
97	sys.exit("Run command: %s" % install_command)	×
98	else:
99	# Run scrapydweb: ModuleNotFoundError: No module named 'MySQLdb'
100	pymysql.install_as_MySQLdb()	1✔
101
NEW 102	conn = pymysql.connect(host=host, port=int(port), user=username, password=urlquote(password),	1✔
103	charset='utf8', cursorclass=pymysql.cursors.DictCursor)
104	cur = conn.cursor()	1✔
105	for dbname in DBS:	1✔
106	if SCRAPYDWEB_TESTMODE:	1✔
107	drop_database(cur, dbname)	1✔
108	# pymysql.err.ProgrammingError: (1007, "Can't create database 'scrapydweb_apscheduler'; database exists")
109	# cur.execute("CREATE DATABASE IF NOT EXISTS %s CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'" % dbname)
110	try:	1✔
111	cur.execute("CREATE DATABASE %s CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'" % dbname)	1✔
112	except Exception as err:	×
113	if 'exists' in str(err):	×
114	pass	×
115	else:
116	raise	×
117	cur.close()	1✔
118	conn.close()	1✔
119
120
121	def setup_postgresql(username, password, host, port):	8✔
122	"""
123	https://github.com/my8100/notes/blob/master/back_end/the-flask-mega-tutorial.md
124	When working with database servers such as MySQL and PostgreSQL,
125	you have to create the database in the database server before running upgrade.
126	"""
127	require_version = '2.7.7' # Jan 23, 2019	1✔
128	install_command = "pip install --upgrade psycopg2"	1✔
129	try:	1✔
130	import psycopg2	1✔
131	assert psycopg2.__version__ >= require_version, install_command	1✔
132	except (ImportError, AssertionError):	×
133	sys.exit("Run command: %s" % install_command)	×
134
NEW 135	conn = psycopg2.connect(host=host, port=int(port), user=username, password=urlquote(password))	1✔
136	conn.set_isolation_level(0) # https://wiki.postgresql.org/wiki/Psycopg2_Tutorial	1✔
137	cur = conn.cursor()	1✔
138	for dbname in DBS:	1✔
139	if SCRAPYDWEB_TESTMODE:	1✔
140	# database "scrapydweb_apscheduler" is being accessed by other users
141	# DETAIL: There is 1 other session using the database.
142	# To restart postgres server on Windonws -> win+R: services.msc
143	drop_database(cur, dbname)	1✔
144
145	# https://www.postgresql.org/docs/9.0/sql-createdatabase.html
146	# https://stackoverflow.com/questions/9961795/
147	# utf8-postgresql-create-database-like-mysql-including-character-set-encoding-a
148
149	# psycopg2.ProgrammingError: invalid locale name: "en_US.UTF-8"
150	# https://stackoverflow.com/questions/40673339/
151	# creating-utf-8-database-in-postgresql-on-windows10
152
153	# cur.execute("CREATE DATABASE %s ENCODING 'UTF8' LC_COLLATE 'en-US' LC_CTYPE 'en-US'" % dbname)
154	# psycopg2.DataError: new collation (en-US) is incompatible with the collation of the template database
155	# (Chinese (Simplified)_People's Republic of China.936)
156	# HINT: Use the same collation as in the template database, or use template0 as template.
157	try:	1✔
158	cur.execute("CREATE DATABASE %s ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8'" % dbname)	1✔
159	except:	×
160	try:	×
161	cur.execute("CREATE DATABASE %s" % dbname)	×
162	except Exception as err:	×
163	# psycopg2.ProgrammingError: database "scrapydweb_apscheduler" already exists
164	if 'exists' in str(err):	×
165	pass	×
166	else:
167	raise	×
168	cur.close()	1✔
169	conn.close()	1✔

my8100 / scrapydweb / 218f960e-2f76-429e-aa73-367d2e11b48e

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous