Getting started - Mayim and SQlite

[seanPH]

Hi all, – running debian —
hoping can get some guidance on this… I am new to Sanic, but very impressed so far ! Very easy to get it installed and configured and running… Any guidance would be VERY greatly appreciated !

So I thought I would install Sqlite (done pip install aiosqlite aiologger ) and just insert some rows , just to make sure that I can do that . The idea is - use mayim (installed via pip install sanic-ext) - and store the sql statements in separate files. …

I tried to follow the doc, and other examples, but have few problems with my structure:

from sanic import Sanic
from sanic.log import logger
from sanic.response import text
from mayim import Mayim, SQLiteExecutor, sql

app = Sanic("erg_back")

# Initialize the database
Mayim(db_path="./user_Agent_log.db")
db = Mayim.db

# Create the table
with db as conn:
    conn.execute(sql("create_table.sql"))

@app.route("/")
async def index(request):
    # Get the client agent details
    ip_address = request.remote_addr
    user_agent = request.headers.get("User-Agent")

    # Insert the client agent details into the database
    with db as conn:
        conn.execute(sql("insert_data.sql"), (ip_address, user_agent))

    # Return the response
    return text("Hello, world!")

if __name__ == "__main__":
    app.run(host="0.0.0.0", port=8000) 

[ahopkins]

Try this…

-- ./queries/insert_data.sql
INSERT INTO user_agent_log (ip_address, user_agent)
VALUES ($ip_address, $user_agent)

-- ./queries/mayim_create_table.sql
CREATE TABLE IF NOT EXISTS user_agent_log(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ip_address VARCHAR,
    user_agent VARCHAR
);

-- ./queries/select_results.sql
SELECT *
FROM user_agent_log;

# ./server.py
from typing import Any
from sanic import Sanic, Request
from sanic.response import text, json
from mayim import Mayim, SQLiteExecutor
from mayim.sql.sqlite.interface import SQLitePool

app = Sanic("erg_back")


class UserAgentExecutor(SQLiteExecutor):
    async def create_table(self) -> None:
        ...

    async def insert_data(self, ip_address: str, user_agent: str) -> None:
        ...

    async def select_results(self) -> list[dict[str, Any]]:
        ...


@app.before_server_start
async def setup_mayim(app: Sanic):
    executor = UserAgentExecutor()
    app.ctx.pool = SQLitePool("./user_Agent_log.db")
    await app.ctx.pool.open()
    Mayim(executors=[executor], pool=app.ctx.pool)
    app.ext.dependency(executor)

    # Make sure the table exists
    await executor.create_table()


@app.after_server_stop
async def shutdown_mayim(app: Sanic):
    await app.ctx.pool.close()


@app.route("/")
async def index(request: Request, executor: UserAgentExecutor):
    # Get the client agent details
    ip_address = request.remote_addr
    user_agent = request.headers.get("User-Agent", "unknown")

    await executor.insert_data(ip_address=ip_address, user_agent=user_agent)

    # Return the response
    return text("Hello, world!")


@app.route("/results")
async def results(request: Request, executor: UserAgentExecutor):
    return json(await executor.select_results())


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=8000, auto_reload=True)

[seanPH]

That is so AWESOME ! Thank you !

Always easier to learn by (working) examples

You even anticipated my next question
Q. how to view the stored data?
A: /results see above, already answered … hahaha A+

[seanPH]

2 quick followups

1. I notice u stored the sql in ./queries , which is not specified anywhere in server.py. So I am assuming it is a mayim default ? and/or is configured in a mayim conf file or something like that ??
2. Am I trying to get the ip address the wrong way ?

async def index(request: Request ):
    # Get the client agent details
    ip_address = request.remote_addr
    return text("Hello, world!" + ip_address)

Gives me nothing in ip_address even from local LAN ?

I think I found answer about ip-address here : Proxy configuration | Sanic Framework
I am using a reverse-proxy… But I cannot see how ? and where ? to set all these ENV variables ??

I also like to change some colors in the sanic console because I cannot read some text, example this line: “Process ack: Sanic-Server-0-0 [824]” the “Process ack:” is too dark to see with dark background, and the “Sanic-Server-0-0 [824]” has text same color as the reverse-video color, so impossible to read it unless i cut/paste into editor.

