-
Notifications
You must be signed in to change notification settings - Fork 0
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Sweep: Switch from SQLite to MySQL #4
Comments
🚀 Here's the PR! #6See Sweep's progress at the progress dashboard! ⚡ Sweep Basic Tier: I'm using GPT-4. You have 4 GPT-4 tickets left for the month and 2 for the day. (tracking ID:
e19267cb86 )For more GPT-4 tickets, visit our payment portal. For a one week free trial, try Sweep Pro (unlimited GPT-4 tickets). Tip I can email you next time I complete a pull request if you set up your email here! Actions (click)
Sandbox Execution ✓Here are the sandbox execution logs prior to making any changes: Sandbox logs for
|
@app.on_event("startup") | |
async def startup(): | |
app.db_connection = await aiosqlite.connect("db.db") | |
@app.on_event("shutdown") | |
async def shutdown(): |
Lines 54 to 59 in 5c16831
async def get_uptime(peer_id): | |
async with app.db_connection.execute("SELECT * FROM peers WHERE id=?", (peer_id,)) as cursor: | |
session_start = await cursor.fetchone() | |
if session_start is None: | |
return 0 | |
session_start = session_start[5] |
Step 2: ⌨️ Coding
Modify api.py with contents:
• Replace the aiosqlite connection setup with aiomysql's pool creation method. This will involve importing the aiomysql library at the top of the file, and then replacing the `aiosqlite.connect` call with `aiomysql.create_pool`. The pool creation method requires the database name, host, user, and password as parameters, so these will need to be provided. The pool should be stored in `app.db_pool` instead of `app.db_connection`.
• In the `shutdown` function, replace the `app.db_connection.close` call with a call to `app.db_pool.close`, followed by `await app.db_pool.wait_closed()`.--- +++ @@ -1,16 +1,17 @@ from fastapi import FastAPI, Query -import aiosqlite +import aiomysql import uvicorn, time app = FastAPI() @app.on_event("startup") async def startup(): - app.db_connection = await aiosqlite.connect("db.db") + app.db_pool = await aiomysql.create_pool(host='localhost', port=3306, user='your_user', password='your_password', db='your_db') @app.on_event("shutdown") async def shutdown(): - await app.db_connection.close() + await app.db_pool.close() + await app.db_pool.wait_closed() def jsonify_peer(peer): return {
- Running GitHub Actions for
api.py
✓ Edit
Check api.py with contents:Ran GitHub Actions for 81b9f9e4d234c54eed2871bab36544268de0eb50:
Modify api.py with contents:
• Replace the aiosqlite query execution with aiomysql's methods. This will involve getting a connection from the pool with `conn = await app.db_pool.acquire()`, and then getting a cursor with `cursor = await conn.cursor()`.
• The query execution should be replaced with `await cursor.execute("SELECT * FROM peers WHERE id=%s", (peer_id,))`. Note that MySQL uses `%s` as the placeholder instead of `?`.
• The `fetchone` call should be replaced with `await cursor.fetchone()`.
• After the query execution and fetching the results, the connection should be released back to the pool with `app.db_pool.release(conn)`.--- +++ @@ -1,174 +1,205 @@ -from fastapi import FastAPI, Query -import aiosqlite -import uvicorn, time - -app = FastAPI() - -@app.on_event("startup") -async def startup(): - app.db_connection = await aiosqlite.connect("db.db") - -@app.on_event("shutdown") -async def shutdown(): - await app.db_connection.close() - -def jsonify_peer(peer): - return { - "id": peer[0], - "ip": peer[1], - "port": peer[2], - "online": peer[3], - "last_seen": peer[4], - "session_start": peer[5], - "last_check": peer[6], - "version": peer[7], - "sub_version": peer[8], - } - -async def jsonify_peers(peers, pages): - result = [] - for peer in peers: - result.append(jsonify_peer(peer)) - result[-1]["uptime"] = await get_uptime(peer[0]) - return result, {"pages": int(pages)} - -def jsonify_peer_history(peer_history, pages): - res = [] - for history in peer_history: - res.append({ - "id": history[0], - "peer_id": history[1], - "online": history[2], - "timestamp": history[3], - }) - return res, {"pages": int(pages)} - -def jsonify_versions_history(versions_history, pages): - return { - "id": versions_history[0], - "version": versions_history[1], - "sub_version": versions_history[2], - "timestamp": versions_history[3], - }, {"pages": int(pages)} - -async def get_uptime(peer_id): - async with app.db_connection.execute("SELECT * FROM peers WHERE id=?", (peer_id,)) as cursor: - session_start = await cursor.fetchone() - if session_start is None: - return 0 - session_start = session_start[5] - return int(time.time()) - session_start - -def show_days(seconds): - return int(seconds / 86400) - -@app.get("/peers") -async def get_peers(page: int = Query(0, alias="page")): - """Get a list of peers. (50 per page) - - Args: - page (int): The page number.""" - async with app.db_connection.execute("SELECT * FROM peers ORDER BY online DESC, last_seen DESC LIMIT 50 OFFSET ?", (page * 50,)) as cursor: - total_pages = await app.db_connection.execute("SELECT COUNT(*) FROM peers") - total_pages = await total_pages.fetchone() - total_pages = total_pages[0] / 50 - res = await jsonify_peers(await cursor.fetchall(), total_pages) - return res - -@app.get("/peer/get/{ip}/{port}") -async def get_peer(ip: str, port: int): - """Get a peer by ip and port. - - Args: - ip (str): The ip address of the peer. - port (int): The port of the peer.""" - async with app.db_connection.execute("SELECT * FROM peers WHERE ip=? AND port=?", (ip, port)) as cursor: - return jsonify_peer(await cursor.fetchone()) - -@app.get("/peer/get/{peer_id}") -async def get_peer_by_id(peer_id: int): - """Get a peer by id. - - Args: - peer_id (int): The id of the peer.""" - async with app.db_connection.execute("SELECT * FROM peers WHERE id=?", (peer_id,)) as cursor: - peer = await cursor.fetchone() - if peer is None: - return {"error": "Peer not found."} - return { - "peer": jsonify_peer(peer), - "uptime": await get_uptime(peer_id), - } - -@app.get("/peer/{peer_id}/history") -async def get_peer_history(peer_id: int, page: int = Query(0, alias="page")): - """Get a peer's history. (50 per page) - - Args: - peer_id (int): The id of the peer. - page (int): The page number.""" - async with app.db_connection.execute("SELECT * FROM peer_history WHERE peer_id=? ORDER BY timestamp ASC LIMIT 50 OFFSET ?", (peer_id, page * 50)) as cursor: - total_pages = await app.db_connection.execute("SELECT COUNT(*) FROM peer_history WHERE peer_id=?", (peer_id,)) - total_pages = await total_pages.fetchone() - total_pages = total_pages[0] / 50 - return jsonify_peer_history(await cursor.fetchall(), total_pages) - -@app.get("/peer/{peer_id}/uptime") -async def get_peer_uptime(peer_id: int): - """Get a peer's uptime - Eg: 1 day, 2 hours, 3 minutes, 4 seconds - - Args: - peer_id (int): The id of the peer.""" - days = show_days(await get_uptime(peer_id)) - return f"{days} days, {time.strftime('%H hours, %M minutes, %S seconds', time.gmtime(await get_uptime(peer_id)))}" - -@app.get("/versions/history") -async def get_versions_history(page: int = Query(0, alias="page")): - """Get the versions history. (50 per page) - - Args: - page (int): The page number.""" - async with app.db_connection.execute("SELECT * FROM versions_history LIMIT 50 OFFSET ?", (page * 50,)) as cursor: - total_pages = await app.db_connection.execute("SELECT COUNT(*) FROM versions_history") - total_pages = await total_pages.fetchone() - total_pages = total_pages[0] / 50 - return jsonify_versions_history(await cursor.fetchall(), total_pages) - -@app.get("/nodes/online") -async def get_nodes_online(): - """Get the number of nodes online.""" - async with app.db_connection.execute("SELECT COUNT(*) FROM peers WHERE online=?", (1,)) as cursor: - count = await cursor.fetchone() - return {"count": count[0]} - -@app.get("/peer/{peer_id}/uptime/percentage/{amount}/{period_unit}") -async def get_peer_uptime_percentage(peer_id: int, amount: int, period_unit: str): - """Get a peer's uptime percentage. - - Args: - peer_id (int): The id of the peer. - amount (int): The amount of the period. - period_unit (str): The unit of the period. (hour, day, week, month, year)""" - if period_unit == "hour": - period = 3600 - elif period_unit == "day": - period = 86400 - elif period_unit == "week": - period = 604800 - elif period_unit == "month": - period = 2629743 - elif period_unit == "year": - period = 31556926 - else: - return {"error": "Invalid period unit."} - async with app.db_connection.execute("SELECT COUNT(*) FROM peer_history WHERE peer_id=? AND online=1 AND timestamp > ?", (peer_id, int(time.time()) - amount * period)) as cursor: - count_online = await cursor.fetchone() - async with app.db_connection.execute("SELECT COUNT(*) FROM peer_history WHERE peer_id=? AND timestamp > ?", (peer_id, int(time.time()) - amount * period)) as cursor: - count_total = await cursor.fetchone() - return {"percentage": count_online[0] / count_total[0] * 100} - - -if __name__ == "__main__": - import uvicorn - uvicorn.run(app) +from fastapi import FastAPI, Query +import aiomysql +import uvicorn, time + +app = FastAPI() + +@app.on_event("startup") +async def startup(): + app.db_pool = await aiomysql.create_pool(host='localhost', port=3306, user='your_user', password='your_password', db='your_db') + +@app.on_event("shutdown") +async def shutdown(): + await app.db_pool.close() + await app.db_pool.wait_closed() + +def jsonify_peer(peer): + return { + "id": peer[0], + "ip": peer[1], + "port": peer[2], + "online": peer[3], + "last_seen": peer[4], + "session_start": peer[5], + "last_check": peer[6], + "version": peer[7], + "sub_version": peer[8], + } + +async def jsonify_peers(peers, pages): + result = [] + for peer in peers: + result.append(jsonify_peer(peer)) + result[-1]["uptime"] = await get_uptime(peer[0]) + return result, {"pages": int(pages)} + +def jsonify_peer_history(peer_history, pages): + res = [] + for history in peer_history: + res.append({ + "id": history[0], + "peer_id": history[1], + "online": history[2], + "timestamp": history[3], + }) + return res, {"pages": int(pages)} + +def jsonify_versions_history(versions_history, pages): + return { + "id": versions_history[0], + "version": versions_history[1], + "sub_version": versions_history[2], + "timestamp": versions_history[3], + }, {"pages": int(pages)} + +async def get_uptime(peer_id): + conn = await app.db_pool.acquire() + cursor = await conn.cursor() + await cursor.execute("SELECT * FROM peers WHERE id=%s", (peer_id,)) + session_start = await cursor.fetchone() + app.db_pool.release(conn) + if session_start is None: + return 0 + session_start = session_start[5] + return int(time.time()) - session_start + +def show_days(seconds): + return int(seconds / 86400) + +@app.get("/peers") +async def get_peers(page: int = Query(0, alias="page")): + """Get a list of peers. (50 per page) + + Args: + page (int): The page number.""" + conn = await app.db_pool.acquire() + cursor = await conn.cursor() + await cursor.execute("SELECT * FROM peers ORDER BY online DESC, last_seen DESC LIMIT 50 OFFSET %s", (page * 50,)) + total_pages = await cursor.execute("SELECT COUNT(*) FROM peers") + total_pages = await total_pages.fetchone() + total_pages = total_pages[0] / 50 + res = await jsonify_peers(await cursor.fetchall(), total_pages) + app.db_pool.release(conn) + return res + +@app.get("/peer/get/{ip}/{port}") +async def get_peer(ip: str, port: int): + """Get a peer by ip and port. + + Args: + ip (str): The ip address of the peer. + port (int): The port of the peer.""" + conn = await app.db_pool.acquire() + cursor = await conn.cursor() + await cursor.execute("SELECT * FROM peers WHERE ip=%s AND port=%s", (ip, port)) + result = jsonify_peer(await cursor.fetchone()) + app.db_pool.release(conn) + return result + +@app.get("/peer/get/{peer_id}") +async def get_peer_by_id(peer_id: int): + """Get a peer by id. + + Args: + peer_id (int): The id of the peer.""" + conn = await app.db_pool.acquire() + cursor = await conn.cursor() + await cursor.execute("SELECT * FROM peers WHERE id=%s", (peer_id,)) + peer = await cursor.fetchone() + app.db_pool.release(conn) + if peer is None: + return {"error": "Peer not found."} + return { + "peer": jsonify_peer(peer), + "uptime": await get_uptime(peer_id), + } + +@app.get("/peer/{peer_id}/history") +async def get_peer_history(peer_id: int, page: int = Query(0, alias="page")): + """Get a peer's history. (50 per page) + + Args: + peer_id (int): The id of the peer. + page (int): The page number.""" + conn = await app.db_pool.acquire() + cursor = await conn.cursor() + await cursor.execute("SELECT * FROM peer_history WHERE peer_id=%s ORDER BY timestamp ASC LIMIT 50 OFFSET %s", (peer_id, page * 50)) + total_pages = await cursor.execute("SELECT COUNT(*) FROM peer_history WHERE peer_id=%s", (peer_id,)) + total_pages = await total_pages.fetchone() + total_pages = total_pages[0] / 50 + result = jsonify_peer_history(await cursor.fetchall(), total_pages) + app.db_pool.release(conn) + return result + +@app.get("/peer/{peer_id}/uptime") +async def get_peer_uptime(peer_id: int): + """Get a peer's uptime + Eg: 1 day, 2 hours, 3 minutes, 4 seconds + + Args: + peer_id (int): The id of the peer.""" + days = show_days(await get_uptime(peer_id)) + return f"{days} days, {time.strftime('%H hours, %M minutes, %S seconds', time.gmtime(await get_uptime(peer_id)))}" + +@app.get("/versions/history") +async def get_versions_history(page: int = Query(0, alias="page")): + """Get the versions history. (50 per page) + + Args: + page (int): The page number.""" + conn = await app.db_pool.acquire() + cursor = await conn.cursor() + await cursor.execute("SELECT * FROM versions_history LIMIT 50 OFFSET %s", (page * 50,)) + total_pages = await cursor.execute("SELECT COUNT(*) FROM versions_history") + total_pages = await total_pages.fetchone() + total_pages = total_pages[0] / 50 + result = jsonify_versions_history(await cursor.fetchall(), total_pages) + app.db_pool.release(conn) + return result + +@app.get("/nodes/online") +async def get_nodes_online(): + """Get the number of nodes online.""" + conn = await app.db_pool.acquire() + cursor = await conn.cursor() + await cursor.execute("SELECT COUNT(*) FROM peers WHERE online=%s", (1,)) + count = await cursor.fetchone() + app.db_pool.release(conn) + return {"count": count[0]} + +@app.get("/peer/{peer_id}/uptime/percentage/{amount}/{period_unit}") +async def get_peer_uptime_percentage(peer_id: int, amount: int, period_unit: str): + """Get a peer's uptime percentage. + + Args: + peer_id (int): The id of the peer. + amount (int): The amount of the period. + period_unit (str): The unit of the period. (hour, day, week, month, year)""" + if period_unit == "hour": + period = 3600 + elif period_unit == "day": + period = 86400 + elif period_unit == "week": + period = 604800 + elif period_unit == "month": + period = 2629743 + elif period_unit == "year": + period = 31556926 + else: + return {"error": "Invalid period unit."} + conn = await app.db_pool.acquire() + cursor = await conn.cursor() + await cursor.execute("SELECT COUNT(*) FROM peer_history WHERE peer_id=%s AND online=1 AND timestamp > %s", (peer_id, int(time.time()) - amount * period)) + app.db_pool.release(conn) + count_online = await cursor.fetchone() + conn = await app.db_pool.acquire() + cursor = await conn.cursor() + await cursor.execute("SELECT COUNT(*) FROM peer_history WHERE peer_id=%s AND timestamp > %s", (peer_id, int(time.time()) - amount * period)) + count_total = await cursor.fetchone() + app.db_pool.release(conn) + return {"percentage": count_online[0] / count_total[0] * 100} + + +if __name__ == "__main__": + import uvicorn + uvicorn.run(app)
- Running GitHub Actions for
api.py
✓ Edit
Check api.py with contents:Ran GitHub Actions for 159a27f5026422a627527eded963692f8b3d101a:
Step 3: 🔁 Code Review
I have finished reviewing the code for completeness. I did not find errors for sweep/switch_from_sqlite_to_mysql
.
🎉 Latest improvements to Sweep:
- We just released a dashboard to track Sweep's progress on your issue in real-time, showing every stage of the process – from search to planning and coding.
- Sweep uses OpenAI's latest Assistant API to plan code changes and modify code! This is 3x faster and significantly more reliable as it allows Sweep to edit code and validate the changes in tight iterations, the same way as a human would.
- Try using the GitHub issues extension to create Sweep issues directly from your editor! GitHub Issues and Pull Requests.
💡 To recreate the pull request edit the issue title or description. To tweak the pull request, leave a comment on the pull request.
Join Our Discord
Details
In all files, instead of sqlite, change ti mysql and for th api use an async library for mysql. make sure to change everything needed to be changed
Checklist
api.py
✓ 81b9f9e Editapi.py
✓ Editapi.py
✓ 159a27f Editapi.py
✓ EditThe text was updated successfully, but these errors were encountered: