Mercurial > repos > tduigou > get_db_info

annotate testMock.py @ 0:41ac63b5d221 draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
author tduigou
date Thu, 10 Apr 2025 08:45:18 +0000
parents
children 61158f32e5c3
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
1 import psycopg2
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
2 from psycopg2 import sql
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
3
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
4 def create_db_and_insert_data():
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
5 """Creates a database, a table, and inserts mock data into the PostgreSQL database."""
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
6 # Connect to the PostgreSQL container (default 'postgres' database for setup)
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
7 conn = psycopg2.connect(
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
8 dbname='postgres', # Default database
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
9 user='postgres', # Default user
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
10 password='RK17', # Password from Docker environment
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
11 host='localhost', # Running locally on the default Docker network
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
12 port='5432' # Default PostgreSQL port
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
13 )
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
14
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
15 conn.autocommit = True # Necessary to create a database
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
16 cursor = conn.cursor()
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
17
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
18 # Check if the test database already exists
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
19 cursor.execute("SELECT 1 FROM pg_catalog.pg_database WHERE datname = 'test_fragments_db';")
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
20 exists = cursor.fetchone()
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
21
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
22 if exists:
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
23 print("Database 'test_fragments_db' already exists, dropping it...")
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
24 cursor.execute("DROP DATABASE test_fragments_db;")
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
25
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
26 # Create the new database for testing
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
27 cursor.execute('CREATE DATABASE test_fragments_db;')
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
28 print("Database 'test_fragments_db' created.")
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
29
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
30 cursor.close()
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
31 conn.close()
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
32
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
33 # Now connect to the new test database
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
34 conn = psycopg2.connect(
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
35 dbname='test_fragments_db',
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
36 user='postgres',
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
37 password='RK17',
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
38 host='localhost',
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
39 port='5432'
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
40 )
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
41
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
42 cursor = conn.cursor()
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
43
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
44 # Create the 'sample' table instead of 'fragments'
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
45 cursor.execute('''
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
46 CREATE TABLE sample (
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
47 fragment TEXT PRIMARY KEY,
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
48 sequence TEXT,
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
49 metadata_1 TEXT,
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
50 metadata_2 TEXT
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
51 );
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
52 ''')
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
53
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
54 # Insert mock data
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
55 cursor.executemany('''
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
56 INSERT INTO sample (fragment, sequence, metadata_1, metadata_2) VALUES (%s, %s, %s, %s);
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
57 ''', [
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
58 ('ACP10001AaCbbBS', 'seq for ACP10001AaCbbBS', 'Metadata1 for ACP10001AaCbbBS', 'Metadata2 for ACP10001AaCbbBS'),
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
59 ('CFP10002AaCbbBS', 'seq for CFP10002AaCbbBS', 'Metadata1 for CFP10002AaCbbBS', 'Metadata2 for CFP10002AaCbbBS'),
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
60 ('XYZ10003AaCbbBS', 'seq for XYZ10003AaCbbBS', 'Metadata1 for XYZ10003AaCbbBS', 'Metadata2 for XYZ10003AaCbbBS'),
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
61 ('QWE10004AaCbbBS', 'seq for QWE10004AaCbbBS', 'Metadata1 for QWE10004AaCbbBS', 'Metadata2 for QWE10004AaCbbBS')
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
62 ])
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
63
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
64 conn.commit()
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
65 print("Mock data inserted into 'sample' table.")
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
66
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
67 cursor.close()
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
68 conn.close()
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
69
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
70 if __name__ == "__main__":
41ac63b5d221 planemo upload for repository https://github.com/brsynth commit 15dbdd1f0a222a8e1b0fb5c16b36885520a3d005
tduigou
parents:
diff changeset
71 create_db_and_insert_data()