[ahopkins]

@seanPH

So I am assuming it is a mayim default ? and/or is configured in a mayim conf file or something like that ??

Yes. It is a Mayim preference. You can of course configure it as needed.

@seanPH

Am I trying to get the ip address the wrong way ?

Nope. That is correct. Almost always you want request.remote_addr.

@seanPH

But I cannot see how ? and where ? to set all these ENV variables ??

Have you seen this? You have a number of ways to set them to suit your needs.

@seanPH

I also like to change some colors in the sanic console because I cannot read some text

Can you send me a screenshot? Sanic mainly just uses whatever colors you have set on your terminal though. The only exception is the pink branding color. But, that generally looks okay on both light and dark.

---

I approve of your generous usage of emojis.

[seanPH]

Thanks again for your reply, and I will work my way through those links.

About the screen colors:

This from my Screen session, when I rejoin it :

[seanPH]

This when I start it manually in my ssh session:

[ahopkins]

Oh my… blue on blue.

[seanPH]

Yeah - exactly ! And the reverse-video, blue on blue is even worse.

BTW - I have no idea why those 2 are different. The Screen (that I reconnect to ) is auto-started at boot by cron. I was surprised first-time I opened it and saw the bash colors are different to my usual profile (which has the red “Build Fast, Run Fast” logo). That said, my usual-profile is only marginally better, as you can see.

p.s. thanks again for being so responsive !

[seanPH]

Hi Adam,
Thanks again for your earlier help, I got the real IP address showing up now and my config setting loading too…

Now am I am trying to get a favicon.ico showing. So I added like this

app = Sanic("erg_back")
app.update_config("./first_config.py")
app.static("/", "/static")

and then I put the favicon.ico in that static directory… but nothing shows up.
I mean when I do a https://<mydomain>/favicon.ico it shows a nice 404 message.

I feel that I am close . Before I added about the static there was a console message at Sanic start about missing favicon - now that is gone… but favicon is not displaying in the browser.

Some guidance please

[seanPH]

just realized my error — above I left out the “.” … should be ./static

[ahopkins]

glad you solved it

[seanPH]

Hi Adam,

about the SQL INSERT … what I like to do is get the feedback from SQLite each time INSERT is run so I can be sure the INSERT did run successful ( or see whatever is the error) . In the code above that you supplied - i just added :

result = await executor,insert_data(....................
print(result)

But it does not show any return code about the INSERT ? It always just print “null”?
How is that usually achieved ?

[ahopkins]

If you have an insert query that returns something…

... --sql here
RETURNING inserted_id;

Then you could have a return on the method:

    async def insert_data(self) -> int:
        ...

Then your call gets a return from the insert query.

Is this what you meant? Or, you mean that you want to see the query that was actually run?

[seanPH]

Ok I did manage to get the SQL INSERT working successfully…

BUT - with with no logging, no tracing to help … was so very difficult… and doing it by guess work and first principles like that, is not sustainable way going forward.

A few things - please comment on these -

1. My debian latest sqlite is 3.34 , the new syntax “RETURNING” starts in 3.35 , I hope mayim supports it whenever my debian finally gets it …
2. It seems like mayim has a requirement - that the filename must not begin with uppercase letters. Otherwise identical AR_insert.sql does not work, but insert_data_AR.sql - does work.
3. my decorator — please help with this — if I add this decorator, my function stops working… remove the decorator, the function works again. Ideally, I want them both to work, obviously . The decorator itself works ( ie shows me the params in the console ) .

Decorator:

def print_args(func):
    async def wrapper(*args, **kwargs):
        print(f"Calling {func.__name__} with args: {args}, kwargs: {kwargs}")
        return await func(*args, **kwargs)
    return wrapper

Function:

    async def insert_data_AR(self, 
        ip_address: str, 
        user_agent: str,
        AR_status_code: int,
        AR_request_id: str,
        AR_user_id: str,
        AR_email_id: str,
        AR_email: str,
        AR_email_verified: bool,
        AR_created_at: str,
        AR_status: str,
        AR_reset_sessions: bool,
        AR_raw_json: str
        ) -> None:
        ...