def drop_email(): query = """ DROP TABLE IF EXISTS emails """ with get_connection() as connection: try: cursor = connection.cursor() cursor.execute(query) except ProgrammingError as e: print(f'Erro ao executar script DROP - DDL. Error:{e.msg}')
def update_contatos(data): with get_connection() as connection: sql = 'UPDATE contatos SET grupo_id= %s WHERE id = %s' try: cursor = connection.cursor() cursor.execute(sql, data) connection.commit() except ProgrammingError as e: print(f'Erro ao fazer updade do contato {e.msg}') else: print(f'{cursor.rowcount} Registro(s) atualizado(s)')
def update_contato_by_id(contato): with get_connection() as connection: try: sql = 'UPDATE contatos SET nome = %s, tel = %s WHERE id = %s' cursor = connection.cursor() cursor.execute(sql, contato) connection.commit() except ProgrammingError as e: print(f'Erro ao atualizar contato por id {e.msg}') else: print(f'{cursor.rowcount} registro(s) atualidado(s).')
def delete_contato_by_id(id): with get_connection() as connection: try: sql = 'DELETE FROM contatos WHERE id = %s' cursor = connection.cursor() cursor.execute(sql, (id, )) connection.commit() except ProgrammingError as e: print(f'Erro ao deletar contato por id {e.msg}') else: print(f'{cursor.rowcount} registro(s) deletad(s).')
def include_grupos(): sql = 'INSERT INTO grupos (descricao) VALUES (%s)' params = (('Trabalho', ), ('Inglês', ), ('Python', )) with get_connection() as connection: try: cursor = connection.cursor() cursor.executemany(sql, params) connection.commit() except ProgrammingError as e: print(f'Erro ao inserir grupos: {e.msg}') else: print(f'{cursor.rowcount} Registros incluidos')
def include_contato(): sql = 'INSERT INTO contatos (nome, tel) VALUES (%s, %s)' params = ('Tiaozinho', '98987-7845') with get_connection() as connection: try: cursor = connection.cursor() cursor.execute(sql, params) connection.commit() except ProgrammingError as e: print(f'Erro ao inserir contatos: {e.msg}') else: print(f'Registro incluido, ID: {cursor.lastrowid}')
def alter_table(): query = """ ALTER TABLE contatos ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY """ tables = [] with get_connection() as connection: try: cursor = connection.cursor() cursor.execute(query) for table in enumerate(cursor): tables.append(table[1]) except ProgrammingError as e: print(f'Erro ao executar script ALTER - DDL. Error:{e.msg}')
def select_contatos(): contatos = [] with get_connection() as connection: try: sql = 'SELECT id, nome, tel FROM contatos ORDER BY nome' cursor = connection.cursor() cursor.execute(sql) for response in cursor.fetchall(): contatos.append(Contato(*response)) except ProgrammingError as e: print(f'Erro ao buscar contatos {e.msg}') for contato in contatos: print(contato)
def select_contato_by_name(name): contatos = [] with get_connection() as connection: try: sql = "SELECT id, nome, tel FROM contatos WHERE nome like %s " params = (f'%{name}%', ) cursor = connection.cursor() cursor.execute(sql, params) for response in cursor.fetchall(): contatos.append(Contato(*response)) except ProgrammingError as e: print(f'Erro ao buscar contato por nome {e.msg}') for contato in contatos: print(contato)
def show_tables(): query = """ SHOW TABLES """ tables = [] with get_connection() as connection: try: cursor = connection.cursor() cursor.execute(query) for table in enumerate(cursor): tables.append(table[1]) except ProgrammingError as e: print(f'Erro ao executar script DROP - DDL. Error:{e.msg}') print(tables)
def select_contatos_limit_offset(): contatos = [] with get_connection() as connection: try: sql = 'SELECT id, nome, tel FROM contatos LIMIT %s OFFSET %s' params = (3, 5) cursor = connection.cursor() cursor.execute(sql, params) for response in cursor.fetchall(): contatos.append(Contato(*response)) except ProgrammingError as e: print(f'Erro ao buscar contatos {e.msg}') for contato in contatos: print(contato)
def select_contato_by_id(id): contato = None with get_connection() as connection: try: sql = 'SELECT id, nome, tel FROM contatos WHERE id = %s' # sql = 'SELECT id, nome, tel FROM contatos WHERE id = {}'.format(id) # pode causar um sql injection # cursor.execute(sql) cursor = connection.cursor() cursor.execute(sql, (id, )) response = cursor.fetchone() contato = Contato(*response) except ProgrammingError as e: print(f'Erro ao buscar contato por id {e.msg}') print(contato)
def create_table(): table_contatos = """ CREATE TABLE IF NOT EXISTS contatos( nome VARCHAR(50), tel VARCHAR(40) ) """ table_emails = """ CREATE TABLE IF NOT EXISTS emails( id INT AUTO_INCREMENT PRIMARY KEY, dono VARCHAR(50) ) """ with get_connection() as connection: try: cursor = connection.cursor() cursor.execute(table_contatos) cursor.execute(table_emails) except ProgrammingError as e: print(f'Erro ao executar script de DDL. Error:{e.msg}')
def include_contato(): sql = 'INSERT INTO contatos (nome, tel) VALUES (%s, %s)' params = ( ('Jose', '9877-7845'), ('Ana', '9877-9045'), ('Castilho', '9877-7775'), ('Mario', '9877-7215'), ('Ze', '9877-3545'), ('João', '9877-7455'), ('Bia', '9877-78574'), ('Carlos', '9877-45415'), ) with get_connection() as connection: try: cursor = connection.cursor() cursor.executemany(sql, params) connection.commit() except ProgrammingError as e: print(f'Erro ao inserir contatos: {e.msg}') else: print(f'{cursor.rowcount} Registros incluidos')
def create_table(): table_grupos = """ CREATE TABLE IF NOT EXISTS grupos( id INT AUTO_INCREMENT PRIMARY KEY, descricao VARCHAR(50) ) """ alter_contato_add_column = """ ALTER TABLE contatos ADD COLUMN IF NOT EXISTS grupo_id INT """ alter_contato = """ ALTER TABLE contatos ADD FOREIGN KEY IF NOT EXISTS (grupo_id) REFERENCES grupos(id) """ with get_connection() as connection: try: cursor = connection.cursor() cursor.execute(table_grupos) cursor.execute(alter_contato_add_column) cursor.execute(alter_contato) except ProgrammingError as e: print(f'Erro ao executar script de DDL. Error:{e.msg}')
#!/usr/bin/python3 # coding: utf-8 from config_db import get_connection with get_connection() as connection: if connection.is_connected(): print('Conectado')