def readSQLScriptFromFile(scriptPath: str, cur: db.Cursor) -> []:
result = []
with open(scriptPath, 'r') as inp:
i = 0
sqlQuery = ''
for line in inp:
if line == 'GO\n':
i = i + 1
print(f'Query #{i}')
# print(sqlQuery)
cur.execute(sqlQuery)
try:
result.append(
pd.DataFrame.from_records(
cur.fetchall(),
columns=[x[0] for x in cur.description]))
except:
print('no results')
# print('****')
sqlQuery = ''
elif 'PRINT' in line:
disp = line.split("'")[1]
print(f'{disp}')
else:
sqlQuery = sqlQuery + line
inp.close()
return (result)
def get_chores_helper(cursor: pyodbc.Cursor, account_id: str) -> dict:
"""Helper function for GetChoresParent to get the chores given a parent account id."""
try:
cursor.execute(queries.get_chores_by_parent(), account_id)
except Exception as exc:
abort(500, str(exc))
return GetChoresResponseModel(cursor.fetchall()).get_response()
def select_movie_with_like(cursor: pyodbc.Cursor, slug: str):
query = "SELECT * FROM [dbo].[testTable] WHERE title like ?;"
cursor.execute(query, (f"%{slug}%"))
movies = cursor.fetchall()
for movie in movies:
print(movie)
def select_all_movie_year(cursor: pyodbc.Cursor, year: int):
# Crear consulta
query = "SELECT * FROM [dbo].[testTable] WHERE years > ?;"
# Hacer la peticion
cursor.execute(query, (year))
# Traemos todos los datos
movies = cursor.fetchall()
# imprimir los datos
for movie in movies:
print(movie)
def display_users(db_cursor: pyodbc.Cursor):
''' Displays users data. '''
db_cursor.execute('SELECT * FROM users')
transferred_data = db_cursor.fetchall()
template = '{:<5} {:<20} {:<10}'
print(template.format('ID', 'NAME', 'CITY'))
print('-' * 32)
for row in transferred_data:
print(template.format(row.id, row.name, row.city))
def transfer_data(source_db_cursor: pyodbc.Cursor, dest_db_cursor: pyodbc.Cursor, dest_db_conn: pyodbc.Connection):
''' Extracts users data from source database and stores them in destination database. '''
print('Extracting users data from source database.')
source_db_cursor.execute('SELECT * FROM users')
rows = source_db_cursor.fetchall()
print('Transferring users data to destination database.')
for row in rows:
dest_db_cursor.execute(SQL_INSERT_DATA, (row.id, row.name, row.city))
print(f'{len(rows)} rows transferred\n')
dest_db_conn.commit()
def save_cursor_result_to_df(cursor: pyodbc.Cursor) -> pd.DataFrame:
"""Save the executed query cursor to a pandas DataFrame
:param cursor: query cursor
:type cursor: pyodbc.Cursor
:return: result Pandas DataFrame
:rtype: pd.DataFrame
"""
descriptions = cursor.description
col_names = [desc[0] for desc in descriptions]
df = pd.DataFrame.from_records(cursor.fetchall(), columns=col_names)
LOG.debug('saved cursor to DataFrame')
return df
def select_all_movies(cursor: pyodbc.Cursor):
cursor.execute("SELECT * FROM [dbo].[testTable];")
movies = cursor.fetchall()
for movie in movies:
print(movie)
def fetch_data(cur: pyodbc.Cursor) -> List:
''' Fetch all data from the table. '''
print('List of data.')
cur.execute('SELECT * FROM users')
return cur.fetchall()