Question

我需要使用 SQLAlchemy 在 MySQL 中创建 db, 如果 db 已经存在, 我可以连接到 db, 但是如果它不存在, 我想能够创建它。这些是我的表格 :

    #def __init__(self):
Base = declarative_base()

class utente(Base):
    __tablename__="utente"
    utente_id=Column(Integer,primary_key=True)
    nome_utente=Column(Unicode(20))
    ruolo=Column(String(10))
    MetaData.create_all()

    def __repr(self):
        return "utente: {0}, {1}, id: {2}".format(self.ruolo,self.nome_utente,self.utente_id)
    
    
class dbmmas(Base):
    
    __tablename__="dbmmas"
    db_id=Column(Integer,primary_key=True,autoincrement=True)
    nome_db=Column(String(10))
    censimento=Column(Integer)
    versione=Column(Integer)
    ins_data=Column(DateTime)
    mod_data=Column(DateTime)
    ins_utente=Column(Integer)
    mod_utente=Column(Integer)
    MetaData.create_all()

    def __repr(self):
        return "dbmmas: {0}, censimento {1}, versione {2}".format(self.nome_db,self.censimento,self.versione)    
    
class funzione(Base):
    __tablename__="funzione"
    funzione_id=Column(Integer,primary_key=True,autoincrement=True)
    categoria=Column(String(10))
    nome=Column(String(20))
    def __repr__(self):
        return "funzione:{0},categoria:{1},id:{2} ".format(self.nome,self.categoria,self.funzione_id)
    
class profilo(Base):
    __tablename__="rel_utente_funzione" 
    utente_id=Column(Integer,primary_key=True)
    funzione_id=Column(Integer,primary_key=True)
    amministratore=Column(Integer)
    MetaData.create_all()
    
    def __repr(self):
        l=lambda x: "amministratore" if x==1 else "generico"
        return "profilo per utente_id:{0}, tipo: {1}, funzione_id: {2}".format(self.utente_id,l(self.amministratore),self.funzione_id)    
    
class aree(Base):
    __tablename__="rel_utente_zona"
    UTB_id=Column(String(10), primary_key=True) # "in realta  si tratta della seatureSignature della feature sullo shapefile"
    utente_id=Column(Integer, primary_key=True)
    amministratore=Column(Integer)
    MetaData.create_all()
    def __repr(self):
        l=lambda x: "amministratore" if x==1 else "generico"
        return "zona: {0}, pe utente_id:{1}, {2}".format(self.UTB_id,self.utente_id,l(self.amministratore))
    
class rel_utente_dbmmas(Base):
    __tablename__="rel_utente_dbmmas"
    utente_id=Column(Integer,primary_key=True)
    db_id=Column(Integer,primary_key=True)
    amministratore=(Integer)
    MetaData.create_all()
    def __repr(self):
        l=lambda x: "amministratore" if x==1 else "generico"
        return "dbregistrato: {0} per l utente{1} {2}".format(self.db_id,self.utente_id,l(self.amministratore))

Answer 1

要创建 mysql 数据库, 您只需连接到服务器, 就可以创建数据库 :

