00e9f230-625d-4f4a-a266-4f41b44d9fe5

Committed 06 Mar 2024 01:37AM UTC coverage: 85.866% (+0.1%) from 85.767%

Build # 00e9f230-625d-4f4a-a266-4f41b44d9fe5

Build Type

Pull #229

circleci

Committed by

web-flow

Commit Message

Merge branch 'my8100:master' into dev

Pull Request Pull Request #229: in none English environment pg instance, 'exists' will not in str(err), use DuplicateDatabase instead

Run Details

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

1 existing line in 1 file now uncovered.

3469 of 4040 relevant lines covered (85.87%)

8.48 hits per line

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

85.0

/scrapydweb/utils/setup_database.py

# coding: utf-8
import os
import re
import sys


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=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=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 psycopg2.errors.DuplicateDatabase:
                pass
            except Exception as err:
                raise err
    cur.close()
    conn.close()

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

my8100 / scrapydweb / 00e9f230-625d-4f4a-a266-4f41b44d9fe5

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