如何使用 doobie 对 Scala 中的 PostgreSQL 数据库执行字符串 SQL 语句列表? [英] How to execute list of string SQL statements against a PostgreSQL db in Scala using doobie?

问题描述

我正在将以下 10 行 Python 代码移植到 Scala:

I am porting the following 10 lines of Python code to Scala:

import psycopg2

def execute(user, password, database, host, port, *queries):
    connection = psycopg2.connect(user=user, password=password, host=host, port=port, database=database)
    cursor = connection.cursor()
    for sql in queries:
        print(sql)
        cursor.execute(sql)
    connection.commit()
    cursor.close()
    connection.close()

我有以下等效的 Scala 代码:

I have the following equivalent Scala code:

def execute(user: String, password: String, database: String, host: String, port: Int, queries: String*): Unit = {
  ???
}    

我想在针对数据库(假设它是 Postgres)的单个事务中执行(并打印)一堆 SQL 语句并完成.

I want to execute (and print) bunch of SQL statements in a single transaction against the database (assume it to be Postgres) and be done.

如何使用 doobie 做到这一点?

How do I do that using doobie?

注意:

1. 我无法将接口更改为我的 execute()(包括我无法添加类型或隐式参数).它必须接受字符串用户、密码等和一个 queries: String* 的可变参数，从而保持与 Python 相同的界面.

1. I cannot change the interface to my execute() (including I cannot add type or implicit params). It must take in String user, password etc. and a vararg of queries: String* and thus keep the interface same as the Python one.

还请提及所有需要的进口

Please also mention all imports needed

推荐答案

您可以使用 for-comprehension 在 doobie 中的一个事务中运行多个查询，例如:

You can run multiple queries in one transaction in doobie using for-comprehension, for example:

val query = for {
   _  <- sql"insert into person (name, age) values ($name, $age)".update.run
   id <- sql"select lastval()".query[Long].unique
} yield p

但是这个解决方案在你的情况下不起作用，因为你有一个动态的查询列表.幸运的是，我们可以使用traverse来自猫:

But this solution won't work in your case, because you've got a dynamic list of queries. Fortunately, we can use traverse from cats:

import cats.effect.ContextShift
import doobie._
import doobie.implicits._
import cats.effect._
import scala.concurrent.ExecutionContext
import cats.implicits._
import cats._
import cats.data._

def execute(user: String, password: String, database: String, host: String, port: Int, queries: String*): Unit = {

     //you can use other executor if you want
     //it would be better to pass context shift as implicit argument to method
    implicit val cs: ContextShift[IO] = IO.contextShift(ExecutionContext.global) 

    //let's create transactor  
    val xa = Transactor.fromDriverManager[IO](
      "org.postgresql.Driver",
       s"jdbc:postgresql://$host:$port/$database", //remember to change url or make it dynamic, if you run it agains another database
       user,
       password
    )

    val batch = queries
        .toList //we need to change String* to list, since String* doesn't have necessary typeclass for Aplicative
        .traverse(query => Update0(query, None).run) //we lift strings to Query0 and then run them, then we change List[ConnectionIO[Int]] to ConnectionIO[List[Int]]
        //above can be done in two steps using map and sequence

    batch  //now we've got single ConnectionIO which will run in one transaction
      .transact(xa) //let's make it IO[Int]
      .unsafeRunSync() //we need to block since your method returns Unit

  }

可能您的 IDE 会显示此代码无效，但它是正确的.IDE 无法处理 Scala 魔法.

Probably your IDE will show you this code is invalid, but it's correct. IDEs just can't handle Scala magic.

您也可以考虑使用 unsafeRunTimed 而不是 unsafeRunSync 来添加时间限制.

You might also consider using unsafeRunTimed instead of unsafeRunSync to add the time limit.

另外，记得添加 postgresql 驱动 jdbc 和 cats 到您的 build.sbt.Doobie 在幕后使用猫，但我认为可能需要显式依赖.

Also, remember to add postgresql driver for jdbc and cats to your build.sbt. Doobie uses cats under the hood, but I think explicit dependency might be necessary.

这篇关于如何使用 doobie 对 Scala 中的 PostgreSQL 数据库执行字符串 SQL 语句列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！