import sqlalchemy
engine = sqlalchemy.create_engine( mysql://user:password@server ) # connect to server
engine.execute("CREATE DATABASE dbname") #create db
engine.execute("USE dbname") # select new db
# use the new db
# continue with your work...

当然,您的用户必须拥有创建数据库的权限。

Answer 2

您可以为此使用 < a href=> "http://sqlalchemy-utils.readthedocs.io/en/latest/index.html" rel= "noreferrer" >SQLAlchemy-Utils 。

pip 安装 sqlalchemy-utels

这样你就可以做一些事情比如

from sqlalchemy_utils import create_database, database_exists
url =  mysql://{0}:{1}@{2}:{3} .format(user, pass, host, port)
if not database_exists(url):
    create_database(url)

我找到答案

Answer 3

我不知道什么是卡通式的方法, 但这里有一个方法来检查数据库是否存在, 检查数据库是否与数据库列表对照, 如果数据库不存在, 并创建数据库。

from sqlalchemy import create_engine

# This engine just used to query for list of databases
mysql_engine = create_engine( mysql://{0}:{1}@{2}:{3} .format(user, pass, host, port))

# Query for existing databases
existing_databases = mysql_engine.execute("SHOW DATABASES;")
# Results are a list of single item tuples, so unpack each tuple
existing_databases = [d[0] for d in existing_databases]

# Create database if not exists
if database not in existing_databases:
    mysql_engine.execute("CREATE DATABASE {0}".format(database))
    print("Created database {0}".format(database))

# Go ahead and use this engine
db_engine = create_engine( mysql://{0}:{1}@{2}:{3}/{4} .format(user, pass, host, port, db))

在此选择一种方法,如果您不需要知道数据库是否已经创建。

from sqlalchemy import create_engine

# This engine just used to query for list of databases
mysql_engine = create_engine( mysql://{0}:{1}@{2}:{3} .format(user, pass, host, port))

# Query for existing databases
mysql_engine.execute("CREATE DATABASE IF NOT EXISTS {0} ".format(database))

# Go ahead and use this engine
db_engine = create_engine( mysql://{0}:{1}@{2}:{3}/{4} .format(user, pass, host, port, db))

Answer 4

CREATE DATABASE IF NOT EXISTS dbName;

Answer 5

建议使用 有 的 :


from sqlalchemy import create_engine

username =   
password =   
host =  localhost 
port = 3306
DB_NAME =  db_name 

engine = create_engine(f"mysql://{username}:{password}@{host}:{port}")

with engine.connect() as conn:
    # Do not substitute user-supplied database names here.
    conn.execute(f"CREATE DATABASE IF NOT EXISTS {DB_NAME}")

Answer 6

基准测试中 < 强度> Mysqlclient 似乎比 < 强度 > PyMyMySQL 快10倍,见: < a href=" https://stackoverflow.com/a/46396881/11154841> MySQLdb、 Mysqlclient 和 MySQL connector/Python之间有什么区别?

然而,为什么不至少对 Python 使用一个已安装的 Python 软件包, 如果它不是每个查询时间的第二个时间呢? “ 坚固” PyMySQL

Python packages:

用 pip 安装, 充其量安装为“ 要求. txt ” :

PyMySQL
SQLAlchemy

again, 如果是关于查询的最佳速度, 请使用 Mysqlclient 软件包。然后您需要安装一个附加的 Linux 软件包, 包括 sudo apt- get 安装 libmysqlclient- dev 。

import statements

只需要一个:

import sqlalchemy

Connection string (= db_url)

从开始的连接字符串"https://docs.sqlalchemy.org/en/14/diaects/mysql.html" rel="不跟随 norefererr"\dialec/DBAPI/adriver}:

db_url = mysql+pymysql://

此处 pymysql 表示用过的 Python 软件包“ PyMySQL” 作为驱动程序。

Again, 如果是关于查询的最佳速度, 请使用 Mysqlclient 软件包。您此时需要 mysql+msqldb:// 。
对于远程连接,您需要在连接字符串中添加 : host user password database port (the port only if it is not the standard 3306) 您可以使用多种方法创建您的 db_ url 。不要直接在字符串中写入用户和密码以及最多其他变量值, 以避免可能的攻击 :sqlalchemy.engine.URL.create(), or with .url.URL, see an example at Connecting from Cloud Functions to Cloud SQL or an example which automatically adds ? suffixes, for example ?driver=SQL+Server, at the end of the string at Building a connection URL for mssql+pyodbc with sqlalchemy.engine.url.URL f"""...{my_var}...""" """...{my_var}...""".format(my_var=xyz_var) ... 没有 SQLAlchemy url 助手的示例 : db_url = "{dialect}+{driver}://{user}:{password}@{host}:{port}/{database}".format( 或: db_url = "{dialect}+{driver}://{user}:{password}@{host}/{database}?host={host}?port={port}".format( dialect = mysql , driver = pymysql , username=db_user, password=db_pass, database=db_name, host=db_host, port=db_port ) Other engine configurations 关于其他连接驱动器、方言和方法,见SQLAlchemy 1.4文档-引擎配置 Create the db if not exists 见engine = sqlalchemy.create_engine(db_url) if not sqlalchemy.database_exists(engine.url): create_database(engine.url) with engine.connect() as conn: conn.execute("commit") conn.execute("create database test")

Python packages:

`import` statements

Connection string (= db_url)

Other engine configurations

Create the db if not exists

友情链接

Python packages:

import statements

Connection string (= db_url)

Other engine configurations

Create the db if not exists

友情链接

`import` statements