{"id":5588,"date":"2023-03-06T19:45:43","date_gmt":"2023-03-06T18:45:43","guid":{"rendered":"https:\/\/arduino.net.pl\/?p=5588"},"modified":"2023-08-06T12:21:24","modified_gmt":"2023-08-06T10:21:24","slug":"flask-i-sqlalchemy","status":"publish","type":"post","link":"https:\/\/arduino.net.pl\/index.php\/flask-i-sqlalchemy\/","title":{"rendered":"Flask i SQLAlchemy"},"content":{"rendered":"\n<figure class=\"wp-block-image is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/flask-sqlalchemy.palletsprojects.com\/en\/3.0.x\/_static\/flask-sqlalchemy-logo.png\" alt=\"https:\/\/flask-sqlalchemy.palletsprojects.com\/en\/3.0.x\/_static\/flask-sqlalchemy-logo.png\" width=\"170\" height=\"148\"\/><figcaption class=\"wp-element-caption\"><a href=\"https:\/\/flask-sqlalchemy.palletsprojects.com\/en\/3.0.x\/\" title=\"\">https:\/\/flask-sqlalchemy.palletsprojects.com\/en\/3.0.x\/<\/a><\/figcaption><\/figure>\n\n\n\n<p>Ka\u017cda kolumna w tabeli ma okre\u015blony typ danych, na przyk\u0142ad tekstowy, liczbowy lub datowy. Typ danych okre\u015bla, jakie rodzaje danych mo\u017cna przechowywa\u0107 w danej kolumnie.<\/p>\n\n\n\n<p>Bazy danych SQL sk\u0142adaj\u0105 si\u0119 z trzech podstawowych element\u00f3w:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Tabele &#8211; to struktury przechowuj\u0105ce dane w postaci wierszy i kolumn. Ka\u017cda tabela ma unikaln\u0105 nazw\u0119 i sk\u0142ada si\u0119 z jednego lub wielu kolumn.<\/li>\n\n\n\n<li>Kolumny &#8211; ka\u017cda kolumna ma swoj\u0105 nazw\u0119 oraz okre\u015blony typ danych, kt\u00f3ry okre\u015bla rodzaj danych, kt\u00f3re mo\u017cna przechowywa\u0107 w tej kolumnie.<\/li>\n\n\n\n<li>Wiersze &#8211; to pojedyncze rekordy w tabeli, kt\u00f3re sk\u0142adaj\u0105 si\u0119 z danych przechowywanych w ka\u017cdej z kolumn. Ka\u017cdy wiersz ma unikalny identyfikator (klucz g\u0142\u00f3wny), kt\u00f3ry s\u0142u\u017cy do identyfikowania go w bazie danych.<\/li>\n<\/ol>\n\n\n\n<p>Bazy danych SQL umo\u017cliwiaj\u0105 manipulowanie danymi za pomoc\u0105 j\u0119zyka SQL, kt\u00f3ry umo\u017cliwia wykonywanie operacji takich jak tworzenie, aktualizowanie i usuwanie danych z tabel.&#8221; Koniec cytatu z inteligencji;)<\/p>\n\n\n\n<p>Poni\u017cej bardzo dobre narz\u0119dzie do pracy z bazami danych SQLite:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>DB Browser for SQLite. \n<ul class=\"wp-block-list\">\n<li><em><a href=\"https:\/\/sqlitebrowser.org\/dl\/\" title=\"\">The Official home of the DB Browser for SQLite<\/a><\/em><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">KOD<\/h4>\n\n\n\n<p>Poni\u017cej kod, kt\u00f3ry pokazuje istot\u0119 dzia\u0142ania biblioteki SQLAlchemy. Tworzymy baz\u0119, kreujemy klas\u0119, kt\u00f3ra buduje struktur\u0119 bazy. Serwer dla czujnik\u00f3w na ESP8266 albo ESP32<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; light: false; title: Kod:; toolbar: true; notranslate\" title=\"Kod:\">\nfrom flask import Flask, request, jsonify\nfrom flask_sqlalchemy import SQLAlchemy\nfrom datetime import datetime\nimport json\n\napp = Flask(__name__)\n\n# Konfiguracja bazy danych\napp.config&#x5B;&#039;SQLALCHEMY_DATABASE_URI&#039;] = &#039;sqlite:\/\/\/iot_data.db&#039;  # U\u017cyj SQLite jako przyk\u0142adowej bazy danych\ndb = SQLAlchemy(app)\n\n# Model danych\nclass SensorData(db.Model):\n    id = db.Column(db.Integer, primary_key=True)\n    device_name = db.Column(db.String(50), nullable=False)\n    data = db.Column(db.String(200), nullable=False)\n    timestamp = db.Column(db.DateTime, default=datetime.utcnow)\n\n# Endpoint do dodawania danych pomiarowych\n@app.route(&#039;\/add_data&#039;, methods=&#x5B;&#039;POST&#039;])\ndef add_data():\n    data = request.json\n    device_name = data.get(&#039;device_name&#039;)\n    data_json = json.dumps(data.get(&#039;data&#039;))  # Konwersja s\u0142ownika na JSON\n\n    new_data = SensorData(device_name=device_name, data=data_json)\n\n    db.session.add(new_data)\n    db.session.commit()\n\n    return jsonify({&#039;message&#039;: &#039;Dane pomiarowe dodane poprawnie.&#039;}), 201\n\n@app.route(&#039;\/get_data&#039;, methods=&#x5B;&#039;GET&#039;])\ndef get_data():\n    all_data = SensorData.query.all()\n    data_list = &#x5B;]\n    for data in all_data:\n        data_list.append({&#039;device_name&#039;: data.device_name, &#039;data&#039;: data.data, &#039;timestamp&#039;: data.timestamp})\n    \n    return jsonify(data_list)\n\nif __name__ == &#039;__main__&#039;:\n    with app.app_context():\n        db.create_all()  # Tworzenie tabeli w bazie danych, je\u015bli nie istnieje\n    app.run(debug=True)\n\n\n\n<\/pre><\/div>\n\n\n<h4 class=\"wp-block-heading\">Typy danych<\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><a href=\"https:\/\/docs.sqlalchemy.org\/en\/14\/core\/type_basics.html#sqlalchemy.types.Integer\"><code>Integer<\/code><\/a><\/td><td>an integer<\/td><\/tr><tr><td><a href=\"https:\/\/docs.sqlalchemy.org\/en\/14\/core\/type_basics.html#sqlalchemy.types.String\"><code>String(size)<\/code><\/a><\/td><td>a string with a maximum length (optional in some databases, e.g. PostgreSQL)<\/td><\/tr><tr><td><a href=\"https:\/\/docs.sqlalchemy.org\/en\/14\/core\/type_basics.html#sqlalchemy.types.Text\"><code>Text<\/code><\/a><\/td><td>some longer unicode text<\/td><\/tr><tr><td><a href=\"https:\/\/docs.sqlalchemy.org\/en\/14\/core\/type_basics.html#sqlalchemy.types.DateTime\"><code>DateTime<\/code><\/a><\/td><td>date and time expressed as Python <a href=\"https:\/\/docs.python.org\/3\/library\/datetime.html#datetime.datetime\"><code>datetime<\/code><\/a> object.<\/td><\/tr><tr><td><a href=\"https:\/\/docs.sqlalchemy.org\/en\/14\/core\/type_basics.html#sqlalchemy.types.Float\"><code>Float<\/code><\/a><\/td><td>stores floating point values<\/td><\/tr><tr><td><a href=\"https:\/\/docs.sqlalchemy.org\/en\/14\/core\/type_basics.html#sqlalchemy.types.Boolean\"><code>Boolean<\/code><\/a><\/td><td>stores a boolean value<\/td><\/tr><tr><td><a href=\"https:\/\/docs.sqlalchemy.org\/en\/14\/core\/type_basics.html#sqlalchemy.types.PickleType\"><code>PickleType<\/code><\/a><\/td><td>stores a pickled Python object<\/td><\/tr><tr><td><a href=\"https:\/\/docs.sqlalchemy.org\/en\/14\/core\/type_basics.html#sqlalchemy.types.LargeBinary\"><code>LargeBinary<\/code><\/a><\/td><td>stores large arbitrary binary data<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Ka\u017cda kolumna w tabeli ma okre\u015blony typ danych, na przyk\u0142ad tekstowy, liczbowy lub datowy. Typ danych okre\u015bla, jakie rodzaje danych mo\u017cna przechowywa\u0107 w danej kolumnie. Bazy danych SQL sk\u0142adaj\u0105 si\u0119&#8230;<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[141,65],"tags":[154,159],"class_list":["post-5588","post","type-post","status-publish","format-standard","hentry","category-micropython","category-programowanie","tag-flask","tag-sqlalchemy"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/arduino.net.pl\/index.php\/wp-json\/wp\/v2\/posts\/5588","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/arduino.net.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/arduino.net.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/arduino.net.pl\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/arduino.net.pl\/index.php\/wp-json\/wp\/v2\/comments?post=5588"}],"version-history":[{"count":11,"href":"https:\/\/arduino.net.pl\/index.php\/wp-json\/wp\/v2\/posts\/5588\/revisions"}],"predecessor-version":[{"id":5807,"href":"https:\/\/arduino.net.pl\/index.php\/wp-json\/wp\/v2\/posts\/5588\/revisions\/5807"}],"wp:attachment":[{"href":"https:\/\/arduino.net.pl\/index.php\/wp-json\/wp\/v2\/media?parent=5588"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/arduino.net.pl\/index.php\/wp-json\/wp\/v2\/categories?post=5588"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/arduino.net.pl\/index.php\/wp-json\/wp\/v2\/tags?post=5588"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}