August 24, 2020
  • 7:26 am Cách chèn nhạc vào ảnh và video trên Facebook
  • 6:59 am Tóm Tắt The Walking Dead (Xác Sống) | Season 4
  • 6:15 am [Mất gốc Hóa – số 16] – HƯỚNG DẪN CÁCH GỌI TÊN OXIT AXIT – OXIT BAZƠ NHANH THUỘC
  • 5:49 am Cách cài Win từ ổ cứng: cả Win 7/8/8.1 và Win10 (làm mẫu với Win 10)
  • 5:12 am Thủ thuật excel: Cách khóa dữ liệu không cho chỉnh sửa trong excel

Learn how to connect to your database:

In the python world, there are several great tools that we can use when working with databases. One of those is SQLAlchemy that we will be using throughout this course. SQLAlchemy will allow us to generate SQL queries by writing Python code. You should still consider learning how to write queries in SQL as well.

SQLAlchemy has two main components. The part we will be focusing on is often referred to as “core” part of SQLAlchemy. It’s really focused around the relational model of the database. Additionally, there is the Object Relational Model or ORM part of SQLAlchemy that is really focused around data models and classes that you as a programmer create.

There are many different types of databases, and each database type has its own quirks and unique capabilities. You’ll commonly find SQLite, PostgreSQL, MySQL, Microsoft SQL Server, and Oracle when working with data. SQLAlchemy provides a way to operate across all of these database types in a consistent manner.

To connect to a database, we need a way to talk to it, and an engine provides that common interface. To create an engine, we import the create_engine function from sqlalchemy; we then use the create_engine function and supply it a connection string that provides the details needed to connect to a database. Finally once we have an engine, we are ready to make a connection using the connect method on the engine. It’s worth noting that SQLAlchemy won’t actually make the connection until we give it some work to execute. So to review, an engine is the common interface to the database, which requires a connection string to provide the details used to find and connect to the database.

Before we go any further, let’s talk a bit more about connection strings. In their simplest form, they tell us what kind of database we are talking to and how we should access it. In this example, you can see that we are using the sqlite database driver and the database file named census_nyc.sqlite which is in the current directory.

Now that we have an engine and a connection, we need to know what tables are in the database. We’ll start again by importing the create_engine function and creating an engine to our database. Finally, We can use the table_names method of the engine which returns a list of tables.

Once we know what table we want to work on, we need a way to access that table with python. To do that we are going to use a handy process called reflection, which reads the database and builds a Table object that we can use in our code. We already have created our engine, so we begin by importing the MetaData and Table objects needed for reflection. The MetaData object is a catalog that stores database information such as tables so we don’t have to keep looking them up. To reflect the table, we initialize a MetaData object. Next, we use the SQLAlchemy Table object and provide the table name we got earlier from the table_names method. We also supply our metadata instance, and then instruct it to autoload the table using the engine. Finally, we can use the function repr to view the details of our table that we stored as census. This allows us to see the names of the columns, such as ‘state’ and ‘sex’, along with their types, such as VARCHAR and INTEGER.

This process of reflection may seem a bit of an overhead, but it will make understanding your databases and extracting information from them far easier downstream. Now it’s your turn to practice writing connection strings, connecting to databases and reflecting tables. Then we’ll be back here writing our first SQL queries.


Xem thêm bài viết khác:




  1. DrSpooglemon Posted on July 11, 2020 at 3:58 pm

    Yes! SQLAlchemy ftw.

  2. San Luthra Posted on July 11, 2020 at 3:58 pm

    Do you have video on pooling with sqlalchemy and using pymsql adapter ?

  3. Haneul Kim Posted on July 11, 2020 at 3:58 pm

    Thanks for the video. I want to clarify, in order to connect to database I need to have sqlite file? can I connect to db in mySQL so I can add data?

  4. amiola _connect Posted on July 11, 2020 at 3:58 pm

    please the file?

  5. Shan Jafri Posted on July 11, 2020 at 3:58 pm

    Where do you get connection strings?

  6. Mehdi Mhedhbhi Posted on July 11, 2020 at 3:58 pm

    what do you think about the package PETL for python ETL ?

  7. dayanand hedraban Posted on July 11, 2020 at 3:58 pm

    Hai, I am new to python, can you explain the concept of Data Accessing Object in python

  8. Tejas Posted on July 11, 2020 at 3:58 pm

    Awesome! Where is the next part…?

  9. parvathy gopan Posted on July 11, 2020 at 3:58 pm

    How can I add data to this census table?

  10. itishree das Posted on July 11, 2020 at 3:58 pm

    thank you ,this vedio has saved my life

  11. Mantej Singh Dhanjal Posted on July 11, 2020 at 3:58 pm

    Thank you for making this.