[{"_1":2,"_1104":-5,"_1105":-5},"loaderData",{"_3":4,"_22":23},"root",{"_5":6},"current",{"_7":8,"_9":10,"_21":8},"development",false,"env",{"_11":12,"_13":14,"_15":16,"_17":18,"_19":20},"COMMIT_SHA","032c64b08f7dd5f7f1f6f3923a1c6d1445f4fb68","IMAGE_CDN","https://planetscale-images.imgix.net","IMAGE_CDN_ENABLED","true","INTERNAL_API","https://api.planetscale.com","SENTRY_DSN","https://bd81903b44804e22a06bdc0c1a91b303@o499952.ingest.us.sentry.io/4504531942572032","userSignedIn","routes/blog.author.$slug",{"_24":25,"_30":31,"_1094":1095,"_3":1103},"author",{"_26":27,"_28":29},"slug","anthony","name","Anthony Herbert","blogs",[32],{"_26":33,"_34":35,"_1035":1036,"_1037":-7,"_1038":1039,"_1044":1075,"_1076":1077,"_1080":1081,"_1083":1084,"_1085":1086,"_1087":1088},"using-mysql-with-sql-alchemy-hands-on-examples","body",[36,54,59,84,97,109,119,124,155,175,197,208,213,218,227,239,257,262,267,295,300,311,316,322,339,363,368,373,384,389,400,405,416,421,437,448,453,458,463,468,474,488,499,504,509,514,519,542,553,569,574,579,590,595,628,633,644,660,671,682,699,713,718,730,746,769,774,779,791,802,817,822,848,863,868,873,878,883,899,920,935,940,945,955,960,965,970,981,986,997,1014,1019,1030],["SingleFetchClassInstance",37],{"_38":39,"_28":40,"_41":42,"_43":44},"$$mdtype","Tag","p","attributes",{},"children",[45,53],["SingleFetchClassInstance",46],{"_38":39,"_28":47,"_41":48,"_43":51},"a",{"_49":50},"href","https://www.sqlalchemy.org/",[52],"SQLAlchemy"," is a popular Python library that gives you many tools to interact with SQL databases. With SQLAlchemy, you can do things like send raw queries to a database, programmatically construct SQL statements, and even map Python classes to database tables with the object-relational mapper (ORM). SQLAlchemy doesn't force you to use any particular features, so it has the flexibility to support many different approaches to working with databases. You can use SQLAlchemy for one-off scripts, web apps, desktop apps, and more. Anywhere that you'd use a SQL database along with Python, you can use SQLAlchemy.",["SingleFetchClassInstance",55],{"_38":39,"_28":40,"_41":56,"_43":57},{},[58],"This tutorial will cover setting up an engine object in Python SQLAlchemy 2.0, which is the first step to using SQLAlchemy. Then, it will cover two specific ways of interacting with a database: with raw SQL statements and by using the object-relational mapper.",["SingleFetchClassInstance",60],{"_38":39,"_28":40,"_41":61,"_43":62},{},[63],["SingleFetchClassInstance",64],{"_38":39,"_28":65,"_41":66,"_43":69},"Callout",{"_67":68},"type","note",[70,71,77,78,83]," This tutorial covers the most recent version of SQLAlchemy, which is 2.0, as of writing this. After running ",["SingleFetchClassInstance",72],{"_38":39,"_28":73,"_41":74,"_43":75},"code",{},[76],"pip install sqlalchemy",", you can run ",["SingleFetchClassInstance",79],{"_38":39,"_28":73,"_41":80,"_43":81},{},[82],"pip list"," to verify your SQLAlchemy version is greater than 2.0. ",["SingleFetchClassInstance",85],{"_38":39,"_28":86,"_41":87,"_43":90},"h2",{"_88":89},"id","set-up-planetscale-database",[91],["SingleFetchClassInstance",92],{"_38":39,"_28":47,"_41":93,"_43":95},{"_49":94},"#set-up-planetscale-database",[96],"Set up PlanetScale database",["SingleFetchClassInstance",98],{"_38":39,"_28":40,"_41":99,"_43":100},{},[101,102,108],"To demonstrate the examples in this SQLAlchemy tutorial, you'll need a MySQL-compatible database. If you don't have one already, you can get a database by ",["SingleFetchClassInstance",103],{"_38":39,"_28":47,"_41":104,"_43":106},{"_49":105},"https://auth.planetscale.com/sign-up",[107],"signing up for a PlanetScale account",".",["SingleFetchClassInstance",110],{"_38":39,"_28":65,"_41":111,"_43":113},{"_67":112},"warning",[114],["SingleFetchClassInstance",115],{"_38":39,"_28":40,"_41":116,"_43":117},{},[118],"This blog post is over a year old and may be out of date.",["SingleFetchClassInstance",120],{"_38":39,"_28":40,"_41":121,"_43":122},{},[123],"Once you have an account and are on the dashboard, create a new database by doing the following:",["SingleFetchClassInstance",125],{"_38":39,"_28":126,"_41":127,"_43":128},"ol",{},[129,135,140,145,150],["SingleFetchClassInstance",130],{"_38":39,"_28":131,"_41":132,"_43":133},"li",{},[134],"Click the \"Create\" link at the bottom of the dashboard.",["SingleFetchClassInstance",136],{"_38":39,"_28":131,"_41":137,"_43":138},{},[139],"Give your database a name.",["SingleFetchClassInstance",141],{"_38":39,"_28":131,"_41":142,"_43":143},{},[144],"Select a region.",["SingleFetchClassInstance",146],{"_38":39,"_28":131,"_41":147,"_43":148},{},[149],"Click the \"Create database\" button.",["SingleFetchClassInstance",151],{"_38":39,"_28":131,"_41":152,"_43":153},{},[154],"Finally, you can click on the \"Connect\" button and select \"General\" in the dropdown to see your database credentials. You'll need these credentials to create an engine in SQLAlchemy.",["SingleFetchClassInstance",156],{"_38":39,"_28":40,"_41":157,"_43":158},{},[159],["SingleFetchClassInstance",160],{"_38":39,"_28":161,"_41":162,"_43":163},"picture",{},[164],["SingleFetchClassInstance",165],{"_38":39,"_28":166,"_41":167,"_43":174},"img",{"_168":169,"_170":171,"_172":173},"alt","Create a PlanetScale database","loading","lazy","src","/assets/blog/content/mysql-sqlalchemy/create-sqlalchemy-database.png",[],["SingleFetchClassInstance",176],{"_38":39,"_28":40,"_41":177,"_43":178},{},[179],["SingleFetchClassInstance",180],{"_38":39,"_28":65,"_41":181,"_43":182},{"_67":68},[183,184,190,191,196]," PlanetScale uses a ",["SingleFetchClassInstance",185],{"_38":39,"_28":47,"_41":186,"_43":188},{"_49":187},"/docs/vitess/schema-changes/branching",[189],"branching workflow",", similar to git, so you can branch off of your production database when you need to make schema changes. This workflow lets you easily test changes before merging them into your production schema (again, very similar to what we're used to when deploying code changes). For this tutorial, you can just use the default initial branch, ",["SingleFetchClassInstance",192],{"_38":39,"_28":73,"_41":193,"_43":194},{},[195],"main",", for development. ",["SingleFetchClassInstance",198],{"_38":39,"_28":86,"_41":199,"_43":201},{"_88":200},"set-up-the-engine-object",[202],["SingleFetchClassInstance",203],{"_38":39,"_28":47,"_41":204,"_43":206},{"_49":205},"#set-up-the-engine-object",[207],"Set up the engine object",["SingleFetchClassInstance",209],{"_38":39,"_28":40,"_41":210,"_43":211},{},[212],"The first thing to do when using SQLAlchemy is to set up the engine object. The engine object is used by SQLAlchemy to manage connections to your database. Later, when you go to perform some action on the database, a connection will be requested from the engine and used to send the request.",["SingleFetchClassInstance",214],{"_38":39,"_28":40,"_41":215,"_43":216},{},[217],"Before creating the engine, you first need to know the credentials of your database along with the database driver you'll use to connect to the database. For MySQL, connection strings look like this:",["SingleFetchClassInstance",219],{"_38":39,"_28":220,"_41":221,"_43":226},"CodeBlock",{"_222":223,"_224":225},"html","

mysql+<drivername>://<username>:<password>@<server>:<port>/dbname\n

","language","bash",[],["SingleFetchClassInstance",228],{"_38":39,"_28":229,"_41":230,"_43":232},"h3",{"_88":231},"install-the-python-mysql-database-driver",[233],["SingleFetchClassInstance",234],{"_38":39,"_28":47,"_41":235,"_43":237},{"_49":236},"#install-the-python-mysql-database-driver",[238],"Install the Python MySQL database driver",["SingleFetchClassInstance",240],{"_38":39,"_28":40,"_41":241,"_43":242},{},[243,244,250,251,108],"Since SQLAlchemy works with many different database types, you'll need an underlying library, called a database driver, to connect to your database and communicate with it. You don't have to use this driver directly, because as long as SQLAlchemy has the correct driver, it will automatically use it for everything. The Python MySQL Connector is used as the driver in this tutorial, but other good ones are ",["SingleFetchClassInstance",245],{"_38":39,"_28":47,"_41":246,"_43":248},{"_49":247},"https://pypi.org/project/PyMySQL/",[249],"PyMySQL"," and ",["SingleFetchClassInstance",252],{"_38":39,"_28":47,"_41":253,"_43":255},{"_49":254},"https://mysqlclient.readthedocs.io/user_guide.html",[256],"MySQLdb",["SingleFetchClassInstance",258],{"_38":39,"_28":40,"_41":259,"_43":260},{},[261],"You'll need to install your driver with pip.",["SingleFetchClassInstance",263],{"_38":39,"_28":220,"_41":264,"_43":266},{"_222":265,"_224":225},"

pip install mysql-connector-python\n

",[],["SingleFetchClassInstance",268],{"_38":39,"_28":40,"_41":269,"_43":270},{},[271,272,277,278,277,283,288,289,294],"So let's say your username, password, hostname, port, and database name are ",["SingleFetchClassInstance",273],{"_38":39,"_28":73,"_41":274,"_43":275},{},[276],"user1",", ",["SingleFetchClassInstance",279],{"_38":39,"_28":73,"_41":280,"_43":281},{},[282],"pscale_pw_abc123",["SingleFetchClassInstance",284],{"_38":39,"_28":73,"_41":285,"_43":286},{},[287],"us-east.connect.psdb.cloud",", and ",["SingleFetchClassInstance",290],{"_38":39,"_28":73,"_41":291,"_43":292},{},[293],"3306",", respectively. Your connection string would look like the following if you were using mysqlconnector as your driver to connect to a database named sqlalchemy.",["SingleFetchClassInstance",296],{"_38":39,"_28":220,"_41":297,"_43":299},{"_222":298,"_224":225},"

mysql+mysqlconnector://user1:pscale_pw_abc123@us-east.connect.psdb.cloud:3306/sqlalchemy\n

",[],["SingleFetchClassInstance",301],{"_38":39,"_28":229,"_41":302,"_43":304},{"_88":303},"create-sqlalchemy-engine-object",[305],["SingleFetchClassInstance",306],{"_38":39,"_28":47,"_41":307,"_43":309},{"_49":308},"#create-sqlalchemy-engine-object",[310],"Create SQLAlchemy engine object",["SingleFetchClassInstance",312],{"_38":39,"_28":40,"_41":313,"_43":314},{},[315],"Once you have your driver installed and your connection string ready to go, you can create an engine like this:",["SingleFetchClassInstance",317],{"_38":39,"_28":220,"_41":318,"_43":321},{"_222":319,"_224":320},"

from sqlalchemy import create_engine\nconnection_string = \"mysql+mysqlconnector://user1:pscale_pw_abc123@us-east.connect.psdb.cloud:3306/sqlalchemy\"\nengine = create_engine(connection_string, echo=True)\n

","python",[],["SingleFetchClassInstance",323],{"_38":39,"_28":40,"_41":324,"_43":325},{},[326,327,332,333,338],"Typically, you don't need ",["SingleFetchClassInstance",328],{"_38":39,"_28":73,"_41":329,"_43":330},{},[331],"echo"," set to ",["SingleFetchClassInstance",334],{"_38":39,"_28":73,"_41":335,"_43":336},{},[337],"True",", but it's here so you can see the SQL statements that SQLAlchemy sends to your database.",["SingleFetchClassInstance",340],{"_38":39,"_28":40,"_41":341,"_43":342},{},[343,344,349,350,355,356,362],"By default, SSL/TLS usage in ",["SingleFetchClassInstance",345],{"_38":39,"_28":73,"_41":346,"_43":347},{},[348],"mysql-connector-python"," is enabled, which is required to connect to PlanetScale. This means you do not need to pass it into ",["SingleFetchClassInstance",351],{"_38":39,"_28":73,"_41":352,"_43":353},{},[354],"create_engine()"," as a connection arguement. See the ",["SingleFetchClassInstance",357],{"_38":39,"_28":47,"_41":358,"_43":360},{"_49":359},"https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html",[361],"Python connection arguments MySQL docs"," for more info and to see all of the possible arguments.",["SingleFetchClassInstance",364],{"_38":39,"_28":40,"_41":365,"_43":366},{},[367],"If you run the code and get no errors, then SQLAlchemy has no trouble connecting to your database. If you get an error like \"access denied\" or \"server not found,\" then you'll need to fix your connection string before proceeding.",["SingleFetchClassInstance",369],{"_38":39,"_28":40,"_41":370,"_43":371},{},[372],"With the engine object working, you can then continue with SQLAlchemy in various ways. This article covers how to use it to send raw queries to your database and how to use it as an ORM.",["SingleFetchClassInstance",374],{"_38":39,"_28":86,"_41":375,"_43":377},{"_88":376},"raw-sql-statements-in-sqlalchemy",[378],["SingleFetchClassInstance",379],{"_38":39,"_28":47,"_41":380,"_43":382},{"_49":381},"#raw-sql-statements-in-sqlalchemy",[383],"Raw SQL statements in SQLAlchemy",["SingleFetchClassInstance",385],{"_38":39,"_28":40,"_41":386,"_43":387},{},[388],"Now that we have our engine object working let's use it to send raw SQL statements to the database and receive the results in return.",["SingleFetchClassInstance",390],{"_38":39,"_28":229,"_41":391,"_43":393},{"_88":392},"create-a-connection-object",[394],["SingleFetchClassInstance",395],{"_38":39,"_28":47,"_41":396,"_43":398},{"_49":397},"#create-a-connection-object",[399],"Create a connection object",["SingleFetchClassInstance",401],{"_38":39,"_28":40,"_41":402,"_43":403},{},[404],"To start sending queries over, you'll need to create a connection object. Since the engine manages connections, you need to ask the engine for a connection before you can send statements over to the database.",["SingleFetchClassInstance",406],{"_38":39,"_28":40,"_41":407,"_43":408},{},[409,410,415],"We need to call engine.connect() to get a connection, and because the connect method is defined with a context manager, we can use a ",["SingleFetchClassInstance",411],{"_38":39,"_28":73,"_41":412,"_43":413},{},[414],"with"," statement to work with the connection.",["SingleFetchClassInstance",417],{"_38":39,"_28":220,"_41":418,"_43":420},{"_222":419,"_224":320},"

with engine.connect() as connection:\n

",[],["SingleFetchClassInstance",422],{"_38":39,"_28":40,"_41":423,"_43":424},{},[425,426,431,432,108],"Now that you have the connection, you can execute any SQL statement that works on your database by importing the text function from SQLAlchemy. Add ",["SingleFetchClassInstance",427],{"_38":39,"_28":73,"_41":428,"_43":429},{},[430],"from sqlalchemy import text"," in your Python file. You then pass the query as a string to the text function and then finally pass the text function to ",["SingleFetchClassInstance",433],{"_38":39,"_28":73,"_41":434,"_43":435},{},[436],"connection.execute()",["SingleFetchClassInstance",438],{"_38":39,"_28":229,"_41":439,"_43":441},{"_88":440},"create-a-table",[442],["SingleFetchClassInstance",443],{"_38":39,"_28":47,"_41":444,"_43":446},{"_49":445},"#create-a-table",[447],"Create a table",["SingleFetchClassInstance",449],{"_38":39,"_28":40,"_41":450,"_43":451},{},[452],"To create a table, you can run the following code:",["SingleFetchClassInstance",454],{"_38":39,"_28":220,"_41":455,"_43":457},{"_222":456,"_224":320},"

connection.execute(text(\"CREATE TABLE example (id INTEGER, name VARCHAR(20))\"))\n

",[],["SingleFetchClassInstance",459],{"_38":39,"_28":40,"_41":460,"_43":461},{},[462],"If you run that and get no errors, that means the table was created. If you try to run the code again, you'll get an error saying the table already exists.",["SingleFetchClassInstance",464],{"_38":39,"_28":40,"_41":465,"_43":466},{},[467],"You can also go back to your PlanetScale dashboard to confirm the table was added. Click on your database, click \"Console\", connect to your branch, and run the following:",["SingleFetchClassInstance",469],{"_38":39,"_28":220,"_41":470,"_43":473},{"_222":471,"_224":472},"

SHOW tables;\n

","sql",[],["SingleFetchClassInstance",475],{"_38":39,"_28":40,"_41":476,"_43":477},{},[478],["SingleFetchClassInstance",479],{"_38":39,"_28":161,"_41":480,"_43":481},{},[482],["SingleFetchClassInstance",483],{"_38":39,"_28":166,"_41":484,"_43":487},{"_168":485,"_170":171,"_172":486},"PlanetScale console - show tables command","/assets/blog/content/mysql-sqlalchemy/show-tables.png",[],["SingleFetchClassInstance",489],{"_38":39,"_28":229,"_41":490,"_43":492},{"_88":491},"add-data-to-a-table",[493],["SingleFetchClassInstance",494],{"_38":39,"_28":47,"_41":495,"_43":497},{"_49":496},"#add-data-to-a-table",[498],"Add data to a table",["SingleFetchClassInstance",500],{"_38":39,"_28":40,"_41":501,"_43":502},{},[503],"Next, let's insert some data into our new table.",["SingleFetchClassInstance",505],{"_38":39,"_28":220,"_41":506,"_43":508},{"_222":507,"_224":320},"

connection.execute(text(\"INSERT INTO example (name) VALUES (:name)\"), {\"name\": \"Ashley\"})\nconnection.execute(text(\"INSERT INTO example (name) VALUES (:name)\"), [{\"name\": \"Barry\"}, {\"name\": \"Christina\"}])\nconnection.commit()\n

",[],["SingleFetchClassInstance",510],{"_38":39,"_28":40,"_41":511,"_43":512},{},[513],"The first execute statement will create just one row because a single dictionary was passed. But the second statement will create two rows since a list of two dictionaries was passed. Just make sure the keys in the dictionary match the placeholders you have with a colon in front of the name.",["SingleFetchClassInstance",515],{"_38":39,"_28":40,"_41":516,"_43":517},{},[518],"Even though we aren't dealing with user input here, it's still a good idea to make a habit of passing in parameters instead of the data directly inside of an insert statement or select statement.",["SingleFetchClassInstance",520],{"_38":39,"_28":40,"_41":521,"_43":522},{},[523,524,529,530,535,536,541],"Unlike ",["SingleFetchClassInstance",525],{"_38":39,"_28":73,"_41":526,"_43":527},{},[528],"CREATE"," statements, ",["SingleFetchClassInstance",531],{"_38":39,"_28":73,"_41":532,"_43":533},{},[534],"INSERT"," statements happen in transactions, so we have to save them to the database by calling ",["SingleFetchClassInstance",537],{"_38":39,"_28":73,"_41":538,"_43":539},{},[540],".commit()"," after we execute our insert statements.",["SingleFetchClassInstance",543],{"_38":39,"_28":229,"_41":544,"_43":546},{"_88":545},"query-data",[547],["SingleFetchClassInstance",548],{"_38":39,"_28":47,"_41":549,"_43":551},{"_49":550},"#query-data",[552],"Query data",["SingleFetchClassInstance",554],{"_38":39,"_28":40,"_41":555,"_43":556},{},[557,558,562,563,568],"Finally, now that we have some data in the database, let's go ahead and query that data so we can see it again. We can assign the result of ",["SingleFetchClassInstance",559],{"_38":39,"_28":73,"_41":560,"_43":561},{},[436]," to a variable called result, and if we loop over ",["SingleFetchClassInstance",564],{"_38":39,"_28":73,"_41":565,"_43":566},{},[567],"result.mappings()",", we'll see that we get dictionaries for each row, where the key in each dictionary represents the column name. This makes it easy for us to retrieve the data and display it in a loop.",["SingleFetchClassInstance",570],{"_38":39,"_28":220,"_41":571,"_43":573},{"_222":572,"_224":320},"

result = connection.execute(text(\"SELECT * FROM example WHERE name = :name\"), dict(name=\"Ashley\"))\n\nfor row in result.mappings():\n    print(\"Author:\" , row[\"name\"])\n

",[],["SingleFetchClassInstance",575],{"_38":39,"_28":40,"_41":576,"_43":577},{},[578],"As you can see, you only need to know a few things to write raw queries using SQLAlchemy. If you want to use it as an ORM, you can do that as well.",["SingleFetchClassInstance",580],{"_38":39,"_28":86,"_41":581,"_43":583},{"_88":582},"using-sqlalchemy-orm-to-write-queries",[584],["SingleFetchClassInstance",585],{"_38":39,"_28":47,"_41":586,"_43":588},{"_49":587},"#using-sqlalchemy-orm-to-write-queries",[589],"Using SQLAlchemy ORM to write queries",["SingleFetchClassInstance",591],{"_38":39,"_28":40,"_41":592,"_43":593},{},[594],"The idea behind ORM (object-relational mapping) is to create a code representation of your database using classes and objects instead of writing raw SQL statements. The classes represent the tables in your database, and the objects of those classes represent rows. So the first step to using ORM is to define classes that map to your tables. Classes that represent tables in an ORM are called models.",["SingleFetchClassInstance",596],{"_38":39,"_28":40,"_41":597,"_43":598},{},[599,600,605,606,610,611,616,617,622,623,627],"Before we can do the mapping, we need something called the ",["SingleFetchClassInstance",601],{"_38":39,"_28":73,"_41":602,"_43":603},{},[604],"DeclarativeBase"," from SQLAlchemy. Even though our classes could inherit directly from ",["SingleFetchClassInstance",607],{"_38":39,"_28":73,"_41":608,"_43":609},{},[604],", we will instead create our own ",["SingleFetchClassInstance",612],{"_38":39,"_28":73,"_41":613,"_43":614},{},[615],"Base"," class that inherits it and then call ",["SingleFetchClassInstance",618],{"_38":39,"_28":73,"_41":619,"_43":620},{},[621],"pass",". This makes it easy to add additional settings to our ",["SingleFetchClassInstance",624],{"_38":39,"_28":73,"_41":625,"_43":626},{},[615]," class in the future since all of our models will inherit from this one.",["SingleFetchClassInstance",629],{"_38":39,"_28":220,"_41":630,"_43":632},{"_222":631,"_224":320},"

class Base(DeclarativeBase):\n    pass\n

",[],["SingleFetchClassInstance",634],{"_38":39,"_28":229,"_41":635,"_43":637},{"_88":636},"create-models",[638],["SingleFetchClassInstance",639],{"_38":39,"_28":47,"_41":640,"_43":642},{"_49":641},"#create-models",[643],"Create models",["SingleFetchClassInstance",645],{"_38":39,"_28":40,"_41":646,"_43":647},{},[648,649,654,655,659],"Now we can create our models. Let's first create an ",["SingleFetchClassInstance",650],{"_38":39,"_28":73,"_41":651,"_43":652},{},[653],"Author"," model which will map to an ",["SingleFetchClassInstance",656],{"_38":39,"_28":73,"_41":657,"_43":658},{},[24]," table. The idea here is to first define a tablename, which is the attribute __tablename__.",["SingleFetchClassInstance",661],{"_38":39,"_28":229,"_41":662,"_43":664},{"_88":663},"define-the-columns",[665],["SingleFetchClassInstance",666],{"_38":39,"_28":47,"_41":667,"_43":669},{"_49":668},"#define-the-columns",[670],"Define the columns",["SingleFetchClassInstance",672],{"_38":39,"_28":40,"_41":673,"_43":674},{},[675,676,681],"Next, we need to define the columns. Starting in SQLAlchemy 2.0, we can use the Python typing system to define the columns for us. So the format of each column is the name of the column, followed by a class called ",["SingleFetchClassInstance",677],{"_38":39,"_28":73,"_41":678,"_43":679},{},[680],"Mapped"," with the Python type that closest matches the type you want in the database.",["SingleFetchClassInstance",683],{"_38":39,"_28":40,"_41":684,"_43":685},{},[686,687,692,693,698],"So for an ID column, we would have ",["SingleFetchClassInstance",688],{"_38":39,"_28":73,"_41":689,"_43":690},{},[691],"id: Mapped[int]",". Next, that attribute is going to be set equal to the ",["SingleFetchClassInstance",694],{"_38":39,"_28":73,"_41":695,"_43":696},{},[697],"mapped_column"," function call, where we could set additional properties on our column like primary key, max length, nullable, etc.",["SingleFetchClassInstance",700],{"_38":39,"_28":40,"_41":701,"_43":702},{},[703,704,250,708,712],"So let's create an Author model with two fields: ",["SingleFetchClassInstance",705],{"_38":39,"_28":73,"_41":706,"_43":707},{},[88],["SingleFetchClassInstance",709],{"_38":39,"_28":73,"_41":710,"_43":711},{},[28],", which means we'll have a table with two columns. SQLAlchemy requires each model to have a primary key so it can internally keep track of each object, so let's make ID the primary key.",["SingleFetchClassInstance",714],{"_38":39,"_28":220,"_41":715,"_43":717},{"_222":716,"_224":320},"

class Author(Base):\n    __tablename__ = \"author\"\n\n    id: Mapped[int] = mapped_column(primary_key=True)\n    name: Mapped[str] = mapped_column(String(30))\n

",[],["SingleFetchClassInstance",719],{"_38":39,"_28":720,"_41":721,"_43":723},"h4",{"_88":722},"handling-relationships-and-foreign-key-constraints",[724],["SingleFetchClassInstance",725],{"_38":39,"_28":47,"_41":726,"_43":728},{"_49":727},"#handling-relationships-and-foreign-key-constraints",[729],"Handling relationships and foreign key constraints",["SingleFetchClassInstance",731],{"_38":39,"_28":65,"_41":732,"_43":733},{"_67":68},[734],["SingleFetchClassInstance",735],{"_38":39,"_28":40,"_41":736,"_43":737},{},[738,739,745],"PlanetScale now supports ",["SingleFetchClassInstance",740],{"_38":39,"_28":47,"_41":741,"_43":743},{"_49":742},"/docs/vitess/foreign-key-constraints",[744],"foreign key constraints",". This information below is out of date, but it will still correctly work.",["SingleFetchClassInstance",747],{"_38":39,"_28":40,"_41":748,"_43":749},{},[750,751,756,757,761,762,768],"Next, let's create a Post model, which will have a relationship to the Author model. We can create an author_id column inside of Post that holds the reference to the author who created the post. For most database systems, you'd pass ",["SingleFetchClassInstance",752],{"_38":39,"_28":73,"_41":753,"_43":754},{},[755],"ForeignKey"," to ",["SingleFetchClassInstance",758],{"_38":39,"_28":73,"_41":759,"_43":760},{},[697]," to create an actual foreign key constraint in the database. But with PlanetScale, we ",["SingleFetchClassInstance",763],{"_38":39,"_28":47,"_41":764,"_43":766},{"_49":765},"/docs/vitess/operating-without-foreign-key-constraints",[767],"don't recommend using foreign key constraints",". However, we can still use SQLAlchemy to manage the relationship for us.",["SingleFetchClassInstance",770],{"_38":39,"_28":40,"_41":771,"_43":772},{},[773],"Since databases with foreign key constraints are very common, variations for those databases are included in the commented-out lines.",["SingleFetchClassInstance",775],{"_38":39,"_28":220,"_41":776,"_43":778},{"_222":777,"_224":320},"

class Post(Base):\n    __tablename__ = \"post\"\n\n    id: Mapped[int] = mapped_column(primary_key=True)\n    title: Mapped[str] = mapped_column(String(30))\n    #author_id: Mapped[int] = mapped_column(ForeignKey(\"author.id\"))\n    author_id: Mapped[int]\n

",[],["SingleFetchClassInstance",780],{"_38":39,"_28":40,"_41":781,"_43":782},{},[783,784,790],"The advantages of having no foreign keys are we can have ",["SingleFetchClassInstance",785],{"_38":39,"_28":47,"_41":786,"_43":788},{"_49":787},"/docs/vitess/best-practices",[789],"multiple versions of our database schema"," in the same way we have multiple versions of code through things like git branches. It also allows us to make schema changes to production databases without any downtime. And finally, it makes it easier to scale the database through sharding.",["SingleFetchClassInstance",792],{"_38":39,"_28":40,"_41":793,"_43":794},{},[795,796,801],"But even without a foreign key, we can still have a relationship between two tables. To get SQLAlchemy to manage the relationship for us, we can create a ",["SingleFetchClassInstance",797],{"_38":39,"_28":73,"_41":798,"_43":799},{},[800],"relationship"," attribute. Unlike the attributes for the columns, no column gets created in the database. Instead, the relationship only exists in our code while it's running.",["SingleFetchClassInstance",803],{"_38":39,"_28":40,"_41":804,"_43":805},{},[806,807,811,812,816],"So we can add a relationship attribute and the type will be a list of ",["SingleFetchClassInstance",808],{"_38":39,"_28":73,"_41":809,"_43":810},{},[653]," classes, which we can pass to the ",["SingleFetchClassInstance",813],{"_38":39,"_28":73,"_41":814,"_43":815},{},[680]," class as the type.",["SingleFetchClassInstance",818],{"_38":39,"_28":220,"_41":819,"_43":821},{"_222":820,"_224":320},"

posts: Mapped[list[\"Post\"]] = relationship(primaryjoin='foreign(Post.author_id) == Author.id')\n

",[],["SingleFetchClassInstance",823],{"_38":39,"_28":40,"_41":824,"_43":825},{},[826,827,831,832,837,838,842,843,847],"Since we're not using ",["SingleFetchClassInstance",828],{"_38":39,"_28":73,"_41":829,"_43":830},{},[755],", we need to tell SQLAlchemy how to handle our relationship. We can do that we the ",["SingleFetchClassInstance",833],{"_38":39,"_28":73,"_41":834,"_43":835},{},[836],"primaryjoin"," argument to relationship. If we used a database with foreign keys, then the ",["SingleFetchClassInstance",839],{"_38":39,"_28":73,"_41":840,"_43":841},{},[755]," being passed to the ",["SingleFetchClassInstance",844],{"_38":39,"_28":73,"_41":845,"_43":846},{},[697]," would be enough.",["SingleFetchClassInstance",849],{"_38":39,"_28":40,"_41":850,"_43":851},{},[852,853,857,858,862],"We can also create a ",["SingleFetchClassInstance",854],{"_38":39,"_28":73,"_41":855,"_43":856},{},[39]," model in a similar way. This ",["SingleFetchClassInstance",859],{"_38":39,"_28":73,"_41":860,"_43":861},{},[39]," model represents tags that each post could have.",["SingleFetchClassInstance",864],{"_38":39,"_28":220,"_41":865,"_43":867},{"_222":866,"_224":320},"

class Tag(Base):\n    __tablename__ = \"tag\"\n\n    id: Mapped[int] = mapped_column(primary_key=True)\n    text: Mapped[str] = mapped_column(String(30))\n

",[],["SingleFetchClassInstance",869],{"_38":39,"_28":40,"_41":870,"_43":871},{},[872],"Because one post can have many tags and one tag can belong to many posts, we need to create a many-to-many relationship. We can create a post_tag table to represent this relationship. Many-to-many relationships have the foreign key stored in a separate table called an association table. We can create that table directly in SQLAlchemy. You could also use a model for this, but it's better to use a table because you won't be working with this table directly. Instead, SQLAlchemy will automatically manage the data in this table for you by using the relationships you define.",["SingleFetchClassInstance",874],{"_38":39,"_28":40,"_41":875,"_43":876},{},[877],"You can create the table like this:",["SingleFetchClassInstance",879],{"_38":39,"_28":220,"_41":880,"_43":882},{"_222":881,"_224":320},"

post_tag = Table(\n    \"post_tag\",\n    Base.metadata,\n    #Column(\"author_id\", ForeignKey(\"author.id\"), primary_key=True),\n    #Column(\"tag_id\", ForeignKey(\"tag.id\"), primary_key=True),\n    Column(\"post_id\", Integer, primary_key=True),\n    Column(\"tag_id\", Integer, primary_key=True)\n)\n

",[],["SingleFetchClassInstance",884],{"_38":39,"_28":229,"_41":885,"_43":887},{"_88":886},"create-tables-with-create_all",[888],["SingleFetchClassInstance",889],{"_38":39,"_28":47,"_41":890,"_43":892},{"_49":891},"#create-tables-with-create_all",[893,894],"Create tables with ",["SingleFetchClassInstance",895],{"_38":39,"_28":73,"_41":896,"_43":897},{},[898],"create_all()",["SingleFetchClassInstance",900],{"_38":39,"_28":40,"_41":901,"_43":902},{},[903,904,909,910,914,915,919],"Now that we have the Tag table defined, to create the tables in the database, you can call ",["SingleFetchClassInstance",905],{"_38":39,"_28":73,"_41":906,"_43":907},{},[908],"create_all"," on your ",["SingleFetchClassInstance",911],{"_38":39,"_28":73,"_41":912,"_43":913},{},[615]," class. The ",["SingleFetchClassInstance",916],{"_38":39,"_28":73,"_41":917,"_43":918},{},[908]," call takes an engine object, so you can reuse the one we created earlier.",["SingleFetchClassInstance",921],{"_38":39,"_28":40,"_41":922,"_43":923},{},[924,929,930,934],["SingleFetchClassInstance",925],{"_38":39,"_28":73,"_41":926,"_43":927},{},[928],"Create_all"," will take about ",["SingleFetchClassInstance",931],{"_38":39,"_28":73,"_41":932,"_43":933},{},[604]," and instruct it to create statements for each one of our tables and add them to the database. You'll see that printed to your terminal when you run.",["SingleFetchClassInstance",936],{"_38":39,"_28":220,"_41":937,"_43":939},{"_222":938,"_224":320},"

Base.metadata.create_all(engine)\n

",[],["SingleFetchClassInstance",941],{"_38":39,"_28":40,"_41":942,"_43":943},{},[944],"With our tables created, we can go ahead and insert data into the tables and then query the tables.",["SingleFetchClassInstance",946],{"_38":39,"_28":40,"_41":947,"_43":948},{},[949,950,954],"Since we're working with the ORM, the way to create new rows is first by creating objects. So for example, to create a new ",["SingleFetchClassInstance",951],{"_38":39,"_28":73,"_41":952,"_43":953},{},[653],", we can instantiate an author object.",["SingleFetchClassInstance",956],{"_38":39,"_28":40,"_41":957,"_43":958},{},[959],"For relationships, we can set one object to be related to another when we instantiate the related object. We want to use the relationship attribute instead of the _id field directly because SQLAlchemy will take care of the ID field for us. For many-to-many relationships, we append to the relationship attribute like it's a Python list. We only need to append children of the relationship.",["SingleFetchClassInstance",961],{"_38":39,"_28":40,"_41":962,"_43":963},{},[964],"To add them to the database, we need to first add them to the session. Finally, we need to call commit to save them to the database. When you run the script, you'll see the actual insert statements being printed.",["SingleFetchClassInstance",966],{"_38":39,"_28":220,"_41":967,"_43":969},{"_222":968,"_224":320},"

with Session(engine) as session:\n    author = Author(name=\"David\")\n    post = Post(title=\"Python Essentials\", author=author)\n    session.add(author)\n    session.add(post)\n\n    post2 = Post(title=\"SQL Secrets\", author=author)\n    post3 = Post(title=\"Advanced MySQL\", author=author)\n    session.add_all([post2, post3])\n\n    tag1 = Tag(text=\"python\")\n    tag2 = Tag(text=\"sql\")\n    tag3 = Tag(text=\"mysql\")\n    session.add_all([tag1, tag2, tag3])\n\n    post.tags.append(tag1)\n    post2.tags.append(tag2)\n    post3.tags.append(tag2)\n    post3.tags.append(tag3)\n\n    session.commit()\n

",[],["SingleFetchClassInstance",971],{"_38":39,"_28":229,"_41":972,"_43":974},{"_88":973},"query-the-data",[975],["SingleFetchClassInstance",976],{"_38":39,"_28":47,"_41":977,"_43":979},{"_49":978},"#query-the-data",[980],"Query the data",["SingleFetchClassInstance",982],{"_38":39,"_28":40,"_41":983,"_43":984},{},[985],"Now that we have some data in the database, we can go ahead and query that data. First, we write our query statement.",["SingleFetchClassInstance",987],{"_38":39,"_28":40,"_41":988,"_43":989},{},[990,991,996],"Start by passing your Model call to the select function. Then you have the option to use the where attribute on the resulting object. We can then pass all of that to ",["SingleFetchClassInstance",992],{"_38":39,"_28":73,"_41":993,"_43":994},{},[995],"session.scalar"," to run the query. With the result of scalar, we can print out the results to the terminal. We can also look at the values in the relationship. For each post, we can look at the tags as well.",["SingleFetchClassInstance",998],{"_38":39,"_28":40,"_41":999,"_43":1000},{},[1001,1002,1007,1008,1013],"If you want to leave out the where and get all of the posts, you will use ",["SingleFetchClassInstance",1003],{"_38":39,"_28":73,"_41":1004,"_43":1005},{},[1006],"scalars"," instead of ",["SingleFetchClassInstance",1009],{"_38":39,"_28":73,"_41":1010,"_43":1011},{},[1012],"scalar",". Then we can loop over the object returned and print out all the titles.",["SingleFetchClassInstance",1015],{"_38":39,"_28":220,"_41":1016,"_43":1018},{"_222":1017,"_224":320},"

stmt = select(Author).where(Author.name == \"David\")\nauthor = session.scalar(stmt)\n\nfor post in author.posts:\n    print(post.title)\n    for tag in post.tags:\n        print(\" \", tag.text)\n\nfor post in session.scalars(select(Post)):\n    print(post.title)\n

",[],["SingleFetchClassInstance",1020],{"_38":39,"_28":86,"_41":1021,"_43":1023},{"_88":1022},"conclusion",[1024],["SingleFetchClassInstance",1025],{"_38":39,"_28":47,"_41":1026,"_43":1028},{"_49":1027},"#conclusion",[1029],"Conclusion",["SingleFetchClassInstance",1031],{"_38":39,"_28":40,"_41":1032,"_43":1033},{},[1034],"With your new knowledge of SQLAlchemy, you should have a good starting point to continue using it in any Python project that uses a SQL database. As long as you can set up the engine object, you'll be able to decide whether you want to simply send raw SQL statements, construct SQL statements using the SQLAlchemy API, or map your Python classes and objects to your database tables and data.","body_text","SQLAlchemy is a popular Python library that gives you many tools to interact with SQL databases. With SQLAlchemy, you can do things like send raw queries to a database, programmatically construct SQL statements, and even map Python classes to database tables with the object-relational mapper (ORM). SQLAlchemy doesn't force you to use any particular features, so it has the flexibility to support many different approaches to working with databases. You can use SQLAlchemy for one-off scripts, web apps, desktop apps, and more. Anywhere that you'd use a SQL database along with Python, you can use SQLAlchemy.\nThis tutorial will cover setting up an engine object in Python SQLAlchemy 2.0, which is the first step to using SQLAlchemy. Then, it will cover two specific ways of interacting with a database: with raw SQL statements and by using the object-relational mapper.\n This tutorial covers the most recent version of SQLAlchemy, which is 2.0, as of writing this. After running pip install sqlalchemy, you can run pip list to verify your SQLAlchemy version is greater than 2.0. \nSet up PlanetScale database\nTo demonstrate the examples in this SQLAlchemy tutorial, you'll need a MySQL-compatible database. If you don't have one already, you can get a database by signing up for a PlanetScale account.\nThis blog post is over a year old and may be out of date.\nOnce you have an account and are on the dashboard, create a new database by doing the following:\nClick the \"Create\" link at the bottom of the dashboard.\nGive your database a name.\nSelect a region.\nClick the \"Create database\" button.\nFinally, you can click on the \"Connect\" button and select \"General\" in the dropdown to see your database credentials. You'll need these credentials to create an engine in SQLAlchemy.\n\n PlanetScale uses a branching workflow, similar to git, so you can branch off of your production database when you need to make schema changes. This workflow lets you easily test changes before merging them into your production schema (again, very similar to what we're used to when deploying code changes). For this tutorial, you can just use the default initial branch, main, for development. \nSet up the engine object\nThe first thing to do when using SQLAlchemy is to set up the engine object. The engine object is used by SQLAlchemy to manage connections to your database. Later, when you go to perform some action on the database, a connection will be requested from the engine and used to send the request.\nBefore creating the engine, you first need to know the credentials of your database along with the database driver you'll use to connect to the database. For MySQL, connection strings look like this:mysql+://:@:/dbname\n\nInstall the Python MySQL database driver\nSince SQLAlchemy works with many different database types, you'll need an underlying library, called a database driver, to connect to your database and communicate with it. You don't have to use this driver directly, because as long as SQLAlchemy has the correct driver, it will automatically use it for everything. The Python MySQL Connector is used as the driver in this tutorial, but other good ones are PyMySQL and MySQLdb.\nYou'll need to install your driver with pip.pip install mysql-connector-python\n\nSo let's say your username, password, hostname, port, and database name are user1, pscale_pw_abc123, us-east.connect.psdb.cloud, and 3306, respectively. Your connection string would look like the following if you were using mysqlconnector as your driver to connect to a database named sqlalchemy.mysql+mysqlconnector://user1:pscale_pw_abc123@us-east.connect.psdb.cloud:3306/sqlalchemy\n\nCreate SQLAlchemy engine object\nOnce you have your driver installed and your connection string ready to go, you can create an engine like this:from sqlalchemy import create_engine\nconnection_string = \"mysql+mysqlconnector://user1:pscale_pw_abc123@us-east.connect.psdb.cloud:3306/sqlalchemy\"\nengine = create_engine(connection_string, echo=True)\n\nTypically, you don't need echo set to True, but it's here so you can see the SQL statements that SQLAlchemy sends to your database.\nBy default, SSL/TLS usage in mysql-connector-python is enabled, which is required to connect to PlanetScale. This means you do not need to pass it into create_engine() as a connection arguement. See the Python connection arguments MySQL docs for more info and to see all of the possible arguments.\nIf you run the code and get no errors, then SQLAlchemy has no trouble connecting to your database. If you get an error like \"access denied\" or \"server not found,\" then you'll need to fix your connection string before proceeding.\nWith the engine object working, you can then continue with SQLAlchemy in various ways. This article covers how to use it to send raw queries to your database and how to use it as an ORM.\nRaw SQL statements in SQLAlchemy\nNow that we have our engine object working let's use it to send raw SQL statements to the database and receive the results in return.\nCreate a connection object\nTo start sending queries over, you'll need to create a connection object. Since the engine manages connections, you need to ask the engine for a connection before you can send statements over to the database.\nWe need to call engine.connect() to get a connection, and because the connect method is defined with a context manager, we can use a with statement to work with the connection.with engine.connect() as connection:\n\nNow that you have the connection, you can execute any SQL statement that works on your database by importing the text function from SQLAlchemy. Add from sqlalchemy import text in your Python file. You then pass the query as a string to the text function and then finally pass the text function to connection.execute().\nCreate a table\nTo create a table, you can run the following code:connection.execute(text(\"CREATE TABLE example (id INTEGER, name VARCHAR(20))\"))\n\nIf you run that and get no errors, that means the table was created. If you try to run the code again, you'll get an error saying the table already exists.\nYou can also go back to your PlanetScale dashboard to confirm the table was added. Click on your database, click \"Console\", connect to your branch, and run the following:SHOW tables;\n\n\nAdd data to a table\nNext, let's insert some data into our new table.connection.execute(text(\"INSERT INTO example (name) VALUES (:name)\"), {\"name\": \"Ashley\"})\nconnection.execute(text(\"INSERT INTO example (name) VALUES (:name)\"), [{\"name\": \"Barry\"}, {\"name\": \"Christina\"}])\nconnection.commit()\n\nThe first execute statement will create just one row because a single dictionary was passed. But the second statement will create two rows since a list of two dictionaries was passed. Just make sure the keys in the dictionary match the placeholders you have with a colon in front of the name.\nEven though we aren't dealing with user input here, it's still a good idea to make a habit of passing in parameters instead of the data directly inside of an insert statement or select statement.\nUnlike CREATE statements, INSERT statements happen in transactions, so we have to save them to the database by calling .commit() after we execute our insert statements.\nQuery data\nFinally, now that we have some data in the database, let's go ahead and query that data so we can see it again. We can assign the result of connection.execute() to a variable called result, and if we loop over result.mappings(), we'll see that we get dictionaries for each row, where the key in each dictionary represents the column name. This makes it easy for us to retrieve the data and display it in a loop.result = connection.execute(text(\"SELECT * FROM example WHERE name = :name\"), dict(name=\"Ashley\"))\n\nfor row in result.mappings():\n print(\"Author:\" , row[\"name\"])\n\nAs you can see, you only need to know a few things to write raw queries using SQLAlchemy. If you want to use it as an ORM, you can do that as well.\nUsing SQLAlchemy ORM to write queries\nThe idea behind ORM (object-relational mapping) is to create a code representation of your database using classes and objects instead of writing raw SQL statements. The classes represent the tables in your database, and the objects of those classes represent rows. So the first step to using ORM is to define classes that map to your tables. Classes that represent tables in an ORM are called models.\nBefore we can do the mapping, we need something called the DeclarativeBase from SQLAlchemy. Even though our classes could inherit directly from DeclarativeBase, we will instead create our own Base class that inherits it and then call pass. This makes it easy to add additional settings to our Base class in the future since all of our models will inherit from this one.class Base(DeclarativeBase):\n pass\n\nCreate models\nNow we can create our models. Let's first create an Author model which will map to an author table. The idea here is to first define a tablename, which is the attribute __tablename__.\nDefine the columns\nNext, we need to define the columns. Starting in SQLAlchemy 2.0, we can use the Python typing system to define the columns for us. So the format of each column is the name of the column, followed by a class called Mapped with the Python type that closest matches the type you want in the database.\nSo for an ID column, we would have id: Mapped[int]. Next, that attribute is going to be set equal to the mapped_column function call, where we could set additional properties on our column like primary key, max length, nullable, etc.\nSo let's create an Author model with two fields: id and name, which means we'll have a table with two columns. SQLAlchemy requires each model to have a primary key so it can internally keep track of each object, so let's make ID the primary key.class Author(Base):\n __tablename__ = \"author\"\n\n id: Mapped[int] = mapped_column(primary_key=True)\n name: Mapped[str] = mapped_column(String(30))\n\nHandling relationships and foreign key constraints\nPlanetScale now supports foreign key constraints. This information below is out of date, but it will still correctly work.\nNext, let's create a Post model, which will have a relationship to the Author model. We can create an author_id column inside of Post that holds the reference to the author who created the post. For most database systems, you'd pass ForeignKey to mapped_column to create an actual foreign key constraint in the database. But with PlanetScale, we don't recommend using foreign key constraints. However, we can still use SQLAlchemy to manage the relationship for us.\nSince databases with foreign key constraints are very common, variations for those databases are included in the commented-out lines.class Post(Base):\n __tablename__ = \"post\"\n\n id: Mapped[int] = mapped_column(primary_key=True)\n title: Mapped[str] = mapped_column(String(30))\n #author_id: Mapped[int] = mapped_column(ForeignKey(\"author.id\"))\n author_id: Mapped[int]\n\nThe advantages of having no foreign keys are we can have multiple versions of our database schema in the same way we have multiple versions of code through things like git branches. It also allows us to make schema changes to production databases without any downtime. And finally, it makes it easier to scale the database through sharding.\nBut even without a foreign key, we can still have a relationship between two tables. To get SQLAlchemy to manage the relationship for us, we can create a relationship attribute. Unlike the attributes for the columns, no column gets created in the database. Instead, the relationship only exists in our code while it's running.\nSo we can add a relationship attribute and the type will be a list of Author classes, which we can pass to the Mapped class as the type.posts: Mapped[list[\"Post\"]] = relationship(primaryjoin='foreign(Post.author_id) == Author.id')\n\nSince we're not using ForeignKey, we need to tell SQLAlchemy how to handle our relationship. We can do that we the primaryjoin argument to relationship. If we used a database with foreign keys, then the ForeignKey being passed to the mapped_column would be enough.\nWe can also create a Tag model in a similar way. This Tag model represents tags that each post could have.class Tag(Base):\n __tablename__ = \"tag\"\n\n id: Mapped[int] = mapped_column(primary_key=True)\n text: Mapped[str] = mapped_column(String(30))\n\nBecause one post can have many tags and one tag can belong to many posts, we need to create a many-to-many relationship. We can create a post_tag table to represent this relationship. Many-to-many relationships have the foreign key stored in a separate table called an association table. We can create that table directly in SQLAlchemy. You could also use a model for this, but it's better to use a table because you won't be working with this table directly. Instead, SQLAlchemy will automatically manage the data in this table for you by using the relationships you define.\nYou can create the table like this:post_tag = Table(\n \"post_tag\",\n Base.metadata,\n #Column(\"author_id\", ForeignKey(\"author.id\"), primary_key=True),\n #Column(\"tag_id\", ForeignKey(\"tag.id\"), primary_key=True),\n Column(\"post_id\", Integer, primary_key=True),\n Column(\"tag_id\", Integer, primary_key=True)\n)\n\nCreate tables with create_all()\nNow that we have the Tag table defined, to create the tables in the database, you can call create_all on your Base class. The create_all call takes an engine object, so you can reuse the one we created earlier.\nCreate_all will take about DeclarativeBase and instruct it to create statements for each one of our tables and add them to the database. You'll see that printed to your terminal when you run.Base.metadata.create_all(engine)\n\nWith our tables created, we can go ahead and insert data into the tables and then query the tables.\nSince we're working with the ORM, the way to create new rows is first by creating objects. So for example, to create a new Author, we can instantiate an author object.\nFor relationships, we can set one object to be related to another when we instantiate the related object. We want to use the relationship attribute instead of the _id field directly because SQLAlchemy will take care of the ID field for us. For many-to-many relationships, we append to the relationship attribute like it's a Python list. We only need to append children of the relationship.\nTo add them to the database, we need to first add them to the session. Finally, we need to call commit to save them to the database. When you run the script, you'll see the actual insert statements being printed.with Session(engine) as session:\n author = Author(name=\"David\")\n post = Post(title=\"Python Essentials\", author=author)\n session.add(author)\n session.add(post)\n\n post2 = Post(title=\"SQL Secrets\", author=author)\n post3 = Post(title=\"Advanced MySQL\", author=author)\n session.add_all([post2, post3])\n\n tag1 = Tag(text=\"python\")\n tag2 = Tag(text=\"sql\")\n tag3 = Tag(text=\"mysql\")\n session.add_all([tag1, tag2, tag3])\n\n post.tags.append(tag1)\n post2.tags.append(tag2)\n post3.tags.append(tag2)\n post3.tags.append(tag3)\n\n session.commit()\n\nQuery the data\nNow that we have some data in the database, we can go ahead and query that data. First, we write our query statement.\nStart by passing your Model call to the select function. Then you have the option to use the where attribute on the resulting object. We can then pass all of that to session.scalar to run the query. With the result of scalar, we can print out the results to the terminal. We can also look at the values in the relationship. For each post, we can look at the tags as well.\nIf you want to leave out the where and get all of the posts, you will use scalars instead of scalar. Then we can loop over the object returned and print out all the titles.stmt = select(Author).where(Author.name == \"David\")\nauthor = session.scalar(stmt)\n\nfor post in author.posts:\n print(post.title)\n for tag in post.tags:\n print(\" \", tag.text)\n\nfor post in session.scalars(select(Post)):\n print(post.title)\n\nConclusion\nWith your new knowledge of SQLAlchemy, you should have a good starting point to continue using it in any Python project that uses a SQL database. As long as you can set up the engine object, you'll be able to decide whether you want to simply send raw SQL statements, construct SQL statements using the SQLAlchemy API, or map your Python classes and objects to your database tables and data.","aside","toc",[1040,1045,1052,1062,1073],{"_43":1041,"_88":89,"_1042":1043,"_1044":96},[],"level",2,"title",{"_43":1046,"_88":200,"_1042":1043,"_1044":207},[1047,1050],{"_43":1048,"_88":231,"_1042":1049,"_1044":238},[],3,{"_43":1051,"_88":303,"_1042":1049,"_1044":310},[],{"_43":1053,"_88":376,"_1042":1043,"_1044":383},[1054,1056,1058,1060],{"_43":1055,"_88":392,"_1042":1049,"_1044":399},[],{"_43":1057,"_88":440,"_1042":1049,"_1044":447},[],{"_43":1059,"_88":491,"_1042":1049,"_1044":498},[],{"_43":1061,"_88":545,"_1042":1049,"_1044":552},[],{"_43":1063,"_88":582,"_1042":1043,"_1044":589},[1064,1066,1068,1071],{"_43":1065,"_88":636,"_1042":1049,"_1044":643},[],{"_43":1067,"_88":663,"_1042":1049,"_1044":670},[],{"_43":1069,"_88":886,"_1042":1049,"_1044":1070},[],"Create tables with create_all()",{"_43":1072,"_88":973,"_1042":1049,"_1044":980},[],{"_43":1074,"_88":1022,"_1042":1043,"_1044":1029},[],"Using MySQL with SQLAlchemy: Hands-on Examples","authors",[1078],{"_26":27,"_28":29,"_1079":-7},"x","categories",[1082],"tutorials","excerpt","Learn how to use Python SQLAlchemy with MySQL by working through an example of creating tables, inserting data, and querying data with both raw SQL and SQLAlchemy ORM.","createdAt","2023-03-07T14:00:00Z","meta",{"_1089":1090,"_1091":1084,"_1092":1093,"_1044":1075},"canonical","https://planetscale.com/blog/using-mysql-with-sql-alchemy-hands-on-examples","description","image","/assets/using-mysql-with-sql-alchemy-hands-on-examples-social-hluDc86u.png","pagination",{"_1096":1097,"_1098":8,"_1099":8,"_1100":1097,"_1101":1102},"page",1,"hasPrev","hasNext","totalPages","url","https://planetscale.com/blog/author/anthony","https://planetscale.com","actionData","errors"]

Posts by Anthony Herbert

Using MySQL with SQLAlchemy: Hands-on Examples